[postgis-users] Query to select dangling line segments

Brian Stempin brian.stempin at gmail.com
Mon Nov 29 14:16:49 PST 2010

Hi Kevin,
Thanks for taking a stab at my problem.

Response in-line:

On Mon, Nov 29, 2010 at 4:36 PM, Kevin Neufeld <kneufeld.ca at gmail.com>wrote:

>  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)?

There are places where segments run through endpoints and segments run
through other segments.

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
>    SELECT osm_id, ST_EndPoint(way) AS pt
>    FROM "OSMData".osm_mn_data_highway_20101129_101234
> ) AS grouped
> HAVING count(*) = 1;

Yeah, I'm getting waaay too many results from this query.  I take it that
this is because my data is not fully noded?

> 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.

My data is precise enough to support this.

> ie.
> SELECT st_astext(st_collect(column1))
>   ('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)
> (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

Thanks for the stab.  I'll stew on this more when I get home.  Time to catch
the train!
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20101129/9c2ca832/attachment.html>

More information about the postgis-users mailing list