Postgis meassuring distance between first and third points of a polygon

Question

I am using ST_DumpPoints() function first to get the list of the different points in the polygon but after doing that, the first point is in one record and the third point is in another record and I don't know how to meassure the distance between them.

So I would need to have first and third points' geometry in the same record so that i can use st_distance() with both geometries.

SELECT c.id,(points).path[2] as index,(points).geom
FROM
(select 
id,
ST_DumpPoints(the_geom) as points
from my_polygon_layer) as c

Answer 1

You can also use ST_PointN on the exterior ring of your polygon.

SELECT
  ST_Distance(ST_PointN(geom, 1), ST_PointN(geom, 3)) dist
FROM
(
  SELECT
    ST_ExteriorRing(geom) geom
  FROM
    my_polygon_layer
) a;

Answer 2

Well I finally made it using array_agg() function after st_dumppoints():

SELECT a.id, st_distance(a.points[1],a.points[2])
FROM(
SELECT c.id,array_agg((points).geom) as points
FROM
(select 
id,
ST_DumpPoints(the_geom) as points
from my_polygon_layer) as c
where (points).path[2] = 1 OR (points).path[2] =3
group by c.id

I don't know if there is a more elegant solution. I hope there is.