cursor states lost with identify tool on postgis layer

[qgib]

Author Name: Sandro Santilli (@strk)
Original Redmine Issue: 4470
Affected QGIS version: master
Redmine category:data_provider/postgis

---

Trying to identify a feature from a postgis layer I get a message:
Warning: Query: CLOSE qgisf2 returned 7 [ERROR: current transaction is aborted, commands ignored until end of transaction block
]

Server logs show:
2011-10-31 13:11:15 CET ERROR: Operation on mixed SRID geometries
2011-10-31 13:11:15 CET STATEMENT: fetch forward 200 from qgisf2
2011-10-31 13:11:15 CET ERROR: current transaction is aborted, commands ignored until end of transaction block

I dunno how to further debug this issue. In particular I don't know what's the query being sent...

[qgib]

Author Name: Giovanni Manghi (@gioman)

---

• fixed_version_id was configured as Version 1.7.4

[qgib]

Author Name: Sai Nerella (Sai Nerella)

---

Any update on this issue? I am using Quantum GIS 1.7.3 to identify features on Postgis 1.5.2 layers. I have the same issue.

1 cursor states lost.
SQL: declare qgisf2 binary cursor for select
function intersects(geography, geometry) does not exist....
LINE 1: ...4336602647 43.8916652467107795)'::box3d,4326) and intersects........
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
)...

---

• version was configured as master
• crashes_corrupts_data was configured as 0

[qgib]

Author Name: Alexander Bruy (@alexbruy)

---

Confirmed on master with PostGIS 2.0SVN database. Errors are

1 cursor states lost.
SQL: CLOSE qgisf0
Result: 7 (ERROR:  current transaction is aborted, commands ignored until end of transaction block)
Fetching from cursor qgisf0 failed
Database error: ERROR:  Operation on mixed SRID geometries
Query: CLOSE qgisf0 returned 7 [ERROR:  current transaction is aborted, commands ignored until end of transaction block]

[qgib]

Author Name: Sandro Santilli (@strk)

---

Alexander: are you also using 1.7.3 ? Did you try with 1.7 branch ?

Sai Nerella: your case is a different one. If you still have problem with the 1.7 git branch please file another ticket.

[qgib]

Author Name: Sandro Santilli (@strk)

---

I think my issue is about having geometries with mixed SRIDs into the same table. Something for which support seems fixed in master in that rows are also filtered by SRID. Do you confirm jef ?

[qgib]

Author Name: Alexander Bruy (@alexbruy)

---

Sandro Santilli wrote:

Alexander: are you also using 1.7.3 ? Did you try with 1.7 branch ?

No, I'm on master. Now with 27249d8 I can't reproduce this error

[qgib]

Author Name: Sandro Santilli (@strk)

---

I'll assume it's fixed then. Could not reproduce with 1.7, if anyone can feel free to reopen or file another one.

---

• fixed_version_id was changed from Version 1.7.4 to Version 2.0.0
• resolution was changed from to fixed

[qgib]

Author Name: Jürgen Fischer (@jef-n)

---

• status_id was changed from Open to Closed

[qgib]

Author Name: Rudi von Staden (@rudivs)

---

Description
I am seeing this on QGIS 1.8 and on master (revision ee96d38) with a particular table. I think the problem occurs when the SRID of a field is different from the SRID of the data contained in the field. I encountered the issue when I loaded an unprojected shapefile (attached) into postgis, and then updated the srid with @update testdata SET geom=ST_SetSRID(geom,4326);@ The result of @select find_srid('public','testdata','geom');@ is then 0, but @select DISTINCT ST_SRID(geom) FROM testdata;@ returns 4326. Loading such a table into QGIS will result in the errors below.

Error messages

Database error: ERROR:  Operation on mixed SRID geometries

Query: CLOSE qgisf0 returned 7 [ERROR:  current transaction is aborted, commands ignored until end of transaction block]

1 cursor states lost.
SQL: CLOSE qgisf0
Result: 7 (ERROR:  current transaction is aborted, commands ignored until end of transaction block)

Steps to reproduce

1. Load attached shapefile in postgis (postgis 2.0, postgres 9.1.8 on ubuntu 12.10):

2. Update the srid of all records in the table:

3. Load in new QGIS session (either 1.8 or master) using DB Manager
4. Try to identify a feature using identify features.

Workaround

1. Add a new geometry column to the table, eg

2. Transform the geometry to the new column, eg

3. Drop the old column:

4. Rename the new column to the old name:

5. Check that the following two commands give the same result:

SELECT DISTINCT ST_SRID(geom) FROM testdata;
select find_srid('public','testdata','geom');

---

• status_id was changed from Closed to Reopened

[qgib]

Author Name: Jürgen Fischer (@jef-n)

---

Fixed in changeset "f990886b61d920cf1ef82d940413e56611bc5685".

---

• status_id was changed from Reopened to Closed