Get nearest places (rows) from MySQL based on a Latitude and Longitude co-ordinate

I’ve recently been developing a C# desktop application for Microsoft Windows that integrates with Microsoft Flight Simulator and Lockheed Martin’s Prepar3d – The software is designed to monitor the landing rate of a user’s aircraft upon landing and then displays this information to the user to aid them perfect their landings whilst flying in the simulated environment.

Anyway, the application gives the user the ability to upload their landing statistics including their Aircraft type, heading and the latitude and longitude of the the aircraft upon touch-down to this website.

The website displays a map and plots the landing on the globe, as well as this I wanted to determine the closest airport (within 3KM) in order to determine the airport at which airport they’ve landed at.

So the scenario is that I have a large database table of ~24,500 airports of which contain their name, ICAO code and of course their Lat/Long co-ordinates, I also know the latitude and longitude of the user’s aircraft at touchdown so with this data I then want to return the closest airport (or airports) so we can use the data in the application.

So although my use-case is specific to returning the closest airport this can of course be used in any scenario where you want to return the closest location to a given lat/long point such as a user’s/site visitors location, so I thought I’d share the MySQL code that I used to do this with….

-- Set your users current location in LAT/LONG here
set @lat=51.891648;
set @lng=0.244799;

-- The main SQL query that returns the closest 5 airports.
SELECT id, icao, lat, lng, 111.045 * DEGREES(ACOS(COS(RADIANS(@lat))
 * COS(RADIANS(lat))
 * COS(RADIANS(lng) - RADIANS(@lng))
 + SIN(RADIANS(@lat))
 * SIN(RADIANS(lat))))
 AS distance_in_km
FROM airports
ORDER BY distance_in_km ASC
LIMIT 0,5;

You can simply copy and paste this and then replace the MySQL variables at the top of the SQL to set what in affect could be your user’s current location and have it query a table such as ‘shops’ to then return and allow your users to see your closest shops to their current location.

The above SQL example will only return the top 5 closest airports but you can obviously remove or adjust the last line “LIMIT 0,5;” to change this.

I hope this was useful to others!

Share