[postgis-users] how to keep geometry_columns in sync wit tables and views (and new PostGIS 2.0 plans)

Thu May 19 22:40:13 PDT 2011

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-bounces at postgis.refractions.net [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Ben Madin
> Sent: Wednesday, May 18, 2011 9:27 PM
> To: pcreso at 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, pcreso at 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 <lists at remoteinformation.com.au> wrote:
>> 
>> From: Ben Madin <lists at remoteinformation.com.au>
>> Subject: Re: [postgis-users] how to keep geometry_columns in sync with tables and views
>> To: "PostGIS Users Discussion" <postgis-users at postgis.refractions.net>
>> 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 <emacgillavry at hotmail.com> wrote:
>>> 
>>> From: Edward Mac Gillavry <emacgillavry at hotmail.com>
>>> Subject: Re: [postgis-users] how to keep geometry_columns in sync with tables and views
>>> To: postgis-users at 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: gves2000 at yahoo.com
>>> To: postgis-users at 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 at postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
>>> 
>>> -----Inline Attachment Follows-----
>>> 
>>> _______________________________________________
>>> postgis-users mailing list
>>> postgis-users at postgis.refractions.net
>>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>> _______________________________________________
>>> postgis-users mailing list
>>> postgis-users at postgis.refractions.net
>>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>> 
>> 
>> -----Inline Attachment Follows-----
>> 
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at postgis.refractions.net
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at postgis.refractions.net
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20110520/3206b1eb/attachment.html>