mysql - 在MySQL中查找与纬度经度最近的邮政编码

我有一张可以保存所有英国邮政编码的表格（我认为大约为180万）

CREATE TABLE `Postcode` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Postcode` varchar(8) DEFAULT NULL,
  `Postcode_Simple` varchar(8) DEFAULT NULL,
  `Positional_Quality_Indicator` int(11) DEFAULT NULL,
  `Eastings` int(11) DEFAULT NULL,
  `Northings` int(11) DEFAULT NULL,
  `Latitude` double DEFAULT NULL,
  `Longitude` double DEFAULT NULL,
  `LatLong` point DEFAULT NULL,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `Postcode` (`Postcode`),
  KEY `Postcode_Simple` (`Postcode_Simple`),
  KEY `LatLong` (`LatLong`(25))
) ENGINE=InnoDB AUTO_INCREMENT=1755933 DEFAULT CHARSET=latin1;

我要实现的是...给一个坐标，找到离该坐标最近的邮政编码。问题是我在编写查询时遇到了问题（实际上是在存储过程中）。查询是：

SELECT
    Postcode
FROM
    (SELECT
        Postcode,
        GLENGTH(
            LINESTRINGFROMWKB(
                LINESTRING(
                    LatLong,
                    GEOMFROMTEXT(CONCAT('POINT(', varLatitude, ' ', varLongitude, ')'))
                )
            )
        ) AS distance
    FROM
        Postcode
    WHERE
        NOT LatLong IS NULL) P
ORDER BY
    Distance
LIMIT
    1;

我遇到的问题是查询需要花12秒钟才能运行，而我却不能花那么长时间才能得到结果。谁能想到我可以可靠地加快查询速度的任何方法？

（这是查询的解释）

id select_type table      type possible_keys key    key_len ref    rows    Extra
1  PRIMARY     <derived2> ALL  (NULL)        (NULL) (NULL)  (NULL) 1688034 Using filesort
2  DERIVED     Postcode   ALL  LatLong       (NULL) (NULL)  (NULL) 1717998 Using where

我一直在尝试一种方法来缩小我必须执行距离计算的初始数据量，但是我无法提出任何不限制在给定范围内查找邮政编码的方法距离。

最佳答案

也许尝试以下方法：

SELECT Postcode, lat, lon
FROM
(
SELECT Postcode, MAX(latitude) AS lat, MAX(longitude) AS lon
FROM PostCode

-- field name
GROUP BY Postcode

HAVING MAX(latitude)<varLatitude AND MAX(longitude)<varLongitude

LIMIT 1
) AS temp

这基本上将带来其lat和lon小于您指定的邮政编码，但大于小于您的vars的任何其他lat / lon组合的邮政编码；因此最有效地接近您的var的经/纬度，因此也是最接近的邮政编码。您可以使用MIN和更大的值尝试相同的方法，然后反之亦然。

以上只会给您一个结果/邮政编码。如果您希望获得一些更新颖的东西，例如查找一组以lat / long特定半径给出的邮政编码，那么您应该看看https://developers.google.com/maps/articles/phpsqlsearch_v3#findnearsql中解释的公式

关于mysql - 在MySQL中查找与纬度经度最近的邮政编码，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/12934746/