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

Here we'll create function that will perform distance calculations on Lat-Long coordinates.

USE colombo;

DROP FUNCTION IF EXISTS fn_distance;

CREATE FUNCTION fn_distance
  (p_x1 FLOAT, p_y1 FLOAT, p_x2 FLOAT, p_y2 FLOAT)
  RETURNS FLOAT
  DETERMINISTIC
BEGIN
  DECLARE v_dist FLOAT;
  DECLARE A FLOAT; DECLARE B FLOAT;
  DECLARE C FLOAT; DECLARE D FLOAT;
  /*
   returns distance calculation between two points in
   LAT-LONG coordinates
  */

  SET v_dist = 0;

  -- convert to radians
  SET A = p_x1 / 57.29577951;
  SET B = p_y1 / 57.29577951;
  SET C = p_x2 / 57.29577951;
  SET D = p_y2 / 57.29577951;

  IF (A = C && B = D) THEN
   SET v_dist = 0;
  ELSEIF ((sin(A)*sin(C)+cos(A)*cos(C)*cos(B - D)) > 1) THEN
   SET v_dist = 3963.1 * acos(1);
  ELSE
   SET v_dist = 3963.1*acos(sin(A)*sin(C) + cos(A)*cos(C)*cos(B - D));
  END IF;

  SET v_dist = v_dist * 1.609;

  /* return distance in km. */
  RETURN v_dist;

END;



See results:
/* distance from Duomo to Castello Sforzesco */
SELECT fn_distance (9.1918377,45.4642822,9.1794726,45.4702864) AS dist_km;


dist_km
1.525988817215




sqlexamples.info