Re: [postgis-users] how to keep geometry_columns in sync wit tables and views (and new PostGIS 2.0 plans)
Thanks for the heads-up Regina, I'm not really over most of the issues with type etc, but from my perspective : I'm not a big fan of doing things because of specifications written in the past - I've never really understood the geometry_columns table as anything except a metadata table - and while I'm sure that there are advantages in terms of clients connection management, as someone who rarely has more than 50 -80 tables (each with only 1 or 2 geometry columns) and only Gigabytes of data, not Terabytes, since the introduction of functions like populate_geometry_columns(), I've not worried too much about it. It was a pain prior to that! My concerns (from my use case!) would relate to the risk that clients might struggle to find a table that doesn't exist, or isn't the one that is updated. I suspect that applications under current development would / could be changed, and those that are older may not support the update to 2.0 anyway. Probably better not to go the hybrid route - it might get worse than ugly. If you are going to make a change, I agree that a major version is the time to do it. We would probably selectively not migrate certain applications rather than going down the line of upgrading and rewriting code - I don't suppose that is a surprise to many people! cheers Ben On 20/05/2011, at 1:26 AM, Paragon Corporation wrote: > Populate_Geometry_Columns is a function introduced in PostGIS 1.4. So yes you > are right the probe_geometry_columns is a lighter weight that doesn't look at > views and just looks at the constraints of tables. > > Speaking of this. In PostGIS 2.0, the plan is to use typmod support for > geometry (like what we currently have for geography) as well and make > geometry_columns a view instead of a table as it is now > > There are a couple of issues with this: > 1) Existing data does not use typmod so there is a portability question of if > people want to use the new geometry_columns should they be forced to convert > their data to typmod. > (I say no). > > 2) Exotic uses of geometry_columns that inspecting the system catalogs will > not handle (e.g. views and other reasons for manual registration) > > Anyrate the thread is outlined here: > > http://trac.osgeo.org/postgis/ticket/944 > > I think the typmod is a done deal -- we are all in agreement we want this. > What is not a done deal is how best to formulate geometry_columns view. > > I proposed a hybrid -- where part of the geometry_columns view reads from the > system catalog and the other part reads from a static table (basically old > geometry_columns table would be renamed and populate and so forth would be > changed to add to this table). > > Anyway I admit the hybrid is less than pretty, but the alternatives look even > more ugly to me from a migration standpoint and supporting more exotic uses. > > We'd be interested in hearing how people feel about these approaches and any > other ideas as to how we can fuse the old with the new. > > Thanks, > Regina > http://www.postgis.us > > > From: postgis-users-boun...@postgis.refractions.net > [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Ben Madin > Sent: Wednesday, May 18, 2011 9:27 PM > To: pcr...@pcreso.com; PostGIS Users Discussion > Subject: Re: [postgis-users] how to keep geometry_columns in sync with tables > and views > > G'day Brent, > > I'm forever creating tables as subsets of existing tables so it is a truly > useful function, however, I've suffered the same concerns - perhaps it is > worth pursuing the name being changed? > > I've also never really understood the distinction between the populate_ and > the probe_ functions? the probe_ one appears to be a 'lite' version, but it > may have some other purpose that I don't understand? > > cheers > > Ben > > > > > > On 19/05/2011, at 9:02 AM, pcr...@pcreso.com wrote: > >> I foubd this an unfortunately ambiguous name. >> >> it doesn't populate geometry columns so much as update the geometry_columns >> table. >> >> But irrespective of the name, it is nice to have :-) >> >> >> Cheers >> >> Brent Wood >> >> --- On Thu, 5/19/11, Ben Madin wrote: >> >> From: Ben Madin >> Subject: Re: [postgis-users] how to keep geometry_columns in sync with >> tables and views >> To: "PostGIS Users Discussion" >> Date: Thursday, May 19, 2011, 12:50 PM >> >> Ge, >> >> Try >> >> SELECT Populate_Geometry_Columns(); >> >> http://postgis.refractions.net/docs/Populate_Geometry_Columns.html >> >> which promises to truncate the geometry columns table first, then rebuild it. >> >> cheers >> >> Ben >> >> >> >> On 18/05/2011, at 8:05 PM, G. van Es wrote: >> >>> Hi Edward, >>> >>> This will not work because this function doesn't do anything with views. >>> Also stale records aren't removed. >>> >>> Ge >>> >>> --- On Wed, 5/18/11, Edward Mac Gillavry wrote: >>> >>> From: Edward Mac Gillavry >>> Subject: Re: [postgis-user
Re: [postgis-users] how to keep geometry_columns in sync wit tables and views (and new PostGIS 2.0 plans)
Populate_Geometry_Columns is a function introduced in PostGIS 1.4. So yes you are right the probe_geometry_columns is a lighter weight that doesn't look at views and just looks at the constraints of tables. Speaking of this. In PostGIS 2.0, the plan is to use typmod support for geometry (like what we currently have for geography) as well and make geometry_columns a view instead of a table as it is now There are a couple of issues with this: 1) Existing data does not use typmod so there is a portability question of if people want to use the new geometry_columns should they be forced to convert their data to typmod. (I say no). 2) Exotic uses of geometry_columns that inspecting the system catalogs will not handle (e.g. views and other reasons for manual registration) Anyrate the thread is outlined here: http://trac.osgeo.org/postgis/ticket/944 I think the typmod is a done deal -- we are all in agreement we want this. What is not a done deal is how best to formulate geometry_columns view. I proposed a hybrid -- where part of the geometry_columns view reads from the system catalog and the other part reads from a static table (basically old geometry_columns table would be renamed and populate and so forth would be changed to add to this table). Anyway I admit the hybrid is less than pretty, but the alternatives look even more ugly to me from a migration standpoint and supporting more exotic uses. We'd be interested in hearing how people feel about these approaches and any other ideas as to how we can fuse the old with the new. Thanks, Regina http://www.postgis.us _ From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Ben Madin Sent: Wednesday, May 18, 2011 9:27 PM To: pcr...@pcreso.com; PostGIS Users Discussion Subject: Re: [postgis-users] how to keep geometry_columns in sync with tables and views G'day Brent, I'm forever creating tables as subsets of existing tables so it is a truly useful function, however, I've suffered the same concerns - perhaps it is worth pursuing the name being changed? I've also never really understood the distinction between the populate_ and the probe_ functions? the probe_ one appears to be a 'lite' version, but it may have some other purpose that I don't understand? cheers Ben On 19/05/2011, at 9:02 AM, pcr...@pcreso.com wrote: I foubd this an unfortunately ambiguous name. it doesn't populate geometry columns so much as update the geometry_columns table. But irrespective of the name, it is nice to have :-) Cheers Brent Wood --- On Thu, 5/19/11, Ben Madin wrote: From: Ben Madin Subject: Re: [postgis-users] how to keep geometry_columns in sync with tables and views To: "PostGIS Users Discussion" Date: Thursday, May 19, 2011, 12:50 PM Ge, Try SELECT Populate_Geometry_Columns(); http://postgis.refractions.net/docs/Populate_Geometry_Columns.html which promises to truncate the geometry columns table first, then rebuild it. cheers Ben On 18/05/2011, at 8:05 PM, G. van Es wrote: Hi Edward, This will not work because this function doesn't do anything with views. Also stale records aren't removed. Ge --- On Wed, 5/18/11, Edward Mac Gillavry > wrote: From: Edward Mac Gillavry > Subject: Re: [postgis-users] how to keep geometry_columns in sync with tables and views To: postgis-users@postgis.refractions.net Date: Wednesday, May 18, 2011, 4:57 AM Hi Ge, You may want to check Probe_Geometry_Columns (http://postgis.refractions.net/docs/Probe_Geometry_Columns.html). Kind regards, Edward _ Date: Wed, 18 May 2011 04:38:51 -0700 From: gves2...@yahoo.com To: postgis-users@postgis.refractions.net Subject: [postgis-users] how to keep geometry_columns in sync with tables and views Hi All, We have a lot of tables and views updated, or better said, replaced on a daily basis. We have seen that under certain conditions (which are unclear) entries of the geometry_columns table are removed. So a mismatch occurs so now and then resulting in showing either no data or being very slow when an application has to do a table scan to obtain the geometry type. What I like to have is a procedure which checks all tables and views against the geometry_columns table and makes if necessary the right corrections. Before inventing the wheel again, does anyone know if this procedure already exist or knows perhaps another/better way to achieve this? Thanks in advance, Ge ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users -Inline Attachment Follows- ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing l
