mysql - 使用 POINT 列的邮政编码半径搜索

Question

我有一个 MySQL 例程，当通过 Haversin 方程传递纬度和经度时，它会获取 50 英里半径范围内的记录。

虽然这很好用，而且速度非常快(考虑到它正在搜索 82k 条记录)，但我认为我可以通过使用 POINT 列创建类似的过程来获得更好的性能。

所以，在我的表中，我创建了一个名为 Location 的额外列，为其指定了 POINT 数据类型，更新了我的数据以将经纬度传递给 Location 列。数据有效，很好。，并添加了一个 Spatial Index

问题是，如何将以下查询转换为使用 Location 列，而不是 lat 和 lon 列。

SET @LAT := '37.953';
SET @LON := '-105.688';

SELECT DISTINCT
BPZ.`store_id`,         
3956 * 2 * ASIN(SQRT(POWER(SIN((@LAT - abs(Z.`lat`)) * pi()/180 / 2),2) + COS(@LAT * pi()/180 ) * COS(abs(Z.`lat`) *  pi()/180) * POWER(SIN((@LON - Z.`lon`) *  pi()/180 / 2), 2))) as distance,
c.`name`,c.`address`,c.`city`,c.`state`,c.`phone`,c.`zip`,c.`premise_type`
FROM
`zip_codes` as Z, 
`brand_product_zip` as BPZ
LEFT JOIN `customers` c ON c.`store_id` = BPZ.`store_id`
WHERE
BPZ.`zip` = Z.`zip`
AND 
3956 * 2 * ASIN(SQRT(POWER(SIN((@LAT - abs(Z.`lat`)) * pi()/180 / 2),2) + COS(@LAT * pi()/180 ) * COS(abs(Z.`lat`) *  pi()/180) * POWER(SIN((@LON - Z.`lon`) *  pi()/180 / 2), 2))) <= 50
ORDER BY
distance LIMIT 20

我知道之前有人问过这个问题，但是，我看到的所有内容都指向基于 lat 和 lon 而不是 POINT 的计算专栏

更新代码:

SET @lat = 41.92;
SET @lon = -72.65;
SET @kmRange = 80.4672; -- = 50 Miles

SELECT *, (3956 * 2 * ASIN(SQRT(POWER(SIN((@lat - abs(`lat`)) * pi()/180 / 2),2) + COS(@lat * pi()/180 ) * COS(abs(`lat`) *  pi()/180) * POWER(SIN((lon - `lon`) *  pi()/180 / 2), 2)))) as distance
FROM    `zip_codes`
WHERE   MBRContains(LineString(Point(@lat + @kmRange / 111.1, @lon + @kmRange / (111.1 / COS(RADIANS(@lat)))), Point(@lat - @kmRange / 111.1, @lon - @kmRange / (111.1 / COS(RADIANS(@lat))))), `Location`)
Order By distance
LIMIT 20

Answer 1

您是否研究过希尔伯特曲线解？空间索引不能提供准确的解决方案？ .对于 mysql 空间索引，您可以使用 mbrcontains:

CREATE TABLE lastcrawl (id INT NOT NULL PRIMARY KEY, pnt POINT NOT NULL) ENGINE=MyISAM;

INSERT
INTO    lastcrawl
VALUES  (1, POINT(40, -100));

SET @lat = 40;
SET @lon = -100;

SELECT  *
FROM    lastcrawl
WHERE   MBRContains
                (
                LineString
                        (
                        Point
                                 (
                                 @lat + 10 / 111.1,
                                 @lon + 10 / ( 111.1 / COS(RADIANS(@lat)))
                                 ),
                        Point    (
                                 @lat - 10 / 111.1,
                                 @lon - 10 / ( 111.1 / COS(RADIANS(@lat)))
                                 )
                        ),
                pnt
                );

看这里:MySQL - selecting near a spatial point .这里:http://www.drdobbs.com/database/space-filling-curves-in-geospatial-appli/184410998