2

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

3

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

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)
  • Taras, are you sure that your request works correctly? – Cyril Jun 27 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 at 6:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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