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

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 09:46
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
The best answer is here https://gis.stackexchange.com/a/57020 — Sumit Kumar Gupta, Jul 14 '21 at 07:51

score 16 · Answer 1 · answered Jun 26 '13 at 09:22

16

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,

answered Jun 26 '13 at 09:22

Gery

2,135
1
15
37

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

score 10 · Answer 2 · edited Apr 13 '17 at 12:43

10

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

edited Apr 13 '17 at 12:43

Community

1

answered Dec 01 '16 at 21:09

Evan Carroll

7,071
2
32
58

This helped me because I was looking for Postgres native solution – Yuseferi Sep 20 '22 at 23:25

juanluisrp · Accepted Answer · 2013-06-27T07:00:09.523

6

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.

edited Jun 27 '13 at 07:00

answered Jun 26 '13 at 14:30

juanluisrp

770
3
13

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

score 5 · Answer 4 · answered May 03 '18 at 01:54

5

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;

answered May 03 '18 at 01:54

Jason

275
2
10

score 2 · Answer 5 · edited Sep 20 '21 at 11:08

2

Here is the answer:

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

edited Sep 20 '21 at 11:08

Kadir Şahbaz

76,800
56
247
389

answered Sep 20 '21 at 11:01

Prateek Sharma

21
1

Thanks for a straight working solution – Jonatan Cloutier Aug 10 '23 at 01:06

Extract lat/long from native PostgeSQL point type column

5 Answers5