[postgis-devel] [PostGIS] #1702: SetSRID, ST_Transform and geometry_columns in sync?

[PostGIS]

#1702: SetSRID, ST_Transform and geometry_columns in sync?
-----------------------+----------------------------------------------------
 Reporter:  darkblueb  |       Owner:  pramsey      
     Type:  defect     |      Status:  new          
 Priority:  medium     |   Milestone:  PostGIS 2.0.0
Component:  postgis    |     Version:  trunk        
 Keywords:             |  
-----------------------+----------------------------------------------------
 {{{

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

 }}}

-- 
Ticket URL: <http://trac.osgeo.org/postgis/ticket/1702>
PostGIS <http://trac.osgeo.org/postgis/>
The PostGIS Trac is used for bug, enhancement & task tracking, a user and developer wiki, and a view into the subversion code repository of PostGIS project.