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

Thu May 19 10:26:41 PDT 2011

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
<x-msg://119/mc/compose?to=emacgillavry@hotmail.com> > wrote:

From: Edward Mac Gillavry <emacgillavry at hotmail.com
<x-msg://119/mc/compose?to=emacgillavry@hotmail.com> >
Subject: Re: [postgis-users] how to keep geometry_columns in sync with
tables and views
To: postgis-users at postgis.refractions.net
<x-msg://119/mc/compose?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: gves2000 at yahoo.com <x-msg://119/mc/compose?to=gves2000@yahoo.com> 
To: postgis-users at postgis.refractions.net
<x-msg://119/mc/compose?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 at postgis.refractions.net
<x-msg://119/mc/compose?to=postgis-users@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
<x-msg://119/mc/compose?to=postgis-users@postgis.refractions.net> 
http://postgis.refractions.net/mailman/listinfo/postgis-users

-----Inline Attachment Follows-----

_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
<x-msg://119/mc/compose?to=postgis-users@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-devel/attachments/20110519/0f2c9f05/attachment.html>