[postgis-users] polygons to linestrings

[Nicolas Ribot]

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
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20140523/8f696557/attachment.html>