[postgis-users] create tables smarter

[Kevin Neufeld]

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)

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