Opened 12 years ago

Closed 11 years ago

#2123 closed enhancement (fixed)

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

Reported by: msieczka Owned by: Even Rouault
Priority: normal Milestone: 1.5.2
Component: OGR_SF Version: 1.5.0 betas/RCs
Severity: normal Keywords: postgis pg
Cc: warmerdam, project10, Jeff McKenna

Description (last modified by warmerdam)

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

Attachments (1)

2123_pg_authsrid.patch (3.2 KB) - added by project10 12 years ago.

Download all attachments as: .zip

Change History (10)

comment:1 Changed 12 years ago by warmerdam

Cc: warmerdam added
Component: OGR_SRSOGR_SF
Description: modified (diff)
Keywords: postgis pg added
Milestone: 1.5.1
Owner: changed from warmerdam to Mateusz Łoskot

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.

Changed 12 years ago by project10

Attachment: 2123_pg_authsrid.patch added

comment:2 Changed 12 years ago by project10

Cc: project10 added

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.

comment:3 Changed 12 years ago by 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.

comment:4 Changed 12 years ago by Even Rouault

Milestone: 1.5.11.6.0

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

comment:5 Changed 11 years ago by Jeff McKenna

Cc: Jeff McKenna added

comment:6 Changed 11 years ago by Jeff McKenna

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

comment:7 Changed 11 years ago by mloskot

Owner: changed from Mateusz Łoskot to Even Rouault

Even,

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

comment:8 Changed 11 years ago by Even Rouault

Status: newassigned

comment:9 Changed 11 years ago by Even Rouault

Milestone: 1.6.01.5.2
Resolution: fixed
Status: assignedclosed

Backported in branches/1.5 in r14403

Note: See TracTickets for help on using tickets.