[postgis-users] create tables smarter

[Paul Ramsey]

Triggers on system tables aren't allowed, try and see.  I like that
GEOMETRY_COLUMNS provides a visible and standard way for 3rd party
apps to understand the geometry in the database, and I hate having to
maintain it. I've been wanting to make it magical for many years.

There's other ways to do it, of course. My favorite stop-gap idea is
to add some code to the hooks called by ANALYZE to update the
GEOMETRY_COLUMNS table at the same time.  That way pg_autovacuum would
occasionally update it in the background, so if things went out of
synch due to users changing things, they'd come back again in a bit.

P

On Wed, Jun 4, 2008 at 1:40 PM, Chris Hermansen
<chris.hermansen at timberline.ca> wrote:
> 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
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>