纠正MySQL空间查询的逻辑 - Correcting Logic For MySQL Spatial Query

Correcting Logic For MySQL Spatial Query

本文关键字：查询 MySQL 空间纠正 | 更新日期: 2023-09-27

我的主要目标是在MySQL中创建一个PROCEDURE，根据传递的经度和纬度查询位置。该查询在传递给PROCEDURE的特定半径内获取位置的id、纬度和经度。我还试图添加一个JOIN来查询每个位置的照片的"照片"表。

查询部分有效。它需要返回所有不带double的位置，以及所有字符串形式的照片。

GROUP_CONCAT(CONVERT(photos.pid, CHAR(8))) AS photo

添加了这段代码后，查询只返回1个结果，并将所有照片id连接到一个字段中，即使它与该位置没有关联。如果我删除这段代码，它会返回每个位置的1，但对于有1张或更多照片的位置，结果会翻倍，所以photos字段需要被连接，但我这样做不对。

CREATE PROCEDURE `GEODIST`( IN userid int, IN dist int, IN olat float, IN olon float ) DETERMINISTIC READS SQL DATA
BEGIN
DECLARE mylon DOUBLE;
DECLARE mylat DOUBLE;
DECLARE lon1 FLOAT;
DECLARE lon2 FLOAT;
DECLARE lat1 FLOAT;
DECLARE lat2 FLOAT;
SET mylon = olon;
SET mylat = olat;
SET lon1 = mylon - dist / abs( cos( radians( mylat ) ) * 69 );
SET lon2 = mylon + dist / abs( cos( radians( mylat ) ) * 69 );
SET lat1 = mylat - ( dist / 69 );
SET lat2 = mylat + ( dist / 69 );
SELECT GROUP_CONCAT(CONVERT(photos.pid, CHAR(8))) AS photo, destination.id, 
destination.latitude, destination.longitude,
3956 * 2 * ASIN(SQRT( POWER(SIN((origin.latitude -destination.latitude) * pi()/180 / 2), 2)
+COS(origin.latitude * pi()/180) * COS(destination.latitude * pi()/180)
*POWER(SIN((origin.longitude -destination.longitude) * pi()/180 / 2), 2) ))
AS distance FROM locations destination CROSS JOIN locations origin LEFT JOIN photos ON photos.lid = destination.id WHERE origin.id = userid
AND destination.longitude BETWEEN lon1 AND lon2 AND destination.latitude BETWEEN lat1 and lat2 
HAVING distance < dist ORDER BY distance LIMIT 50;
END

我想象它应该是这样的，但我没有办法测试它。

SELECT 
    GROUP_CONCAT( CONVERT( DISTINCT( photos.pid ), CHAR(8) ) ) ) AS photo,
    destination.id, 
    destination.latitude,
    destination.longitude,
    3956 * 2 * ASIN(SQRT( POWER(SIN((origin.latitude -destination.latitude) * pi()/180 / 2), 2)
        +COS(origin.latitude * pi()/180) * COS(destination.latitude * pi()/180)
        *POWER(SIN((origin.longitude -destination.longitude) * pi()/180 / 2), 2) ))
    AS distance
FROM
    locations destination
CROSS JOIN
    locations origin
LEFT JOIN
    photos ON photos.lid = destination.id
WHERE
    origin.id = userid
    AND
    destination.longitude BETWEEN lon1 AND lon2
    AND destination.latitude BETWEEN lat1 and lat2 
    HAVING distance < dist
GROUP BY
    destination.id  
ORDER BY
    distance
LIMIT 50;