use the "auth_srid" to avoid mismatches between OGR and PostGIS WKTs of EPSG codes

[msieczka]

To avoid mismatches between OGR's EPSG->WKT and PostGIS's "srtext" strings due to formatting differences, look-up the "spatial_ref_sys" table for "auth_srid" equal to the EPSG code requested, 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

Some more details in discussion at [1].

[1] http://thread.gmane.org/gmane.comp.gis.gdal.devel/10433/focus=13927

[warmerdam]

Mateusz,

I agree that the OGRPGDataSource::FetchSRSId() method should first (if the coordinate system has a root EPSG based authority node) scan based on that. And only fall back to the srtext comparison if that fails.

It might also be prudent for the method to populate the authority name and id information in the spatial_ref_sys table if it is available.

[project10]

I worked up a quick patch to do searches based on the EPSG authority code, if EPSG is the root authority name. If the search fails, WKT translation and searching based on that occurs as before.

Also, when adding a new record to the spatial_ref_sys table, if EPSG is the root authority node name, the auth_name and auth_srid columns will be populated.

It is also possible to populate auth_name/auth_srid for existing records (after a successful srtext match). This would speed up future searches for the same SRS. I did not do this so as to not change /too/ much.

[project10]

It occurs to me that my patch potentially opens up an SQL injection vulnerability; the authority name is not escaped prior to being used in SQL statements (1 SELECT, 1 INSERT).

This can be resolved easily through the use of PQescapeStringConn()/PQescapeString(), and it might be beneficial to use this method instead of the one currently employed at ogrpgtablelayer.cpp:985.

I can submit an updated patch which addresses these issues.

[rouault]

Thanks for the patch. There was no risk of SQL injection since the authority name is tested to be EPSG ;-)

Applied in trunk in r14213. Test added in ogr_py in r14214

[jmckenna]

i was hitting this same issue with gdal 1.5.0. Tested with trunk r14398 (on windows) and problem is gone!

[mloskot]

Even,

As you have fixed it, I'm assigning the ticket to you.

[rouault]

Backported in branches/1.5 in r14403