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

[Greg Polanski]

Hello,

I have a question about snapping a huge number of linestrings such that all lines that are almost parallel and within 50m collapse into one.

I have some 500,000 streets from Openstreetmap (classified as secondary or larger roads). Most of them are made of two or more lanes. For a more compact storage and faster rendering I need only a visual representation of each road. How can I collapse for example the 6 lanes of a motorway into one linestring?

Do I have to run a loop over all roads, snapping each of them to all others within the given tolerance? This seems extremely slow. Or can it be done once for all geometries?
Do you have some SQL examples for a similar processing step?

Thanks
Greg

[Nicolas Ribot]

Hello,

I would create a table containing linestrings points (st_dumppoints)
then search for all lines whose points are within 50 meters from
another line, then either snap these points or remove the whole line.
Based on your dataset, you may have to union linestrings based on
attributes, to recreate continuous lines.

Nicolas

> _______________________________________________
> postgis-users mailing list
> postgi...@postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
_______________________________________________
postgis-users mailing list
postgi...@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

[Greg Polanski]

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?

Thanks

Greg

---

From: Nicolas Ribot <nicola...@gmail.com>
To: Greg Polanski <polans...@yahoo.com>; PostGIS Users Discussion <postgi...@postgis.refractions.net>
Sent: Monday, June 18, 2012 1:33 PM
Subject: Re: [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

[Sandro Santilli]

On Mon, Jun 18, 2012 at 04:31:18PM +0200, Nicolas Ribot wrote:
> > 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.

You could also look at ST_HausdorffDistance.

--strk;

Sent from our free software
http://www.gnu.org/philosophy/free-sw.html

[Greg Polanski]

Thank you. Nicolas! Your explanations and sql example help a lot.

Greg

---

From: Nicolas Ribot <nicola...@gmail.com>

To: PostGIS Users Discussion <postgi...@postgis.refractions.net>
Sent: Monday, June 18, 2012 4:31 PM

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

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.

...
Nicolas