[postgis] Different geometry types for columns

[Dave Blasby]

I made a minor change for AddGeometryColumn(); the type is now a
varchar.  
It can be one of GEOMETRY, GEOMETRYCOLLECTION,POINT,MULTIPOINT,POLYGON,
MULTIPOLYGON,LINESTRING, or MULTILINESTRING.  AddGeometryColumn() will
put
a constraint on the column ensuring that only that type is used.
This means you can have a column with type 'POINT' or 'POLYGON' which
will
not accept trying to insert geometries with other types.  If you dont
want
this constraint, you can set the type to the generic type, GEOMETRY.

In the future, I'll add conversion functions so that you can force type
conversion on inserts/updates using triggers (ie. automatically change a
POINT 
into a MULTIPOINT when inserting into a column of type MULTIPOINT).

For example;

new_test=# select AddGeometryColumn('new_test','d','geom2',2,'POINT',3);
                           addgeometrycolumn                            
------------------------------------------------------------------------
 Geometry column geom2 added to table d with a SRID of 2 and type POINT
(1 row)

new_test-# \d d
            Table "d"
 Attribute |   Type   | Modifier 
-----------+----------+----------
 id        | integer  | 
 geom1     | geometry | 
 geom2     | geometry | 
Constraints: (srid(geom1) = 2)
             (geometrytype(geom1) = 'POINT'::text)
             (srid(geom2) = 2)
             (geometrytype(geom2) = 'POINT'::text)
new_test=# insert into d values (2, geometryfromtext('POINT(0 0 0)',2),
geometryfromtext('POINT(1 1 1)',2) );
INSERT 5035844 1

but if you try to put in a non-POINT type into the table, it will throw
an error;

new_test=# insert into d values (2, geometryfromtext('POINT(0 0 0)',2),
geometryfromtext('LINESTRING(1 1 1,2 2 2)',2) );
ERROR:  ExecAppend: rejected due to CHECK constraint $4

FUNCTION
AddGeometryColumn(varchar,varchar,varchar,integer,varchar,integer)

--- AddGeometryColumn(<db name>,<table name>,<column name>, <srid>,
<type>,<dim>)
--- type can be one of GEOMETRY,
GEOMETRYCOLLECTION,POINT,MULTIPOINT,POLYGON,
--- MULTIPOLYGON,LINESTRING, or MULTILINESTRING 
--- types (except GEOMETRY) are checked for consistency using a CHECK
constraint
--- 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


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/