下午,
我很难理解这个问题。我有一个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
表示终点。latitude
和longitude
作为DECIMAL值提供。
有了d1
的一个"已知"点,我可以通过这个表达式排序,首先得到"最接近"的d2
。(对于多个原点,我可以通过d1.id
排序,然后通过这个表达式为每个d1
首先获得最接近的d2
。但我的问题已经足够了…
我从你的问题中复制了查询并对其进行了修改(如下)。基本上,我删除了"分数"列,并将其替换为一个进行距离计算的表达式:
SELECT id
, outcode AS thecode
, 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
),此表达式将计算表中的纬度/经度与"已知"点之间的距离。
注意:别名为v
的内联视图就在那里,因此您可以只绑定一次纬度,并将值分配给可以引用的用户变量。可以省略该内联视图,并且可以看到需要绑定纬度两次的位置。
注意:这是以"英里"为单位计算距离的。通过用不同的值代替3958.82
常数,可以很容易地获得以千米(km)为单位的距离。
注意:没有必要返回距离;如果您只想返回按距离排序的10个最接近的表达式,例如,则可以将该表达式放入ORDER BY子句中
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
如果你正在寻找两点之间的距离以外的东西,请告诉我,因为在这种情况下,这个答案对你真的没有帮助。