Monday, March 14, 2016

Geographical Distance Calculation - Haversine


My first quick&dirty distance calculation was okay...but not good:
http://10pm-blog.blogspot.de/2016/03/ads-b-filtering-by-latitudelongitude.html

After a bit of research of converting Latitude/Longitude I finally found somesimple code for the Haversine function at https://gist.github.com/aramonc/6259563

Pressed the code into a query like this and executed in mySQL workbench:

#Haversine SQL function pressed into a query
#code adapted from https://gist.github.com/aramonc/6259563

SET @myLat := '53.465';
SET @myLon := '09.697';

select logdate, loghexid, logsign, altitude, latitude, longitude,

round(3959 * 2 * ATAN2( SQRT( 
pow(sin((RADIANS(latitude) - RADIANS(@myLat))/2),2) + 
pow(sin((RADIANS(longitude) - RADIANS(@myLon))/2),2) * COS(RADIANS(longitude)) * COS(RADIANS(@myLat))
), SQRT( 1 - 
pow(sin((RADIANS(latitude) - RADIANS(@myLat))/2),2) + 
pow(sin((RADIANS(longitude) - RADIANS(@myLon))/2),2) * COS(RADIANS(longitude)) * COS(RADIANS(@myLat))
))) as HaversineDistance

from flightlog
where logdate=curdate();


Exported the data to a CSV and mapped on cartodb.com - viola!

ADS-B blips color-coded by distance

Next step: create the mySQL function so the code in the query is a bit less comples...

Good night!

No comments:

Post a Comment