So when i start the web app, the server gets the user geolocation and save it as variable (latitude/longitude) I've got a database with: ID, Name, Latitude, Longitude
Now i want to select all entries from the database and order them by distance
I know how to calculate the distance between coordinates, but i dont know how to order things "live"
Can you guys give me a hint how to solve this?
Best How To :
You can simply specify the distance calculation (say for example its (Longitude + Latitude) divided by 42) in the
order by clause
select ID, Name, Latitude, Longitude, (Longitude + Latitude) / 42 as Distance
order by (Longitude + Latitude) / 42
However, because the distance calculation is likely to be slightly more complex than (Longitude + Latitude) / 42, you might want to consider ordering your results based on the ordinal of the column in the result set (I think most databases support this). So something like.....
select ID, Name, Latitude, Longitude, /* Insert Distance Calculation Here */ as Distance
order by 5 -- Column ordinals are 1-based
You should note though that there are some gotchas to this approach. For example, if the order of the columns are changed or columns are added, the
order by ordinal must also be changed or you will see unexpected results. Obviously if you order by field name or calculation this isn't an issue.