MySQL User Defined Function for Latitude Longitude

问题:

I have created a MySQL function to determine if a set of latitude and longitude coordinates are within a certain range of another set of latitude and longitude coordinates. However, the function is giving me a syntax error so I cannot test to see if it is working properly. Any help figuring out what is causing the error would be greatly appreciated. The function along with a description of it is written below:

It works by having the starting lat/long coordinates passed to the function. The database contains the rows targa, targb, and targc, that contain the latitude, longitude, and range (respectively) to compare to. The targ column in the database specifies whether or not this row should be checked against for latitude/longitude range.

CREATE FUNCTION inrange(
 lat1 decimal(11, 7), 
 lon1 decimal(11, 7))
 READS SQL DATA
 RETURNS INT(1)
BEGIN
 DECLARE distance decimal(18, 10);

 SET distance = ACOS(SIN(lat1)*SIN(targ2)+COS(lat1)*COS(targ2)*COS(targ3-lon1))*6371;

 IF distance <= targ4 THEN 
  RETURN 1;
 END IF;

 RETURN 0;
END$$

The error that mysql is giving me 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 'READS SQL DATA RETURNS INT(1) BEGIN DECLARE distance decimal(18, 10)' at line 4

I can't seem to figure out how to get past this error.

Also, if anyone came to this post looking for such a mysql function, I also had another related post:

MySQL Function to Determine Zip Code Proximity / Range

I'm very grateful to the other poster for his function (which served as the inspiration for this one), but I need something more compact. Hence, the function in question.

EDIT: The following code is functional and working. Just remember to set the delimiter to $$. Once again, thanks all for the help.

Working Code:

CREATE FUNCTION inrange(
    lat1 decimal(11, 7), 
    long1 decimal(11, 7),
    lat2 decimal(11, 7),
    long2 decimal(11, 7),
    rng decimal(18, 10))
    RETURNS INT(1)
BEGIN
    DECLARE distance decimal(18, 10);

    SET lat1 = lat1 * PI() / 180.0,
       long1 = long1 * PI() / 180.0,
       lat2 = lat2 * PI() / 180.0,
       long2 = long2 * PI() / 180.0;

    SET distance = ACOS(SIN(lat1)*SIN(lat2)+COS(lat1)*COS(lat2)*COS(long2-long1))*6371;

    IF distance <= rng THEN 
        RETURN 1;
    END IF;

    RETURN 0;
END$$

回答1:

You have your RETURNS and READS SQL DATA out of order. RETURNS comes first:

CREATE FUNCTION inrange(
    lat1 decimal(11, 7), 
    lon1 decimal(11, 7))
    RETURNS INT(1)
    READS SQL DATA
BEGIN

EDIT: Also, you refer to columns in your database, as you said in your comment. Not only does your function not know to which table those columns belong, but generally, you should not refer to columns from within functions. Instead, add parameters to your function:

CREATE FUNCTION inrange(
    lat1 decimal(11, 7), 
    long1 decimal(11, 7),
    lat2 decimal(11, 7),
    long2 decimal(11, 7),
    rng decimal(18, 10))
    RETURNS INT(1)
BEGIN
    DECLARE distance decimal(18, 10);

    SET distance = ACOS(SIN(lat1)*SIN(lat2)+COS(lat1)*COS(lat2)*COS(long2-long1))*6371;

    IF distance <= rng THEN 
        RETURN 1;
    END IF;

    RETURN 0;
END$$

Then, in your queries, you can pass the column names to the function.

(Disclaimer: Make sure that I put the lats and longs in the correct places. I think I did.)

回答2:

From the procedure you have included, it looks like your delimiter is set to $$.

If so, please try the following:

CREATE FUNCTION inrange(
 lat1 decimal(11, 7), 
 lon1 decimal(11, 7))
 RETURNS INT(1)
 READS SQL DATA
BEGIN
 DECLARE distance decimal(18, 10);

 SET distance = ACOS(SIN(lat1)*SIN(targ2)+COS(lat1)*COS(targ2)*COS(targ3-lon1))*6371;

 IF distance <= targ4 THEN 
  RETURN 1;
 END IF;

 RETURN 0;
END$$