PostgreSQL select query to extract latitude and longitude from a point

Question

What SELECT query should be used to extract latitude and longitude from a point?
I cannot use PostGIS.

Example point (point type value) stored in the database:

   my_point
--------------
(50.850,4.383)

Expected result after executing the query:

  lat  |  lng
---------------
50.850 | 4.383

The query below works fine but it does not look efficient.

SELECT 
    split_part(trim(my_point::text, '()'), ',', 1)::float AS lat, 
    split_part(trim(my_point::text, '()'), ',', 2)::float AS lng
FROM my_table;

score 15 · Accepted Answer · answered Aug 28 '13 at 15:30

15

Always Read The Fine Manuals

It is possible to access the two component numbers of a point as though the point were an array with indexes 0 and 1. For example, if t.p is a point column then SELECT p[0] FROM t retrieves the X coordinate and UPDATE t SET p1 = ... changes the Y coordinate. In the same way, a value of type box or lseg can be treated as an array of two point values.

answered Aug 28 '13 at 15:30

Richard Huxton

21,516
3
39
51

Thank you, this is exactly what I needed. The funny thing is I already read it before asking this question except the lines below the tables. – rafis Aug 29 '13 at 07:33
Worth noting that extracting it from a literal directly will not work: Select point(25,55)[0]; -- will not work!! You need existing point: with a as (select point(25,55) p) select p[0] from a; -- will work! – Valentin V Apr 04 '18 at 17:56
1

@Valentin it works with parentheses: `SELECT (POINT(25,55))[0];` – Florian Castellane Sep 25 '19 at 04:36

score 0 · Answer 2 · answered Dec 26 '22 at 13:42

0

Another option would be:

SELECT 
    ST_X(point) as longitude, 
    ST_Y(point) as latitude
FROM your_table_name

answered Dec 26 '22 at 13:42

ruhanbidart

4,564
1
26
13

PostgreSQL select query to extract latitude and longitude from a point

2 Answers2