纬度和经度-查找城市和使用bigquery的状态 [英] Latitude and Longitude - find city & state using bigquery

问题描述

我的桌子上有纬度和经度信息.我想使用lat找到最近的城市和州名，并使用bigquery来找到长信息.

I have latitude and longitude information in my table. I want to find the nearest city and state name using lat and long info using bigquery.

推荐答案

希望以下快速示例为您提供从

Hope below quick example will give you good direction to start with

SELECT 
  yourLon, yourLat, ROUND(distance) AS distance, city, state 
FROM (
  SELECT 
    yourLon, yourLat, distance, city, state, 
    ROW_NUMBER() OVER (PARTITION BY yourLon, yourLat ORDER BY distance) AS win
  FROM 
  JS((
    // input table
    SELECT 
      points.yourLon AS yourLon, 
      points.yourLat AS yourLat, 
      cities.city AS city, 
      cities.state AS state, 
      cities.lon AS lon, 
      cities.lat AS lat
    FROM (
      SELECT city, state, lon, lat FROM
        (SELECT 'acampo' AS city, 'ca' AS state,  -121.23200000000001 AS lon, 38.1964 AS lat), 
        (SELECT 'adelanto ' AS city,  'ca' AS state,    -117.48 AS lon,   34.5894  AS lat), 
        (SELECT 'agoura hills ' AS city,  'ca' AS state,    -118.76 AS lon,   34.1445 AS lat), 
        (SELECT 'alameda  ' AS city,  'ca' AS state,    -122.26 AS lon,   37.7606 AS lat),  
        (SELECT 'alamo  ' AS city,  'ca' AS state,    -122.019 AS lon,  37.8525 AS lat),  
        (SELECT 'albany ' AS city,  'ca' AS state,    -122.294 AS lon,  37.8882 AS lat),  
        (SELECT 'albion ' AS city,  'ca' AS state,    -123.705 AS lon,  39.2141 AS lat),  
        (SELECT 'alhambra ' AS city,  'ca' AS state,    -118.12999999999998 AS lon,   34.0899 AS lat),  
        (SELECT 'aliso viejo  ' AS city,  'ca' AS state,    -117.736 AS lon,  33.5761 AS lat),  
        (SELECT 'altadena ' AS city,  'ca' AS state,    -118.14000000000001 AS lon,   34.1884 AS lat),  
        (SELECT 'anaheim  ' AS city,  'ca' AS state,    -117.92667647058823 AS lon,   33.83603382352941 AS lat),  
        (SELECT 'anderson ' AS city,  'ca' AS state,    -122.299 AS lon,  40.469 AS lat),    
        (SELECT 'angels camp  ' AS city,  'ca' AS state,    -120.625 AS lon,  38.0746 AS lat),  
        (SELECT 'antelope ' AS city,  'ca' AS state,    -121.38099999999999 AS lon,   38.6861 AS lat),  
        (SELECT 'antioch  ' AS city,  'ca' AS state,    -121.806 AS lon,  37.99594999999999 AS lat),  
        (SELECT 'anza ' AS city,  'ca' AS state,    -116.743 AS lon,  33.5886 AS lat),  
        (SELECT 'apple valley ' AS city,  'ca' AS state,    -117.19333333333333 AS lon,   34.47484444444444 AS lat),  
        (SELECT 'aptos  ' AS city,  'ca' AS state,    -121.877 AS lon,  36.9926 AS lat),  
        (SELECT 'arbuckle ' AS city,  'ca' AS state,    -122.015 AS lon,  39.0326 AS lat),  
        (SELECT 'arcadia  ' AS city,  'ca' AS state,    -118.04199999999999 AS lon,   34.1325 AS lat)
    ) AS cities
    CROSS JOIN (
      SELECT yourLon, yourLat FROM
      (SELECT -122 AS yourLon, 38 AS yourLat),
      (SELECT -117 AS yourLon, 34 AS yourLat),
    ) AS points
  ) ,
  // input columns
  yourLon, yourLat, city, state, lon, lat,
  // output schema
  "[{name: 'yourLon', type: 'float'},
  {name: 'yourLat', type: 'float'},
  {name: 'city', type: 'string'},
  {name: 'state', type: 'string'},
  {name: 'distance', type: 'float'}]",
  // function
  "function(r, emit){
    var R = 3959; // Radius of the earth in miles
    var dLat = deg2rad(r.lat-r.yourLat);  
    var dLon = deg2rad(r.lon-r.yourLon); 
    var a = Math.sin(dLat/2) * Math.sin(dLat/2) +
      Math.cos(deg2rad(r.yourLat)) * Math.cos(deg2rad(r.lat)) * 
      Math.sin(dLon/2) * Math.sin(dLon/2); 
    var c = 2 * Math.atan2(Math.sqrt(a), Math.sqrt(1-a)); 
    emit({yourLon: r.yourLon, yourLat: r.yourLat, city: r.city, state: r.state, distance: R * c});

    function deg2rad(deg) {
      return deg * (Math.PI/180)
    }
  }"
  )
)
WHERE win = 1

结果如下

yourLon yourLat distance    city    state    
-117.0  34.0    32.0        anza    ca   
-122.0  38.0    10.0        alamo   ca   

这篇关于纬度和经度-查找城市和使用bigquery的状态的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！