3

I have a polygon table in PostGIS and need to calculate the length of each line segment.

enter image description here

How can I achieve this? I have done it in QGIS but would prefer to do it in the database. I have found a solution in QGIS to do this.

4

You need to first get the boundary linestring of the polygon. You can do this with ST_Boundary

SELECT ST_AsText(x)
FROM ST_Boundary(
  ST_GeomFromText(
    $$POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))$$
  )
)
  AS t(x);
            st_astext            
---------------------------------
 LINESTRING(0 0,1 0,1 1,0 1,0 0)  <- NOTE: LINESTRING() not POLYGON()
(1 row)

Now you need to solve problem of getting the individual line strings with only two points. For this we turn to PostGIS in Action, 2nd Edition by Regina O. Obe and Leo S. Hsu, Chapter 11. A book every GIS developer worth their salt should have already read. See this question for more details.

They use a method of CROSS JOIN LATERAL with generate_series and ST_MakeLine. As far as I know, there is no better method.

SELECT ST_AsText(t.geom), gs.*, ST_AsText(line.geom) AS line
FROM ST_Boundary(
  ST_GeomFromText(
    $$POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))$$
  )
)
  AS t(geom)
CROSS JOIN LATERAL generate_series(1, ST_NPoints(geom) - 1)
  AS gs(x)
CROSS JOIN LATERAL ST_MakeLine(
  ST_PointN(geom, x),
  ST_PointN(geom, x+1)
) AS line(geom);
            st_astext            | x |        line         
---------------------------------+---+---------------------
 LINESTRING(0 0,1 0,1 1,0 1,0 0) | 1 | LINESTRING(0 0,1 0)
 LINESTRING(0 0,1 0,1 1,0 1,0 0) | 2 | LINESTRING(1 0,1 1)
 LINESTRING(0 0,1 0,1 1,0 1,0 0) | 3 | LINESTRING(1 1,0 1)
 LINESTRING(0 0,1 0,1 1,0 1,0 0) | 4 | LINESTRING(0 1,0 0)
(4 rows)

From this it's easy. Displaying the length is only adding ST_Length(line.geom) to the SELECT list however that's not too interesting since all of our lines have a length of 1. The perfect square I've chosen isn't the most interesting example; next time you can dump simplified data and your desired output with ST_AsText().

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.