Extract lat/long from native PostgeSQL point type column

Question

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

Answer 1

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,

Answer 2

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

Answer 3

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.

Answer 4

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;

Answer 5

Here is the answer:

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