[postgis-users] Query to select dangling line segments

[Kevin Neufeld]

On 11/29/2010 2:16 PM, Brian Stempin wrote:
>
>     Is your dataset fully noded (where the only intersection between
>     geometries occur at the endpoints)?
>
>
> There are places where segments run through endpoints and segments run 
> through other segments.

Ah.  Then in that case the GROUP BY approach won't work for you.  I used 
it quite successfully over a large fully-noded stream network I used to 
work on where I needed to look for locations of interest (head waters, 
degree-2 nodes, confluences of 3 streams coming together, etc).

So for you, what if you reversed the logic of your query?  Do a 
self-join as you specified, but look for the ids that intersect and then 
negate it.
ie.

-- list all ids where the startpoint doesn't intersect
SELECT osm_id
FROM "OSMData".osm_mn_data_highway_20101129_101234 t1
WHERE osm_id NOT IN (

    -- list all ids where the startpoint intersects something.
    SELECT t1.osm_id
    FROM "OSMData".osm_mn_data_highway_20101129_101234 t1,
    "OSMData".osm_mn_data_highway_20101129_101234 t2
    WHERE t1.osm_id <> t2.osm_id
    AND ST_Intersects(ST_StartPoint(t1.way), t2.way)

)

-- do the same for endpoints.

Alternatively, you should get similar results by doing a LEFT JOIN and 
filter all cases that don't match.
-- This is of course untested, but here's the idea
SELECT t1.osm_id
FROM "OSMData".osm_mn_data_highway_20101129_101234 t1
    LEFT JOIN "OSMData".osm_mn_data_highway_20101129_101234 t2
       ON (t1.osm_id <> t2.osm_id AND
           ST_Intersects(ST_StartPoint(t1.way), t2.way))
WHERE t2.osm_id IS NULL;

Cheers,
Kevin

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20101130/d9f6ed4b/attachment.html>