在所有locations和所有places之间执行距离计算将是非常昂贵的，因此我们将创建一个边界框，该边界框可以使用可用索引来获得places的子集，在该子集上执行距离计算。
我们正在构建一个略大于我们感兴趣的1500m半径的边界框（考虑到不准确性）：

dist in degrees latitude = (dist / rEarth) * (180 / pi)
dist in degrees longitude = (dist / rEarth) * (180 / pi) / cos(latitude * pi/180)

dist = 1.550 km
rEarth ≈ 6371 km

dist in degrees latitude = (dist / rEarth) * (180 / pi)
dist in degrees latitude = (1.55 / 6371) * (180 / pi)
dist in degrees latitude = DEGREES(1.55/6371)
dist in degrees latitude = 0.013939

dist in degrees longitude = (dist / rEarth) * (180 / pi) / cos(latitude * pi/180)
dist in degrees longitude = 0.013939 / COS(RADIANS(latitude))

以位置为中心的3.100 km x 3.100 km边界框开始连接。坐标然后对找到的实体进行点到点距离计算：

SELECT *,
    /*
     * We are pre-splitting the coords here so we don't have to do
     * it repeatedly inside the correlated sub-query
     */
    SUBSTRING_INDEX(l.coords, ',', 1) AS loc_lat,
    SUBSTRING_INDEX(l.coords, ',', -1) AS loc_lon,
    (
        SELECT CONCAT(
                ROUND(ST_Distance_Sphere(
                    ST_GeomFromText(CONCAT('POINT(', REPLACE(l.coords, ',', ' '),')')),
                    ST_GeomFromText(CONCAT('POINT(', lat, ' ', lon,')'))
                ), -1),
                '::',
                place
            ) dist
        FROM places
        WHERE lat BETWEEN loc_lat - 0.013939 AND loc_lat + 0.013939
        AND lon BETWEEN loc_lon - (0.013939 / COS(RADIANS(loc_lat))) AND loc_lon + (0.013939 / COS(RADIANS(loc_lat)))
        HAVING dist <= 1500
        ORDER BY dist ASC
        LIMIT 1
    ) AS place,
    (
        SELECT
            CASE
                WHEN SUBSTRING_INDEX(place, '::', 1) < 300 THEN SUBSTRING_INDEX(place, '::', -1)
                WHEN SUBSTRING_INDEX(place, '::', 1) BETWEEN 300 AND 1500 THEN CONCAT('Near ', SUBSTRING_INDEX(place, '::', -1), '~', SUBSTRING_INDEX(place, '::', 1))
                ELSE 'N/A'
            END
    ) AS place_text
FROM locations l;

这个查询可以工作，但是它如何执行完全是另一回事。理想情况下，你应该有两个复合索引：(lat, lon)和(lon, lat)，但至少具有其中一个是至关重要的，否则您将不断地进行表扫描。