[postgis] Different geometry types for columns

[Timothy H. Keitt]

 From the redundancy department of redundancy... :-)  Is it necessary to 
specify the db_name in AddGeometryColumn?  Its not orthogonal to "psql 
db_name -c 'select AddGeometryColumn(table,...);'".

T.

Dave Blasby wrote:

>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
>
>
>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/ 
>
>

-- 
Timothy H. Keitt
Department of Ecology and Evolution
State University of New York at Stony Brook
Stony Brook, New York 11794 USA
Phone: 631-632-1101, FAX: 631-632-7626
http://life.bio.sunysb.edu/ee/keitt/




------------------------ 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/