[GIS] Polygons to lines in PostGIS

polygonpostgisqgis

I have a Postgis table containing land parcels as polygons. These land parcels have boundaries defined by hedges, walls, fences etc. I would like to create a table of the individual boundary components for further survey and evaluation. The first step to this would be cutting the polygon boundaries into strings at each node.
It looks like ArcMap can do this using the "Polygon To Line" function (http://desktop.arcgis.com/en/arcmap/10.3/tools/data-management-toolbox/polygon-to-line.htm) but I can't find anything equivalent in Postgis or QGIS.

Any suggestions?

Best Answer

Here is what we can do,

  1. Create a GeometryCollection with one polygon using the ST_Polygonize aggregate. This is our test data.
  2. Access the first element of the GeometryCollection with ST_GeometryN(geom,1). If your input is already a polygon, you'll pick up from here.
  3. Extract the boundary with ST_Boundary. The boundary is a LINESTRING.
  4. Decompose that LINESTRING into points using ST_PointN, and using those points as arguments to ST_MakeLine

It looks like this,

SELECT ST_AsText(ST_Boundary(ST_GeometryN(ST_Polygonize(l),1)))
FROM ( VALUES
  ( ST_MakeLine(ST_MakePoint(5,0),ST_MakePoint(0,0)) ),
  ( ST_MakeLine(ST_MakePoint(0,0),ST_MakePoint(0,5)) ),
  ( ST_MakeLine(ST_MakePoint(0,5),ST_MakePoint(5,5)) ),
  ( ST_MakeLine(ST_MakePoint(5,5),ST_MakePoint(5,0)) )
) AS t(l);
            st_astext            
---------------------------------
 LINESTRING(5 0,0 0,0 5,5 5,5 0)
(1 row)

Now we have to decompose the boundary into 2-point line strings. First we move the aggregate into an inner-select

SELECT ST_AsText(b)
FROM (
  SELECT ST_Boundary(ST_GeometryN(ST_Polygonize(l),1)) AS b
  FROM ( VALUES
    ( ST_MakeLine(ST_MakePoint(5,0),ST_MakePoint(0,0)) ),
    ( ST_MakeLine(ST_MakePoint(0,0),ST_MakePoint(0,5)) ),
    ( ST_MakeLine(ST_MakePoint(0,5),ST_MakePoint(5,5)) ),
    ( ST_MakeLine(ST_MakePoint(5,5),ST_MakePoint(5,0)) )
  ) AS t(l)
) AS i(b);

Then we recompose simple lines,

SELECT ST_AsText(ST_MakeLine(
  ST_PointN(b.geom,gs.pt),
  ST_PointN(b.geom,gs.pt+1)
))
FROM (
  SELECT ST_Boundary(ST_GeometryN(ST_Polygonize(l),1)) AS b
  FROM ( VALUES
    ( ST_MakeLine(ST_MakePoint(5,0),ST_MakePoint(0,0)) ),
    ( ST_MakeLine(ST_MakePoint(0,0),ST_MakePoint(0,5)) ),
    ( ST_MakeLine(ST_MakePoint(0,5),ST_MakePoint(5,5)) ),
    ( ST_MakeLine(ST_MakePoint(5,5),ST_MakePoint(5,0)) )
  ) AS t(l)
) AS b(geom)
CROSS JOIN LATERAL generate_series(1,ST_NPoints(b.geom)-1) gs(pt);

That returns..

      st_astext      
---------------------
 LINESTRING(5 0,0 0)
 LINESTRING(0 0,0 5)
 LINESTRING(0 5,5 5)
 LINESTRING(5 5,5 0)
(4 rows)
Related Question