CODEWITHSUNDEEP
mysqlsqlfunctioncoordinatesdistance
Ariel
Ariel

Reputation: 1232

What is the error for this MySQL function for lat-lng distance?

I am trying to insert a function that I found on the web into my MySQL database that should calculate the distance between 2 points given their respective coordinates. I found the function here: http://www.sqlexamples.info/SPAT/mysql_distance.htm

This is what I'm trying to insert into MySQL:

USE city_notifier; /*it is the name of my database*/

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;

It continues to give me an error: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 6. But I cannot find any error in the function. Can you guys help me please? What is wrong with that function?

Upvotes: 0

Views: 232

Answers (2)

user2196728
user2196728

Reputation: 3037

This should work :

USE city_notifier; /*it is the name of my database*/

DROP FUNCTION IF EXISTS fn_distance;

DELIMITER $$
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$$
DELIMITER ;

Upvotes: 1

Brett Schneider
Brett Schneider

Reputation: 4103

i think you should try

  IF ((A = C) && (B = D)) THEN

to make it work.

Upvotes: 0

Related Questions