[postgis] AddGeometryColumn(), DropGeometryColumn(), Spatial References

[Dave Blasby]

This refers to the CVS version.  As I said before you'll have to
dump/restore your database before you upgrade. I'm sending this info out
to stimulate discussion and get some feedback - dont upgrade to the CVS
version just yet.


I've been wandering through the PostGIS code to ensure the Spatial
Referencing ID is both kept (ie. when creating new geometries like with
envelope()) and respected (ie. geometry1 << geometry2 will through an
error if the have different SRIDs).  
The GiST index has been changed so it knows about SRIDs and will throw
an error if you try to index using a different spatial reference system.

Table creation has been changed.  You first create a table using the
standard sql CREATE TABLE command.  Then you add a geometry column to it
with AddGeometryColumn().  Here's a simple example;

NB: You need to have plpgsql installed on your system (see below)

new_test=# create table test_table (gid integer);
CREATE
new_test=# select AddGeometryColumn('new_test','test_table','mygeom',33,
0, 3);
                         addgeometrycolumn                          
--------------------------------------------------------------------
 Geometry column mygeom added to table test_table with a SRID of 33
(1 row)

new_test=# select * from geometry_columns;
 f_table_catalog | f_table_schema | f_table_name | f_geometry_column |
coord_dimension | srid | type 
-----------------+----------------+--------------+-------------------+-----------------+------+------
                 | new_test       | test_table   | mygeom           
|               3 |   33 |    0
(1 rows)

new_test=# insert into test_table values (1,GeometryFromText('POINT(0 0
0)',33));
INSERT 5035766 1
new_test=# select gid,astext(mygeom),srid(mygeom) from test_table;
 gid |    astext    | srid 
-----+--------------+------
   1 | POINT(0 0 0) |   33
(1 row)

Also, for indexing/searching, you have to set the SRID of the query
window or it will throw an error;
new_test=# select gid,astext(mygeom),srid(mygeom) from test_table where
setSRID('BOX3D(-5 -5 -5, 5 5 5 )'::BOX3D,33) && mygeom;


The AddGeometryColumn() function will add a constraint on the column to
ensure all the SRIDs in the column are correct. It will give an error if
you voilate this.

new_test=# \d test_table
       Table "test_table"
 Attribute |   Type   | Modifier 
-----------+----------+----------
 gid       | integer  | 
 mygeom    | geometry | 
Constraint: (srid(mygeom) = 33)

new_test=# insert into test_table values (1,GeometryFromText('POINT(0 0
0)',666));
ERROR:  ExecAppend: rejected due to CHECK constraint $1


If you want to delete the column, use DropGeometryColumn() function. 
 
NB: PostgreSQL does NOT have the sql ALTER TABLE DROP COLUMN or ALTER
TABLE DROP CONSTRAINT command, so I dont actually drop the column.  Its
de-referenced in the geometry_columns table, and its set to all NULLs. 
All future inserts into that column will throw an error.  Effectively,
the column is dead.

new_test=# select DropGeometryColumn('new_test','test_table','mygeom');
           dropgeometrycolumn           
----------------------------------------
 test_table.mygeom effectively removed.
(1 row)

new_test=# \d test_table
       Table "test_table"
 Attribute |   Type   | Modifier 
-----------+----------+----------
 gid       | integer  | 
 mygeom    | geometry | 
Constraints: (srid(mygeom) = 33)
             (mygeom ISNULL)

new_test=# select gid,astext(mygeom),srid(mygeom) from test_table;
 gid | astext | srid 
-----+--------+------
   1 |        |     
(1 row)

comments
--------

in all case, you should use the name of the current DB for the
functions.  Does anyone know how to get the name of the current db?

For AddGeometryColumn(varchar,varchar,varchar,integer,integer,integer)

--- AddGeometryColumn(<db name>,<table name>,<column name>, <srid>,
<type>,<dim>)
--- only type available is 0 GEOMETRY_GENERIC, so its ignored (for
future expansion)
--- uses SQL ALTER TABLE command to add the geometry column to the table
--- added a row to geometry_columns with info (catalog = '', schema =
<db name>)
--- addes a constraint on the table that all the geometries MUST have
the same SRID
--- checks the coord_dimension to make sure its between 0 and 3
--- should also check the precision grid (future expansion)
---  also checks to see if the database_name is in the pg_database table


for DropGeometryColumn(varchar,varchar,varchar)

----  DropGeometryColumn(<db name>,<table name>,<column name>)
--- There is no ALTER TABLE DROP COLUMN command in postgresql
--- There is no ALTER TABLE DROP CONSTRAINT command in postgresql
--- So, we ;
---  1. remove the unwanted geom column reference from the
geometry_columns table
---  2. update the table so that the geometry column is all NULLS
----        This is okay since the CHECK srid(geometry) = <srid> is not
----           checked if geometry is NULL (the isstrict attribute on
srid())
---  3. add another constraint that the geometry column must be NULL
---  This, effectively kills the geometry column 
----   (a) its not in the geometry_column table
----   (b) it only has nulls in it
----   (c) you cannot add anything to the geom column because it must be
NULL
----  
----  This will screw up if you put a NOT NULL constraint on the
geometry column, so the
----     first thing we must do is remove this constraint (its a
modification of the
----     pg_attribute system table)
---
----   We also check to see if the table/column exists in the
geometry_columns table 



Installing plpgsql
------------------
Its fairly easy - its included in the default installation (although not
always turned on).  You'll need to change the '' to wherever your
postgresql lib directory is.  Easy sneezy (from the manual);

1. The following command tells the database where to find the shared
object for the PL/pgSQL language's call handler function. 

CREATE FUNCTION plpgsql_call_handler () RETURNS OPAQUE AS
    '/usr/local/pgsql/lib/plpgsql.so' LANGUAGE 'C';

2. The command 

 CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql'
    HANDLER plpgsql_call_handler
    LANCOMPILER 'PL/pgSQL';

then defines that the previously declared call handler function should
be invoked for functions and trigger procedures where the language
attribute is 'plpgsql'. 


Comments?
dave

------------------------ Yahoo! Groups Sponsor ---------------------~-->
Small business owners...
Tell us what you think!
http://us.click.yahoo.com/vO1FAB/txzCAA/ySSFAA/PhFolB/TM
---------------------------------------------------------------------~->

To unsubscribe from this group, send an email to:
postgis-unsubscribe at yahoogroups.com

 

Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/