Sunday, March 6, 2016

ADS-B Filtering by Latitude/Longitude SQL query

Now that my scripts are collecting ADS-B data to mySQL it's time to learn more SQL queries.

How can I find the flights furthest away from me?
I simply let mySQL calculate the distance - with simplified Pythagoras (a² + b² = c²), assuming that Latitude and Longitude are somewhat similar...

SQL QUERY


select logsign, altitude, 
latitude, (latitude-53.46) as LatDiff, 
longitude, (longitude-9.69) as LonDiff,
sqrt((latitude-53.46)*(latitude-53.46) + (longitude-9.69)*(longitude-9.69)) as LatLonDistance
from flightlog
order by sqrt((latitude-53.46)*(latitude-53.46) + (longitude-9.69)*(longitude-9.69)) desc
limit 100;

Resulting Data  (using mySQL workbench)

Query Result

Resulting Map (using cartodb.com)

Now mapping the resulting data on a map gives me this nice picture. As you can see, Latitude and Longitude are not perfectly square - resulting in a nice oval cut out in the middle....

Map

No comments:

Post a Comment