87

This line:

SELECT geom FROM myLayer.geom

will return a hex value.

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

4 Answers 4

89

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;
4
  • 6
    I believe that ST_X & ST_Y will work only with points. They do not work with polygons or lines. Jul 31, 2013 at 9: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.
    – Aragon
    Jul 31, 2013 at 9:19
  • 8
    For polygons/lines, use st_x(st_centroid(the_geom)) Sep 18, 2013 at 22:26
  • @Aragon is the "the_geom" in your answer represents an object??does that mean that i can pass an object geometry?
    – LetsamrIt
    May 15, 2021 at 6:39
64

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.

18

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 
2
SELECT * ST_AsText(the_geom) FROM table2;

Source: Getting geometry from multiple tables using PostGIS?

1
  • 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, 2018 at 15:11

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.