[postgis-users] Query to select dangling line segments

[Kevin Neufeld]

On 11/29/2010 11:54 AM, Brian Stempin wrote:
> I want to select rows who's geometry's start/end point does not 
> intersect *anything*

Is your dataset fully noded (where the only intersection between 
geometries occur at the endpoints)?

If so, you could perform a very quick GROUP BY query to find degree-1 
nodes in your network.

SELECT min(osm_id), pt
FROM (
    SELECT osm_id, ST_StartPoint(way) AS pt
    FROM "OSMData".osm_mn_data_highway_20101129_101234

    UNION ALL

    SELECT osm_id, ST_EndPoint(way) AS pt
    FROM "OSMData".osm_mn_data_highway_20101129_101234
) AS grouped
GROUP BY pt
HAVING count(*) = 1;


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.
ie.
SELECT st_astext(st_collect(column1))
FROM ( VALUES
   ('POINT(0 0)'::geometry),
   ('POINT(0 1)'::geometry),
   ('POINT(0 0.0000001)'::geometry)
) AS foo
GROUP BY column1;
         st_astext
-------------------------
  MULTIPOINT(0 0,0 1e-07)
  MULTIPOINT(0 1)
(2 rows)

See how the first and last points have the same bounding box though 
different geometries?.

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)

Cheers,
Kevin


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