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
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
