Opened 5 years ago

Closed 5 years ago

#5287 closed defect (fixed)

Wrong SRID detected for tables having 2 geometric fields

Reported by: strk Owned by: warmerdam
Priority: normal Milestone: 1.10.2
Component: OGR_SF Version: 1.10.1
Severity: normal Keywords:
Cc:

Description

A query like "SELECT g1 FROM ( SELECT * FROM mytable ) foo", with "mytable" having two geometries "g1" and "g2" with different SRIDS, results in ogr2ogr assuming the wrong SRID for the output geometry, which is as a consequence badly transformed to the output projection.

It's also been noted that running the query as a user with NO PERMISSION to read "geometry_columns" results in the correct SRID being detected.

This has been seen with version 1.10.1, I'm still trying to produce a self-contained test.

See also #5131 Origin of the bug report: https://github.com/CartoDB/CartoDB-SQL-API/issues/116

Change History (10)

comment:1 Changed 5 years ago by Jukka Rahkonen

There is a RFC aiming at next GDAL versio. Read, think, and comment it.

http://trac.osgeo.org/gdal/wiki/rfc41_multiple_geometry_fields

comment:2 Changed 5 years ago by strk

So I could reproduce. It just takes the geometry column which is NOT queried created first in the CREATE TABLE statement, so that it gets found as first record from geometry_columns.

The test would be along these lines:

psql -c "CREATE TABLE test5287 AS SELECT 
 'SRID=3857;POINT(0 0)'::geometry(point, 3857) g1,
 'SRID=4326;POINT(50 50)'::geometry(geometry, 4326) g2;"
ogr2ogr -f KML /dev/stdout PG: -sql 'SELECT "g2" from test5287'

Expected coordinates: 50,50 Obtained coordinates: 0.00044915764206,0.00044915764205

comment:3 Changed 5 years ago by strk

I'm not sure the RFC is relevant. I'll read it, but for the sake of this bug we're fetching data from a query, and the query returns a _single_ column.

comment:4 Changed 5 years ago by strk

Trunk as of r26532, is not affected. Any idea about which commit fixed this ? I'll try 1.10 branch next.

comment:5 Changed 5 years ago by strk

1.10 branch as of r26577 is still bogus

comment:6 Changed 5 years ago by Jukka Rahkonen

If it is enough to get the right result, I would have a try with -a_srs/-s_srs/-t_srs parameters of ogr2ogr. Perhaps you can override the automatically detected SRID.

comment:7 Changed 5 years ago by strk

Yes jratike80, that's what I'll do in the meanwhile.

comment:8 Changed 5 years ago by Jukka Rahkonen

It may be difficult to make safe assumptions if data are selected with SQL because nothing prevents to do "SELECT g1 AS g2" or more realistic, "SELECT ST_Transform(g1,3067) as g3". But feel free to suggest roughly how you would do it yourself so we can weight it all together. Even better if you could sketch the SQL query which GDAL should use for finding the right SRID from geometry_columns. I suppose you are talking about PostGIS and not for example Oracle but in this case the difference is probably not so big.

comment:9 Changed 5 years ago by strk

What I'd do is always look at the first non-null feature coming out from the query, and use that as the SRID. This is exactly what happens when "geometry_columns" is non-readable, for example, or doesn't contain SRID information. It works fine.

comment:10 Changed 5 years ago by Even Rouault

Milestone: 1.10.2
Resolution: fixed
Status: newclosed

branches/1.10 r26584 "PG: on SQL result layer, avoid using the wrong SRID for tables having several geometry fields (#5287)"

trunk doesn't need that changeset because of all the work done for RFC 41.

Note: See TracTickets for help on using tickets.