MySQL function to calculate the distance between two coordinates using the Haversine formula. Leaving it here for future reference.

distance.sql

DELIMITER $$
CREATE FUNCTION `haversine` (lat1 DECIMAL(8,6), lng1 DECIMAL(8,6), lat2 DECIMAL(8,6), lng2 DECIMAL(8,6)) RETURNS DECIMAL(8,6)
BEGIN
    DECLARE R INT;
    DECLARE dLat DECIMAL(30,15);
    DECLARE dLng DECIMAL(30,15);
    DECLARE a1 DECIMAL(30,15);
    DECLARE a2 DECIMAL(30,15);
    DECLARE a DECIMAL(30,15);
    DECLARE c DECIMAL(30,15);
    DECLARE d DECIMAL(30,15);

    SET R = 3959; -- Earth's radius in miles
    SET dLat = RADIANS( lat2 ) - RADIANS( lat1 );
    SET dLng = RADIANS( lng2 ) - RADIANS( lng1 );
    SET a1 = SIN( dLat / 2 ) * SIN( dLat / 2 );
    SET a2 = SIN( dLng / 2 ) * SIN( dLng / 2 ) * COS( RADIANS( lng1 )) * COS( RADIANS( lat2 ) );
    SET a = a1 + a2;
    SET c = 2 * ATAN2( SQRT( a ), SQRT( 1 - a ) );
    SET d = R * c;
    RETURN d;
END$$

DELIMITER ;

Tested it with some GTFS data and it seems to work pretty well.

R=6371; -- Earth's radius in Km
R=6371000; -- Earth's radius in metres

Nice code - works well.
I did not manage yet to setup mySQL functions on my AWS RDS yet...so I crunched the function into a query.

See resulting data visualization of ADS-B aircraft position data at
http://10pm-blog.blogspot.de/2016/03/geographical-distance-calculation.html

Many thanks!!

Could you please elaborate on what this does exactly?

There is a bug on this line,

SET a2 = SIN( dLng / 2 ) * SIN( dLng / 2 ) * COS( RADIANS( lng1 )) * COS( RADIANS( lat2 ) );

The correct formula is,

SET a2 = SIN( dLng / 2 ) * SIN( dLng / 2 ) * COS( RADIANS( lat1 )) * COS( RADIANS( lat2 ) );

Source: http://www.movable-type.co.uk/scripts/latlong.html

Thanks i have latitude/longitude coordinates i want to get the users who are within 50km of these latitude/longitude coordinates can some one please help me on this.
Thanks in advance.

The short version, tested in MySql 5.7

create function GetDistance(lat1 real, lng1 real, lat2 real, lng2 real) returns real no sql
return ATAN2(SQRT(POW(COS(RADIANS(lat2)) * SIN(RADIANS(lng1 - lng2)), 2) +
POW(COS(RADIANS(lat1)) * SIN(RADIANS(lat2)) - SIN(RADIANS(lat1)) *
COS(RADIANS(lat2)) * COS(RADIANS(lng1 - lng2)), 2)),
(SIN(RADIANS(lat1)) * SIN(RADIANS(lat2)) + COS(RADIANS(lat1)) *
COS(RADIANS(lat2)) * COS(RADIANS(lng1 - lng2)))) * 6372.795;

CREATE FUNCTION GetDistance(
    lat1 REAL,
    lng1 REAL,
    lat2 REAL,
    lng2 REAL
) RETURNS REAL NO SQL RETURN ATAN2(
    SQRT(
        POW(
            COS(RADIANS(lat2)) * SIN(RADIANS(lng1 - lng2)),
            2
        ) + POW(
            COS(RADIANS(lat1)) * SIN(RADIANS(lat2)) - SIN(RADIANS(lat1)) * COS(RADIANS(lat2)) * COS(RADIANS(lng1 - lng2)),
            2
        )
    ),
    (
        SIN(RADIANS(lat1)) * SIN(RADIANS(lat2)) + COS(RADIANS(lat1)) * COS(RADIANS(lat2)) * COS(RADIANS(lng1 - lng2))
    )
) * 6372.795;

Please use datatype float instead of decimal in this. Mysql doesnt support -ve values for decimal datatype. You'll hit issues if you use points crossing hemispheres with this code