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 😉