SetSRID, ST_Transform and geometry_columns in sync?

[darkblueb]

--
-- the following sequence is an exposition on 
--     SetSRID, Transform and the geometry_columns VIEW
--
-- Are the expectations of the relationships here clearly defined? 
-- Is this the behavior desired in 2.0 Final ?
--

geom_test=# create table test1 (pkey integer PRIMARY KEY, the_geom geometry);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "test1_pkey" for table "test1"
CREATE TABLE

geom_test=# create index t_geom_idx on test1 using GIST(the_geom);
CREATE INDEX

geom_test=# \d
             List of relations
 Schema |       Name        | Type  | Owner 
--------+-------------------+-------+-------
 public | geography_columns | view  | dbb
 public | geometry_columns  | view  | dbb
 public | spatial_ref_sys   | table | dbb
 public | test1             | table | dbb
(4 rows)

--
-- INSERT a geometry with no intrinsic projection defined
--
geom_test=# insert into test1 VALUES (1,'POINT(1 1)');
INSERT 0 1


geom_test=# select * from geometry_columns ;
 f_table_catalog | f_table_schema | f_table_name | f_geometry_column | coord_dimension | srid |   type   
-----------------+----------------+--------------+-------------------+-----------------+------+----------
 geom_test       | public         | test1        | the_geom          |               2 |    0 | GEOMETRY


---------------------------------------------------------------------------------
geom_test=# insert into test1 VALUES (2,st_geomfromEWKT('SRID=3310;POINT(1 1)'));
INSERT 0 1

--
-- geometry_columns VIEW shows no knowledge of the new POINT with intrinsic SRID
--
geom_test=# select * from geometry_columns ;
 f_table_catalog | f_table_schema | f_table_name | f_geometry_column | coord_dimension | srid |   type   
-----------------+----------------+--------------+-------------------+-----------------+------+----------
 geom_test       | public         | test1        | the_geom          |               2 |    0 | GEOMETRY


-- explicitly SetSRID on ALL POINTS
--
geom_test=# select st_setSRID(the_geom,3310) from test1;
                     st_setsrid                     
----------------------------------------------------
 0101000020EE0C0000000000000000F03F000000000000F03F
 0101000020EE0C0000000000000000F03F000000000000F03F
(2 rows)

-- geometry_columns still shows no knowledge of intrinsic SRIDs after SetSRID
--
geom_test=# select * from geometry_columns ;
 f_table_catalog | f_table_schema | f_table_name | f_geometry_column | coord_dimension | srid |   type   
-----------------+----------------+--------------+-------------------+-----------------+------+----------
 geom_test       | public         | test1        | the_geom          |               2 |    0 | GEOMETRY


--
--  geometry binary unchanged, also
--
geom_test=# select st_asEWKT(the_geom) from test1;
      st_asewkt       
----------------------
 POINT(1 1)
 SRID=3310;POINT(1 1)
(2 rows)


-- now add a new POINT with intrinsic SRID
--
geom_test=# insert into test1 VALUES (3,st_geomfromEWKT('SRID=3310;POINT(2 2)'));
INSERT 0 1

geom_test=# select st_asEWKT(the_geom) from test1;
      st_asewkt       
----------------------
 POINT(1 1)
 SRID=3310;POINT(1 1)
 SRID=3310;POINT(2 2)
(3 rows)

--
--  transform binary geometry contents
--
geom_test=# update test1 set the_geom = st_transform(the_geom,4326) where pkey = 3;
UPDATE 1
geom_test=# select st_asEWKT(the_geom) from test1;
                      st_asewkt                      
-----------------------------------------------------
 POINT(1 1)
 SRID=3310;POINT(1 1)
 SRID=4326;POINT(-119.999977189465 38.0163834586186)
(3 rows)

--
--  no reflection in geometry_columns VIEW ?
--
geom_test=# select * from geometry_columns ;
 f_table_catalog | f_table_schema | f_table_name | f_geometry_column | coord_dimension | srid |   type   
-----------------+----------------+--------------+-------------------+-----------------+------+----------
 geom_test       | public         | test1        | the_geom          |               2 |    0 | GEOMETRY


--
--  explitly call setSRID on top of instrinsic SRID on third POINT
-- 
geom_test=# select st_setSRID(the_geom,4326) from test1 where pkey = 3;
                     st_setsrid                     
----------------------------------------------------
 0101000020E6100000EC5F53A0FFFF5DC02A7B69DA18024340
(1 row)

--
-- an attempt to transform all POINTs is caught on POINT 1 w/o instrinsic SRID
--
geom_test=# update test1 set the_geom = st_transform(the_geom,4326);
ERROR:  Input geometry has unknown (0) SRID

geom_test=# select pkey,st_asEWKT(the_geom) from test1;
 pkey |                      st_asewkt                      
------+-----------------------------------------------------
    1 | POINT(1 1)
    2 | SRID=3310;POINT(1 1)
    3 | SRID=4326;POINT(-119.999977189465 38.0163834586186)
(3 rows)


--
--  geometry_columns VIEW does not reflect any change 
--
 f_table_catalog | f_table_schema | f_table_name | f_geometry_column | coord_dimension | srid |   type   
-----------------+----------------+--------------+-------------------+-----------------+------+----------
 geom_test       | public         | test1        | the_geom          |               2 |    0 | GEOMETRY
(1 row)

[robe]

dbb -- yes this is the behavior we want.

geometry_columns works off the definition of your column not what you put in it. What you put in it is constrained by what the definition allows.

You defined your column as an unconstrained geometry column, which means you are free to throw in whatever crap geometry you want in it.

Think of geometry_columns as metadata listing WHAT IS ALLOWED, not WHAT IS IN the table. How owuld you expect an empty table to display?

NOTE: You either have to define your geometry column as

geometry(POINT,3310)

or use AddGeometryColumn ... http://trac.osgeo.org/postgis/ticket/1693 with use_typmod=false to get a constraint constrained geometry column.

[robe]

oops wrong link -- meant to link to this: ​http://www.postgis.org/documentation/manual-svn/AddGeometryColumn.html