Measure length of each segment for a polygon in Postgis

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

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.

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()`.