[postgis-users] polygons to linestrings

Sat May 24 16:04:26 PDT 2014

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.

@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.

Again, thank you very much. Your answers both solved my immediate problem
and taught me a lot.

Rich

On Fri, May 23, 2014 at 4:32 AM, Nicolas Ribot <nicolas.ribot at gmail.com>wrote:

> Hi Richard,
>
> You could use st_node on the polygon boundaries to rebuild unique,
> complete linestrings:
> Boundaries are dumped into linestrings in order to build a Multilinestring
> containing all PG boundaries.
> st_node is performed on this collection, then result is dumped into
> individual lines:
>
> with tmp as (
>     select (st_dump(st_boundary(geom))).geom
>     from mytable
> ) select nexval('seq01') as id, (st_dump(st_node(st_collect(geom)))).geom
> from tmp;
>
> If your dataset is big, a cut-by-grid approach could speed up the process.
>
> Nicolas
>
>
> On 23 May 2014 12:26, Rémi Cura <remi.cura at gmail.com> wrote:
>
>> Hey,
>> 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.
>>
>> (for the rest I'm referring to this function :
>> https://github.com/Remi-C/PPPP_utilities/blob/master/postgis/rc_DumpSegments.sql
>> )
>>
>> 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)
>>
>> _convert polygon to boundaries, generating a unique ID per
>> polygon/boundaries
>> _break each boundaries into segment and keep the path of each segment
>> (for instance with my function)
>> _now you can remove duplicate, but you have to order the querry so has to
>> keep continuous parts
>> _Then you can recompose your geometry :
>>
>> Here is an example :
>> ---------
>>
>>  WITH the_geom AS ( --creating a fake geom for test purpose, function
>> available here :
>> https://github.com/Remi-C/PPPP_utilities/blob/master/postgis/cdb_GenerateGrid.sql
>>         SELECT  row_number() over() AS id, geom
>>         FROM CDB_RectangleGrid(ST_GeomFromtext('polygon((0 0, 100 0, 100
>> 100, 0 100 , 0 0))'), 10,10) AS geom
>>  )
>>  ,dmp_seg AS ( --breaking the boundary of polygons into segments.
>> Function is available here:
>> https://github.com/Remi-C/PPPP_utilities/blob/master/postgis/rc_DumpSegments.sql
>>      SELECT id, rc_DumpSegments(ST_Boundary(geom)) as dmpgeom
>>      FROm the_geom
>>  )
>> ,cleaned_ds AS ( --snapping to grid to avoid precision issue, replace 0.1
>> by your alloxed precision
>>  SELECT id, (dmpgeom).path, ST_SNapToGrid((dmpgeom).geom,0.1) as geom
>>  FROM dmp_seg
>>  )
>> ,dedup AS ( --deleting the duplicates in the segments, but not randomly :
>> provide an order to be able ot reconstruct after
>>  SELECT DISTINCT ON ( geom  )  *
>>  FROM cleaned_ds
>>  ORDER BY  geom, id, path
>>  ) --reconstructing lines from segment, but again with the right order
>>  ,reconstructed_lines AS (
>>  SELECT id,   ST_MakeLine(array_agg(geom ORDER BY  dedup.path) ) as geom
>>  FROM dedup
>> GROUP BY id
>>  ) --simple check, can be suppressed : it should output no row
>>  SELECT id, geom
>>  FROM reconstructed_lines
>>  WHERE st_IsValid(geom)  = FALSE
>> ---------
>>
>> Cheers,
>>
>> Rémi-C
>>
>>
>>
>> 2014-05-23 6:05 GMT+02:00 Roxanne Reid-Bennett <rox at tara-lu.com>:
>>
>>  On 5/22/2014 12:28 PM, Richard Greenwood wrote:
>>>
>>>  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.
>>>
>>>
>>> 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".
>>>
>>>
>>>  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.
>>>
>>> So, crazy "out of the blue" question.. have  you considered creating a
>>> line (or lines) from the centroids?
>>>
>>>
>>>  All this is turning into more steps that I anticipated and I wonder if
>>> I'm missing simpler approach?
>>>
>>> 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.
>>>
>>> Roxanne
>>>
>>>
>>>  Thanks,
>>> Rich
>>>
>>> --
>>> Richard W. Greenwood, PLS
>>> www.greenwoodmap.com
>>>
>>>
>>> _______________________________________________
>>> postgis-users mailing listpostgis-users at lists.osgeo.orghttp://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>>>
>>>
>>>
>>> _______________________________________________
>>> postgis-users mailing list
>>> postgis-users at lists.osgeo.org
>>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>>>
>>
>>
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at lists.osgeo.org
>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>>
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>

-- 
Richard W. Greenwood, PLS
www.greenwoodmap.com
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20140524/1c4fc964/attachment.html>