[postgis-users] create tables smarter
lee.hachadoorian at gmail.com
Wed Jun 4 12:18:40 PDT 2008
In that case I think Andreas could accomplish this using
CREATE TABLE new_table (
LIKE old_table INCLUDING CONSTRAINTS
followed by probe_geometry_columns( ). I tested this in my database
on an arbitrarily selected spatial table and it seems to have worked.
PhD Student, Geography
Program in Earth & Environmental Sciences
CUNY Graduate Center
On Wed, Jun 4, 2008 at 11:57 AM, Kevin Neufeld <kneufeld at refractions.net> 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)
> 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.
> Lee Hachadoorian wrote:
>> 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
>> 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?
>> Lee Hachadoorian
>> PhD Student, Geography
>> Program in Earth & Environmental Sciences
>> CUNY Graduate Center
>> postgis-users mailing list
>> postgis-users at postgis.refractions.net
> postgis-users mailing list
> postgis-users at postgis.refractions.net
More information about the postgis-users