0

I have the following code:

CREATE FUNCTION CalculateDistance
    (`Code1` VARCHAR(8), `Code2` VARCHAR(8)) RETURNS FLOAT
BEGIN
    DECLARE Code1Lat FLOAT;
    DECLARE Code1Long FLOAT;
    DECLARE Code2Lat FLOAT;
    DECLARE Code2Long FLOAT;
    DECLARE DLat FLOAT;
    DECLARE DLong FLOAT;
    DECLARE A FLOAT;
    DECLARE C FLOAT;
    Code1Lat = SELECT `lat` FROM `vb_postcodes` WHERE `code` LIKE Code1;
    Code1Long = SELECT `lng` FROM `vb_postcodes` WHERE `code` LIKE Code1;
    Code2Lat = SELECT `lat` FROM `vb_postcodes` WHERE `code` LIKE Code2;
    Code2Long = SELECT `lng` FROM `vb_postcodes` WHERE `code` LIKE Code2;
    DLat = (Code2Lat - Code1Lat) * PI() / 180;
    DLong = (Code2Long - Code1Long) * PI() / 180;
    Code1Lat = Code1Lat * PI() / 180;
    Code2Lat = Code2Lat * PI() / 180;
    A = (SIN(DLat / 2) * SIN(DLat / 2)) + (SIN(DLong / 2) * SIN(DLong / 2)) * COS(Lat1)    * COS(Lat2);
    C = 2 * ATAN2(SQRT(A), SQRT(1 - A));
    Return (3960 * C);
END

This is the haversine formula that will calculate the distance between two co-ordinates. The co-ordinates are stored in the vb_postcodes table with the attributes: code, lng and lat. When I try to execute the code, I get a syntax error.

Please excuse my issues, this is the first time I am making a user defined function.

user2879284
  • 1
  • 2
  • pleas let us know what exact error you are getting? – Pawan Jan 15 '14 at 12:03
  • @Pawan - It seems to be something with the declare statement, the error is: "#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 4" – user2879284 Jan 15 '14 at 12:11
  • 1
    Possible duplicate of http://stackoverflow.com/questions/6740932/mysql-create-function-syntax – Pawan Jan 15 '14 at 12:16
  • @Pawan - That has worked for me, thank you! The only issue now is that I have another 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 'SELECT `lat` FROM `vb_postcodes` WHERE `code` LIKE Code1; SET Code1Long = S' at line 12" – user2879284 Jan 15 '14 at 12:23
  • instead of `code` LIKE Code1; write WHERE 'code' LIKE CONCAT("%",Code1,"%") and do it at all places in all 4 queries. – Pawan Jan 15 '14 at 12:31

0 Answers0