php – 在十进制列中查找MySQL中最接近的匹配项

发布时间：2020-12-13 17:20:31 所属栏目：PHP教程来源：网络整理

导读：下午, 我在解决这个问题时遇到了一些困难.我有一个MySQL表,其中列出了英国邮政编码及其经度和纬度值.我希望能够在桌面上进行搜索,找到与给定长/纬度对最接近的邮政编码. 我一直试图使用的查询是： "SELECT id,outcode AS thecode,@la := MATCH(lat) AGAINST(

下午,

我在解决这个问题时遇到了一些困难.我有一个MySQL表,其中列出了英国邮政编码及其经度和纬度值.我希望能够在桌面上进行搜索,找到与给定长/纬度对最接近的邮政编码.

我一直试图使用的查询是：

"SELECT id,outcode AS thecode,@la := MATCH(lat) AGAINST(?) AS score_lat,@ln := MATCH(lng) AGAINST(?) AS score_lng,@la + @ln AS score_total FROM postcodes ORDER BY score_total DESC LIMIT 10

然而,这只会返回看似随机的邮政编码,例如使用Lat：55.775549和Long：-4.047556

Array
(
[0] => Array
    (
        [id] => 929
        [thecode] => FK14
        [score_lat] => 0
        [score_lng] => 0
        [score_total] => 0
    )

[1] => Array
    (
        [id] => 2785
        [thecode] => UB3
        [score_lat] => 0
        [score_lng] => 0
        [score_total] => 0
    )

[2] => Array
    (
        [id] => 993
        [thecode] => G70
        [score_lat] => 0
        [score_lng] => 0
        [score_total] => 0
    )

[3] => Array
    (
        [id] => 2849
        [thecode] => WC2B
        [score_lat] => 0
        [score_lng] => 0
        [score_total] => 0
    )

[4] => Array
    (
        [id] => 1057
        [thecode] => GU29
        [score_lat] => 0
        [score_lng] => 0
        [score_total] => 0
    )

[5] => Array
    (
        [id] => 2913
        [thecode] => WS13
        [score_lat] => 0
        [score_lng] => 0
        [score_total] => 0
    )

[6] => Array
    (
        [id] => 1121
        [thecode] => HP20
        [score_lat] => 0
        [score_lng] => 0
        [score_total] => 0
    )

[7] => Array
    (
        [id] => 1185
        [thecode] => IG6
        [score_lat] => 0
        [score_lng] => 0
        [score_total] => 0
    )

[8] => Array
    (
        [id] => 1249
        [thecode] => IV25
        [score_lat] => 0
        [score_lng] => 0
        [score_total] => 0
    )

[9] => Array
    (
        [id] => 1313
        [thecode] => KA8
        [score_lat] => 0
        [score_lng] => 0
        [score_total] => 0
    )
)

数据库的架构是：

CREATE TABLE `postcodes` (
  `id` int(11) NOT NULL auto_increment,`outcode` varchar(4) NOT NULL,`lat` varchar(20) NOT NULL,`lng` varchar(20) NOT NULL,PRIMARY KEY  (`id`),FULLTEXT KEY `lat` (`lat`),FULLTEXT KEY `lng` (`lng`)
) ENGINE=MyISAM AUTO_INCREMENT=2975 DEFAULT CHARSET=latin1 AUTO_INCREMENT=2975 ;

我希望有人能帮帮忙！如果您需要更多信息,请询问……

谢谢,

tip2tail

解决方法

MySQL MATCH()函数用于全文搜索以匹配字符串. (所以它返回零值并不奇怪.)

如果“最接近”你的意思是你想要计算距离(如’乌鸦飞’那样测量),在地图上的两个点之间,坐标以(十进制度)纬度和经度给出,你真的需要使用大圆距离(GCD)计算.

http://en.wikipedia.org/wiki/Great-circle_distance

您可以跳过所有这些血腥的细节,只需使用我的实现.下面是我的一个SQL语句的SELECT列表的摘录,这个表达式计算两点之间的距离(以英里为单位)……

,ACOS(
          COS(RADIANS( d2.latitude ))
        * COS(RADIANS( d1.latitude ))
        * COS(RADIANS( d2.longitude ) - RADIANS( d1.longitude ))
        + SIN(RADIANS( d2.latitude ))
        * SIN(RADIANS( d1.latitude ))
           )*3958.82 AS distance_miles

在此示例中,d1表示原点,d2表示目标点.纬度和经度以DECIMAL值的形式提供.

对于d1有一个“已知”点,我可以通过这个表达式来排序,以便首先获得“最接近”的d2. (对于多个原点,我可以通过d1.id排序,然后通过这个表达式来获得每个d1最接近的d2.但足够我的问题…

我从您的问题中复制了查询并对其进行了修改(如下).基本上,我删除了“得分”列,并将其替换为进行距离计算的表达式：

SELECT id,ACOS(
           COS(RADIANS( d2.latitude ))
         * COS(RADIANS( @d1_latitude ))
         * COS(RADIANS( d2.longitude ) - RADIANS( @d1_longitude ))
         + SIN(RADIANS( d2.latitude ))
         * SIN(RADIANS( @d1_latitude ))
           )*3958.82 AS distance_miles
  FROM postcodes d2
  JOIN (SELECT @d1_latitude := ?,@d1_longitude := ?) v
 ORDER BY distance_miles LIMIT 10

在这种情况下,@ d1_变量(从绑定变量分配)是“已知”点的纬度和经度.对于postcodes表中的每一行(为方便起见,我将其别名为d2),此表达式计算表中lat / long与“已知”点之间的距离.

注意：作为v别名的内联视图就在那里,因此您只需将纬度绑定一次,并将值分配给可以引用的用户变量.可以省略该内联视图,您可以看到将纬度绑定两次所需的位置.

注意：这以“英里”计算距离.您可以通过用不同的值代替3958.82常数来轻松获得以公里(km)为单位的距离.

注意：没有必要返回距离;你可以把这个表达式放在ORDER BY子句中,如果你只想按距离返回最近的10个,例如

SELECT id,outcode AS thecode
  FROM postcodes d2
  JOIN (SELECT @d1_latitude := ?,@d1_longitude := ?) v
 ORDER
    BY ACOS(
           COS(RADIANS( d2.latitude ))
         * COS(RADIANS( @d1_latitude ))
         * COS(RADIANS( d2.longitude ) - RADIANS( @d1_longitude ))
         + SIN(RADIANS( d2.latitude ))
         * SIN(RADIANS( @d1_latitude ))
           )*3958.82 AS distance_miles
 LIMIT 10

如果你正在寻找两点以外的距离,请告诉我,因为在这种情况下,这个答案对你没有任何帮助.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!