Hi George,<div><br></div><div>Beside the st_polygonize function I used yesterday, I made some other tests based on the previous code I sent, here are some remarks concerning this code:</div><div><br></div><div><div>First, as I understand your data, they are coming from a shapefile format in which each feature is a simple line.</div>
<div>Maybe a loading with shp2pgsql created multilinestrings out of your linestrings.</div><div>You can check if the contour data are indeed simple lines by running:</div><div><br></div><div>select count(distinct st_numgeometries(geom)) from seg;</div>
<div><br></div><div>If its returns 1, then all your segments are simple.</div><div><br></div><div>That may explain why all "path" values in the seg table are equal to 1. The purpose of this column is to contain one distinct value for each segment composing a contour line.</div>
<div><br></div><div>So first, based on your seg table, I updated the path value to generate unique value for each segment (I could have used the sid column in your data as it seems to represent a unique identifier for a row, but I kept "path" to keep in line with the code)</div>
<div><br></div><div>update seg set path = p </div><div>from (</div><div> select gid, row_number() over (partition by elev) as p from seg</div><div>) as foo where foo.gid = seg.gid;</div><div> </div><div>Then, based on the particular topology of this dataset, where segments composing contour lines are touching each other, I modified a bit the query I sent previously:</div>
<div>To recall, this query creates a table listing, for each segment, the 2 other closest segments:</div><div><br></div><div>create table tmp as (</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>with closest as (</div>
<div><span class="Apple-tab-span" style="white-space:pre"> </span>select s1.elev as elev, s1.path as path, s2.path as closest, </div><div><span class="Apple-tab-span" style="white-space:pre"> </span>s2.geom,</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>row_number() over (</div>
<div><span class="Apple-tab-span" style="white-space:pre"> </span>partition by s1.elev, s1.path </div><div><span class="Apple-tab-span" style="white-space:pre"> </span>order by s1.elev, s1.path, </div><div><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</div>
<div><span class="Apple-tab-span" style="white-space:pre"> </span>from seg s1, seg s2</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>where s1.elev = s2.elev</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>and s1.path <> s2.path</div>
<div><span class="Apple-tab-span" style="white-space:pre"> </span>) select distinct * from closest</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>where r < 3</div><div>);</div><div><br></div><div>
The main difference is that it uses row_number() instead of rank() window function: as segments are touching, distance returns 0 for 2 segments, so the rank is 1 for both of them.</div><div>row_number will give us a unique value in all the cases (touches, or just close segments).</div>
<div><br></div><div>I also had to slightly change the st_makeClosedLine to also cope with touching segments. I still have one bug, but I will send you the new version when I find it.</div><div><br></div><div>It should then be able to handle cases where lines are not ordered, are touching each other or have gaps between the (whatever the points orientation is).</div>
<div><br></div><div>I'll keep you posted.</div><div><br></div><div>Nicolas</div><div><br></div><br><div class="gmail_quote">On 17 May 2012 09:41, georgew <span dir="ltr"><<a href="mailto:gws293@hotmail.com" target="_blank">gws293@hotmail.com</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">Nicolas, your code is really magic in the way it closes those segments even<br>
though they are in random sequence! I am beginning to think that I am going<br>
to need a combination of all the solutions proposed above to cover all the<br>
various contour scenarios, including those with gaps, without gaps, open<br>
against one map edge or multiple edges, fully enclosed with all segments<br>
already in sequence or in random sequence and/or with inverse point order.<br>
Brent, your links and examples may well be another strategy, I have a lot to<br>
learn, but I will start by trying to adapt your script.<br>
All this is going to take me a bit of time to evaluate, but with your help<br>
so far I am way ahead of where I was a week ago.<br>
Thanks again, I will not fail to take up your offer of help if I get stuck!.<br>
George<br>
<br>
--<br>
View this message in context: <a href="http://postgis.17.n6.nabble.com/Closing-polylines-tp4971098p4991411.html" target="_blank">http://postgis.17.n6.nabble.com/Closing-polylines-tp4971098p4991411.html</a><br>
Sent from the PostGIS - User mailing list archive at Nabble.com.<br>
<div class="HOEnZb"><div class="h5">_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br>
<a href="http://postgis.refractions.net/mailman/listinfo/postgis-users" target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>
</div></div></blockquote></div><br></div>