2

I use QGIS 3.4 on Windows 10

I have a polygon layer and a point layer. The points have a streetname attribute. I want to check if there are polygons with more then 1 streetname.

I took 2 steps, first Join attributes by location to get the streetnames as attribute in the polygons. The second step is a self join in a virtual layer

SELECT T1.POLid, T1.Postalcode, T1.streetname, T2.streetname
FROM TestStreet AS T1, TestStreet AS T2
WHERE T1.POLid = T2.POLid AND T1.streetname <> T2.streetname;

I have to interrupt the processing after waiting very long.

My workaround is:

Save features As csv file
Import csv file in MSAccess
MSAccess gives instantaneous results with the same query.

What is it what I do wrong in QGIS, is there a better way just with QGIS?

1 Answer 1

1

The bottleneck is likely with the join by attribute being re-evaluated many times.

You can skip this step and compute everything at once in a virtual layer. As a bonus, it will tell you if you have more than 2 streets in a polygon. Eventually, you can filter the entries having a single street name

SELECT p.polyID, COUNT(DISTINCT s.streetName) as cnt, GROUP_CONCAT(DISTINCT s.streetName) as names
FROM myPolygonLayer p
JOIN myStreetLayer s ON ST_INTERSECTS(p.geometry, s.geometry)
GROUP BY p.polyID
--HAVING COUNT(DISTINCT s.streetName) >= 2
3
  • The first step join attribute by location was fast, the virtual layer part was very slow. I get the following error by copy and pasting your code (I only changed the layer- and filednames: Query preparation error on SELECT "COUNT(DISTINCT s.openbare_ruimte)", "GROUP_CONCAT(DISTINCT s.openbare_ruimte, ';')" FROM _tview LIMIT 1: DISTINCT aggregates must have exactly one argument
    – Jan
    Oct 1, 2020 at 19:06
  • @Jan see edits with the removed separator (not too sure why it complains when Distinct is there)
    – JGH
    Oct 1, 2020 at 20:06
  • I took your edits, now when I hit TEST there is no error message but I stopped the proces after several minutes.
    – Jan
    Oct 1, 2020 at 21:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.