<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html; charset=ISO-8859-1"
http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
On 11/29/2010 2:16 PM, Brian Stempin wrote:
<blockquote
cite="mid:AANLkTinCBq7OP1Kf7kvREQZCQo1DODTPL2UvvK9JTmTL@mail.gmail.com"
type="cite">
<blockquote class="gmail_quote" style="margin: 0pt 0pt 0pt 0.8ex;
border-left: 1px solid rgb(204, 204, 204); padding-left: 1ex;">
<div bgcolor="#ffffff" text="#000000">Is your dataset fully
noded (where the only intersection between geometries occur at
the endpoints)?<br>
</div>
</blockquote>
<div><br>
</div>
<div>There are places where segments run through endpoints and
segments run through other segments.</div>
</blockquote>
<br>
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).<br>
<br>
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.<br>
ie.<br>
<br>
<tt>-- list all ids where the startpoint doesn't intersect<br>
SELECT osm_id <br>
FROM "OSMData".osm_mn_data_highway_20101129_101234 t1<br>
WHERE osm_id NOT IN (<br>
<br>
-- list all ids where the startpoint intersects something.<br>
SELECT t1.osm_id<br>
FROM "OSMData".osm_mn_data_highway_20101129_101234 t1,<br>
"OSMData".osm_mn_data_highway_20101129_101234 t2<br>
WHERE t1.osm_id <> t2.osm_id<br>
AND ST_Intersects(ST_StartPoint(t1.way), t2.way)<br>
<br>
)<br>
<br>
-- do the same for endpoints.</tt><br>
<br>
Alternatively, you should get similar results by doing a LEFT JOIN
and filter all cases that don't match.<br>
<tt>-- This is of course untested, but here's the idea</tt><br>
<tt>SELECT t1.osm_id<br>
FROM "OSMData".osm_mn_data_highway_20101129_101234 t1<br>
LEFT JOIN "OSMData".osm_mn_data_highway_20101129_101234 t2<br>
ON (t1.osm_id <> t2.osm_id AND <br>
ST_Intersects(ST_StartPoint(t1.way), t2.way))<br>
WHERE t2.osm_id IS NULL;</tt><br>
<br>
Cheers,<br>
Kevin<br>
<br>
</body>
</html>