Search code examples
sqldistancelive

calculate distance live and order asc|desc

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?

Solution

  • 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
  from YourTable
 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
  from YourTable
 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.