Skip to content

Instantly share code, notes, and snippets.

@zerothabhishek

zerothabhishek/distance.sql

Forked from aramonc/distance.sql
Created February 12, 2017 10:59
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save zerothabhishek/51c7c8cc5bae83c3c9a7e235d224de16 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save zerothabhishek/51c7c8cc5bae83c3c9a7e235d224de16 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. @aramonc aramonc revised this gist Sep 24, 2013. 1 changed file with 8 additions and 8 deletions.
    16 changes: 8 additions & 8 deletions distance.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -1,14 +1,14 @@
    DELIMITER $$
    CREATE FUNCTION `distance` (lat1 DECIMAL(8,6), lng1 DECIMAL(8,6), lat2 DECIMAL(8,6), lng2 DECIMAL(8,6)) RETURNS DECIMAL(8,6)
    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;
    DECLARE dLng DECIMAL;
    DECLARE a1 DECIMAL;
    DECLARE a2 DECIMAL;
    DECLARE a DECIMAL;
    DECLARE c DECIMAL;
    DECLARE d DECIMAL;
    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 );
  2. @aramonc aramonc revised this gist Sep 24, 2013. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion distance.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -14,7 +14,7 @@ BEGIN
    SET dLat = RADIANS( lat2 ) - RADIANS( lat1 );
    SET dLng = RADIANS( lng2 ) - RADIANS( lng1 );
    SET a1 = SIN( dLat / 2 ) * SIN( dLat / 2 );
    SET a2 = SIN( dLon / 2 ) * SIN( dLon / 2 ) * COS( RADIANS( lng1 )) * COS( RADIANS( lat2 ) );
    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;
  3. @aramonc aramonc created this gist Aug 18, 2013.
    24 changes: 24 additions & 0 deletions distance.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,24 @@
    DELIMITER $$
    CREATE FUNCTION `distance` (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;
    DECLARE dLng DECIMAL;
    DECLARE a1 DECIMAL;
    DECLARE a2 DECIMAL;
    DECLARE a DECIMAL;
    DECLARE c DECIMAL;
    DECLARE d DECIMAL;

    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( dLon / 2 ) * SIN( dLon / 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 ;