Category Archive for 'SQL'

Distance and bearings between GPS coordinates in SQL

Wednesday, January 16th, 2008

Another post from my sql abominations series. I’m running this on MySQL 5 particularly.
Assume you have a table of locations with Latitude and Longitude for each one. In my case the table is “station”, primary key being “LocID”. With help from this article, first we create a view to get 3D coordinates (6378 = Earth’s [...]

Posted in How To, SQL | 2 Comments »

Average direction in SQL

Wednesday, August 15th, 2007

Given a column of polar directions in degrees, this is a single SQL expression to compute their average, for use in a GROUP BY query. Some functions may be MySQL-specific.
IF(DEGREES(ATAN2(
-AVG(SIN(RADIANS(direction_deg)))
,-AVG(COS(RADIANS(direction_deg))))
) < 180
[...]

Posted in How To, SQL | 2 Comments »