纬度经度语法的 MySQL 用户定义函数

2022-01-05 00:00:00 distance user-defined-functions latitude-longitude mysql phpmyadmin

我创建了一个 MySQL 函数来确定一组经纬度坐标是否在另一组经纬度坐标的特定范围内.但是，该函数给了我一个语法错误，所以我无法测试它是否正常工作.任何帮助找出导致错误的原因将不胜感激.函数及其描述如下:

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:

它的工作原理是将起始纬度/经度坐标传递给函数.数据库包含行 targa、targb 和 targc，其中包含要比较的纬度、经度和范围(分别).数据库中的 targ 列指定是否应检查此行的纬度/经度范围.

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

mysql 给我的错误是:

The error that mysql is giving me is:

1064 - 您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，了解在READS SQL DATA"附近使用的正确语法退货积分(1)开始在第 4 行声明距离小数 (18, 10)'

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.

另外，如果有人来这个帖子寻找这样的mysql函数，我还有另一个相关的帖子:

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

确定邮政编码邻近度/范围的 MySQL 函数

我非常感谢另一张海报的功能(它是这个海报的灵感来源)，但我需要更紧凑的东西.因此，有问题的函数.

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.

以下代码可以正常工作.请记住将分隔符设置为 $$.再次感谢大家的帮助.

工作代码:

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

推荐答案

您的 RETURNS 和 READS SQL DATA 出现乱序.退货优先:

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

此外，正如您在评论中所说，您指的是数据库中的列.您的函数不仅不知道这些列属于哪个表，而且通常您不应该从函数内部引用列.相反，向您的函数添加参数:

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.)

相关文章