I have a project where the geographical coordinates for records are stored in a single field separated by a comma. While this may suit general display purposes, it eliminates the capability to search by distance.
Sample data: 34.7451738,-86.5941693
My solution was to add two new FLOAT(10, 6) fields named “lat” and “lng” to the database table. Then I ran this query to populate the two new fields with the existing “coordinates” data.
UPDATE `locations` SET `lat` = SUBSTRING_INDEX(SUBSTRING_INDEX(`coordinates`, ',', 1) , ',', -1), `lng` = SUBSTRING_INDEX(SUBSTRING_INDEX(`coordinates`, ',', 2), ',', -1);
Who doesn’t love one-liners for mass-updates in a table 😉
For those of you who might be looking for a distance calculation query…
1. “3959” for miles, “6371” for kilometers
2. Origin latitude “28.18”
3. Origin longitude “-81.83”
4. Desired radius “50”
5. Record limit “10”
SELECT name, address, ( 3959 * ACOS( COS( RADIANS( 28.18 ) ) * COS( RADIANS( lat ) ) * COS( RADIANS( lng ) – RADIANS( – 81.83 ) ) + SIN( RADIANS( 28.18 ) ) * SIN( RADIANS( lat ) ) ) ) AS distance FROM `location` HAVING distance <50 ORDER BY distance LIMIT 0 , 10
You could of course run your distance queries against the combined coordinates field as well…
SUBSTRING_INDEX(SUBSTRING_INDEX(`coordinates`, ‘,’, 1) , ‘,’, -1) AS `lat`, SUBSTRING_INDEX(SUBSTRING_INDEX(`coordinates`, ‘,’, 2), ‘,’, -1) AS `lng`
For my purposes, splitting the field seemed like a better idea at the time.