[postgis-users] snapping all close-by lines in a set of 500, 000

[Nicolas Ribot]

> Hello Nicolas,
> thank you for the reply. I don't have enough experience with postgis to
> understand how your suggestion helps.
> Do you suggest to loop over all linestrings (which is really slow!) after
> st_dumppoints, or is there a single command that does the snapping at
once?
> What is the advantage of the intermediate points table? st_snap works
with a
> given tolerance, or st_distance can help to select close-by lines... where
> do the points come in?
>

I find useful to explode a linestring into its points to be able to
identify pseudo-parallel lines: st_distance is not enough as perpendicular,
touching lines will return 0 for the distance though they are not close to
each other at each location.
If all linestring points are within a given distance to a line, then
chances are good these 2 lines are aligned.

This dump will be quite fast, though it will generate a big table (500 000
linestrings are still light to work with).
st_snap will reduce precision for all your dataset. To identify lines that
should be snapped or removed, the comparison between original lines and the
point-dumped counterparts will be useful:

-- creates the points table. Test contains original linestrings
create table line_points as (
select gid, (st_dumppoints(geom)).*
from test
);

-- finds all lines whose all points are closer than 50 meters to other lines
with close_points as (
select t.gid, l.gid as lgid, count(l.path[2])
from test t, line_points l
where st_dwithin(t.geom, l.geom, 50)
and t.gid <> l.gid
group by t.gid, lgid
) select t.gid, t.geom
from close_points c, test t
where count = st_npoints(t.geom)
and t.gid = c.lgid;

As said, this naive approach does not deal well with segmentized
linestrings. It may then be useful to union these linestrings based on road
attributes to generate "long" streets.

Nicolas
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20120618/2e5fa68a/attachment.html>