We are building a REST api for a third party that needs to consume data from out Mendix app. One of te request is based on geodata (longitude, latitude) of a mobileapp user. They want to access "nearest location" data. The database has location data of more then 10.000 location and they want to retrieve the 20 nearest location to a given/dynamic enduser location (long/lat). Is there a way to retrieve the 20 nearest locations (based on distance) without having to calculate the distances to all 10.000 locations for that specific users first. I know data some databases have specific spatial query operators to do this (e.g. https://msdn.microsoft.com/en-us/library/ff929109.aspx).
As a first try I would do 2 retrieves
Limit longitude +/- range and latitude +/- wider range
Limit latitude +/- range and longitude +/- wider range
If enough results take the locations that are in both lists (Union).
Calculate the distance for that (quick google: d = acos( sin(lat1)*sin(lat2) + cos(lat1)*cos(lat2)*cos(lon2-lon1) ) * R)
The suggested SQL =
Select Id, Postcode, Lat, Lon,
acos(sin(:lat)sin(radians(Lat)) + cos(:lat)cos(radians(Lat))cos(radians(Lon)-:lon)) * :R As D
Where acos(sin(:lat)sin(radians(Lat)) + cos(:lat)cos(radians(Lat))cos(radians(Lon)-:lon)) * :R < :rad