[postgis-users] create tables smarter

[Chris Hermansen]

Paul says "the maintenance of GEOMETRY_COLUMNS has been a longstanding
bug'a'bear"...

Why don't I find that surprising :-)

But anyway, Paul also says that triggers on system tables could have
been used.  And I guess that makes me wonder, is there anything to stop
a person from putting triggers on system tables in PostgreSQL?  Does
that cause a problem somewhere else down the line?

Paul Ramsey wrote:
> 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
> table.
>
> P
>
> 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.
>>>
>>> CREATE TABLE ROADS
>>>   (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
>>
>>     
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>   


-- 
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