Query on geolocation

0
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).
asked
2 answers
1

As a first try I would do 2 retrieves

  1. Limit longitude +/- range and latitude +/- wider range
  2. 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)

Sort on the distance.

Things to solve: meridian and equator.

Maybe this helps if you can use SQL directly

answered
0

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 From MyTable Where acos(sin(:lat)sin(radians(Lat)) + cos(:lat)cos(radians(Lat))cos(radians(Lon)-:lon)) * :R < :rad

Does anyone know if this will also work in OQL.?

answered