如何在MySQL中查询具有地理空间“点”字段的表

Question

我有一个“places”表，其中包含lat、lng和point字段。我一直用哈弗森公式来计算lat，lng的距离，但我读到使用点场更有效？我只是找不到关于怎么做的文件。这是我的查询，因为它现在是查询所有的地方和排序他们的距离

 SELECT `places`.*, `places`.`id` AS `pid`,   
        ((6371*acos(cos(radians(37.776708))*cos(radians(lat))*
          cos(radians(lng)-radians(-122.398050))+sin(radians(37.776708))*
          sin(radians(lat))))*1000) AS `distance` 
   FROM `places` 
  WHERE `page_id` IS NOT null 
  ORDER BY `distance` ASC 
  LIMIT 40 OFFSET 0

我如何将其更改为使用表中的“point”字段而不是LAT/LNG

Answer 1

要使用MySQL地理空间扩展来完成这类工作，您需要做一些事情。

1. 您必须使用MyISAM表作为您的地理空间数据。
2. 表中需要有几何图形列，并且该列不能为空。
3. 您不能使用问题中提到的球余弦定律距离计算作为您的主要搜索标准。您需要使用一个边界框来代替（也就是说，您需要在您的搜索点的特定距离内搜索点。

您当前的查询只是通过检查places表中的所有点并按邻近顺序排序来找到最接近（37.77671,-122.3980）的点。当你使用地理空间的时候，你正在做一些比这更聪明的事情。你需要建立一个接近限制。我们称它为10.0规约英里的半径。请注意，每纬度有69.0规约英里。

首先给自己做一个有几何图形列的表格，可能是这样的：

CREATE TABLE geoplace (
  id INT NOT NULL,
  geo GEOMETRY NOT NULL,
  PRIMARY KEY id (id),
  SPATIAL KEY geo (geo)
) ENGINE=MYISAM

然后，按如下方式填充它，在每个geo值中放入一个几何point项。

INSERT INTO geoplace
SELECT DISTINCT id,
                GEOMFROMTEXT(
                  CONCAT('POINT(',
                     lat,
                     ' ',
                     lng,
                ')')  ) AS geo
 FROM places

显然，您可以将几何图形列放入任何您想要的表中。但您不能在InnoDB表中创建正常工作的空间索引；该访问方法不支持空间索引。

SELECT id, X(geo), Y(geo), distance FROM (
SELECT id, geo,r,
        units * DEGREES( ACOS(
                   COS(RADIANS(latpoint))
                 * COS(RADIANS(X(geo)))
                 * COS(RADIANS(longpoint) - RADIANS(Y(geo)))
                 + SIN(RADIANS(latpoint))
                 * SIN(RADIANS(X(geo))))) AS distance
 FROM geoplace
 JOIN (
        SELECT 37.776708  AS latpoint,  -122.398050 AS longpoint,
               10.0 AS r, 69.0 AS units
   ) AS p ON (1=1)
 WHERE MbrContains(GeomFromText(
       CONCAT('LINESTRING(',
              latpoint-(r/units),' ',
              longpoint-(r /(units* COS(RADIANS(latpoint)))),
              ',',
              latpoint+(r/units) ,' ',
              longpoint+(r /(units * COS(RADIANS(latpoint)))),
              ')')),  geo)
) AS d
 WHERE distance <= r
 ORDER BY distance

        SELECT 37.776708  AS latpoint,  -122.398050 AS longpoint,
               10.0 AS r, 69.0 AS units

它将使用mbrcontains进行空间矩形搜索，然后使用球面余弦定律公式计算找到的点的距离，然后对它们进行正确排序。与您开始使用的查询相比，这个东西运行得相当快，因为它利用了空间索引。

这在这里写得更详细。

http://www.plumislandmedia.net/mysql/using-mysqls-geospatial-extension-location-finder/

http://www.plumislandmedia.net/mysql/haversine-mysql-nexarent-loc/