How to display LAT/LONG of polygon?

Question

I have a table with geom field (type: geometry)

I want to display the values of the polygon in lat/long.

How can I do it ?

I saw this post:

https://gis.stackexchange.com/questions/95373/convert-geometry-to-latitude-longitude-using-postgis-st-transform/95376

but the functions ST_X, ST_Y only works on POINT and not on POLYGON.

How can I display the geom field (which contains polygon) in lat/long format ?

Answer 1

Depending on the output you want, it might very well be that ST_ASGEOJSON already does everything you want, but otherwise, you can use ST_DUMPPOINTS to get all the individual points of the polygon.

SELECT polygon.id,
       ( SELECT ARRAY_AGG(ST_Y(geom ORDER BY path))
         FROM ST_DUMPPOINTS(polygon) AS points ) as latitudes,
       ( SELECT ARRAY_AGG(ST_X(geom ORDER BY path))
         FROM ST_DUMPPOINTS(polygon) AS points ) as longitudes
FROM polygons

would return ordered lists of latitudes and longitudes per polygon. If you want to combine, them, use JSON:

SELECT polygon.id,
       ( SELECT ARRAY_AGG(
             JSON_BUILD_OBJECT(
                 'latitude', ST_Y(geom),
                 'longitude', ST_X(geom)
             ) ORDER BY path)
         )
         FROM ST_DUMPPOINTS(polygon) AS points )
FROM polygons