Geek asks and answers

MySQL function to calculate haversin distance

all.

I want to create a mysql function that calculates the haversine distance between two geocodes.

 CREATE FUNCTION haversineDistance
                 (
                  lat1 VARCHAR(256), 
                  long1 VARCHAR(256), 
                  lat2 VARCHAR(256), 
                  long2 VARCHAR(256)
                 )
 RETURNS FLOAT DETERMINISTIC
 BEGIN

      DECLARE radLat1 FLOAT;
      DECLARE radLong1 FLOAT;
      DECLARE radLat2 FLOAT;
      DECLARE radLong2 FLOAT;
      DECLARE dLat FLOAT;
      DECLARE dLong FLOAT;
      DECLARE cordLength FLOAT;
      DECLARE centralAngle FLOAT;
      DECLARE distance FLOAT;

      SET radLat1 = CAST(lat1 AS FLOAT) * PI() / 180;
      SET radLong1 = CAST(long1 AS FLOAT) * PI() / 180;
      SET radLat2 = CAST(lat2 AS FLOAT) * PI() / 180;
      SET radLong2 = CAST(long2 AS FLOAT) * PI() / 180;
      SET dLat = radLat2 - radLat1;
      SET dLong = radLong2 - radLong2;
      SET cordLength = POW(SIN(dLat/2),2)+COS(lat1)*COS(lat2)*POW(SIN(dLon/2),2);
      SET centralAngle = 2 * ATAN2(SQRT(cordLength), SQRT(1-cordLength));
      SET distance = 6367 * centralAngle;

      RETURN distance;
 END

But I have this error:

1064 - You have an error in the SQL syntax; check the manual that matches your version of MySQL server for the correct syntax to use about '' on line 4

Anyone have an idea?

Thanks in advance.

+3
source share
2 answers

Good.

I decided.

 DELIMITER $$
 CREATE FUNCTION haversineDistance
                 (
                  lat1 VARCHAR(256), 
                  long1 VARCHAR(256), 
                  lat2 VARCHAR(256), 
                  long2 VARCHAR(256))
 RETURNS FLOAT DETERMINISTIC
 BEGIN

      DECLARE radLat1 FLOAT;
      DECLARE radLong1 FLOAT;
      DECLARE radLat2 FLOAT;
      DECLARE radLong2 FLOAT;
      DECLARE dLat FLOAT;
      DECLARE dLong FLOAT;
      DECLARE cordLength FLOAT;
      DECLARE centralAngle FLOAT;
      DECLARE distance FLOAT;

      SET radLat1 = CAST(lat1 AS DECIMAL(10,10)) * PI() / 180;
      SET radLong1 = CAST(long1 AS DECIMAL(10,10)) * PI() / 180;
      SET radLat2 = CAST(lat2 AS DECIMAL(10,10)) * PI() / 180;
      SET radLong2 = CAST(long2 AS DECIMAL(10,10)) * PI() / 180;
      SET dLat = radLat2 - radLat1;
      SET dLong = radLong2 - radLong2;
      SET cordLength = POW(SIN(dLat/2),2)+COS(lat1)*COS(lat2)*POW(SIN(dLon/2),2);
      SET centralAngle = 2 * ATAN2(SQRT(cordLength), SQRT(1-cordLength));
      SET distance = 6367 * centralAngle;

      RETURN distance;
 END
+2
source

Now you get an error due to semicolons present as a line separator in your function.

To explain this further by directly quoting the MySQL documentation

mysql , , mysql .

, . Enacapsulate

DELIMITER $$
   your code for function
$$

.

EDIT: ,

mysql> DELIMITER ;
0

More articles:


All Articles