3

I have a set of lines consisting of several single object LineStrings. These line can be collected to routes by attribute, e.g. a street consisting of multiple lines from crossing to crossing, but they all have the same street name.

screenshot1

I can query the start and endpoints of a route with the following virtual layer query:

select id, sts as route_id, st_endpoint(geometry) as geometry, 2 as type from pointclasstest
union
select id, sts as route_id, st_startpoint(geometry) as geometry, 1 as type from pointclasstest

But this yet gives me ALL start & end points from each LineString (see line 101). I only want to get the start and end of each route. adding a group by sts does indeed only give me one start and end point per route, but not necessarily the spatially correct points (the endpoint can be in the middle of the route) as the query dooes not know which one to keep and which to drop.

So how does a query look that chooses the spatially correct start and endpoints?

Constraints:

  • It has to be made via virtual layer
  • I cannot convert the input layer to a multilinestring (although processing through a virtual layer making routes as multilines might be an option?)

This question is a follow-up to a previous question of mine.

1 Answer 1

2

In most situations, the "start" and "end" points would be connected to a single segment of a given street. You can therefore extract the start point and exclude the ones that touch a different segment of the same street. Same for the endpoint.

Beware though that the definition of "start" and "end" doesn't worth much when talking about multiple connected segments, as the digitization direction can be different between segments.

Also such approach would give multiple points if a street has disconnected parts, or no points at all if a street is connected to itself.

select
a.line_id, a.id, ST_STARTPOINT(a.geometry), 'startish' as position
FROM myLineLayer a
WHERE NOT EXISTS (
    SELECT 1
    FROM myLineLayer ln
    WHERE a.line_id = ln.line_id
    AND a.id != ln.id
    AND st_intersects(ST_STARTPOINT(a.geometry), ln.geometry)
    )
UNION ALL
select
a.line_id, a.id, ST_ENDPOINT(a.geometry), 'endish' as position
FROM myLineLayer a
WHERE NOT EXISTS (
    SELECT 1
    FROM myLineLayer ln
    WHERE a.line_id = ln.line_id
    AND a.id != ln.id
    AND st_intersects(ST_ENDPOINT(a.geometry), ln.geometry)
    )

enter image description here

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.