[gdal-dev] Why have I got SRS 900914 in my PostGIS table after ogr2ogr alimentation ?

[Even Rouault]

Le mardi 23 octobre 2012 12:57:26, SAEZ Laurent - SG/SPSSI/CPII/DOM/ETER/PNE 
IG a écrit :
> Hi,
> 
> I used to populate my PostGIS databases using ogr2ogr (GDAL 1.9.0,
> released 2011/12/29 compiled by myself on Bedian).
> It's the first time I've got this behavior: SRS isn't which I expect.
> It's replaced by code 900914 in my table definition :
> CONSTRAINT enforce_srid_the_geom CHECK (st_srid(the_geom) = 900914
> 
> I don't understand why...
> This issue occured with EPSG:4467.

I also reproduce the same behaviour (... but read all the email ;-))

> 
> PostgreSQL 9.1.2, PostGIS 1.5.3, GDAL 1.9.0 run on the same server.
> 
> If I do : gdalsrsinfo "EPSG:4467"
> It returns :
> PROJ.4 : '+proj=utm +zone=21 +ellps=GRS80 +towgs84=0,0,0,0,0,0,0
> +units=m +no_defs '
> 
> OGC WKT :
> PROJCS["RGSPM06 / UTM zone 21N",
>      GEOGCS["RGSPM06",
>          DATUM["Reseau_Geodesique_de_Saint_Pierre_et_Miquelon_2006",
>              SPHEROID["GRS 1980",6378137,298.257222101,
>                  AUTHORITY["EPSG","7019"]],
>              TOWGS84[0,0,0,0,0,0,0],
>              AUTHORITY["EPSG","1038"]],
>          PRIMEM["Greenwich",0,
>              AUTHORITY["EPSG","8901"]],
>          UNIT["degree",0.0174532925199433,
>              AUTHORITY["EPSG","9122"]],
>          AUTHORITY["EPSG","4463"]],
>      PROJECTION["Transverse_Mercator"],
>      PARAMETER["latitude_of_origin",0],
>      PARAMETER["central_meridian",-57],
>      PARAMETER["scale_factor",0.9996],
>      PARAMETER["false_easting",500000],
>      PARAMETER["false_northing",0],
>      UNIT["metre",1,
>          AUTHORITY["EPSG","9001"]],
>      AXIS["Easting",EAST],
>      AXIS["Northing",NORTH],
>      AUTHORITY["EPSG","4467"]]
> 
> It's correct !!! But at this point, I don't know from where comes this
> OGC WKT definition ?

GDAL builds the OGC WKT definition from the pcs.csv and gcs.csv files (and 
possibly other .csv files in its data directory)

> 
> - spatial_ref_sys from my database :
> 4467;"EPSG";4467;"PROJCS["RGSPM06 / UTM zone
> 21N",GEOGCS["RGSPM06",DATUM["Reseau_Geodesique_de_Saint_Pierre_et_Miquelon_
> 2006",SPHEROID["GRS
> 1980",6378137,298.257222101,AUTHORITY["EPSG","7019"]],TOWGS84[0,0,0,0,0,0,
> 0],AUTHORITY["EPSG","1038"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]]
> ,UNIT["degree",0.0174532925199433,AUTHORITY["EPSG","9122"]],AUTHORITY["EPSG
> ","4463"]],UNIT["metre",1,AUTHORITY["EPSG","9001"]],PROJECTION["Transverse_
> Mercator"],PARAMETER["latitude_of_origin",0],PARAMETER["central_meridian",-
> 57],PARAMETER["scale_factor",0.9996],PARAMETER["false_easting",500000],PARA
> METER["false_northing",0],AUTHORITY["EPSG","4467"],AXIS["Easting",EAST],AXI
> S["Northing",NORTH]]";"+proj=utm +zone=21 +ellps=GRS80
> +towgs84=0,0,0,0,0,0,0 +units=m +no_defs "

Are you really sure of that ?  I've checked that the spatial_ref_sys.sql 
script included in the Postgis 1.5 distribution, which is used to initialize a 
postgis DB, does NOT include the definition for EPSG:4467. So the behaviour of 
the OGR PostgreSQL driver is consistant : it inserts a new custom entry in the 
spatial_ref_sys table because there's no existing one before. But this 
shouldn't be a problem if you stay in the OGR "world" : the SRS reported by 
ogrinfo should be OK. It's only if some code (IMHO wrongly) assumes that SRID 
= AUTH_SRID = EPSG code that will have problems.

But the spatial_ref_sys.sql that comes with Postgis 2.0 DOES include the 
definition of EPSG:4467. And when retrying ogr2ogr -a_srs EPSG:4467 with 
Postgis 2.0, the existing entry in spatial_ref_sys is used.

The spatial_ref_sys.sql script that comes with PostGIS is generated by a 
script using GDAL. In PostGIS 1.5, it used an older GDAL version as a 
reference. In PostGIS 2.0, they likely upgraded it from a newer GDAL version 
(1.8 or 1.9 I'm not sure) that included new EPSG codes.

If you're interested in the process of EPSG definitions refresh in the 
libgeotiff, GDAL, PROJ.4 and PostGIS stacks, you can skim through 
http://svn.osgeo.org/metacrs/geotiff/trunk/libgeotiff/csv/README

Best regards,

Even