[postgis-users] create tables smarter

Paul Ramsey pramsey at cleverelephant.ca
Wed Jun 4 13:32:20 PDT 2008

Trust me, Chris, the maintenance of GEOMETRY_COLUMNS has been a
longstanding bug'a'bear and in retrospect we probably would have been
better off without it.  In order to get an automagic GEOMETRY_COLUMNS
though, we either required (a) triggers on system tables or (b)
parameterized user-defined types.  And now it looks like we'll have
(b) in 8.4, so we can start to create our magic GEOMETRY_COLUMNS


On Wed, Jun 4, 2008 at 1:18 PM, Chris Hermansen
<chris.hermansen at timberline.ca> wrote:
> Hi Frank, folks;
> This is the kind of thing that would be really great to see:
>    * a way of creating geometry that relied on SQL and not on calling
>      stored procedures
>    * a way of deleting same
>    * a system table that held ancilliary information
> It bugs me that I can DROP a table and still have a row referring to
> that table in geometry_columns.
> I also find it kind of perverse that some desktop viewers require a
> specific structure in a table containing geometry in order to view it.
> Not to sound negative or anything!  Heaven forfend!  It's just that
> PostGIS is so much better than having to use a programmatic access like
> SDE when I want to use my geometry, that I find it hard to accept that
> it hasn't gone that last few centimetres.
> Frank Warmerdam wrote:
>> Lee Hachadoorian wrote:
>>> Andreas,
>>> AddGeometryColumn does two things: it adds a column of type geometry,
>>> and it adds a row to table geometry_columns.  If I understand what it
>>> is you want to do, you can do the SELECT and then add the row to
>>> geometry_columns with an INSERT statement:
>> Folks,
>> I did a presentation at PGCon and the issue of AddGeometryColumn() being
>> necessary to populate the geometry_columns table came up.  Some of the
>> postgres techies suggested there has been work so that extension defined
>> types could actually take extra arguments when used in the CREATE TABLE
>> statement and that this might let us avoid the need to use
>> AddGeometryColumn().
>> ie.
>>   (id   INT,
>>    name VARCHAR(255),
>>    geom GEOMETRY(<dimension>,<srid>,<type>) )
>> Then the callback for the GEOMETRY type would take care of extending the
>> geometry_columns table, presumably picking up the schema, table and
>> column
>> name from the context.
>> In an ideal world, the dimension, srid and type would even show up when
>> you describe the table, and would be automatically propagated to new
>> tables
>> created from a select.
>> I don't know how hard this is, or if I overestimated how these custom
>> type
>> definition parsers work but I think it would be an appealing direction of
>> development for postgis.
>> Forgive me if I'm stating something already well understand in the
>> postgis
>> community.  I'm a bit of a diletante in this world. :-)
>> Best regards,
> --
> Regards,
> Chris Hermansen         mailto:chris.hermansen at timberline.ca
> tel+1.604.714.2878 · fax+1.604.733.0631 · mob+1.778.232.0644
> Timberline Natural Resource Group · http://www.timberline.ca
> 401 · 958 West 8th Avenue  · Vancouver BC · Canada · V5Z 1E5
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users

More information about the postgis-users mailing list