[postgis-users] create tables smarter

[Stephen Woodbridge]

Kevin Neufeld wrote:
> Actually Lee, addgeometrycolumn does three things.  Two, as you 
> mentioned, and three, it adds constraints to your table so that all 
> geometries in your column have the same srid and are of the same 
> dimension and geometry type.
> 
> The function Stephen mentioned does exist, but it's called 
> probe_geometry_columns().  Unfortunately, it's not in the docs.
> 
> postgis=# \df *probe*
>                             List of functions
>  Schema |          Name          | Result data type | Argument data types
> --------+------------------------+------------------+---------------------
>  public | probe_geometry_columns | text             |
> (1 row)

Thank you for the correction. I shot that post off as I was running out 
the door for a meeting, and didn't check the details.

I have always thought that the requirement for the geometry_columns 
table was a little  weird, but hey I wasn't asked when it was 
implemented :) and I didn't do any of the work so I don't really have 
anything to complain about. That said, it would be nice is this was 
built and maintained totally transparently to the user and/or method of 
creation/removal of a column. I guess this would require closer 
integration with the pg_catalog and triggers on it or something like 
that if that is even possible. I have not really looked into it, because 
things just work most of the time.

-Steve

> This function was written quite some time ago, and in my opinion, only 
> partially works.  It adds missing entries to the geometry_columns table 
> by scanning the system tables for the three above mentioned constraints. 
>  In other words, if the three constraints don't live on your new 
> geometry table, it won't add an entry to the geometry_columns table. 
> Further, this function does not remove obsolete entries from the 
> geometry_columns table.
> 
> I wrote my own function that truncates and repopulates the 
> geometry_columns table by scanning the system tables for columns of type 
> 'geometry', rather than looking for existing constraints.  It tries to 
> add the constraints to the table the geometry is in (sending you a 
> warning if it failed), and inserts a row in the geometry_column table. I 
> suppose one of these days, I should add it to the postgis source.
> 
> Cheers,
> Kevin
> 
> 
> 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:
>>
>> INSERT INTO public.geometry_columns
>> VALUES 
>> ('',<schema_name>,<table_name>,<column_name>,<dimension>,<srid>,<type>);
>>
>> where the arguments use the same values you would use for the
>> AddGeometryColumn function.
>>
>> Stephen, I don't see anything about a probegeometrycolumns( ) function
>> in the PostGIS docs.  Can you point me in the right direction?
>>
>> Thanks,
>> Lee Hachadoorian
>> PhD Student, Geography
>> Program in Earth & Environmental Sciences
>> CUNY Graduate Center
>> _______________________________________________
>> 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