Bug report #10937
Some items have coordinates switched when loading PostGIS layer
|Affected QGIS version:||2.4.0||Regression?:||No|
|Operating System:||Easy fix?:||No|
|Pull Request or Patch supplied:||No||Resolution:||invalid|
|Crashes QGIS or corrupts data:||No||Copied to github as #:||19293|
I am having problem with strange coordinates switching when loading PostGIS layers in QGIS. First, I thought, that problem may be cause by the use of GROUP_BY feature in layer query, but later the problem started to appear in other querie not using this function and finally it happens also with single table actually containing the geometry multilinestring data (SRID:4326).
When the problem appears, coordinates of some items are switched, like this:
LINESTRING(16.87593599289347779 49.7559793078007857, ...
LINESTRING(49.7559793078007857 16.87593599289347779, ...
It appears to me, that "switched items" are different each time. The data themselves are fine, because if I export table to shapefile, all coordinates are correct.I am using:
- PostgreSQL 9.1
- PostGIS 2.1 (full version "POSTGIS="2.1.0 r11822" GEOS="3.3.3-CAPI-1.7.4" PROJ="Rel. 4.7.1, 23 September 2009" GDAL="GDAL 1.9.0, released 2011/12/29" LIBXML="2.8.0" TOPOLOGY RASTER")
Please let me know, if you need more info.
#2 Updated by Giovanni Manghi about 6 years ago
- Status changed from Open to Feedback
This is not very clear to me. Any line in the "lines switched" layer do correspond to a line in the "lines not switched" layer. Anyway... what is the layer we should test to import in PostGIS? what tool did you used to import the vector in postgis? do you see the same issue using different import tools (spit, db manager, shp2pgsql, etc.)?
#3 Updated by Milos Kroulik about 6 years ago
I managed to replicate the problem in another environment by adding another field to PostgreSQL view. The view looks like this:
DROP VIEW IF EXISTS geoserver_liniove_komunikace_view; CREATE VIEW geoserver_liniove_komunikace_view AS SELECT node.nid AS id, node.nid, node.title, unnest(array_agg(distinct poloha.field_poloha_komunikace_wgs_geometry)) AS field_poloha_komunikace_wgs_geometry, -- geomtry field array_agg(distinct historicita.field_historicita_tid) AS hístoricita, -- new field array_agg(distinct cast_obce.field_cast_obce_target_id) AS casti_obce_ids, array_agg(distinct katastralni_uzemi.field_katastralni_uzemi_target_id) AS katastralni_uzemi_ids FROM node LEFT JOIN field_data_field_poloha_komunikace_wgs poloha ON node.nid = poloha.entity_id LEFT JOIN field_data_field_historicita historicita ON node.nid = historicita.entity_id LEFT JOIN field_data_field_cast_obce cast_obce ON node.nid = cast_obce.entity_id LEFT JOIN field_data_field_katastralni_uzemi katastralni_uzemi ON node.nid = katastralni_uzemi.entity_id WHERE node.type = 'komunikace_linie' AND node.status = 1 AND poloha.deleted = 0 GROUP BY node.nid;
Also, the problem appeared on PostGIS 2.0 ("POSTGIS="2.0.1 r9979" GEOS="3.3.3-CAPI-1.7.4" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 1.9.0, released 2011/12/29" LIBXML="2.7.8" TOPOLOGY RASTER")
#4 Updated by Milos Kroulik about 6 years ago
- % Done changed from 0 to 100
- Status changed from Feedback to Closed
In PostgreSQL log, I found following two lines:
2014-07-31 13:17:16 CEST LOG: statement: SELECT l.table_name,l.schema_name,l.feature_column,CASE WHEN l.feature_type = 1 THEN 'MULTIPOINT' WHEN l.feature_type = 2 THEN 'MULTILINESTRING' WHEN l.feature_type = 3 THEN 'MULTIPOLYGON' WHEN l.feature_type = 4 THEN 'GEOMETRYCOLLECTION' END AS type,(SELECT srid FROM topology.topology t WHERE l.topology_id=t.id),2,c.relkind FROM topology.layer l,pg_class c,pg_namespace n WHERE c.relname=l.table_name AND l.schema_name=n.nspname AND n.oid=c.relnamespace AND has_schema_privilege(n.nspname,'usage') AND has_table_privilege('"'||n.nspname||'"."'||c.relname||'"','select') ORDER BY n.nspname,c.relname,l.feature_column 2014-07-31 13:17:16 CEST ERROR: permission denied for schema topology at character 333
And it was right - user, that I was using for connecting to QGIS wasn't owner of that schema, nor had any rights to the tables in that schema. So I corrected this and it seems, that geometry is displaying correctly.
Thanks for help, Giovanni.
#6 Updated by Milos Kroulik about 6 years ago
- Status changed from Closed to Reopened
- % Done changed from 100 to 0
Sorry, I was mistaken, problem is still present. I haven't found any furter errors in PostgreSQL log, so I don't know, what else should I try. Some observations which might have some relevance:
- problem appears when I add multiple PostgreSQL layers to QGIS. Some of the objects become "switched" instantly. The other layer usually brake after I remove the first one from the view (same "switching behavior").
- problem also appears when I try to create virtual layer in QGIS DB manager plugin (in most simple cases such as table with spatial column loaded twice - once through PostGIS layers dialog, once through DB manager
- There also seems to be another related issue, which is likely different, because when the features can't be seen, they can still be selected. In this case, "switching" happens on reload (or zoom). On another one, lines appear correctly, only to disappear again on another reload.
Please let me know, if I should create screencast, or try to debug something.