[postgis-users] Closing polylines

[Nicolas Ribot]

To test the procedure more completely, I added contour lines for elevation
1000m.
Threshold distance was 100m instead of 25 in this test.
These contours have gaps between lines. 2 of them are included in the map
frame, one is cut by the frame.
Img1 shows these new contours.

At the end of step 6, some linestrings are still in the seg table: contours
entirely contained in the map frame.

• Recreate association table with remaining lines:

drop table tmp;
create table tmp as (
with closest as (
select s1.elev as elev, s1.gid, s1.path as path, s2.gid as closest,
s2.geom,
st_distance(st_collect(st_startpoint(s1.geom), st_endpoint(s1.geom)),
st_collect(st_startpoint(s2.geom), st_endpoint(s2.geom))) as dist,
row_number() over (
partition by s1.gid
order by s1.gid,
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.gid <> s2.gid
) select distinct * from closest
where r < 3 and dist < 100
order by elev, gid, r
);

• Then re-iterate to close remaining contours: brute force is used:
each remaining segment will be merged with its neighbors, leading to
several duplicate lines:
 as many duplicate as there are lines in a contour.
The final distinct clause will remove these duplicates.

drop table merged_contour2;
create table merged_contour2 as (
with recursive tab as (
select s.gid, s.elev, t.closest, array[s.gid, t.closest] as gids,
st_makeShortestLine(s.geom, t.geom) as geom, 1 as rank
from seg s, tmp t, frame f
where s.gid = t.gid
and s.elev = t.elev

UNION ALL

select tab.gid, tab.elev, tmp.closest, gids || tmp.closest,
st_makeShortestLine(tab.geom, tmp.geom) as geom, rank+1
from tmp, tab
where tmp.elev = tab.elev
and tmp.gid = tab.closest
and not (tmp.closest = any(gids))
) select distinct on (geom) geom, gid, elev, rank, gids from (
select distinct on (gid) gid, elev, rank, gids, st_forceClosed(geom) as
geom
                from (
select * from tab
order by gid, rank desc
) as foo
) as bar
);

• The final association will group all contours based on same elevation,
from our working tables:
seg_closed, seg_border, merged_contour1 and merged_contour2:

drop table if exists all_contour;
create table all_contour as (
with all_c as (
select elev, geom from merged_contour1
UNION
select elev, geom from merged_contour2
UNION
SELECT elev, geom from seg_border
UNION
select elev, geom from seg_closed
) select elev, st_union(geom) as geom
from all_c
group by elev
);

Img2 shows the final result.

Nicolas
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20120518/8b896db8/attachment.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: img2.png
Type: image/png
Size: 140047 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20120518/8b896db8/attachment.png>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: img1.png
Type: image/png
Size: 39068 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20120518/8b896db8/attachment-0001.png>