我正试图根据社区邮政编码并使用Haversine公式和这里描述的SQL来分配离社区最近的位置。我需要返回一个标量值,但我似乎无法避免第二个计算的距离值,这是确定最近位置所需的。帮助
UPDATE Community AS c
JOIN Postcode p on p.id = c.postcode_id
JOIN (
SELECT 100.0 AS radius, 111.045 AS distance_unit
) AS a
SET c.location_id = (
SELECT l.id,
a.distance_unit
* DEGREES(ACOS(COS(RADIANS(p.latitude))
* COS(RADIANS(l.latitude))
* COS(RADIANS(p.longitude - l.longitude))
+ SIN(RADIANS(p.latitude))
* SIN(RADIANS(l.latitude)))) AS distance
FROM Location AS l
WHERE l.latitude
BETWEEN p.latitude - (a.radius / a.distance_unit)
AND p.latitude + (a.radius / a.distance_unit)
AND l.longitude
BETWEEN p.longitude - (a.radius / (a.distance_unit * COS(RADIANS(p.latitude))))
AND p.longitude + (a.radius / (a.distance_unit * COS(RADIANS(p.latitude))))
HAVING distance <= a.radius
ORDER BY distance
LIMIT 1
)
使用现有结构,需要将距离计算移动到WHERE
和ORDER BY
子句中:
SET c.location_id = (
SELECT l.id
FROM Location AS l
WHERE l.latitude
BETWEEN p.latitude - (a.radius / a.distance_unit)
AND p.latitude + (a.radius / a.distance_unit)
AND l.longitude
BETWEEN p.longitude - (a.radius / (a.distance_unit * COS(RADIANS(p.latitude))))
AND p.longitude + (a.radius / (a.distance_unit * COS(RADIANS(p.latitude))))
AND a.distance_unit
* DEGREES(ACOS(COS(RADIANS(p.latitude))
* COS(RADIANS(l.latitude))
* COS(RADIANS(p.longitude - l.longitude))
+ SIN(RADIANS(p.latitude))
* SIN(RADIANS(l.latitude)))) <= a.radius
ORDER BY a.distance_unit
* DEGREES(ACOS(COS(RADIANS(p.latitude))
* COS(RADIANS(l.latitude))
* COS(RADIANS(p.longitude - l.longitude))
+ SIN(RADIANS(p.latitude))
* SIN(RADIANS(l.latitude))))
LIMIT 1
)