Opened 10 years ago
Closed 10 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 by , 10 years ago
comment:2 by , 10 years ago
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 by , 10 years ago
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 by , 10 years ago
Trunk as of r26532, is not affected. Any idea about which commit fixed this ? I'll try 1.10 branch next.
comment:6 by , 10 years ago
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:8 by , 10 years ago
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 by , 10 years ago
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 by , 10 years ago
Milestone: | → 1.10.2 |
---|---|
Resolution: | → fixed |
Status: | new → closed |
There is a RFC aiming at next GDAL versio. Read, think, and comment it.
http://trac.osgeo.org/gdal/wiki/rfc41_multiple_geometry_fields