Hi George,<div><br></div><div>My bad, I forgot that st_touches does not apply to point/point cases, according to OGC specs...</div><div><br></div><div>Here is the method I took to merge the lines sharing the same elevation:</div>
<div><br></div><div>1°) update the initial seg table to ensure that "path" column contains a unique value for each line forming a contour:</div><div><br></div><div style><font face="'courier new', monospace">update seg set path = p </font></div>
<div style><font face="'courier new', monospace">from (</font></div><div style><font face="'courier new', monospace"> select gid, row_number() over (partition by elev) as p from seg</font></div><div><font face="'courier new', monospace"><span style>) as foo where foo.gid = seg.gid;</span> </font></div>
<div><font face="'courier new', monospace"><br></font></div><div><font face="arial, helvetica, sans-serif">2°) Create the table given the 2 closest segments for each contour segment:</font></div><div><font face="arial, helvetica, sans-serif"><br>
</font></div><div><div><font face="'courier new', monospace">create table tmp as (</font></div><div><font face="'courier new', monospace"><span class="Apple-tab-span" style="white-space:pre"> </span>with closest as (</font></div>
<div><font face="'courier new', monospace"><span class="Apple-tab-span" style="white-space:pre"> </span>select s1.elev as elev, s1.path as path, s2.path as closest, </font></div><div><font face="'courier new', monospace"><span class="Apple-tab-span" style="white-space:pre"> </span>s2.geom,</font></div>
<div><font face="'courier new', monospace"><span class="Apple-tab-span" style="white-space:pre"> </span>row_number() over (</font></div><div><font face="'courier new', monospace"><span class="Apple-tab-span" style="white-space:pre"> </span>partition by s1.elev, s1.path </font></div>
<div><font face="'courier new', monospace"><span class="Apple-tab-span" style="white-space:pre"> </span>order by s1.elev, s1.path, </font></div><div><font face="'courier new', monospace"><span class="Apple-tab-span" style="white-space:pre"> </span>st_distance(st_collect(st_startpoint(s1.geom), st_endpoint(s1.geom)), st_collect(st_startpoint(s2.geom), st_endpoint(s2.geom)))) as r</font></div>
<div><font face="'courier new', monospace"><span class="Apple-tab-span" style="white-space:pre"> </span>from seg s1, seg s2</font></div><div><font face="'courier new', monospace"><span class="Apple-tab-span" style="white-space:pre"> </span>where s1.elev = s2.elev</font></div>
<div><font face="'courier new', monospace"><span class="Apple-tab-span" style="white-space:pre"> </span>and s1.path <> s2.path</font></div><div><font face="'courier new', monospace"><span class="Apple-tab-span" style="white-space:pre"> </span>) select distinct * from closest</font></div>
<div><font face="'courier new', monospace"><span class="Apple-tab-span" style="white-space:pre"> </span>where r < 3</font></div><div><font face="'courier new', monospace">);</font></div><div style="font-family:arial,helvetica,sans-serif">
<br></div><div style="font-family:arial,helvetica,sans-serif">3°) Iterate over this table and the seg table to build a complete contour line: </div><div style="font-family:arial,helvetica,sans-serif">For each contour segment, the iteration will find the closest or touching segment and merge them together, taking into account the relative segments orientation.</div>
<div style="font-family:arial,helvetica,sans-serif"><br></div><div style="font-family:arial,helvetica,sans-serif">4°) The st_ForcedClose function is used to close contours at the end:</div><div style="font-family:arial,helvetica,sans-serif">
<br></div><div><div><font face="'courier new', monospace">create table merged_contour as (</font></div><div><font face="'courier new', monospace"><span class="Apple-tab-span" style="white-space:pre"> </span>with recursive tab as (</font></div>
<div><font face="'courier new', monospace"><span class="Apple-tab-span" style="white-space:pre"> </span>-- begins with first segment of each contour: makes a line with it and its closest segment</font></div><div>
<font face="'courier new', monospace"><span class="Apple-tab-span" style="white-space:pre"> </span>-- storing already processed segments as a stop conditition</font></div><div><font face="'courier new', monospace"><span class="Apple-tab-span" style="white-space:pre"> </span>select s.elev, t.closest as path, array[s.path, t.closest] as paths,</font></div>
<div><font face="'courier new', monospace"><span class="Apple-tab-span" style="white-space:pre"> </span>-- handle segments orientation to guarantee that closest segments will be oriented</font></div><div><font face="'courier new', monospace"><span class="Apple-tab-span" style="white-space:pre"> </span>-- such that calling st_makeline(g1, g2) will connect the shortest distance</font></div>
<div><font face="'courier new', monospace"><span class="Apple-tab-span" style="white-space:pre"> </span>st_makeShortestLine(s.geom, t.geom) as geom, 1 as rank</font></div><div><font face="'courier new', monospace"><span class="Apple-tab-span" style="white-space:pre"> </span>from seg s, tmp t</font></div>
<div><font face="'courier new', monospace"><span class="Apple-tab-span" style="white-space:pre"> </span>where s.path = 1</font></div><div><font face="'courier new', monospace"><span class="Apple-tab-span" style="white-space:pre"> </span>and s.path = t.path</font></div>
<div><font face="'courier new', monospace"><span class="Apple-tab-span" style="white-space:pre"> </span>and s.elev = t.elev</font></div><div><font face="'courier new', monospace"><span class="Apple-tab-span" style="white-space:pre"> </span>and t.r = 1</font></div>
<div><font face="'courier new', monospace"><br></font></div><div><font face="'courier new', monospace"><span class="Apple-tab-span" style="white-space:pre"> </span>UNION ALL</font></div><div><font face="'courier new', monospace"><br>
</font></div><div><font face="'courier new', monospace"><span class="Apple-tab-span" style="white-space:pre"> </span>-- takes closest segment from current one </font></div><div><font face="'courier new', monospace"><span class="Apple-tab-span" style="white-space:pre"> </span>select tab.elev, tmp.closest, paths || tmp.closest, </font></div>
<div><font face="'courier new', monospace"><span class="Apple-tab-span" style="white-space:pre"> </span>st_makeShortestLine(tab.geom, tmp.geom) as geom, rank+1</font></div><div><font face="'courier new', monospace"><span class="Apple-tab-span" style="white-space:pre"> </span>from tmp, tab</font></div>
<div><font face="'courier new', monospace"><span class="Apple-tab-span" style="white-space:pre"> </span>where tmp.elev = tab.elev</font></div><div><font face="'courier new', monospace"><span class="Apple-tab-span" style="white-space:pre"> </span>and tmp.path = tab.path</font></div>
<div><font face="'courier new', monospace"><span class="Apple-tab-span" style="white-space:pre"> </span>and not (tmp.closest = any(paths)) </font></div><div><font face="'courier new', monospace"><span class="Apple-tab-span" style="white-space:pre"> </span>) select distinct on (elev) elev, rank, st_forceClosed(geom) from (</font></div>
<div><font face="'courier new', monospace"><span class="Apple-tab-span" style="white-space:pre"> </span>select * from tab </font></div><div><font face="'courier new', monospace"><span class="Apple-tab-span" style="white-space:pre"> </span>order by elev, rank desc</font></div>
<div><font face="'courier new', monospace"><span class="Apple-tab-span" style="white-space:pre"> </span>) as foo</font></div><div><font face="'courier new', monospace">);</font></div></div><div style="font-family:arial,helvetica,sans-serif">
<br></div><div style="font-family:arial,helvetica,sans-serif">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.</div><div style="font-family:arial,helvetica,sans-serif">
<br></div><div style="font-family:arial,helvetica,sans-serif">Nicolas</div></div>