Bug report #18038: Select by Location not working when data is in PostGIS - QGIS Application - QGIS Issue Tracking

Bug report #18038

Select by Location not working when data is in PostGIS

Added by Randal Hale almost 7 years ago. Updated over 6 years ago.

Status:	Closed
Priority:	Normal
Assignee:	Nyall Dawson
Category:	Processing/QGIS
Affected QGIS version:	master	Regression?:	No
Operating System:	Ubuntu 17.10	Easy fix?:	No
Pull Request or Patch supplied:	Yes	Resolution:
Crashes QGIS or corrupts data:	No	Copied to github as #:	25934

Description

So this might take a bit to explain - bear with me. Attached to this report is two datasets: points and one polygon. Projection is EPSG:102761. The datasets reside in Postgis. I cannot execute a select by location.

If I set the geoprocessing tool up to select all the points that are within the polygon - it gives me a log:
Processing algorithm...
Algorithm 'Select by location' starting...
Input parameters: { 'INPUT' : 'dbname=\'usvi\' host=gis4 port=5432 user=\'rjhale\' password=\'9wikufi6\' sslmode=disable key=\'id\' srid=102761 type=Point table=\'data\'.\'building_x\' (geom) sql=', 'PREDICATE' : [6], 'INTERSECT' : QgsProcessingFeatureSourceDefinition('estates20170930162603312', True), 'METHOD' : 0 }

Execution completed in 0.03 seconds
Results: {'OUTPUT': <qgis._core.QgsVectorLayer object at 0x7fcdf4b0edc8>}

Loading resulting layers
Algorithm 'Select by location' finished

Nothing is selected.

If I export to shapefile:
Processing algorithm...
Algorithm 'Select by location' starting...
Input parameters: { 'INPUT' : '/home/rjhale/temp/qgis_bug/building.shp', 'PREDICATE' : [6], 'INTERSECT' : '/home/rjhale/temp/qgis_bug/estate.shp', 'METHOD' : 0 }

Execution completed in 0.18 seconds
Results: {'OUTPUT': <qgis._core.QgsVectorLayer object at 0x7ff0fb2dfdc8>}

Loading resulting layers
Algorithm 'Select by location' finished

It acts normally and selects points within the polygon.

Versions of Post*:
Postgresql 10.1
postgis 2.4

It worked if the data was in geopackage. So to me this points to PostGIS. PostGIS resides on a 16.04 Box while QGIS sits on a 17.10 Ubuntu box.

bug.zip - Data in EPSG 102761 (31.2 KB) Randal Hale, 2018-02-03 10:36 PM

test.sql (5.63 KB) Randal Hale, 2018-04-10 03:06 AM

Associated revisions

Revision 1357a471
Added by Nyall Dawson over 6 years ago

[processing] Fix Select/Extract by Location algs with PostGIS layers

Fixes #18038

Revision 1e4d32f8
Added by Nyall Dawson over 6 years ago

[processing] Fix Select/Extract by Location algs with PostGIS layers

Fixes #18038

(cherry-picked from 1357a4716)

History

#1 Updated by Nyall Dawson almost 7 years ago

Status changed from Open to Feedback

Do you have issues with any other selection by location tools and this layer? E.g. using the "select by polygon" map tool? Maybe there's a spatial index error in the layer.

#2 Updated by Randal Hale almost 7 years ago

So I did a few other tests. Apologies - I didn't see your reply until this morning.

Select by polygon 'lasso' works fine.
I rebuilt the index on the buildings layer and the estates layer - same result as before .
Went to a different database I hadn't touched in a while and did the same "select by location" exercise and same result (polygon selecting points - still had a very short runtime also - .07 seconds - nothing selected (with an easier to deal with projection EPSG:2274)).
ran a st_within from dbmanager and that worked.

It could be on my end - I'm not an export on Postgis by any stretch and I could have something set up wrong. I don't think I do - but I defer to someone higher up on the foodchain.

Let me get qgis 2.99 installed on my windows 10 box and I'll test it against the database.

#3 Updated by Tudor Bărăscu almost 7 years ago

Indeed, I'm also getting this.
I have a point and a polygon layer and I'm trying to select all points from a specific polygon.
Nothing is selected. Debian Buster, latest master.

#4 Updated by google feud almost 7 years ago

classic MapQuest driving directions, Route Planner, mileage Driving is the easiest and most efficient way to travel between two points – but only if you're really good with directions. Most of us aren't and thus end up circling around the neighborhood or lost in the countryside. Luckily, with Google driving directions getting lost is no longer an option. Apart from showing you the exact classic MapQuest driving directions, Route Planner, mileage mapping category, recently announced the relaunch of its popular Route Planner, a patented technology that simplifies the process of creating, optimizing and manipulating complex routes with multiple stops classic MapQuest driving directions, Route Planner, mileage mapquest

#5 Updated by Randal Hale over 6 years ago

I went back and did some more testing. If I run the test with the current 2.18.17 (windows/linux) it works fine - select by location performs as intended. On 3.0.1 (windows/linux) it doesn't return anything buyt doesn't produce an error. If the dataset is removed from postgis and moved to geopackage it performs as intended on 3.0.1

#6 Updated by Nyall Dawson over 6 years ago

Can you provide a sql dump of the postgis tables used?

#7 Updated by Randal Hale over 6 years ago

File test.sql added

OK - finally got a file to upload. I had to use some stuff that wasn't client sensitive. The data was something I made - it's in EPSG:2274. I dumped from my database by pg_dump -h computer -d testnrgs > test.sql. You should be able to psql -d database -f test.sql. I did test with qgis 2.18 and qgis 3.0 and I hope (I mean I don't hope) you find the select by location doesn't quite work right.