[postgis-users] Query to select dangling line segments

[Brian Stempin]

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

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

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