3

In QGIS 2.18 project there are several layers, lines and polygons respectively

input

My task is to select all lines when their start and end points are laying within polygons using Virtual Layer, see image below.

result

I can achieve the result that I want with the following query

WITH start_p AS (
SELECT l.id AS start_id
FROM line AS l, "Layer_2" AS p
WHERE ST_within(start_point(l.geometry),p.geometry)
),

end_p AS (
SELECT l.id AS end_id
FROM line AS l, "Layer_2" AS p
WHERE ST_within(end_point(l.geometry),p.geometry)
)

SELECT *
FROM line AS l, start_p, end_p
WHERE l.id = start_p.start_id
AND l.id = end_p.end_id

My Question: Why I can not achieve the same result using the following query

SELECT l.*
FROM line AS l, "Layer_2" as p
WHERE ST_within(end_point(l.geometry),p.geometry)
AND ST_within(start_point(l.geometry),p.geometry)

However, a query like (see below) will work

SELECT l.*
FROM line AS l, "Layer_2" as p
WHERE ST_within(start_point(l.geometry),p.geometry)

example

2 Answers 2

4

If you refer to Layer_2 only once, the query would return lines that start and end in the same polygon, as the p.geometry used in the two where clause conditions must be the same.

You could however change the query to use joins

SELECT l.*
FROM line AS l
JOIN "Layer_2" p1 ON ST_within(end_point(l.geometry),p1.geometry)
JOIN "Layer_2" p2 ON ST_within(start_point(l.geometry),p2.geometry)
2
  • To sum up, this is a direct example of what I have asked before Difference between JOIN ON and WHERE in Virtual Layers in QGIS, am I right?
    – Taras
    Jun 25, 2019 at 10:49
  • No. Regardless of the joining method, you can access 1 row at a time. If you want to compare 2 rows (2 different polygons), you would need 2 joins
    – JGH
    Jun 25, 2019 at 10:59
3

After a bit of theory from @JGH, I ended up with one additional query that will lead to the desired output

SELECT l.*
FROM line AS l, "Layer_2" as p1, "Layer_2" as p2
WHERE ST_within(end_point(l.geometry),p1.geometry)
AND ST_within(start_point(l.geometry),p2.geometry)
2
  • Taras, are you sure that your request works correctly? Jun 27, 2019 at 6:35
  • Cyril, it gives me an output, however, I am not that much sure if this query is correct/coherent.
    – Taras
    Jun 27, 2019 at 6:40

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.