<!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><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></DIV></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-users</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.refractions.net</A><BR>http://postgis.refractions.net/mailman/listinfo/postgis-users<BR></BLOCKQUOTE></DIV><BR></DIV></BODY></HTML>