Getting coordinates from geometry in PostGIS

Question

This line:

SELECT geom
FROM myLayer.geom

will return a hex value.

How would I get decimal values, which is a human-readable coordination?

score 101 · Answer 1 · edited Jul 19 '18 at 15:52

101

Use ST_AsText to view the point object:

SELECT ST_AsText(the_geom) 
       FROM myTable;

To view X, Y, and the geom object:

SELECT ST_X(the_geom), ST_Y(the_geom), ST_AsText(the_geom) 
       FROM myTable;

edited Jul 19 '18 at 15:52

Brad Koch

475
5
23

answered Dec 03 '12 at 14:46

urcm

22,533
4
57
109

8

I believe that ST_X & ST_Y will work only with points. They do not work with polygons or lines. – Devdatta Tengshe Jul 31 '13 at 09:16
you are right Devdatta. it works only for points. ST_X — Return the X coordinate of the point, or NULL if not available. Input must be a point. i have updated my answer. – urcm Jul 31 '13 at 09:19
8

For polygons/lines, use st_x(st_centroid(the_geom)) – Steve Bennett Sep 18 '13 at 22:26
@Aragon is the "the_geom" in your answer represents an object??does that mean that i can pass an object geometry? – Amrmsmb May 15 '21 at 06:39

score 73 · Accepted Answer · edited Dec 20 '22 at 12:26

73

In addition to ST_AsText (which returns geometry as WKT / Well Known Text), there are several additional output formats, like ST_AsGeoJSON().

Take a look in http://postgis.net/docs/manual-2.0/reference.html#Geometry_Outputs and choose, what fits your needs best.

edited Dec 20 '22 at 12:26

Kadir Şahbaz

76,800
56
247
389

answered Dec 03 '12 at 14:50

user1702401

2,871
1
18
17

phsaires · Answer 3 · 2014-04-14T08:37:21.883

With a table in UTM

SELECT 
ST_X(table.geometry) AS X1, --point x
ST_Y(table.geometry) AS Y1, --point y
ST_X(ST_TRANSFORM(table.geometry,4674)) AS LONG, -- longitude point x SIRGAS 2000
ST_Y(ST_TRANSFORM(table.geometry,4674)) AS LAT, --latitude point y SIRGAS 2000
ST_ASTEXT(table.geometry) AS XY, --wkt point xy
ST_ASTEXT(ST_TRANSFORM(table.geometry,4674)) AS LongLat --using st_transform to get wkt with longitude and latitude (4674 is the SIRGAS 2000 SRC by south america)
FROM
table

score 2 · Answer 4 · edited Apr 13 '17 at 12:34

2

SELECT * ST_AsText(the_geom) FROM table2;

Source: Getting geometry from multiple tables using PostGIS?

edited Apr 13 '17 at 12:34

Community

1

answered Dec 03 '12 at 14:47

Mapperz

49,701
9
73
132

1

You may need to add a , after the * , in order to properly separate fields: SELECT *, ST_AsText(the_geom) FROM table2; – Pierre Sep 26 '18 at 15:11

Getting coordinates from geometry in PostGIS

4 Answers4

Linked