如何基于距离特定纬度/经度来查询Amazon Redshift表中的用户IDS列表？

问题描述：

目前正在使用：如何基于距离特定纬度/经度来查询Amazon Redshift表中的用户IDS列表？

SELECT 
uid,lat,long, 
(
    6371 * 
    acos(
     cos(radians(value)) * 
     cos(radians(lat)) * 
     cos(
      radians(long) - radians(value) 
     ) + 
     sin(radians(value)) * 
     sin(radians(lat)) 
    ) 
) as distance 
FROM 
    table name 
WHERE 
    lat IS NOT Null AND long IS NOT Null 
HAVING 
    distance < 25 
ORDER BY 
    distance 
LIMIT 
    25;

@Serg美丽的编辑，我从来没有见过半正矢/大圆不看起来很丑。 –

@TimBiegeleisen应归于OP。我刚加了一个ident，ctl/K。 Upvoting为美丽的acos。 – Serg

@TimBiegeleisen我喜欢为此使用UDF！ https://stackoverflow.com/questions/30259410/query-to-calculate-sum-of-distance-longitude-latitude-in-consecutive-rows-in – Strawberry

答

MySQL的排序超载HAVING条款，以允许它被用作与可用的别名一个WHERE条款。虽然红移没有这个功能，你可以先计算在CTE或子查询的距离，然后用计算的距离场，避免WHERE子句中重复距离计算：

WITH cte AS (
    SELECT 
     uid, 
     lat, 
     long, 
     (
      6371 * 
      acos(
       cos(radians(value)) * 
       cos(radians(lat)) * 
       cos(
        radians(long) - radians(value) 
       ) + 
       sin(radians(value)) * 
       sin(radians(lat)) 
      ) 
     ) AS distance 
    FROM yourTable 
    WHERE 
     lat IS NOT Null AND 
     long IS NOT Null 
) 

SELECT  
    t.uid, 
    t.lat, 
    t.long 
FROM cte t 
WHERE t.distance < 25 
ORDER BY t.distance 
LIMIT 25;

如果你的版本Redshift不支持CTE，或者您不想使用CTE，只需将上述CTE放入子查询中即可。

这似乎在纬度上返回重复值并且不同用户标识符的经度列以及距离，检查数据库中是否存在具有相同经度/长度的不同用户标识符的问题。 –

@NeilNandi然后这是一个不同于你在你的问题中提出的问题。我已经直接回答你的原始问题。 –

是的。感谢您的解决方案。似乎工作正常@蒂姆 –

答

我创建了一个UDF我自己：

CREATE OR REPLACE FUNCTION public.f_distance(lat1 double precision, lon1 double precision, lat2 double precision, lon2 double precision) 
RETURNS double precision 
IMMUTABLE AS $$ 
    import math 
    if lat1==None or lat2==None or lon1==None or lon2==None: 
     return None 
    if lat1==lat2 and lon1==lon2: 
     return 0 
    else: 
     return (6371*math.acos(
       math.cos(math.radians(lat1))*math.cos(math.radians(lat2))*math.cos(math.radians(lon2)-math.radians(lon1))+ 
       math.sin(math.radians(lat1))*math.sin(math.radians(lat2)) 
      ) 
     ) 
$$ LANGUAGE plpythonu;

所以它可以直接使用这样的：

SELECT uid,lat,long,f_distance(search_lat,search_long,lat,long) as distance 
FROM your_table 
WHERE f_distance(search_lat,search_long,lat,long)<25 
ORDER BY 4 
LIMIT 25

如何基于距离特定纬度/经度来查询Amazon Redshift表中的用户IDS列表？

相关推荐