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

classic Classic list List threaded Threaded
10 messages Options
Reply | Threaded
Open this post in threaded view
|

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

David Blasby-3
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:
[hidden email]

 

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



Reply | Threaded
Open this post in threaded view
|

[postgis] Different geometry types for columns

David Blasby-3
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:
[hidden email]

 

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



Reply | Threaded
Open this post in threaded view
|

Re: [postgis] Different geometry types for columns

Timothy H. Keitt-2
 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:
>[hidden email]
>
>
>
>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:
[hidden email]

 

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



Reply | Threaded
Open this post in threaded view
|

[postgis] Re: The Next Level(polygon clipping)

Hisaji ONO-3
Hi.

> a) add most/all of the OGC Simple Features Spec (union,
>     intersection, xor, etc)

  For this functions, you'll wait for JST.

 But how about using Java 2D api's Area class which supports polygon
clipping and manipulate coordinates of this result by "Path" like Postscript
or SVG. But Area class doesn't support 'hole'..

  Knudsen, J.(1999): JAVA 2D Graphics, O'Reilly, 54-58.

 PostGIS has already has some functions like boundary box overlaps for
pre-processing of polygon clipping.

 How about my suggestion?



------------------------ 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:
[hidden email]

 

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



Reply | Threaded
Open this post in threaded view
|

Re: [postgis] Re: The Next Level(polygon clipping)

Paul Ramsey-2
Hisaji ONO wrote:
>
> > a) add most/all of the OGC Simple Features Spec (union,
> >     intersection, xor, etc)
>
>   For this functions, you'll wait for JST.
>
>  But how about using Java 2D api's Area class which supports polygon
> clipping and manipulate coordinates of this result by "Path" like Postscript
> or SVG. But Area class doesn't support 'hole'..

Perhaps I misunderstand, but:
- Firstly, we do not have access to a Java Virtual Machine at the
backend (server-side Java is still a wish-list item for the PostgreSQL
team), so therefor we do not have access to the Java2D API.
- Secondly, if the routines cannot handle holes they aren't much use for
a GIS-oriented system. Certainly they would not meet the requirements of
OGC.

Am I missing something here?

>
>   Knudsen, J.(1999): JAVA 2D Graphics, O'Reilly, 54-58.
>
>  PostGIS has already has some functions like boundary box overlaps for
> pre-processing of polygon clipping.
>
>  How about my suggestion?

------------------------ Yahoo! Groups Sponsor ---------------------~-->
Secure your servers with 128-bit SSL encryption! Grab your copy of VeriSign's FREE Guide: "Securing Your Web Site for Business." Get it Now!
http://www.verisign.com/cgi-bin/go.cgi?a=n094442340008000
http://us.click.yahoo.com/n7RbFC/zhwCAA/yigFAA/PhFolB/TM
---------------------------------------------------------------------~->

To unsubscribe from this group, send an email to:
[hidden email]

 

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



Reply | Threaded
Open this post in threaded view
|

Re: [postgis] Re: The Next Level(polygon clipping)

Hisaji ONO-3
Hi.

>
> Perhaps I misunderstand, but:
> - Firstly, we do not have access to a Java Virtual Machine at the
> backend (server-side Java is still a wish-list item for the PostgreSQL
> team), so therefor we do not have access to the Java2D API.
> - Secondly, if the routines cannot handle holes they aren't much use for
> a GIS-oriented system. Certainly they would not meet the requirements of
> OGC.
>

 Sorry, I didn't understand about PostGIS very well. According to GDBC's
document, http://home.gdbc.gov.bc.ca/RFP/RFP1070-20-604.pdf, which is only
info of JST for me, JST is based on Java. So PostGIS supports Java-Run time
and will connect to JST via JNI(Java Native Interface.)


------------------------ 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:
[hidden email]

 

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



Reply | Threaded
Open this post in threaded view
|

RE: [postgis] Re: The Next Level(polygon clipping)

Martin Davis-2
In reply to this post by Hisaji ONO-3
As the JTS developer, I'll stick my 2 cents in here...  

Paul's right, currently there is no connection between JTS and PostGIS.
That connection is obviously desirable, but how to achieve it is currently a
research issue (mostly conducted over beers in the pub, so far...).  It
seems unlikely that we'll be able to use the Java version of JTS on the
server side.  However, it may be that using JTS on the client-side is
sufficient for some applications.

I hope to make a version of JTS available on the Web in the next couple of
months.

By the way, the Java2D Area *does* support holes, via the use of the
subtract method.



-----Original Message-----
From: Hisaji ONO [mailto:[hidden email]]
Sent: Tuesday, July 31, 2001 11:12 PM
To: [hidden email]
Subject: Re: [postgis] Re: The Next Level(polygon clipping)


Hi.

>
> Perhaps I misunderstand, but:
> - Firstly, we do not have access to a Java Virtual Machine at the
> backend (server-side Java is still a wish-list item for the PostgreSQL
> team), so therefor we do not have access to the Java2D API.
> - Secondly, if the routines cannot handle holes they aren't much use for
> a GIS-oriented system. Certainly they would not meet the requirements of
> OGC.
>

 Sorry, I didn't understand about PostGIS very well. According to GDBC's
document, http://home.gdbc.gov.bc.ca/RFP/RFP1070-20-604.pdf, which is only
info of JST for me, JST is based on Java. So PostGIS supports Java-Run time
and will connect to JST via JNI(Java Native Interface.)



To unsubscribe from this group, send an email to:
[hidden email]

 

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


------------------------ 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:
[hidden email]

 

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



Reply | Threaded
Open this post in threaded view
|

Re: [postgis] Re: The Next Level(polygon clipping)

David Blasby-3
> I hope to make a version of JTS available on the Web in the next couple of
> months.
>
> By the way, the Java2D Area *does* support holes, via the use of the
> subtract method.

Since we're talking about JTS, perhaps you could tell the group what it
is and how its different from Java 2D?

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:
[hidden email]

 

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



Reply | Threaded
Open this post in threaded view
|

Re: [postgis] Different geometry types for columns

David Blasby-3
In reply to this post by Timothy H. Keitt-2
>  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,...);'".

You are 100% correct - I'll remove the restriction once I've figured how
to ask the "what database am I?" question.

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:
[hidden email]

 

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



Reply | Threaded
Open this post in threaded view
|

Re: [postgis] Different geometry types for columns

Timothy H. Keitt-2
Ah.  I see the problem.  You don't have a connection object to reference
since its in SQL.  Don't know the answer either.

Tim

Dave Blasby wrote:

>> 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,...);'".
>>
>
>You are 100% correct - I'll remove the restriction once I've figured how
>to ask the "what database am I?" question.
>
>dave
>
>
>To unsubscribe from this group, send an email to:
>[hidden email]
>
>
>
>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:
[hidden email]

 

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