3

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
  • 1
    geometry is not a point - seems clear enough – Ian Turton Jul 8 '20 at 7:39
  • It is not a point, it is a polygon ... and in the case of the polygon, how to proceed? – CoderCoder42 Jul 8 '20 at 7:53
4

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))

  • 1
    thx. @Vincent-bré for correcting the double ST_X – geozelot Jul 8 '20 at 9:54
6

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
2

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...

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.