Joe Celko's SQL for Smarties, 3rd Edition

7.1. Distance Functions

Since geographical data is important, you might find it handy to locate places by their longitude and latitude, then calculate the distances between two points on the globe. This is not a standard function in any SQL, but it is handy to know.

Assume that we have values (Latitude1, Longitude1, Latitude2, Longitude2) that locate the two points, and that they are in radians, and we have trigonometry functions.

To convert decimal degrees to radians, multiply the number of degrees by pi/180 = 0.017453293 radians/degree, where pi is approximately 3.14159265358979:

CREATE FUNCTION Distance (IN latitude1 REAL, IN longitude1 REAL, IN latitude2 REAL, IN longitude2 REAL) RETURNS REAL BEGIN DECLARE r REAL; DECLARE lat REAL; DECLARE ...