[postgis-users] Help with Query

[Stephen Woodbridge]

On 1/11/2013 8:31 PM, Clifford Snow wrote:
> I tried something similar, but the problem is streams are made of up
> various length linestrings. Some have only two nodes but are part of
> what makes up the entire stream. The ones I looking for are just short
> lengths with only one end near the main stream. for example if the
> following is a stream:
>
> 1         2             3               4              5         6
> o---------o-------------o---------------o--------------o---------o
>                         |
>                         |
>                         o
>                         a
>

How do you differentiate 3-a from 1-2 or 5-6? the later two could also 
be two node less than 25' segments? If the flow is to node 1 them you 
might be able to eliminate 1-2 because of the flow.

So you strategy could be:

1. find all nodes that have only on edge associated with them. In this 
model that would be nodes 1, 6, and a and may be filter the edges to be 
short edges only. the following sql is close to this:

select a.pt, count(*) as cnt
   from
        ( select st_startpoint(geom) as pt from flowlines
          union
          select st_endpoint(geom) as pt from flowlines
        ) as a,
        flowlines b
   where st_dwithin(a.pt, b.geom, 0.000001)
         and st_length(b.geom) <  <maxlen>
   group by a.pnt
   having cnt = 1;

2. if your selection criteria is a branch off the main line, rather than 
and extension of the main line, then you can eliminate the end segments 
that have only a sing edge connecting to the far end. This would 
eliminate 5-6 because 5 has too edges, but 3 has 3 edges. You have to 
decide what to do if 5 has a branch to 7 also.

So we should be able to use the points above to select the edges of the 
dangling segments, then get the opposite end point, and count the edges 
connected to the opposite end. The SQL is more complex for this part and 
I need to think more about how to do that.

-Steve

> I'm looking to capture the extension "a" above. It's possible that I'll
> also capture "6" but these short linestrings are always two nodes and
> are less than 25' in length.  However, there are thousands of short two
> node lines strings that are part of the stream.
>
>
> On Fri, Jan 11, 2013 at 5:03 PM, Stephen Woodbridge
> <woodbri at swoodbridge.com <mailto:woodbri at swoodbridge.com>> wrote:
>
>     On 1/11/2013 7:20 PM, Clifford Snow wrote:
>
>         First off, I'm a new at Postgis so please let me know if the right
>         response to my question is RTFM.
>
>         I have the NHD streams for my state that I want to import into
>         OpenStreetMap. When I select just the streams that I'm
>         interested in I
>         find that there are numerous short linestrings (2 nodes) that
>         I'd like
>         to get rid of. These linestrings are only connected at one end.  It
>         seems like ST_Touches() would find them. I did a simple query of
>
>         SELECT s1.gid, s2.gid from flowlines s1, flowlines s2 WHERE
>         ST_Touches(s1.geom,ST_PointN(__s2.geom,1) and s2.pointcount = 2;
>
>         It returns 0 rows. Changing the point on s2.geom to 0 makes no
>         difference.
>
>         Anyone have a suggestion of how I find 2 node streams that
>         connect to
>         another stream?
>
>
>     You might try creating a topology. I have not actually done this
>     myself since I using pgRouting for a lot of stuff and it has its own
>     topology tools for building graphs that I'm more familiar with.
>
>     You also might try using st_dwithin( geom1, geom2, tolerance )
>     where tolerance needs to be in the units of your data.
>
>     select s1.gid, s2.gid
>
>        from flowlines s1, flowlines s2
>       where s2.pointcount = 2
>             and st_dwithin(s1.geom, s2.geom, 0.000001 )
>             and s1.gid != s2.gid;
>
>     YMMV,
>        -Steve
>     _________________________________________________
>     postgis-users mailing list
>     postgis-users at lists.osgeo.org <mailto:postgis-users at lists.osgeo.org>
>     http://lists.osgeo.org/cgi-__bin/mailman/listinfo/postgis-__users
>     <http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users>
>
>
>
>
> --
> Clifford
>
> OpenStreetMap: Maps with a human touch
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>