3

In my table I have a column location that is a point. I tried this:

SELECT ST_X(location), ST_Y(location) FROM locations;

But I still get the error message:

ERROR: function st_x(point) does not exist
  • 1
    If you execute the query SELECT ST_X(ST_GeomFromEWKT('POINT(1 2 3 4)')); does it work or get any error? – juanluisrp Jun 26 '13 at 9:46
  • it returns "1". I have the postgis extension added – George Hown Jun 26 '13 at 12:09
  • what do you get from "\d locations" and "select * from locations"? paste here the first 5 lines of the latter query – Gery Jun 26 '13 at 12:17
1

It looks like your column location type is point (PostgreSQL reference). This is a native PostgreSQL type, not an Postgis datatype.

You have to change location column data type to geometry or add a new geometry column with the Postgis function AddGeometryColumn.

  • AddGeometryColumn is dated now you can just ALTER TABLE ADD COLUMN – Evan Carroll Dec 1 '16 at 21:10
7

you need to use:

SELECT ST_X(geom), ST_Y(geom) FROM locations;

I suppose that locations is a table of points with the geometry named as "geom".

Hope this helps,

  • Property Value Name location Position 35 Data type point Collation Default Sequence Not NULL? No Primary key? No Foreign key? No Storage PLAIN Inherited No Statistics -1 System column? No I don't understand what you mean with this comment – Gery Jun 26 '13 at 12:21
  • my "location" column field table properties...I dont know how to edit this nicely :( – George Hown Jun 26 '13 at 13:07
3

None of these talk about how to extract lat/long with the native point type. Clearly you can't use ST_X() and ST_Y like you can do with a PostGIS Geometery; so here with the native point simply use array-deference syntax [0] and [1]. For example, if you have a native type and you want to go to PostGIS type you can do it like this (from my answer on dba.se).

SELECT ST_MakePoint(p[0],p[1])
FROM ( VALUES (point(-71.1043443253471,42.3150676015829)) ) AS t(p);
2

You can treat the point type like an array to access the x and y components like this:

SELECT location[0], location[1] FROM locations;

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.