[postgis-users] create tables smarter

Lee Hachadoorian lee.hachadoorian at gmail.com
Wed Jun 4 12:18:40 PDT 2008


Kevin,

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.

Lee Hachadoorian
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.
>
> 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
>



More information about the postgis-users mailing list