纬度经度语法的MySQL用户定义函数 [英] MySQL User Defined Function for Latitude Longitude Syntax

问题描述

我创建了一个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'附近使用正确的语法 返回INT(1) 开始 第4行的DECLARE距离小数(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.

(免责声明:请确保将lats和longs放在正确的位置.我想是的.)

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

这篇关于纬度经度语法的MySQL用户定义函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！