61

This line:

SELECT geom FROM myLayer.geom

will return a hex value.

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

53

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.

72

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
    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 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 '13 at 9:19
  • 7
    For polygons/lines, use st_x(st_centroid(the_geom)) – Steve Bennett Sep 18 '13 at 22:26
12

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

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.