[postgis-users] Closing polylines

[Nicolas Ribot]

Hi,

As I understand, you want to connect together several segments sharing
a same elevation ?
Are the segments composing a contour ordered in the collection (i.e.
forming the contour in the right order) (img1) ?

If so, you could dump the lines composing the collection and pass them
to makeLine and to st_forceClosed provided by Mike:

with lines as (
	select elev, (st_dump(geom)).geom as geom
	from contour
	order by elev, (st_dump(geom)).path
) select elev, st_makeLine(geom) as geom
from lines
group by elev;

if segments are not ordered (cf. img2), it could be trickier
(I'm sure an easier approach exists...):

Assuming gaps between segments are short compare to segments
themselves, you could find, for each segment, the 2 closest segments
with same elevation. Reserve in a table.
Then, with a recursive query, rebuild each segment by connecting it
with its closest friend, merging the 2 segments based on their
shortest gap:

Table OC contains contour lines with gaps (cf. img3)

-- first we prepare a table of single segments for each contour,
keeping individual path, by dumping geometries
create table seg as (
	select elev, (st_dump(geom)).path[1] as path, (st_dump(geom)).geom as geom
	from oc
);

-- then the table giving, for each contour segment, the 2 closest
contour with the same elevation
create table tmp as (
	with closest as (
		select s1.elev as elev, s1.path as path, s2.path as closest,
		s2.geom,
		rank() over (partition by s1.elev, s1.path order by s1.elev,
s1.path, st_distance(s1.geom, s2.geom)) as r
		from seg s1, seg s2
		where s1.elev = s2.elev
		and s1.path <> s2.path
	) select * from closest
	where r < 3
);

-- The recursive query that will complete each contour segment by
joining it with its closest segment.
-- Only the last linestrings returned by this function for each
elevation are interesting: they are the most complete contour lines
-- st_forceClosed is used to close contours at the end.
-- (It uses a helper function that connects 2 linestrings based on
their shortest gap)

with recursive tab as (
	select s.elev, t.closest as path, array[s.path, t.closest] as paths,
	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 || tab.path,
	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;

Img4 gives the final result.
This approach does not work well if several contour lines share the
same elevation. Some other attributes have to be defined to identify
contour lines, in this case.

The st_makeShortestLine function definition:

create or replace function st_makeShortestLine(g1 geometry, g2
geometry) returns geometry as $$

	with dist as (
		select  st_distance(st_endPoint($1), st_endPoint($2)) as dee,
	        st_distance(st_endPoint($1), st_startPoint($2)) as des,
	        st_distance(st_startPoint($1), st_endPoint($2)) as dse,
	        st_distance(st_startPoint($1), st_startPoint($2)) as dss
	) select case when dee < des and dee < dse and dee < dss then
st_makeline($1, st_reverse($2))
		      when des < dee and des < dse and des < dss then st_makeline($1, $2)
		      when dss < dee and dss < dse and dss < des then
st_makeline(st_reverse($1), $2)
		      else st_makeline($2, $1) end
	from dist;
$$ language SQL;

Nicolas


On 11 May 2012 23:53, george wash <gws293 at hotmail.com> wrote:
> Hi, does anyone know of a function or algorithm which will help me in
> closing open multilinestrings (contours) (I am using Postgis 2.0, Win 7 64).
> My contours are made up of several segments, all multilinestrings, which
> sometime are closed but often are open at several places along the same
> contour height.
> I can close them when the are open at the edge of the map by using a section
> of the edge as the closing segment but the real problem is when the contours
> are inside the map, e.g. when the several segments making up the contour do
> not share common start or end points but have a gap between them.
> Any suggestions would be most welcome.
> Thank you
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: img1.png
Type: image/png
Size: 20590 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20120513/3a1cc19a/attachment.png>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: img2.png
Type: image/png
Size: 41340 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20120513/3a1cc19a/attachment-0001.png>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: img3.png
Type: image/png
Size: 38462 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20120513/3a1cc19a/attachment-0002.png>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: img4.png
Type: image/png
Size: 42833 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20120513/3a1cc19a/attachment-0003.png>