Error transforming geometry into lat-long coordinates

Question

I'm trying to transform some geometries from the postgis database into lat long coordinates:

This is the query:

SELECT st_x (geometry) as lng, st_y (geometry) as lat FROM final

and this is the error I receive:

SQL error:

ERROR: Argument to X () must be a point

Answer 1

A polygon does not have a single X coordinate and a single Y coordinate. A polygon is an accumulation of vertices with an X and Y coordinate.

You can use the ST_AsText function to get the WKT representation of your geometry :

SELECT ST_AsText(geom) 
FROM polygon -- change the name of your table
-- Result example : 
-- POLYGON((-0.606871159358866 44.8533728143996,-0.607088886443168 44.8527320746944,-0.606672094596075 44.8527569577897,-0.606441925964099 44.8531177626723,-0.606441925964099 44.8531986327321,-0.606871159358866 44.8533728143996))

To get a single X and Y coordinate, you can use the centroid of the polygon.

WITH centro AS (SELECT ST_Centroid(geom) as geom FROM test_poly)
SELECT ST_X(geom) as X, ST_Y(geom) 
FROM centro

Answer 2

To get a table of extracted coordinates from all vertices of the (table of) polygons, run

SELECT poly.<id>,
       dmp.path[1] AS ring,
       dmp.path[2] AS vtx,
       ST_X(dmp.geom) AS lon,
       ST_Y(dmp.geom) AS lat
FROM   final AS poly,
       LATERAL ST_DumpPoints(poly.geometry) AS dmp
ORDER BY
       1, 2, 3
;

where

• ring [INT] represents the (inner) ring the vertex is taken from (in order from outer to inner ring)

• vtx [INT] denotes the vertex sequence (in order from ST_StartPoint to ST_EndPointof the linear component (ring as LineString))

Answer 3

If I understand your question correctly, I'll add another solution.

Run the Postgre/PostGIS SQL script:

SELECT ST_X(geom) as lng, ST_Y(geom) as lat FROM (SELECT (ST_DumpPoints(geometry)).geom FROM final) foo

The result: all the points in the polygon in the format you want...