php - 优化查询(2个坐标之间的距离)

Question

我有一个大的 POI 数据库，现在约为 150K，但会增长到约 1M

我需要返回某个点(经纬度)特定半径内的所有点我有这个查询，在行数增加之前效果很好。而且这个查询使用了大约 60% 的 cpu 并且基本上 super 慢

SELECT 
    *,
    (((acos(sin((57.757134*pi()/180)) * 
                    sin((coX*pi()/180))+cos((57.757134*pi()/180)) * 
                    cos((coX*pi()/180)) * cos(((37.616015- coY)* 
                    pi()/180))))*180/pi())*60*1.1515
                    ) as distance 
FROM 
    places_en
JOIN (   /* these are the query parameters */
      SELECT  
          57.757134  AS latpoint, 37.616015 AS longpoint,
          200 AS radius, 111.045 AS distance_unit) AS p
WHERE 
     description != ''
     AND coX BETWEEN p.latpoint  - (p.radius / p.distance_unit)
                 AND p.latpoint  + (p.radius / p.distance_unit)
     AND coY BETWEEN p.longpoint - (p.radius / (p.distance_unit * COS(RADIANS(p.latpoint))))
                 AND p.longpoint + (p.radius / (p.distance_unit * COS(RADIANS(p.latpoint))))
HAVING 
    distance <= 200  
ORDER BY 
    distance;

我怎样才能让它变得更好？

谢谢

Answer 1

如果有人遇到这个问题，结果如下:

我已将“geoPoint”列设置为 POINT 类型。

   SELECT
            *,
            (GLength(
            LineStringFromWKB(
              LineString(
                geoPoint, 
                GeomFromText('POINT(".$lat." ".$lon.")')
              )
             )
            ))
            AS distance
            FROM TABLE_NAME
            HAVING distance <= ".$radius."
            ORDER BY distance