php对mysql最匹配输出_php – 在十进制列中查找MySQL中最接近的匹配项

下午,

我在解决这个问题时遇到了一些困难.我有一个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