<!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 11:54 AM, Brian Stempin wrote:
<blockquote
cite="mid:AANLkTik+fiK3zKFy4GSbTj+vtx=JoFSxq1v0qqsgU48E@mail.gmail.com"
type="cite"><span class="Apple-style-span" style="font-family:
arial,sans-serif; font-size: 13px; border-collapse: collapse;">I
want to select rows who's geometry's start/end point does not
intersect *anything*</span></blockquote>
<br>
Is your dataset fully noded (where the only intersection between
geometries occur at the endpoints)?<br>
<br>
If so, you could perform a very quick GROUP BY query to find
degree-1 nodes in your network.<br>
<br>
<tt>SELECT min(osm_id), pt<br>
FROM (<br>
SELECT osm_id, ST_StartPoint(way) AS pt<br>
FROM "OSMData".osm_mn_data_highway_20101129_101234 <br>
<br>
UNION ALL<br>
<br>
SELECT osm_id, ST_EndPoint(way) AS pt<br>
FROM "OSMData".osm_mn_data_highway_20101129_101234 <br>
) AS grouped<br>
GROUP BY pt<br>
HAVING count(*) = 1;</tt><br>
<br>
<br>
Note though, that this GROUP BY approach uses the bounding boxes of
the endpoints, *not* the geometries themselves. So for this to
work, the precision of your dataset must be less than what can be
represented in the float4 representation of the bounding box.<br>
ie.<br>
<tt>SELECT st_astext(st_collect(column1)) <br>
FROM ( VALUES <br>
('POINT(0 0)'::geometry), <br>
('POINT(0 1)'::geometry), <br>
('POINT(0 0.0000001)'::geometry)<br>
) AS foo <br>
GROUP BY column1;<br>
st_astext <br>
-------------------------<br>
MULTIPOINT(0 0,0 1e-07)<br>
MULTIPOINT(0 1)<br>
(2 rows)</tt><br>
<br>
See how the first and last points have the same bounding box though
different geometries?.<br>
<br>
As long as your data is fully noded and the precision is less than
what can be represented as a float4, then this approach works very
fast (no expensive spatial predicate operations)<br>
<br>
Cheers,<br>
Kevin<br>
<br>
<br>
</body>
</html>