[postgis] SRID initial support (in CVS)

[Dave Blasby]

I'm making lots of changes to support the metadata information required
by the openGIS specification.  This is currently available in the CVS
version.  

NB: I've made a change to the binary representation of a geometry, so
you'll need to dump/restore your geometry databases BEFORE installing
the CVS version.  I dont recommend using the CVS version right now - I'm
just send the changes out so folks can give me comments and know whats
going on.



1. There is now a spatial_ref_sys table created when you run the
postgis.sql initialization script.  This is defined in the OpenGIS spec
section 3.2.1.2.  This is where you will define the map projection your
data is in.

create table spatial_ref_sys ( srid integer not null primary key,
auth_name varchar(256), auth_srid integer, srtext varchar(2048) );

2. The meta-data information table is also created (section 3.2.2.2);

create table geometry_columns (
f_table_catalog varchar(256) not null,
f_table_schema  varchar(256) not null,
f_table_name    varchar(256) not null,
f_geometry_column varchar(256) not null,
coord_dimension  integer ,
srid		integer,
CONSTRAINT GC_PK primary key ( f_table_catalog,f_table_schema,
f_table_name,f_geometry_column)
) ;

OpenGIS requires that this table auto-adjust itself if the
catalog/schema/table_name change.  This is difficult to support under
PostgreSQL because it doesnt normally allow triggers to be attached to
system tables.  Plus (1) there is no equivelent to 'schema' and (2) you
cannot access information in another database.  Any suggestions?

3. GEOMETRY type now has a SRID (spatial reference system id) built into
it.  Its cannonical text form now looks like; "SRID=75;<wkt>".   You
should no longer use the canonnical form - use the astext() function or
the geometryfromtext() function.

Heres an example of the new cannonical form (previously it was just the
WKT representation of the geometry);

# select * from t;
 gid |            mygeom             
-----+---------------------------
   1 | SRID=-1;POINT(1 1)
   1 | SRID=5;POINT(1 1)
   2 | SRID=7;POINT(100 100 100)
(3 rows)

You can do something like:

insert into <table> values ('SRID=73;LINESTRING(0 0, 10 10)');

If you omit the "SRID=" portion, its set to -1.
I think openGIS frowns upon inserting new geometries this way.  Instead,
you should...

4. new function (section 3.2.6.2) GeometryFromText( String, SRID).  The
string is a WKT representation of some geometry, so you are supposed to
create geometries like;

	GeometryFromText('LINESTRING(0 0, 10 10)', 73)

insert into t values (66, GeometryFromText('LINESTRING(0 0, 10 10)', 73)
);

5. new function SRID(geometry) :- returns the spatial referencing system
id used in that geometry

6. new function astext(geometry) :- returns the WKT representation of
the geometry

So, under the OpenGIS zen, you would look at a table like this;

# select gid, astext(mygeom),srid(geom) from t;
 gid |       astext       | srid 
-----+--------------------+------
   1 | POINT(1 1)         |   -1
   1 | POINT(1 1)         |    5
   2 | POINT(100 100 100) |    7
(3 rows)

7. I'm writing the AddGeometryColumn() function.  According to the
OpenGIS spec, you are not allowed to make geometry columns using the sql
"create table ..." feature.  You first make a table, then use the
AddGeometryColumn() function to stick in a geometry column.  The nice
thing about this is you can easily keep track of all your geometry
columns and add constraints on the table to ensure that all the
geometries have the same spatial referencing system.  I still havent
figured out EXACTLY what AddGeometryColumn() is supposed to do (the
exact details are a bit murky).  Comments?

8.  You're also supposed to include a DropGeometryColumn() function. 
Unfortunately postgresql does not allow you to use the sql "alter table
..." to drop columns.  I'm not sure what exactly to do to get around
this; the postgresql manual says you should create a new temporary table
without the offending column, then delete the original table, then
rename the temporary table.  That can cause problems with referencial
integrity with the the metadata table, plus any constraints you've put
on that table.   Not to mention its quite resource-intensive. Comments?


Any suggestions?
dave
ps. the spec I'm refering to is at
"http://www.opengis.org/techno/specs/99-049.pdf".

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