游客与最近商店之间的测地距离

Question

我有一个很大的.csv数据集，包含10e7点，坐标(纬度、经度)代表游客的位置。我有另一个数据集，包含10e3点，坐标代表商店的位置。

我想用某种测地线公式，把最近的商店和每一位游客联系起来。

我想要一些非常快速和高效的东西，我可以在python (例如熊猫)或Google BigQuery上运行。

谁能给我个线索吗？

Answer 1

为了补充费利佩的答案：

您可以使用SQL UDF和。

JS UDF有一些SQL UDF不具备的限制。

因此，与Felipe的其余代码一起使用的等效SQL UDF是

CREATE TEMPORARY FUNCTION distance(lat1 FLOAT64, lon1 FLOAT64, lat2 FLOAT64, lon2 FLOAT64)
RETURNS FLOAT64 AS ((
WITH constants AS (
  SELECT 0.017453292519943295 AS p
) 
SELECT 12742 * ASIN(SQRT(
  0.5 - COS((lat2 - lat1) * p)/2 + 
  COS(lat1 * p) * COS(lat2 * p) * 
  (1 - COS((lon2 - lon1) * p))/2))
FROM constants
));

我尽量保留各自JS的布局，这样您就可以看到它是如何创建的。

Answer 2

这是一个快速解决方案，找到最近的NOAA气象站在21,221个城市在DBpedia (v2014)。

#standardSQL

CREATE TEMPORARY FUNCTION distance(lat1 FLOAT64, lon1 FLOAT64, lat2 FLOAT64, lon2 FLOAT64)
RETURNS FLOAT64
LANGUAGE js AS """

  var p = 0.017453292519943295;    // Math.PI / 180
  var c = Math.cos;
  var a = 0.5 - c((lat2 - lat1) * p)/2 + 
          c(lat1 * p) * c(lat2 * p) * 
          (1 - c((lon2 - lon1) * p))/2;

  return 12742 * Math.asin(Math.sqrt(a)); // 2 * R; R = 6371 km

""";

SELECT *
FROM (
  SELECT city, country_label, distance, name weather_station, country, 
    RANK() OVER(PARTITION BY city ORDER BY distance DESC) rank
  FROM (
    SELECT city, a.country_label, distance(a.lat,a.lon,b.lat,b.lon) distance, b.name, b.country
    FROM (
      SELECT rdf_schema_label city, country_label, country,
        CAST(REGEXP_EXTRACT(point, r'(-?\d*\.\d*)') as FLOAT64) lat, 
        CAST(REGEXP_EXTRACT(point, r' (-?\d*\.\d*)') as FLOAT64) lon 
      FROM `fh-bigquery.dbpedia2014temp.City`
      WHERE point!='NULL'
    ) a
    JOIN (
      SELECT name, country, usaf, wban, lat, lon
      FROM `bigquery-public-data.noaa_gsod.stations`
      WHERE lat != 0.0 AND lon !=0.0
    ) b
    ON CAST(a.lat as INT64)=CAST(b.lat as INT64)
    AND CAST(a.lon as INT64)=CAST(b.lon as INT64)
  )
)
WHERE rank=1

注意事项：

• 它使用了https://stackoverflow.com/a/22476600/132438的距离公式。
• 通过限制连接优化，只搜索与城市相同的INT(lat)、INT(lon)中的站点。这是可能的改善，但我会留到另一个问题。

​

​