Spatial SQL -> PostGIS. Function that measures distance between two points in Lat Long

Here is function that performs distance calculations on Lat-Long coordinates. Pay attention that numbers returned by two functions are not equal. We get difference of 3 meters. PostGIS extension supposed to supply more accurate results, but possibly there is no absolute truth about distance measured on curved (Earth) surface. You can try and see that other DBMS (MS-SQL, Oracle ...) spatial engines can give you somewhat different results as well.


-- Version of Postgres is 9.2

/*
  This function calculates the distance between two points (in LL)
  Uses radius of earth in kilometers or miles as an argurments

  Earth Aproximate radius: 6387.7 (km) (Default) [or 3963.0 (miles)]
  Returns result in Km
*/


CREATE OR REPLACE FUNCTION get_earth_distance1
(lon1 Float, lat1 Float, lon2 Float, lat2 Float, Radius Float DEFAULT 6387.7)
RETURNS FLOAT AS $$

-- Convert degrees to radians
DECLARE K FLOAT := 57.29577951; v_dist FLOAT;

BEGIN
  -- calculate
  v_dist := (Radius * ACOS((SIN(Lat1 / K) * SIN(Lat2 / K))
  + (COS(Lat1 / K) * COS(Lat2 / K) * COS(Lon2 / K - Lon1 / K))));

  -- Return distance in Km
  RETURN round(CAST (v_dist AS Numeric),3);

END;
$$ LANGUAGE 'plpgsql';


/*
  This function calculates the distance between two points (in LL)
  Uses PostGIS Extension method st_distance_sphere()
  Returns result in Km
*/


CREATE OR REPLACE FUNCTION get_earth_distance2
(lon1 Float, lat1 Float, lon2 Float, lat2 Float)
RETURNS FLOAT AS $$

DECLARE point1 GEOMETRY; point2 GEOMETRY; v_dist FLOAT;

BEGIN
  SELECT st_setSRID(st_makepoint(lon1, lat1),4326) INTO point1;
  SELECT st_setSRID(st_makepoint(lon2, lat2),4326) INTO point2;

  SELECT st_distance_sphere(point1, point2) / 1000 INTO v_dist;

  -- Return distance in Km
  RETURN round(CAST (v_dist AS Numeric),3);
END;
$$ LANGUAGE 'plpgsql';



See results:
/* Milan city. Aproximate distance from Duomo to Castello Sforzesco */
SELECT
  get_earth_distance1(9.1918377,45.4642822,9.1794726,45.4702864) AS AproxDist,
  get_earth_distance2(9.1918377,45.4642822,9.1794726,45.4702864) AS PostGIS_Dist;


AproxDistPostGIS_Dist
1.1761.173




sqlexamples.info