[Gdal-dev] RE: ogr2ogr to PostGIS gives wrong SRID?

[Maciej Sieczka]

Randy George wrote:

> I am attempting to use ogr2ogr to load some shp files into PostGIS
> 
> When translating a .shp file that is in EPSG:2239 and reprojecting to
> EPSG:4326 like this:
> 
> ogr2ogr -f PostgreSQL PG:"user=user dbname=GA host=localhost password=pass
> port=5432" 06_ZoneA.shp -s_srs EPSG:2239 -t_srs EPSG:4326 -nln test -append
> 
> The resulting table has correct coords but not a valid srid:

<snip>

I've just needed to make a similar operation, using a
different EPSG code, and my output PostGIS layer has SRID
set wrong too:

ogr2ogr -f PostgreSQL -t_srs EPSG:2180 -s_srs EPSG:32633
"PG:dbname=dss" -lco "DIM=2" -nln dss_postgis.gminy gminy.shp

The SRID is set to 32767 in geometry_columns:

;dss_postgis;gminy;wkb_geometry;2;32767;POLYGON

This is wrong; there is a match for EPSG:2180 in my
spatial_ref_sys. Tried Randy's work around, but it fails at
the last stage; three first steps go fine:

> My convoluted work around:
> 
> ALTER TABLE "test" DROP CONSTRAINT enforce_srid_wkb_geometry;
> 
> UPDATE "test" SET wkb_geometry = setsrid(wkb_geometry,4326);
> 
> ALTER TABLE "test"  ADD CONSTRAINT enforce_srid_wkb_geometry CHECK
> (srid(wkb_geometry) = 4326);

Which, in my case translate into:

ALTER TABLE dss_postgis.gminy DROP CONSTRAINT
enforce_srid_wkb_geometry;

UPDATE dss_postgis.gminy SET
wkb_geometry=setsrid(wkb_geometry,2180);

ALTER TABLE dss_postgis.gminy ADD CONSTRAINT
enforce_srid_wkb_geometry CHECK (srid(wkb_geometry)=2180);

> UPDATE "geometry_columns" SET srid=4326 WHERE f_table_name='test';

But this last one fails with an error:

UPDATE public.geometry_columns SET srid=2180 WHERE
f_table_name=dss_postgis.gminy;

ERROR:  missing FROM-clause entry for table "dss_postgis"



What should I do in order to change the srid of my PostGIS
layer and why isn't it set properly at first?

Frank suggests the reason of Randy's problem was that OGR
could not find a match for his EPSG:4326 translated into
WKT. Is it my case too? I can see that "SELECT srtext FROM
spatial_ref_sys WHERE srid=2180" and "SELECT srtext FROM
spatial_ref_sys WHERE srid=32767" yield exactly the same
result, only the order of few tags is different.

If the comparison of OGR's EPSG->WKT with PostGIS's "srtext"
is error-prone, could OGR instead just look-up the
spatial_ref_sys table for "auth_srid" equal to the EPSG code
specified, check if "auth_name" is "EPSG" and use the
"auth_srid" value then? That would guarantee that a correct
EPSG code is used; see:

SELECT srid,auth_name,auth_srid FROM spatial_ref_sys WHERE
auth_srid=2180;

 srid | auth_name | auth_srid
------+-----------+-----------
 2180 | EPSG      |      2180


Hmm, actualy, all "auth_name" fields in my spatial_ref_sys
are "EPSG", and all "srid" and "auth_srid" are the same; but
that's maybe just me:

SELECT srid FROM spatial_ref_sys WHERE auth_name!='EPSG';
 srid
------
(0 rows)

SELECT srid FROM spatial_ref_sys WHERE srid!=auth_srid;
 srid
------
(0 rows)



Using PostgreSQL 8.1.10, PostGIS 1.3.2, GDAL 1.5+SVN.

Maciek