Bug report #10937

Some items have coordinates switched when loading PostGIS layer

Added by Milos Kroulik about 10 years ago. Updated almost 10 years ago.

Status:Closed
Priority:Normal
Assignee:-
Category:-
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

Description

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:

Correct: 

LINESTRING(16.87593599289347779 49.7559793078007857, ...

Switched:

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.

shapefiles_switched.zip (62.8 KB) Milos Kroulik, 2014-07-24 01:20 AM

History

#1 Updated by Milos Kroulik about 10 years ago

I managed to create 2 shapefiles, that demonstrate difference between correct and "switched" geometries. Please unzip the attached file and open switched_linex.qgs project.

#2 Updated by Giovanni Manghi almost 10 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 almost 10 years ago

More details:

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 almost 10 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.

#5 Updated by Giovanni Manghi almost 10 years ago

  • Resolution set to invalid

#6 Updated by Milos Kroulik almost 10 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.

#7 Updated by Giovanni Manghi almost 10 years ago

  • Resolution deleted (invalid)

if I should create screencast

that would help

#8 Updated by Milos Kroulik almost 10 years ago

Screencast is available at http://youtu.be/rqS6kaFfUaQ It contains some annotations, that could hopefully help to understand the issue.

#9 Updated by Milos Kroulik almost 10 years ago

  • Status changed from Reopened to Closed

It seems to be solved by upgrading DB to Postgres 9.3. Hopefully I'm right this time.

#10 Updated by Giovanni Manghi almost 10 years ago

  • Resolution set to invalid

Also available in: Atom PDF