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;

 AproxDist PostGIS_Dist 1.176 1.173

sqlexamples.info