<div dir="ltr"><div>Two really interesting and educational examples! Thank you both very much. I learned a lot from both. Nicolas's is obviously a simpler. It produces a few more linestrings than Rémi's because Rémi's connects more segments together. But both accomplish what I want to do. Rémi's example is a very elegant and complete example - thank you for taking the time. I like the way you dynamically created test data and also snapping is a good idea that I would have overlooked.<br>
<br>@Roxanne, thanks for your interest in my problem, sorry that I didn't explain better. When you draw polygon layers with dashed or dotted line styles they can look very bad because lines from adjacent polygons are drawn over top of each other and the symbols (dots, dashes, etc.) do not align. So I just want all the unique linestrings so that they will only be drawn once. I want linestrings, not just individual segments, for cartographic appearance and for performance. And since it's just for cartography, I don't care about attributes, line direction, or topology.<br>
<br></div>Again, thank you very much. Your answers both solved my immediate problem and taught me a lot.<br><br>Rich<br><div><br></div></div><div class="gmail_extra"><br><br><div class="gmail_quote">On Fri, May 23, 2014 at 4:32 AM, Nicolas Ribot <span dir="ltr"><<a href="mailto:nicolas.ribot@gmail.com" target="_blank">nicolas.ribot@gmail.com</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir="ltr"><div>Hi Richard,</div><div><br></div><div>You could use st_node on the polygon boundaries to rebuild unique, complete linestrings:</div>
<div>Boundaries are dumped into linestrings in order to build a Multilinestring containing all PG boundaries.</div>
<div>st_node is performed on this collection, then result is dumped into individual lines:</div><div><br></div><div>with tmp as (</div><div> select (st_dump(st_boundary(geom))).geom</div><div> from mytable</div><div>
) select nexval('seq01') as id, (st_dump(st_node(st_collect(geom)))).geom</div><div>from tmp;</div><div><br></div><div>If your dataset is big, a cut-by-grid approach could speed up the process.</div><span class="HOEnZb"><font color="#888888"><div>
<br></div>
<div>Nicolas<br></div></font></span></div><div class="HOEnZb"><div class="h5"><div class="gmail_extra"><br><br><div class="gmail_quote">On 23 May 2014 12:26, Rémi Cura <span dir="ltr"><<a href="mailto:remi.cura@gmail.com" target="_blank">remi.cura@gmail.com</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir="ltr"><div><div><div><div><div><div><div><div><div><div><div><div><div>Hey,<br></div>you have to keep the path of the dumped segments, and carefully choose which duplicates to keep, inorder to be able to reconstruct in the right order.<br>
</div><div><br>(for the rest I'm referring to this function : <a href="https://github.com/Remi-C/PPPP_utilities/blob/master/postgis/rc_DumpSegments.sql" target="_blank">https://github.com/Remi-C/PPPP_utilities/blob/master/postgis/rc_DumpSegments.sql</a>)<br>
</div><br></div>Supposing that your polygon have an unique ID, and that you only consider there boundaries (interior ring couldn't be shared as they can't touch the boundaries and you said you have no overlaps)<br>
<br></div>_convert polygon to boundaries, generating a unique ID per polygon/boundaries<br></div>_break each boundaries into segment and keep the path of each segment (for instance with my function)<br></div></div></div>
</div>_now you can remove duplicate, but you have to order the querry so has to keep continuous parts<br>_Then you can recompose your geometry :<br><br></div><div>Here is an example :<br>---------<br><br> WITH the_geom AS ( --creating a fake geom for test purpose, function available here :<a href="https://github.com/Remi-C/PPPP_utilities/blob/master/postgis/cdb_GenerateGrid.sql" target="_blank">https://github.com/Remi-C/PPPP_utilities/blob/master/postgis/cdb_GenerateGrid.sql</a><br>
SELECT row_number() over() AS id, geom<br> FROM CDB_RectangleGrid(ST_GeomFromtext('polygon((0 0, 100 0, 100 100, 0 100 , 0 0))'), 10,10) AS geom<br> )<br> ,dmp_seg AS ( --breaking the boundary of polygons into segments. Function is available here: <a href="https://github.com/Remi-C/PPPP_utilities/blob/master/postgis/rc_DumpSegments.sql" target="_blank">https://github.com/Remi-C/PPPP_utilities/blob/master/postgis/rc_DumpSegments.sql</a><br>
SELECT id, rc_DumpSegments(ST_Boundary(geom)) as dmpgeom<br> FROm the_geom<br> )<br>,cleaned_ds AS ( --snapping to grid to avoid precision issue, replace 0.1 by your alloxed precision<br> SELECT id, (dmpgeom).path, ST_SNapToGrid((dmpgeom).geom,0.1) as geom<br>
FROM dmp_seg<br> )<br>,dedup AS ( --deleting the duplicates in the segments, but not randomly : provide an order to be able ot reconstruct after<br> SELECT DISTINCT ON ( geom ) *<br> FROM cleaned_ds<br> ORDER BY geom, id, path<br>
) --reconstructing lines from segment, but again with the right order<br> ,reconstructed_lines AS (<br> SELECT id, ST_MakeLine(array_agg(geom ORDER BY dedup.path) ) as geom<br> FROM dedup <br>GROUP BY id <br> ) --simple check, can be suppressed : it should output no row<br>
SELECT id, geom <br> FROM reconstructed_lines<br> WHERE st_IsValid(geom) = FALSE<br>---------<br></div><div><br><div>Cheers,<br></div></div></div></div></div><div><div><div><div><div><div><div>
<br></div><div>Rémi-C<br></div><div><br></div></div></div></div></div></div></div><div class="gmail_extra"><br><br><div class="gmail_quote">2014-05-23 6:05 GMT+02:00 Roxanne Reid-Bennett <span dir="ltr"><<a href="mailto:rox@tara-lu.com" target="_blank">rox@tara-lu.com</a>></span>:<div>
<div><br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<div text="#000000" bgcolor="#FFFFFF"><div>
<div>On 5/22/2014 12:28 PM, Richard
Greenwood wrote:<br>
</div>
<blockquote type="cite">
<div dir="ltr">
<div>
<div>I have a polygon table that I want to convert to
linestrings without any duplicated (overlapping)
linestrings. I'm doing this simply to improve cartography so
I'm looking for a quick and dirty approach, avoiding
topology if possible. <br>
</div>
</div>
</div>
</blockquote>
<br></div>
I'm not sure I understand what it is you are attempting to do from
this description. I would expect lines going in two directions from
each polygon and I just don't know how you would use that to help
with "improve cartography".
<div><blockquote type="cite">
<div dir="ltr">
<div>
<div>
<br>
</div>
The closest I've gotten so far is to break the polygons
boundaries into simple (two vertex) lines and delete all the
duplicate lines. Now I'm stuck getting the simple lines back
into polylines. Just doing a union of the whole mess isn't
getting me individual linestrings.<br>
</div>
</div>
</blockquote></div>
So, crazy "out of the blue" question.. have you considered creating
a line (or lines) from the centroids?
<div><blockquote type="cite">
<div dir="ltr">
<div>
<br>
</div>
All this is turning into more steps that I anticipated and I
wonder if I'm missing simpler approach?<br>
</div>
</blockquote></div>
Or maybe describe what your target is? There are a lot of people a
lot more knowledgeable than I on this list, and if they haven't
answered, perhaps you haven't presented your challenge in a manner
that they can address.<br>
<br>
Roxanne
<blockquote type="cite"><div>
<div dir="ltr">
<div>
<div>
<div><br>
</div>
<div>Thanks,<br>
Rich<br>
</div>
<div><br>
-- <br>
<div dir="ltr">Richard W. Greenwood, PLS<br>
<a href="http://www.greenwoodmap.com" target="_blank">www.greenwoodmap.com</a></div>
</div>
</div>
</div>
</div>
<br>
<fieldset></fieldset>
<br>
</div><pre>_______________________________________________
postgis-users mailing list
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a>
<a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a></pre>
</blockquote>
<br>
</div>
<br>_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br></blockquote></div></div></div><br></div></div>
<br>_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br></blockquote></div><br></div>
</div></div><br>_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br></blockquote></div><br><br clear="all"><br>-- <br><div dir="ltr">
Richard W. Greenwood, PLS<br><a href="http://www.greenwoodmap.com" target="_blank">www.greenwoodmap.com</a></div>
</div>