CODEWITHSUNDEEP
mysql
AL̲̳I
AL̲̳I

Reputation: 2461

MYSQL query to sort GPS coordinates

I have a table with column 'gps' and it has comma separated latitude and longitude values. (lat,lng) If I want to sort the table by both latitude and longitude how can I do that?

UPDATE: I can sort by latitude 

ORDER BY gps*1 asc/desc

but I want to sort by longitude as well

It would have been easy if I had two separate columns for latitude and longitude but I don't.

Can anyone help me with this?

Thanks in advance

Upvotes: 0

Views: 596

Answers (2)

xlecoustillier
xlecoustillier

Reputation: 16351

Try SUBSTRING_INDEX:

SELECT SUBSTRING_INDEX(gps, ',', 1)  AS longitude,
       SUBSTRING_INDEX(gps, ',', -1) AS latitude
FROM   yourtable
ORDER  BY longitude,
          latitude

Upvotes: 4

jaczes
jaczes

Reputation: 1404

You should use substring_index to locate longitude and latitude

EDIT:

select
SUBSTRING_INDEX(longitude_and_latitude, ',', 1) as longitude,
SUBSTRING_INDEX(longitude_and_latitude, ',', -1) as latitude
from MY_TABLE
order by 1,2

Upvotes: 1

Related Questions