[prev in list] [next in list] [prev in thread] [next in thread]
List: postgis-users
Subject: Re: [postgis-users] how to keep geometry_columns in sync wit
From: "Paragon Corporation" <lr () pcorp ! us>
Date: 2011-05-20 7:21:24
Message-ID: E764B10F4DF94BC9911654223BF47B29 () J
[Download RAW message or body]
[Attachment #2 (multipart/alternative)]
Slight correction in thinking about this more, I suppose if people built
their views something like:
SELECT ST_Transform(geom,4326)::geometry(POINT,4326) As geom
FROM ...
I guess even complex geometry views can be represented correctly in the
system catalogs.
I still would want to go with the new name though and treat geometry_columns
as a deprecated thing of the past, but I am in a minority here on opinion.
Thanks,
Regina
_____
From: Paragon Corporation [mailto:lr@pcorp.us]
Sent: Friday, May 20, 2011 3:15 AM
To: 'PostGIS Users Discussion'
Cc: 'PostGIS Development Discussion'
Subject: RE: [postgis-users] how to keep geometry_columns in sync wit
tablesand views (and new PostGIS 2.0 plans)
One other note -- the SQL/MM standard calls for an st_geometry_columns view
which is a true view that reads the system catalogs and should only read the
system catalogs I think.
geometry_columns is a left over from OGC standard. So my other point is if
we are going to do things the new way, why don't we call it the new name
"st_geometry_columns"
So that is why I was proposing a hybrid -- geometry_columns -- so new
PostGIS can work with older tools
and st_geometry_columns -- which will be strictly pure new way.
Though I suppose that may be more confusing than it's worth and there is the
case of views
such as the form
SELECT .. ST_Transform(..,) As geom
:
Which for performance reasons should not be inspected and can not be
appropriately represented in system catalogs using typmod approach.
Thanks,
Regina
_____
From: postgis-users-bounces@postgis.refractions.net
[mailto:postgis-users-bounces@postgis.refractions.net] On Behalf Of Ben
Madin
Sent: Friday, May 20, 2011 1:40 AM
To: PostGIS Users Discussion
Cc: 'PostGIS Development Discussion'
Subject: Re: [postgis-users] how to keep geometry_columns in sync wit
tablesand 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 <http://www.postgis.us/>
_____
From: postgis-users-bounces@postgis.refractions.net
[mailto:postgis-users-bounces@postgis.refractions.net] On Behalf Of Ben
Madin
Sent: Wednesday, May 18, 2011 9:27 PM
To: pcreso@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@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@remoteinformation.com.au> wrote:
From: Ben Madin <lists@remoteinformation.com.au>
Subject: Re: [postgis-users] how to keep geometry_columns in sync with
tables and views
To: "PostGIS Users Discussion" <postgis-users@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@hotmail.com
<x-msg://119/mc/compose?to=emacgillavry@hotmail.com> > wrote:
From: Edward Mac Gillavry <emacgillavry@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@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@yahoo.com <x-msg://119/mc/compose?to=gves2000@yahoo.com>
To: postgis-users@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@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@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
postgis-users@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@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@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
[Attachment #5 (text/html)]
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META content="text/html; charset=us-ascii" http-equiv=Content-Type>
<META name=GENERATOR content="MSHTML 8.00.7601.17573"></HEAD>
<BODY
style="WORD-WRAP: break-word; -webkit-nbsp-mode: space; -webkit-line-break: \
after-white-space"> <DIV dir=ltr align=left><FONT color=#0000ff size=2 \
face=Arial><SPAN class=326141607-20052011>Slight correction in thinking about this \
more, I suppose if people built their views something like:</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=326141607-20052011></SPAN></FONT><FONT color=#0000ff size=2
face=Arial><SPAN class=326141607-20052011></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=326141607-20052011>SELECT ST_Transform(geom,4326)::geometry(POINT,4326) As
geom</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=326141607-20052011>FROM ...</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=326141607-20052011></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=326141607-20052011> I guess even complex geometry views can be
represented correctly in the system catalogs.</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=326141607-20052011></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=326141607-20052011>I still would want to go with the new name though and
treat geometry_columns as a deprecated thing of the past, </SPAN></FONT><FONT
color=#0000ff size=2 face=Arial><SPAN class=326141607-20052011>but I am in a
minority here on opinion.</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=326141607-20052011></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=326141607-20052011>Thanks,</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=326141607-20052011>Regina</SPAN></FONT></DIV><BR>
<DIV dir=ltr lang=en-us class=OutlookMessageHeader align=left>
<HR tabIndex=-1>
<FONT size=2 face=Tahoma><B>From:</B> Paragon Corporation [mailto:lr@pcorp.us]
<BR><B>Sent:</B> Friday, May 20, 2011 3:15 AM<BR><B>To:</B> 'PostGIS Users
Discussion'<BR><B>Cc:</B> 'PostGIS Development Discussion'<BR><B>Subject:</B>
RE: [postgis-users] how to keep geometry_columns in sync wit tablesand views
(and new PostGIS 2.0 plans)<BR></FONT><BR></DIV>
<DIV></DIV>
<DIV dir=ltr align=left><SPAN class=018160707-20052011><FONT color=#0000ff
size=2 face=Arial>One other note -- the SQL/MM standard calls for an
st_geometry_columns view which is a true view that reads the system catalogs and
should only read the system catalogs I think.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=018160707-20052011><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=018160707-20052011><FONT color=#0000ff
size=2 face=Arial>geometry_columns is a left over from OGC standard. So my
other point is if we are going to do things the new way, why don't we call it
the new name "st_geometry_columns"</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=018160707-20052011><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=018160707-20052011><FONT color=#0000ff
size=2 face=Arial>So that is why I was proposing a hybrid --
geometry_columns -- so new PostGIS can work with older
tools</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=018160707-20052011><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=018160707-20052011><FONT color=#0000ff
size=2 face=Arial>and st_geometry_columns -- which will be strictly pure new
way.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=018160707-20052011><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=018160707-20052011><FONT color=#0000ff
size=2 face=Arial>Though I suppose that may be more confusing than it's worth
and there is the case of views</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=018160707-20052011><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=018160707-20052011><FONT color=#0000ff
size=2 face=Arial>such as the form</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=018160707-20052011><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=018160707-20052011><FONT color=#0000ff
size=2 face=Arial>SELECT .. ST_Transform(..,) As geom</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=018160707-20052011><FONT color=#0000ff
size=2 face=Arial>:</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=018160707-20052011><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=018160707-20052011><FONT color=#0000ff
size=2 face=Arial>Which for performance reasons should not be inspected and can
not be appropriately represented in system catalogs using typmod
approach.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=018160707-20052011><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=018160707-20052011><FONT color=#0000ff
size=2 face=Arial>Thanks,</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=018160707-20052011><FONT color=#0000ff
size=2 face=Arial>Regina</FONT></SPAN></DIV><BR>
<DIV dir=ltr lang=en-us class=OutlookMessageHeader align=left>
<HR tabIndex=-1>
<FONT size=2 face=Tahoma><B>From:</B>
postgis-users-bounces@postgis.refractions.net
[mailto:postgis-users-bounces@postgis.refractions.net] <B>On Behalf Of </B>Ben
Madin<BR><B>Sent:</B> Friday, May 20, 2011 1:40 AM<BR><B>To:</B> PostGIS Users
Discussion<BR><B>Cc:</B> 'PostGIS Development Discussion'<BR><B>Subject:</B> Re:
[postgis-users] how to keep geometry_columns in sync wit tablesand views (and
new PostGIS 2.0 plans)<BR></FONT><BR></DIV>
<DIV></DIV>Thanks for the heads-up Regina,
<DIV><BR></DIV>
<DIV>I'm not really over most of the issues with type etc, but from my
perspective :</DIV>
<DIV><BR></DIV>
<DIV>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!</DIV>
<DIV><BR></DIV>
<DIV>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.</DIV>
<DIV><BR></DIV>
<DIV>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!</DIV>
<DIV><BR></DIV>
<DIV>cheers</DIV>
<DIV><BR></DIV>
<DIV>Ben</DIV>
<DIV><BR></DIV>
<DIV><BR></DIV>
<DIV><BR>
<DIV>
<DIV>On 20/05/2011, at 1:26 AM, Paragon Corporation wrote:</DIV><BR
class=Apple-interchange-newline>
<BLOCKQUOTE type="cite">
<DIV
style="WORD-WRAP: break-word; -webkit-nbsp-mode: space; -webkit-line-break: \
after-white-space"> <DIV dir=ltr align=left><SPAN class=400171317-19052011><FONT \
color=#0000ff size=2 face=Arial>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. </FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=400171317-19052011><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=400171317-19052011><FONT color=#0000ff
size=2 face=Arial>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</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=400171317-19052011><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=400171317-19052011><FONT color=#0000ff
size=2 face=Arial>There are a couple of issues with this:</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=400171317-19052011><FONT color=#0000ff
size=2 face=Arial>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.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=400171317-19052011><FONT color=#0000ff
size=2 face=Arial>(I say no).</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=400171317-19052011><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=400171317-19052011><FONT color=#0000ff
size=2 face=Arial>2) Exotic uses of geometry_columns that inspecting the
system catalogs will not handle (e.g. views and other reasons for manual
registration)</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=400171317-19052011><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=400171317-19052011><FONT color=#0000ff
size=2 face=Arial>Anyrate the thread is outlined here: </FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=400171317-19052011><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV><FONT color=#0000ff size=2 face=Arial><A
href="http://trac.osgeo.org/postgis/ticket/944">http://trac.osgeo.org/postgis/ticket/944</A></FONT></DIV>
<DIV><FONT color=#0000ff size=2 face=Arial></FONT> </DIV>
<DIV><SPAN class=400171317-19052011><FONT color=#0000ff size=2 face=Arial>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.</FONT></SPAN></DIV>
<DIV><SPAN class=400171317-19052011><FONT color=#0000ff size=2
face=Arial></FONT></SPAN> </DIV>
<DIV><SPAN class=400171317-19052011><FONT color=#0000ff size=2 face=Arial>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).</FONT></SPAN></DIV>
<DIV><SPAN class=400171317-19052011><FONT color=#0000ff size=2
face=Arial></FONT></SPAN> </DIV>
<DIV><SPAN class=400171317-19052011><FONT color=#0000ff size=2
face=Arial>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.</FONT></SPAN></DIV>
<DIV><SPAN class=400171317-19052011><FONT color=#0000ff size=2
face=Arial></FONT></SPAN> </DIV>
<DIV><SPAN class=400171317-19052011><FONT color=#0000ff size=2 face=Arial>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.</FONT></SPAN></DIV>
<DIV><SPAN class=400171317-19052011><FONT color=#0000ff size=2
face=Arial></FONT></SPAN> </DIV>
<DIV><SPAN class=400171317-19052011><FONT color=#0000ff size=2
face=Arial>Thanks,</FONT></SPAN></DIV>
<DIV><SPAN class=400171317-19052011><FONT color=#0000ff size=2
face=Arial>Regina</FONT></SPAN></DIV>
<DIV><SPAN class=400171317-19052011><FONT color=#0000ff size=2 face=Arial><A
href="http://www.postgis.us/">http://www.postgis.us</A></FONT></SPAN></DIV>
<DIV><SPAN class=400171317-19052011><FONT color=#0000ff size=2
face=Arial></FONT></SPAN> </DIV>
<DIV><BR></DIV>
<DIV dir=ltr lang=en-us class=OutlookMessageHeader align=left>
<HR tabIndex=-1>
<FONT size=2 face=Tahoma><B>From:</B> <A
href="mailto:postgis-users-bounces@postgis.refractions.net">postgis-users-bounces@postgis.refractions.net</A> \
[mailto:postgis-users-bounces@postgis.refractions.net] <B>On Behalf Of </B>Ben
Madin<BR><B>Sent:</B> Wednesday, May 18, 2011 9:27 PM<BR><B>To:</B> <A
href="mailto:pcreso@pcreso.com">pcreso@pcreso.com</A>; PostGIS Users
Discussion<BR><B>Subject:</B> Re: [postgis-users] how to keep geometry_columns
in sync with tables and views<BR></FONT><BR></DIV>
<DIV></DIV>
<DIV>G'day Brent,</DIV>
<DIV><BR></DIV>
<DIV>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?</DIV>
<DIV><BR></DIV>
<DIV>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?</DIV>
<DIV><BR></DIV>
<DIV>cheers</DIV>
<DIV><BR></DIV>
<DIV>Ben</DIV>
<DIV><BR></DIV>
<DIV><BR>
<DIV><BR></DIV>
<DIV><BR></DIV>
<DIV><BR>
<DIV>
<DIV>On 19/05/2011, at 9:02 AM, <A
href="mailto:pcreso@pcreso.com">pcreso@pcreso.com</A> wrote:</DIV><BR
class=Apple-interchange-newline>
<BLOCKQUOTE type="cite">
<TABLE border=0 cellSpacing=0 cellPadding=0>
<TBODY>
<TR>
<TD vAlign=top>I foubd this an unfortunately ambiguous name.<BR><BR>it
doesn't populate geometry columns so much as update the
geometry_columns table.<BR><BR>But irrespective of the name, it is
nice to have :-)<BR><BR><BR>Cheers<BR><BR> Brent Wood<BR><BR>---
On <B>Thu, 5/19/11, Ben Madin <I><<A
href="mailto:lists@remoteinformation.com.au">lists@remoteinformation.com.au</A>></I></B> \
wrote:<BR>
<BLOCKQUOTE
style="BORDER-LEFT: rgb(16,16,255) 2px solid; PADDING-LEFT: 5px; \
MARGIN-LEFT: 5px"><BR>From: Ben Madin <<A
href="mailto:lists@remoteinformation.com.au">lists@remoteinformation.com.au</A>><BR>Subject: \
Re: [postgis-users] how to keep geometry_columns in sync with tables
and views<BR>To: "PostGIS Users Discussion" <<A
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</A>><BR>Date: \
Thursday, May 19, 2011, 12:50 PM<BR><BR>
<DIV id=yiv25696554>Ge,
<DIV><BR></DIV>
<DIV>Try </DIV>
<DIV><BR></DIV>
<DIV> SELECT Populate_Geometry_Columns();
<DIV><BR></DIV>
<DIV><A
href="http://postgis.refractions.net/docs/Populate_Geometry_Columns.html" \
rel=nofollow
target=_blank>http://postgis.refractions.net/docs/Populate_Geometry_Columns.html</A></DIV>
<DIV><BR></DIV>
<DIV>which promises to truncate the geometry columns table first,
then rebuild it.</DIV>
<DIV><BR></DIV>
<DIV>cheers</DIV>
<DIV><BR></DIV>
<DIV>Ben</DIV>
<DIV><BR></DIV>
<DIV><BR></DIV>
<DIV><BR>
<DIV>
<DIV>On 18/05/2011, at 8:05 PM, G. van Es wrote:</DIV><BR
class=yiv25696554Apple-interchange-newline>
<BLOCKQUOTE type="cite">
<TABLE border=0 cellSpacing=0 cellPadding=0>
<TBODY>
<TR>
<TD
style="FONT-FAMILY: inherit; font-size-adjust: inherit; \
font-stretch: inherit"
vAlign=top>Hi Edward,<BR><BR>This will not work because this
function doesn't do anything with views. Also stale records
aren't removed.<BR><BR>Ge<BR><BR>--- On <B>Wed, 5/18/11,
Edward Mac Gillavry <I><<A
href="x-msg://119/mc/compose?to=emacgillavry@hotmail.com"
rel=nofollow target=_blank
ymailto="mailto:emacgillavry@hotmail.com">emacgillavry@hotmail.com</A>></I></B> \
wrote:<BR>
<BLOCKQUOTE
style="BORDER-LEFT: rgb(16,16,255) 2px solid; PADDING-LEFT: 5px; \
MARGIN-LEFT: 5px"><BR>From: Edward Mac Gillavry <<A
href="x-msg://119/mc/compose?to=emacgillavry@hotmail.com"
rel=nofollow target=_blank
\
ymailto="mailto:emacgillavry@hotmail.com">emacgillavry@hotmail.com</A>><BR>Subject: \
Re: [postgis-users] how to keep geometry_columns in sync
with tables and views<BR>To: <A
\
href="x-msg://119/mc/compose?to=postgis-users@postgis.refractions.net" rel=nofollow \
target=_blank
\
ymailto="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</A><BR>Date: \
Wednesday, May 18, 2011, 4:57 AM<BR><BR>
<DIV id=yiv25696554>
<STYLE>#yiv25696554 .yiv25696554hmmessage P {
PADDING-BOTTOM: 0px; MARGIN: 0px; PADDING-LEFT: 0px; PADDING-RIGHT: 0px; \
PADDING-TOP: 0px }
#yiv25696554 .yiv25696554hmmessage {
FONT-FAMILY: Tahoma; FONT-SIZE: 10pt
}
</STYLE>
Hi Ge,<BR><BR>You may want to check Probe_Geometry_Columns
(<A
\
href="http://postgis.refractions.net/docs/Probe_Geometry_Columns.html" rel=nofollow \
\
target=_blank>http://postgis.refractions.net/docs/Probe_Geometry_Columns.html</A>).<BR><BR>Kind \
regards,<BR><BR>Edward<BR><BR><BR><BR>
<HR id=yiv25696554stopSpelling>
Date: Wed, 18 May 2011 04:38:51 -0700<BR>From: <A
href="x-msg://119/mc/compose?to=gves2000@yahoo.com"
rel=nofollow target=_blank
\
ymailto="mailto:gves2000@yahoo.com">gves2000@yahoo.com</A><BR>To: <A
\
href="x-msg://119/mc/compose?to=postgis-users@postgis.refractions.net" rel=nofollow \
target=_blank
\
ymailto="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</A><BR>Subject: \
[postgis-users] how to keep geometry_columns in sync with
tables and views<BR><BR>
<TABLE border=0 cellSpacing=0 cellPadding=0>
<TBODY>
<TR>
<TD
style="FONT-FAMILY: inherit; font-size-adjust: inherit; \
font-stretch: inherit"
vAlign=top>Hi All,<BR><BR>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.<BR><BR>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.<BR><BR>Before inventing the wheel
again, does anyone know if this procedure already
exist or knows perhaps another/better way to achieve
this? <BR><BR>Thanks in
\
advance,<BR><BR>Ge<BR><BR><BR><BR><BR></TD></TR></TBODY></TABLE><BR>_______________________________________________ \
postgis-users mailing list <A
\
href="x-msg://119/mc/compose?to=postgis-users@postgis.refractions.net" rel=nofollow \
target=_blank
\
ymailto="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</A> \
<A
\
href="http://postgis.refractions.net/mailman/listinfo/postgis-users" rel=nofollow
\
target=_blank>http://postgis.refractions.net/mailman/listinfo/postgis-users</A> \
</DIV><BR>-----Inline Attachment Follows-----<BR><BR>
<DIV
\
class=yiv25696554plainMail>_______________________________________________<BR>postgis-users \
mailing list<BR><A
rel=nofollow>postgis-users@postgis.refractions.net</A><BR><A
\
href="http://postgis.refractions.net/mailman/listinfo/postgis-users" rel=nofollow
\
target=_blank>http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR></D \
IV></BLOCKQUOTE></TD></TR></TBODY></TABLE>_______________________________________________<BR>postgis-users \
mailing list<BR><A
href="x-msg://119/mc/compose?to=postgis-users@postgis.refractions.net"
rel=nofollow target=_blank
ymailto="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</A><BR><A \
href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis. \
refractions.net/mailman/listinfo/postgis-users</A><BR></BLOCKQUOTE></DIV><BR></DIV></DIV></DIV><BR>-----Inline \
Attachment Follows-----<BR><BR>
<DIV
class=plainMail>_______________________________________________<BR>postgis-users \
mailing list<BR><A
href="x-msg://119/mc/compose?to=postgis-users@postgis.refractions.net"
ymailto="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</A><BR><A \
href="http://postgis.refractions.net/mailman/listinfo/postgis-users"
target=_blank>http://postgis.refractions.net/mailman/listinfo/postgis-user \
s</A><BR></DIV></BLOCKQUOTE></TD></TR></TBODY></TABLE>_______________________________________________<BR>postgis-users \
mailing list<BR><A
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</A><BR><A \
href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis. \
refractions.net/mailman/listinfo/postgis-users</A><BR></BLOCKQUOTE></DIV><BR></DIV></DIV></DIV>_______________________________________________<BR>postgis-users \
mailing list<BR><A
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refraction \
s.net</A><BR>http://postgis.refractions.net/mailman/listinfo/postgis-users<BR></BLOCKQUOTE></DIV><BR></DIV></BODY></HTML>
_______________________________________________
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic