[postgis-users] Help with Query

[Stephen Woodbridge]

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