如何提高计算hasversine公式的查询性能？

用户名

给定一张具有经度和纬度的位置表，哪些位置最接近给定位置？

当然，在地球表面找到距离意味着要使用由Haversine公式（也称为球余弦定律公式）得出的大圆距离。

我有以下代码：

 SELECT zip, latitude, longitude, distance
 FROM (
  SELECT z.zip,
         z.latitude, z.longitude,
         p.radius,
         p.distance_unit
             * DEGREES(ACOS(COS(RADIANS(p.latpoint))
             * COS(RADIANS(z.latitude))
             * COS(RADIANS(p.longpoint - z.longitude))
             + SIN(RADIANS(p.latpoint))
             * SIN(RADIANS(z.latitude)))) AS distance
  FROM zip AS z
   JOIN (   /* these are the query parameters */
    SELECT  42.81 AS latpoint, -70.81 AS longpoint,
            50.0  AS radius,  111.045 AS distance_unit
        ) AS p ON 1=1
  WHERE z.latitude
    BETWEEN p.latpoint  - (p.radius / p.distance_unit)
        AND p.latpoint  + (p.radius / p.distance_unit)
   AND z.longitude
    BETWEEN p.longpoint - (p.radius / (p.distance_unit * COS(RADIANS(p.latpoint))))
        AND p.longpoint + (p.radius / (p.distance_unit * COS(RADIANS(p.latpoint))))
) AS d
WHERE distance <= radius

有什么方法可以改善此查询的性能吗？

是否有必要使用PostGIS进行改进，或者仅仅是我的hasversine公式的包装？

克洛多尔多·内托

我猜想计划人员将自己重写此查询，但是值得尝试。至少它更整洁。

select zip, latitude, longitude, distance
from (
    select z.zip,
           z.latitude, z.longitude,
           p.radius,
           p.distance_unit
               * p.degrees_acos_cos_radians_latpoint
               * cos(radians(z.latitude))
               * cos(radians(p.longpoint - z.longitude))
               + p.sin_radians_latpoint
               * sin(radians(z.latitude)))) as distance
    from
        zip z
        cross join (
            select
                latpoint, longpoint, radius, distance_unit,
                latpoint - radius / distance_unit as lat0,
                latpoint + radius / distance_unit as lat1,
                longpoint - radius / distance_unit * cos(radians(latpoint)) as long0,
                longpoint + radius / distance_unit * cos(radians(latpoint)) as long1,
                sin(radians(latpoint)) as sin_radians_latpoint,
                degrees(acos(cos(radians(latpoint)) as degrees_acos_cos_radians_latpoint
            from (
                values (42.81, -70.81, 50.0, 111.045)
            ) v (latpoint, longpoint, radius, distance_unit)
        ) p
    where
        z.latitude between lat0 and lat1
        and
        z.longitude between long0 and long1
) d
where distance <= radius

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。