[postgis-users] Closing polylines

[Nicolas Ribot]

Hi George,

My bad, I forgot that st_touches does not apply to point/point cases,
according to OGC specs...

Here is the method I took to merge the lines sharing the same elevation:

1°) update the initial seg table to ensure that "path" column contains a
unique value for each line forming a contour:

update seg set path = p
from (
      select gid, row_number() over (partition by elev) as p from seg
) as foo where foo.gid = seg.gid;

2°) Create the table given the 2 closest segments for each contour segment:

create table tmp as (
with closest as (
select s1.elev as elev, s1.path as path, s2.path as closest,
s2.geom,
row_number() over (
partition by s1.elev, s1.path
order by s1.elev, s1.path,
st_distance(st_collect(st_startpoint(s1.geom), st_endpoint(s1.geom)),
st_collect(st_startpoint(s2.geom), st_endpoint(s2.geom)))) as r
from seg s1, seg s2
where s1.elev = s2.elev
and s1.path <> s2.path
) select distinct * from closest
where r < 3
);

3°) Iterate over this table and the seg table to build a complete contour
line:
For each contour segment, the iteration will find the closest or touching
segment and merge them together, taking into account the relative segments
orientation.

4°) The st_ForcedClose function is used to close contours at the end:

create table merged_contour as (
with recursive tab as (
-- begins with first segment of each contour: makes a line with it and its
closest segment
-- storing already processed segments as a stop conditition
select s.elev, t.closest as path, array[s.path, t.closest] as paths,
-- handle segments orientation to guarantee that closest segments will be
oriented
-- such that calling st_makeline(g1, g2) will connect the shortest distance
st_makeShortestLine(s.geom, t.geom) as geom, 1 as rank
from seg s, tmp t
where s.path = 1
and s.path = t.path
and s.elev = t.elev
and t.r = 1

UNION ALL

-- takes closest segment from current one
select tab.elev, tmp.closest, paths || tmp.closest,
st_makeShortestLine(tab.geom, tmp.geom) as geom, rank+1
from tmp, tab
where tmp.elev = tab.elev
and tmp.path = tab.path
and not (tmp.closest = any(paths))
) select distinct on (elev) elev, rank, st_forceClosed(geom) from (
select * from tab
order by elev, rank desc
) as foo
);

Attached image shows the final result: The 3 contours are linestring. I
will create some gaps in the dataset to see if this code can handle both
cases.

Nicolas
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20120517/c0a3f464/attachment.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: Screen Shot 2012-05-17 at 1.43.24 PM.png
Type: image/png
Size: 52748 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20120517/c0a3f464/attachment.png>