Get spatial endpoints grouped by attribute via virtual layer

Question

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.

Answer 1

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