Запрос и визуализация данных о местоположении в BigQuery с помощью платформы Google Maps (JavaScript)

1. Обзор

Карты могут быть очень мощным инструментом для визуализации закономерностей в наборе данных, которые каким-либо образом связаны с местоположением. Это может быть название места, определённое значение широты и долготы или название области с определёнными границами, например, переписной участок или почтовый индекс.

Когда эти наборы данных становятся очень большими, к ним может быть сложно обращаться с запросами и визуализировать их с помощью традиционных инструментов. Используя Google BigQuery для запроса данных и API Google Карт для построения запроса и визуализации результатов, вы можете быстро исследовать географические закономерности в своих данных, практически не настраивая систему и не тратя время на управление очень большими наборами данных.

Что вы построите

В этой лабораторной работе вы напишете и выполните несколько запросов, демонстрирующих, как получать геолокационную информацию из очень больших общедоступных наборов данных с помощью BigQuery. Вы также создадите веб-страницу, которая загружает карту с помощью JavaScript API платформы Google Карт, а затем выполняет и визуализирует пространственные запросы к тем же очень большим общедоступным наборам данных, используя клиентскую библиотеку Google API для Javascript и API BigQuery .

Чему вы научитесь

• Как запрашивать петабайтные наборы данных о местоположении за считанные секунды с помощью BigQuery , используя запросы SQL , определяемые пользователем функции и API BigQuery
• Как использовать платформу Google Карт , чтобы добавить карту Google на веб-страницу и дать пользователям возможность рисовать на ней фигуры
• Как визуализировать запросы к большим наборам данных на карте Google Maps, как на примере изображения ниже, где показана плотность мест высадки такси в 2016 году для поездок, начинавшихся от квартала вокруг Эмпайр-стейт-билдинг.

Что вам понадобится

• Базовые знания HTML, CSS, JavaScript, SQL и Chrome DevTools
• Современный веб-браузер, например последние версии Chrome, Firefox, Safari или Edge.
• Текстовый редактор или IDE по вашему выбору

Технология

BigQuery

BigQuery — это сервис аналитики данных Google для очень больших наборов данных. Он имеет RESTful API и поддерживает запросы на SQL. Если у вас есть данные со значениями широты и долготы, их можно использовать для поиска данных по местоположению. Преимущество заключается в том, что вы можете визуально исследовать очень большие наборы данных и выявлять закономерности, не управляя какой-либо инфраструктурой сервера или базы данных. Благодаря высокой масштабируемости и управляемой инфраструктуре BigQuery вы можете получить ответы на свои вопросы за несколько секунд, независимо от размера ваших таблиц.

Платформа Google Карт

Платформа Google Карт предоставляет программный доступ к данным Google о картах, местах и маршрутах. Более 2 миллионов веб-сайтов и приложений в настоящее время используют её для предоставления пользователям встроенных карт и запросов на основе местоположения.

Слой рисования Javascript API платформы Google Карт позволяет рисовать фигуры на карте. Их можно преобразовать во входные данные для выполнения запросов к таблицам BigQuery , в столбцах которых хранятся значения широты и долготы.

Для начала вам понадобится проект Google Cloud Platform с включенными API BigQuery и Maps.

2. Подготовка

Аккаунт Google

Если у вас еще нет учетной записи Google (Gmail или Google Apps), вам необходимо ее создать .

Создать проект

Совет: для используемого вами проекта необходимо включить функцию выставления счетов. Если вы планируете использовать существующий проект, убедитесь, что для него можно включить функцию выставления счетов.

Войдите в консоль Google Cloud Platform ( console.cloud.google.com ) и создайте новый проект. В верхней части экрана находится раскрывающееся меню «Проект»:

После нажатия на раскрывающееся меню этого проекта вы увидите пункт меню, позволяющий создать новый проект:

В поле «Введите новое имя для вашего проекта» введите имя для вашего нового проекта, например «BigQuery Codelab»:

Для вас будет сгенерирован идентификатор проекта. Он уникален для всех проектов Google Cloud. Запомните свой идентификатор проекта, он понадобится вам позже. Указанное выше имя уже занято и не будет работать. Вставьте свой собственный идентификатор проекта везде, где вы видите YOUR_PROJECT_ID в этой практической работе.

Включить биллинг

Чтобы зарегистрироваться в BigQuery, используйте проект, выбранный или созданный на предыдущем шаге. Для этого проекта должен быть включен биллинг. После включения биллинга вы сможете включить API BigQuery.

Примечание: с вас не должна взиматься плата за запросы, выполняемые в этой практической работе, но если вы превысите уровень бесплатного использования BigQuery (1 ТБ запросов данных в месяц) или максимальную бесплатную дневную квоту для API Карт (25 000 загрузок карт в день), с вас может взиматься плата.

Способ включения выставления счетов зависит от того, создаете ли вы новый проект или повторно включаете выставление счетов для существующего проекта.

Google предлагает 12-месячную бесплатную пробную версию на использование Google Cloud Platform стоимостью до 300 долларов США, которую вы можете использовать для этой лабораторной работы. Более подробную информацию можно найти на сайте https://cloud.google.com/free/ .

Новые проекты

При создании нового проекта вам будет предложено выбрать, какой из ваших платёжных аккаунтов вы хотите связать с проектом. Если у вас только один платёжный аккаунт, он будет автоматически связан с вашим проектом.

Если у вас нет платёжного аккаунта, вам необходимо создать его и включить оплату для своего проекта, прежде чем вы сможете использовать многие функции Google Cloud Platform. Чтобы создать новый платёжный аккаунт и включить оплату для своего проекта, следуйте инструкциям в статье Создание нового платёжного аккаунта .

Существующие проекты

Если у вас есть проект, для которого вы временно отключили выставление счетов, вы можете повторно включить выставление счетов:

1. Перейдите в консоль облачной платформы .
2. Из списка проектов выберите проект, для которого необходимо повторно включить выставление счетов.
3. Откройте левое меню консоли и выберите «Выставление счетов». . Вам будет предложено выбрать платежный аккаунт.
4. Нажмите «Настроить учетную запись» .

Создать новый платежный аккаунт

Чтобы создать новый платежный аккаунт:

1. Перейдите в консоль Cloud Platform и войдите в систему или, если у вас еще нет учетной записи, зарегистрируйтесь.
2. Откройте левое меню консоли и выберите «Выставление счетов».
3. Нажмите кнопку «Новый платежный аккаунт» . (Обратите внимание: если это не ваш первый платежный аккаунт, сначала вам необходимо открыть список платежных аккаунтов, щелкнув имя существующего платежного аккаунта в верхней части страницы, а затем нажав «Управление платежными аккаунтами» .)
4. Введите название платёжного аккаунта и свои платёжные данные. Доступные варианты зависят от страны вашего платёжного адреса. Обратите внимание, что для аккаунтов в США изменить налоговый статус после их создания невозможно.
5. Нажмите «Отправить» и включите выставление счетов .

По умолчанию лицо, создавшее учетную запись для выставления счетов, является администратором учетной записи.

Информацию о проверке банковских счетов и добавлении резервных способов оплаты см. в разделе Добавление, удаление или обновление способа оплаты .

Включить API BigQuery

Чтобы включить API BigQuery в своем проекте, перейдите на страницу API BigQuery Marketplace в консоли и нажмите синюю кнопку «Включить».

Примечание : Если вы ещё не подтвердили свой адрес электронной почты, возможно, вам потребуется сделать это, прежде чем вы сможете использовать новый платёжный аккаунт. Подробнее о подтверждении адреса электронной почты см. в разделе «Подтверждение адреса электронной почты» .

3. Запрос данных о местоположении в BigQuery

Существует три способа запроса данных о местоположении, хранящихся в виде значений широты и долготы в BigQuery.

• Запросы по прямоугольнику : укажите интересующую область как запрос, который выбирает все строки в пределах минимального и максимального диапазона широты и долготы.
• Запросы радиуса : укажите интересующую область, рассчитав окружность вокруг точки, используя формулу гаверсина и математические функции для моделирования формы Земли.
• Запросы многоугольников : укажите пользовательскую форму и используйте определяемую пользователем функцию для выражения логики «точка в многоугольнике», необходимой для проверки того, попадают ли широта и долгота каждой строки внутрь формы.

Для начала воспользуйтесь редактором запросов в разделе «Большие запросы» консоли Google Cloud Platform, чтобы выполнить следующие запросы к данным о такси Нью-Йорка.

Стандартный SQL против устаревшего SQL

BigQuery поддерживает две версии SQL: Legacy SQL и Standard SQL . Последняя версия — стандарт ANSI 2011 года. В этом руководстве мы будем использовать Standard SQL, поскольку она лучше соответствует стандартам.

Если вы хотите выполнить Legacy SQL в редакторе BigQuery, вы можете сделать это, выполнив следующие действия:

1. Нажмите кнопку «Подробнее».
2. В раскрывающемся меню выберите «Настройки запроса».
3. В разделе «Диалект SQL» выберите переключатель «Устаревший».
4. Нажмите кнопку «Сохранить».

Запросы прямоугольника

Прямоугольные запросы в BigQuery создавать довольно просто. Нужно лишь добавить предложение WHERE , которое ограничит возвращаемые результаты теми, чьё местоположение находится между минимальным и максимальным значениями широты и долготы.

Попробуйте выполнить пример ниже в консоли BigQuery. Он запрашивает среднюю статистику поездок, начавшихся в прямоугольной области, охватывающей Мидтаун и Нижний Манхэттен. Можно попробовать два разных местоположения. Раскомментируйте второе предложение WHERE , чтобы запрос выполнялся для поездок, начавшихся в аэропорту имени Джона Кеннеди.

SELECT 
ROUND(AVG(tip_amount),2) as avg_tip, 
ROUND(AVG(fare_amount),2) as avg_fare, 
ROUND(AVG(trip_distance),2) as avg_distance, 
ROUND(AVG(tip_proportion),2) as avg_tip_pc, 
ROUND(AVG(fare_per_mile),2) as avg_fare_mile FROM

(SELECT 

pickup_latitude, pickup_longitude, tip_amount, fare_amount, trip_distance, (tip_amount / fare_amount)*100.0 as tip_proportion, fare_amount / trip_distance as fare_per_mile

FROM `bigquery-public-data.new_york_taxi_trips.tlc_yellow_trips_2015`

WHERE trip_distance > 0.01 AND fare_amount <100 AND payment_type = "1" AND fare_amount > 0
)

--Manhattan
WHERE pickup_latitude < 40.7679 AND pickup_latitude > 40.7000 AND pickup_longitude < -73.97 and pickup_longitude > -74.01

--JFK
--WHERE pickup_latitude < 40.654626 AND pickup_latitude > 40.639547 AND pickup_longitude < -73.771497 and pickup_longitude > -73.793755

Результаты двух запросов показывают, что существуют большие различия в средней дальности поездки, стоимости проезда и чаевых за посадку в двух местах.

Манхэттен

avg_tip	avg_fare	avg_distance	avg_tip_pc	avg_fare_mile
2.52	12.03	9.97	22.39	5.97

Кеннеди

avg_tip	avg_fare	avg_distance	avg_tip_pc	avg_fare_mile
9.22	48.49	41.19	22.48	4.36

Запросы радиуса

Запросы на радиус также легко строить в SQL, если вы немного знакомы с математикой. Используя устаревшие математические функции SQL BigQuery, вы можете создать SQL-запрос, используя формулу гаверсина , которая приблизительно вычисляет площадь круга или сферической поверхности на земной поверхности.

Вот пример оператора BigQuery SQL для запроса круга с центром в точке 40.73943, -73.99585 и радиусом 0,1 км.

Для приблизительного определения расстояния, представленного одним градусом, используется постоянное значение 111,045 километра.

Это основано на примере, найденном по адресу http://www.plumislandmedia.net/mysql/haversine-mysql-nearest-loc/ :

SELECT pickup_latitude, pickup_longitude, 
    (111.045 * DEGREES( 
      ACOS( 
        COS( RADIANS(40.73943) ) * 
        COS( RADIANS( pickup_latitude ) ) * 
        COS( 
          RADIANS( -73.99585 ) - 
          RADIANS( pickup_longitude ) 
        ) + 
        SIN( RADIANS(40.73943) ) * 
        SIN( RADIANS( pickup_latitude ) ) 
      ) 
     ) 
    ) AS distance FROM `project.dataset.tableName` 
    HAVING distance < 0.1 

SQL для формулы гаверсина выглядит сложным, но все, что вам нужно сделать, это ввести координаты центра окружности, радиус, а также имена проекта, набора данных и таблицы для BigQuery.

Примечание: формула предполагает, что Земля — идеальная сфера, хотя на самом деле это сплющенный сфероид, поэтому в ней могут быть небольшие неточности, связанные с локальными различиями в форме Земли. Расстояния, используемые в приведённых здесь примерах, пренебрежимо малы и не повлияют на результаты.

BigQuery поддерживает две версии SQL: Legacy SQL и Standard SQL . Последняя версия — стандарт ANSI 2011 года.

Вот пример запроса, который вычисляет среднюю статистику поездок в радиусе 100 метров от Эмпайр-стейт-билдинг. Скопируйте и вставьте его в веб-консоль BigQuery, чтобы увидеть результаты. Измените широту и долготу, чтобы сравнить данные с другими районами, например, с Бронксом.

#standardSQL
CREATE TEMPORARY FUNCTION Degrees(radians FLOAT64) RETURNS FLOAT64 AS
(
  (radians*180)/(22/7)
);

CREATE TEMPORARY FUNCTION Radians(degrees FLOAT64) AS (
  (degrees*(22/7))/180
);

CREATE TEMPORARY FUNCTION DistanceKm(lat FLOAT64, lon FLOAT64, lat1 FLOAT64, lon1 FLOAT64) AS (
     Degrees( 
      ACOS( 
        COS( Radians(lat1) ) * 
        COS( Radians(lat) ) *  
        COS( Radians(lon1 ) -  
        Radians( lon ) ) +  
        SIN( Radians(lat1) ) *  
        SIN( Radians( lat ) ) 
        ) 
    ) * 111.045
);

SELECT 

ROUND(AVG(tip_amount),2) as avg_tip,
ROUND(AVG(fare_amount),2) as avg_fare,
ROUND(AVG(trip_distance),2) as avg_distance,
ROUND(AVG(tip_proportion), 2) as avg_tip_pc,
ROUND(AVG(fare_per_mile),2) as avg_fare_mile

FROM

-- EMPIRE STATE BLDG 40.748459, -73.985731
-- BRONX 40.895597, -73.856085

(SELECT pickup_latitude, pickup_longitude, tip_amount, fare_amount, trip_distance, tip_amount/fare_amount*100 as tip_proportion, fare_amount / trip_distance as fare_per_mile, DistanceKm(pickup_latitude, pickup_longitude, 40.748459, -73.985731)


FROM `bigquery-public-data.new_york_taxi_trips.tlc_yellow_trips_2015`

WHERE 
  DistanceKm(pickup_latitude, pickup_longitude, 40.748459, -73.985731) < 0.1
  AND fare_amount > 0 and trip_distance > 0
  )
WHERE fare_amount < 100

Результаты запроса представлены ниже. Видно, что существуют значительные различия в средних размерах чаевых, стоимости проезда, дальности поездки, соотношении размера чаевых к стоимости проезда и средней стоимости проезда за милю.

Эмпайр-стейт-билдинг:

avg_tip	avg_fare	avg_distance	avg_tip_pc	avg_fare_mile
1.17	11.08	45.28	10.53	6.42

Бронкс

avg_tip	avg_fare	avg_distance	avg_tip_pc	avg_fare_mile
0,52	17.63	4.75	4.74	10.9

Запросы полигонов

SQL не поддерживает запросы с использованием произвольных фигур, кроме прямоугольников и кругов. В BigQuery нет встроенного типа геометрических данных или пространственного индекса, поэтому для выполнения запросов с использованием многоугольников требуется подход, отличный от простых SQL-запросов. Один из подходов — определить геометрическую функцию в JavaScript и выполнить её как пользовательскую функцию (UDF) в BigQuery.

Многие геометрические операции можно написать на JavaScript, поэтому их легко выполнить с таблицей BigQuery, содержащей значения широты и долготы. Необходимо передать пользовательский многоугольник через пользовательскую функцию (UDF) и выполнить проверку для каждой строки, возвращая только те строки, где значения широты и долготы попадают внутрь многоугольника. Подробнее об UDF см. в справочнике BigQuery .

Алгоритм «Точка в многоугольнике»

Существует множество способов определить, попадает ли точка внутрь многоугольника в JavaScript. Вот один из них, портированный с языка C, — известная реализация , использующая алгоритм трассировки лучей для определения, находится ли точка внутри или снаружи многоугольника, подсчитывая количество пересечений границы фигуры бесконечно длинной линией. Это занимает всего несколько строк кода:

function pointInPoly(nvert, vertx, verty, testx, testy){
  var i, j, c = 0;
  for (i = 0, j = nvert-1; i < nvert; j = i++) {
    if ( ((verty[i]>testy) != (verty[j]>testy)) &&
                (testx < (vertx[j]-vertx[i]) * (testy-verty[i]) / (verty[j]-verty[i]) + vertx[i]) )
      c = !c;
  }
  return c;
}

Портирование на JavaScript

JavaScript-версия этого алгоритма выглядит так:

/* This function includes a port of C code to calculate point in polygon
* see http://www.ecse.rpi.edu/Homepages/wrf/Research/Short_Notes/pnpoly.html for license
*/

function pointInPoly(polygon, point){
    // Convert a JSON poly into two arrays and a vertex count.
    let vertx = [],
        verty = [],
        nvert = 0,
        testx = point[0],
        testy = point[1];
    for (let coord of polygon){
      vertx[nvert] = coord[0];
      verty[nvert] = coord[1];
      nvert ++;
    }

        
    // The rest of this function is the ported implementation.
    for (let i = 0, let j = nvert - 1; i < nvert; j = i++) {
      if ( ((verty[i] > testy) != (verty[j] > testy)) &&
         (testx < (vertx[j] - vertx[i]) * (testy - verty[i]) / (verty[j] - verty[i]) + vertx[i]) )
        c = !c;
    }
    return c;
}

При использовании стандартного SQL в BigQuery подход с использованием UDF требует всего одного оператора, но UDF должна быть определена как временная функция в операторе. Вот пример. Вставьте приведённый ниже оператор SQL в окно редактора запросов.

CREATE TEMPORARY FUNCTION pointInPolygon(latitude FLOAT64, longitude FLOAT64)
RETURNS BOOL LANGUAGE js AS """
  let polygon=[[-73.98925602436066,40.743249676056955],[-73.98836016654968,40.74280666503313],[-73.98915946483612,40.741676770346295],[-73.98967981338501,40.74191656974406]];

  let vertx = [],
    verty = [],
    nvert = 0,
    testx = longitude,
    testy = latitude,
    c = false,
    j = nvert - 1;

  for (let coord of polygon){
    vertx[nvert] = coord[0];
    verty[nvert] = coord[1];
    nvert ++;
  }

  // The rest of this function is the ported implementation.
  for (let i = 0; i < nvert; j = i++) {
    if ( ((verty[i] > testy) != (verty[j] > testy)) &&
 (testx < (vertx[j] - vertx[i]) * (testy - verty[i]) / (verty[j] - verty[i]) + vertx[i]) ) {
      c = !c;
    }
  }

  return c;
""";

SELECT pickup_latitude, pickup_longitude, dropoff_latitude, dropoff_longitude, pickup_datetime
FROM `bigquery-public-data.new_york_taxi_trips.tlc_yellow_trips_2016`
WHERE pointInPolygon(pickup_latitude, pickup_longitude) = TRUE
AND (pickup_datetime BETWEEN CAST("2016-01-01 00:00:01" AS DATETIME) AND CAST("2016-02-28 23:59:59" AS DATETIME))
LIMIT 1000

Поздравляю!

Вы выполнили три типа пространственных запросов с помощью BigQuery. Как вы видели, местоположение существенно влияет на результаты запросов к этому набору данных, но если вы не знаете, где выполнять запросы, сложно выявить пространственные закономерности ad hoc, используя только SQL-запросы.

Эх, если бы мы могли визуализировать данные на карте и исследовать их, определяя произвольные области интереса! Что ж, с помощью API Google Карт это возможно. Для начала вам нужно включить API Карт, настроить простую веб-страницу на локальном компьютере и начать использовать API BigQuery для отправки запросов с вашей веб-страницы.

4. Работа с API Google Карт

Выполнив несколько простых пространственных запросов, следующим шагом станет визуализация результатов для выявления закономерностей. Для этого вам нужно подключить API Карт, создать веб-страницу, которая будет отправлять запросы с карты в BigQuery, а затем отображать результаты на карте.

Включить API JavaScript Карт

Для этой лабораторной работы вам потребуется включить в своём проекте Maps Javascript API платформы Google Карт. Для этого выполните следующие действия:

1. В консоли Google Cloud Platform перейдите в Marketplace .
2. В Marketplace найдите «Maps JavaScript API».
3. Щелкните по плитке Maps JavaScript API в результатах поиска.
4. Нажмите кнопку «Включить».

Сгенерировать ключ API

Для отправки запросов к платформе Google Карт вам необходимо сгенерировать API-ключ и отправлять его со всеми запросами. Чтобы сгенерировать API-ключ, выполните следующие действия:

1. В консоли Google Cloud Platform нажмите на меню-гамбургер, чтобы открыть левую навигационную панель.
2. Выберите «API и службы» > «Учетные данные».
3. Нажмите кнопку «Создать учетные данные», затем выберите «Ключ API».
4. Скопируйте новый ключ API

Загрузите код и настройте веб-сервер

Нажмите следующую кнопку, чтобы загрузить весь код для этой лабораторной работы:

Загрузить код

Распакуйте скачанный zip-архив. Будет распакована корневая папка ( bigquery ), содержащая по одной папке на каждый этап этой лабораторной работы, а также все необходимые ресурсы.

Папки stepN содержат желаемое конечное состояние каждого шага этой лабораторной работы. Они приведены для справки. Всю работу по написанию кода мы будем выполнять в каталоге work .

Настройте локальный веб-сервер

Хотя вы можете использовать свой собственный веб-сервер, эта лабораторная работа разработана для работы с веб-сервером Chrome. Если у вас ещё не установлено это приложение, вы можете установить его из интернет-магазина Chrome.

Установить веб-сервер для Chrome

После установки откройте приложение. В Chrome это можно сделать следующим образом:

Дополнительная помощь: добавление и открытие приложений Chrome

1. Откройте Chrome
2. В адресной строке вверху введите chrome://apps
3. Нажмите Enter
4. В открывшемся окне щелкните значок «Веб-сервер». Вы также можете щелкнуть правой кнопкой мыши по приложению, чтобы открыть его в обычной или закрепленной вкладке, на весь экран или в новом окне. Далее вы увидите диалоговое окно, позволяющее настроить локальный веб-сервер:
5. Нажмите «ВЫБРАТЬ ПАПКУ» и выберите место, куда вы загрузили файлы примеров кодовой лаборатории.
6. В разделе «Параметры» установите флажок рядом с опцией «Автоматически показывать index.html»:
7. Передвиньте переключатель «Веб-сервер: ЗАПУЩЕН» влево, а затем обратно вправо, чтобы остановить и перезапустить веб-сервер.

5. Загрузка карты и инструментов рисования

Создайте простую страницу карты

Начните с простой HTML-страницы, которая загружает карту Google с помощью Maps JavaScript API и нескольких строк JavaScript. Отличный пример — код из примера простой карты платформы Google Карт. Он представлен здесь, и вы можете скопировать его и вставить в любой текстовый редактор или IDE по вашему выбору, или найти его, открыв index.html из скачанного вами репозитория.

1. Скопируйте index.html в work папку в вашей локальной копии репозитория.
2. Скопируйте папку img/ в папку work/ в вашей локальной копии репозитория.
3. Откройте work/ index.html в текстовом редакторе или IDE.
4. Замените YOUR_API_KEY на API-ключ, который вы создали ранее.

<script src="https://maps.googleapis.com/maps/api/js?key=YOUR_API_KEY&callback=initMap"
    async defer></script>

5. В браузере откройте localhost:<port>/work , где port — номер порта, указанный в настройках вашего локального веб-сервера. Порт по умолчанию — 8887 . Вы должны увидеть первые карты.

Если вы получили сообщение об ошибке в браузере, проверьте правильность вашего ключа API и активность вашего локального веб-сервера.

Изменить местоположение и уровень масштабирования по умолчанию

Код, который задает местоположение и уровень масштабирования, находится в строках 27 и 28 файла index.html и в настоящее время центрирован на Сиднее, Австралия:

<script>
      let map;
      function initMap() {
        map = new google.maps.Map(document.getElementById('map'), {
          center: {lat: -34.397, lng: 150.644},
          zoom: 8
        });
      }
</script>

Это руководство работает с данными BigQuery о поездках такси по Нью-Йорку , поэтому далее вам нужно изменить код инициализации карты, чтобы она центрировалась на местоположении в Нью-Йорке при соответствующем уровне масштабирования — 13 или 14 должны хорошо подойти.

Совет: чтобы узнать широту и долготу места, воспользуйтесь сайтом maps.google.com , чтобы найти это место, затем щелкните правой кнопкой мыши и в появившемся контекстном меню выберите «Что здесь?».

Для этого обновите приведенный выше блок кода следующим образом, чтобы центрировать карту на Эмпайр-стейт-билдинг, и установите уровень масштабирования 14:

<script>
      let map;
      function initMap() {
        map = new google.maps.Map(document.getElementById('map'), {
          center: {lat: 40.7484405, lng: -73.9878531},
          zoom: 14
        });
      }
</script>

Затем перезагрузите карту в браузере, чтобы увидеть результаты.

Загрузите библиотеки чертежей и визуализации

Чтобы добавить возможности рисования на вашу карту, вам нужно изменить скрипт, загружающий API JavaScript Карт, добавив необязательный параметр, который сообщает платформе Google Карт о необходимости включить библиотеку рисования.

В этой лабораторной работе также используется HeatmapLayer , поэтому вам также потребуется обновить скрипт для запроса библиотеки визуализации. Для этого добавьте параметр libraries и укажите библиотеки visualization и drawing , разделив их запятыми, например: libraries= visualization,drawing

Это должно выглядеть так:

<script src='http://maps.googleapis.com/maps/api/js?libraries=visualization,drawing&callback=initMap&key=YOUR_API_KEY' async defer></script>

Добавьте DrawingManager

Чтобы использовать нарисованные пользователем фигуры в качестве входных данных для запроса, добавьте DrawingManager на карту, включив инструменты Circle , Rectangle и Polygon .

Хорошей идеей будет поместить весь код настройки DrawingManager в новую функцию, поэтому в вашей копии index.html выполните следующее:

1. Добавьте функцию setUpDrawingTools() со следующим кодом, чтобы создать DrawingManager и задать его свойство map для ссылки на объект карты на странице.

Параметры, передаваемые в google.maps.drawing.DrawingManager(options) задают тип отрисовки фигур по умолчанию и параметры отображения для нарисованных фигур. Для выбора областей карты для отправки в виде запросов, фигуры должны иметь нулевую непрозрачность. Подробнее о доступных параметрах см. в разделе «Параметры DrawingManager» .

function setUpDrawingTools() {
  // Initialize drawing manager
  drawingManager = new google.maps.drawing.DrawingManager({
    drawingMode: google.maps.drawing.OverlayType.CIRCLE,
    drawingControl: true,
    drawingControlOptions: {
      position: google.maps.ControlPosition.TOP_LEFT,
      drawingModes: [
        google.maps.drawing.OverlayType.CIRCLE,
        google.maps.drawing.OverlayType.POLYGON,
        google.maps.drawing.OverlayType.RECTANGLE
      ]
    },
    circleOptions: {
      fillOpacity: 0
    },
    polygonOptions: {
      fillOpacity: 0
    },
    rectangleOptions: {
      fillOpacity: 0
    }
  });
  drawingManager.setMap(map);
}

2. Вызовите setUpDrawingTools() в функции initMap() после создания объекта карты.

function initMap() {
  map = new google.maps.Map(document.getElementById('map'), {
    center: {lat: 40.744593, lng: -73.990370}, // Manhattan, New York.
    zoom: 12
  });

  setUpDrawingTools();
}

3. Перезагрузите index.html и убедитесь, что инструменты рисования видны. Также проверьте, можно ли с их помощью рисовать круги, прямоугольники и многоугольники.

Вы можете щелкать и перетаскивать курсор, чтобы рисовать круги и прямоугольники, но многоугольники нужно рисовать, щелкая по каждой вершине и дважды щелкая, чтобы завершить форму.

Обработка событий рисования

Вам нужен код для обработки событий , которые возникают, когда пользователь заканчивает рисовать фигуру, точно так же, как вам нужны координаты нарисованных фигур для построения SQL-запросов.

Мы добавим код для этого позже, а пока создадим три пустых обработчика событий для обработки событий rectanglecomplete , circlecomplete и polygoncomplete . На данном этапе обработчикам не нужно выполнять никакой код.

Добавьте следующее в конец функции setUpDrawingTools() :

drawingManager.addListener('rectanglecomplete', rectangle => {
    // We will add code here in a later step.
});
drawingManager.addListener('circlecomplete', circle => {
  // We will add code here in a later step.
});

drawingManager.addListener('polygoncomplete', polygon => {
  // We will add code here in a later step.
});

Рабочий пример этого кода вы можете найти в локальной копии репозитория в папке step2 : step2/map.html .

6. Использование API клиента BigQuery

Клиентский API Google BigQuery поможет вам избежать написания большого количества шаблонного кода, необходимого для создания запросов, анализа ответов и обработки аутентификации. В этой лабораторной работе используется API BigQuery через клиентскую библиотеку Google API для JavaScript, поскольку мы разрабатываем браузерное приложение.

Далее вы добавите код для загрузки этого API на веб-страницу и будете использовать его для взаимодействия с BigQuery.

Примечание: В своих приложениях вы можете использовать любой клиент для отправки запросов. API BigQuery поддерживает HTTP-запросы и использует данные JSON для запросов и ответов.

Добавьте API клиента Google для JavaScript

Вы будете использовать Google Client API для Javascript для выполнения запросов к BigQuery. В вашей копии index.html (в work папке) загрузите API с помощью тега <script> , как показано ниже. Разместите этот тег сразу под тегом <script> , загружающим Maps API:

<script src='https://apis.google.com/js/client.js'></script>

После загрузки Google Client API авторизуйте пользователя для доступа к данным в BigQuery. Для этого можно использовать OAuth 2.0. Для начала необходимо настроить учётные данные в проекте Google Cloud Console.

Создать учетные данные OAuth 2.0

1. В консоли Google Cloud в меню навигации выберите API и службы > Учетные данные .

Прежде чем вы сможете настроить свои учетные данные, вам необходимо добавить некоторые настройки для экрана авторизации, который увидит конечный пользователь вашего приложения, когда он разрешит вашему приложению доступ к данным BigQuery от его имени.

Для этого перейдите на вкладку «Экран согласия OAuth» . 2. Необходимо добавить API Big Query в области действия этого токена. Нажмите кнопку «Добавить область действия» в разделе «Области действия для API Google». 3. В списке установите флажок рядом с записью API Big Query с областью действия ../auth/bigquery . 4. Нажмите «Добавить ». 5. Введите имя в поле «Имя приложения». 6. Нажмите «Сохранить» , чтобы сохранить настройки. 7. Далее необходимо создать идентификатор клиента OAuth. Для этого нажмите «Создать учётные данные» .

8. В раскрывающемся меню выберите Идентификатор клиента OAuth .
9. В разделе «Тип приложения» выберите «Веб-приложение» .
10. В поле «Название приложения» введите название вашего проекта. Например, «BigQuery and Maps».
11. В разделе «Ограничения» в поле «Разрешенные источники JavaScript» введите URL-адрес локального хоста, включая номера портов. Например: http://localhost:8887

Совет : необходимо ввести исходный URL-адрес как «localhost», а не «127.0.0.1».

12. Нажмите кнопку «Создать» .

Во всплывающем окне будут показаны идентификатор клиента и секретный ключ клиента. Идентификатор клиента необходим для аутентификации в BigQuery. Скопируйте его и вставьте в work/index.html как новую глобальную переменную JavaScript с именем clientId .

let clientId = 'YOUR_CLIENT_ID';

7. Авторизация и инициализация

Перед инициализацией карты вашей веб-странице потребуется авторизовать пользователя для доступа к BigQuery. В этом примере мы используем OAuth 2.0, как описано в разделе «Авторизация» документации JavaScript Client API . Для отправки запросов необходимо использовать идентификатор клиента OAuth и идентификатор вашего проекта.

После загрузки API клиента Google на веб-страницу вам необходимо выполнить следующие действия:

• Авторизуйте пользователя.
• Если вы авторизованы, загрузите API BigQuery.
• Загрузите и инициализируйте карту.

Пример того, как будет выглядеть готовая HTML-страница, см. на странице step3/map.html.

Авторизуйте пользователя

Конечному пользователю приложения необходимо авторизовать его для доступа к данным в BigQuery от его имени. Для этого используется клиентский API Google для JavaScript, реализующий логику OAuth.

В реальном приложении у вас есть много вариантов интеграции этапа авторизации.

Например, вы можете вызвать метод authorize() из элемента пользовательского интерфейса, например, кнопки, или сделать это после загрузки страницы. В данном случае мы решили авторизовать пользователя после загрузки Google Client API для JavaScript, используя функцию обратного вызова в методе gapi.load() .

Напишите код сразу после тега <script> , который загружает API клиента Google для Javascript, чтобы загрузить как клиентскую библиотеку, так и модуль аутентификации, чтобы мы могли сразу же аутентифицировать пользователя.

<script src='https://apis.google.com/js/client.js'></script>
<script type='text/javascript'>
  gapi.load('client:auth', authorize);
</script>

Примечание: процесс авторизации использует всплывающее окно, поэтому убедитесь, что вы не заблокировали его при первом запуске кода.

При авторизации загрузите API BigQuery

После авторизации пользователя загрузите API BigQuery.

Сначала вызовите gapi.auth.authorize() с переменной clientId , добавленной на предыдущем шаге. Обработайте ответ в функции обратного вызова handleAuthResult .

Параметр immediate управляет показом всплывающего окна пользователю. Установите значение true , чтобы отключить отображение всплывающего окна авторизации, если пользователь уже авторизован.

Добавьте на страницу функцию handleAuthResult() . Функция должна принимать параметр authresult , который позволит вам управлять логикой в зависимости от того, был ли пользователь успешно авторизован или нет.

Также добавьте функцию loadApi для загрузки API BigQuery, если пользователь успешно авторизован.

Добавьте логику в функцию handleAuthResult() для вызова loadApi() если в функцию передан объект authResult , и если свойство error объекта имеет значение false .

Добавьте код в функцию loadApi() для загрузки API BigQuery с помощью метода gapi.client.load() .

let clientId = 'your-client-id-here';
let scopes = 'https://www.googleapis.com/auth/bigquery';

// Check if the user is authorized.
function authorize(event) {
  gapi.auth.authorize({client_id: clientId, scope: scopes, immediate: false}, handleAuthResult);
  return false;
}

// If authorized, load BigQuery API
function handleAuthResult(authResult) {
  if (authResult && !authResult.error) {
    loadApi();
    return;
  }
  console.error('Not authorized.')  
}

// Load BigQuery client API
function loadApi(){
  gapi.client.load('bigquery', 'v2');
}

Загрузить карту

Последний шаг — инициализация карты. Для этого необходимо немного изменить порядок логики. В настоящее время инициализация происходит после загрузки JavaScript-кода Maps API.

Это можно сделать, вызвав функцию initMap() из метода then() после метода load() объекта gapi.client .

// Load BigQuery client API
function loadApi(){
  gapi.client.load('bigquery', 'v2').then(
   () => initMap()
  );
}

Совет : удалите параметр &callback=initMap из тега JavaScript API Карт. Теперь initMap вызывается из функции loadApi() после загрузки API BigQuery.

8. Концепции API BigQuery

Вызовы API BigQuery обычно выполняются за считанные секунды, но могут не возвращать ответ немедленно. Вам нужна логика для опроса BigQuery, чтобы узнать статус длительно выполняемых заданий, и извлечения результатов только после их завершения.

Полный код этого шага находится в step4/map.html .

Отправка запроса

Добавьте функцию Javascript в work/index.html для отправки запроса с использованием API, а также несколько переменных для хранения значений набора данных BigQuery и проекта, содержащего таблицу для запроса, а также идентификатор проекта, по которому будет взиматься плата.

Примечание: Перед запуском кода замените 'datasetId' , 'publicProjectId' и 'billingProjectId' значениями из соответствующих проектов BigQuery. Это будет рассмотрено на следующем шаге.

let datasetId = 'your_dataset_id';
let billingProjectId = 'your_project_id';
let publicProjectId = 'bigquery-public-data';

function sendQuery(queryString){
  let request = gapi.client.bigquery.jobs.query({
      'query': queryString,
      'timeoutMs': 30000,
      'datasetId': datasetId,
      'projectId': billingProjectId,
      'useLegacySql':false
  });
  request.execute(response => {
      //code to handle the query response goes here.
  });
}

Примечание: Набор данных — это коллекция таблиц. Подробнее см. в документации BigQuery .

Проверить статус задания

Функция checkJobStatus ниже показывает, как периодически проверять статус задания, используя метод API get и jobId , возвращаемый исходным запросом. Вот пример, который выполняется каждые 500 миллисекунд до завершения задания.

let jobCheckTimer;

function checkJobStatus(jobId){
  let request = gapi.client.bigquery.jobs.get({
    'projectId': billingProjectId,
    'jobId': jobId
  });
  request.execute(response =>{
    if (response.status.errorResult){
      // Handle any errors.
      console.log(response.status.error);
      return;
    }

    if (response.status.state == 'DONE'){
      // Get the results.
      clearTimeout(jobCheckTimer);
      getQueryResults(jobId);
      return;
    }
    // Not finished, check again in a moment.
    jobCheckTimer = setTimeout(checkJobStatus, 500, [jobId]);    
  });
}

Измените метод sendQuery так, чтобы он вызывал метод checkJobStatus() как обратный вызов в вызове request.execute() . Передайте идентификатор задания в checkJobStatus . Он отображается в объекте ответа как jobReference.jobId .

function sendQuery(queryString){
  let request = gapi.client.bigquery.jobs.query({
      'query': queryString,
      'timeoutMs': 30000,
      'datasetId': datasetId,
      'projectId': billingProjectId,
      'useLegacySql':false
  });
  request.execute(response => checkJobStatus(response.jobReference.jobId));
}

Получение результатов запроса

Чтобы получить результаты запроса после его завершения, используйте API-вызов jobs.getQueryResults . Добавьте на страницу функцию getQueryResults() , которая принимает параметр jobId :

function getQueryResults(jobId){
  let request = gapi.client.bigquery.jobs.getQueryResults({
    'projectId': billingProjectId,
    'jobId': jobId
  });
  request.execute(response => {
    // Do something with the results.
  })
}

9. Запрос данных о местоположении с помощью API BigQuery

Существует три способа использования SQL для выполнения пространственных запросов к данным в BigQuery:

• выбрать по прямоугольнику (также известному как ограничивающая рамка),
• выбрать по радиусу, и
• мощная функция определяемых пользователем функций .

Примеры запросов с ограничивающим прямоугольником и радиусом см. в разделе «Математические функции» справочника устаревшего SQL BigQuery в разделе «Расширенные примеры».

Для запросов с ограничивающим прямоугольником и радиусом вы можете вызвать метод query API BigQuery. Сформируйте SQL-код для каждого запроса и передайте его функции sendQuery , созданной на предыдущем шаге.

Рабочий пример кода для этого шага находится в step4/map.html .

Запросы прямоугольника

Самый простой способ отобразить данные BigQuery на карте — запросить все строки, где широта и долгота попадают в прямоугольник, используя операторы сравнения «меньше» и «больше». Это может быть текущее представление карты или нарисованная на ней фигура.

Чтобы использовать фигуру, нарисованную пользователем, измените код в index.html так, чтобы он обрабатывал событие рисования, возникающее при завершении рисования прямоугольника. В этом примере код использует getBounds() объекта прямоугольника, чтобы получить объект, представляющий границы прямоугольника в координатах карты, и передаёт его функции с rectangleQuery :

drawingManager.addListener('rectanglecomplete', rectangle => rectangleQuery(rectangle.getBounds()));

rectangleQuery использует координаты верхнего правого угла (северо-восток) и нижнего левого угла (юго-запад) для сравнения «меньше/больше» для каждой строки таблицы BigQuery. Вот пример запроса к таблице со столбцами 'pickup_latitude' и 'pickup_longitude' , в которых хранятся значения местоположения.

Указание таблицы BigQuery

Чтобы запросить таблицу с помощью API BigQuery, необходимо указать полное имя таблицы в SQL-запросе. В стандартном SQL это выглядит так: project.dataset.tablename . В устаревшем SQL это project.dataset.tablename .

Доступно множество таблиц с данными о поездках такси Нью-Йорка. Чтобы просмотреть их, перейдите в веб-консоль BigQuery и разверните пункт меню «Публичные наборы данных». Найдите набор данных new_york и разверните его, чтобы просмотреть таблицы. Выберите таблицу с данными о поездках Yellow Taxi: bigquery-public-data.new_york_taxi_trips.tlc_yellow_trips_2016 ).

Указание идентификатора проекта

В вызове API необходимо указать имя вашего проекта Google Cloud Platform для выставления счетов. В этой лабораторной работе это не тот проект, который содержит таблицу. Если бы вы работали с таблицей, созданной в вашем проекте путём загрузки данных, этот идентификатор проекта был бы таким же, как в вашем SQL-запросе.

Добавьте в код переменные JavaScript для хранения ссылок на проект Public Datasets, содержащий таблицу, к которой вы обращаетесь, а также имя таблицы и имя набора данных. Вам также потребуется отдельная переменная для ссылки на идентификатор вашего проекта для выставления счетов.

Добавьте глобальные переменные Javascript с именами billingProjectId, publicProjectId, datasetId и tableName в вашу копию index.html.

Инициализируйте переменные 'publicProjectId' , 'datasetId' и 'tableName' данными из проекта BigQuery Public Datasets. Инициализируйте billingProjectId вашим собственным идентификатором проекта (созданным ранее в разделе «Настройка» этой практической работы).

let billingProjectId = 'YOUR_PROJECT_ID';
let publicProjectId = 'bigquery-public-data';
let datasetId = 'new_york_taxi_trips';
let tableName = 'tlc_yellow_trips_2016';

Примечание: каждый запрос будет использовать часть вашей ежемесячной бесплатной квоты в 1 ТБ. Каждый запрос BigQuery приводит к полному сканированию таблицы, поэтому перед выполнением запроса проверяйте её размер и указывайте только необходимые столбцы. Используйте оператор LIMIT , чтобы уменьшить объём возвращаемых данных.

Теперь добавьте в свой код две функции для генерации SQL и отправки запроса в BigQuery с помощью функции sendQuery , созданной на предыдущем шаге.

Первая функция должна называтьсяrectSQL rectangleSQL() и должна принимать два аргумента: пару объектов google.Maps.LatLng , представляющих углы прямоугольника в координатах карты.

Вторая функция должна называтьсяrectQuery rectangleQuery() . Она передаёт текст запроса в функцию sendQuery .

let billingProjectId = 'YOUR_PROJECT_ID';
let publicProjectId = 'bigquery-public-data';
let datasetId = 'new_york';
let tableName = 'tlc_yellow_trips_2016';

function rectangleQuery(latLngBounds){
  let queryString = rectangleSQL(latLngBounds.getNorthEast(), latLngBounds.getSouthWest());
  sendQuery(queryString);
}

function rectangleSQL(ne, sw){
  let queryString = 'SELECT pickup_latitude, pickup_longitude '
  queryString +=  'FROM `' + publicProjectId +'.' + datasetId + '.' + tableName + '`'
  queryString += ' WHERE pickup_latitude > ' + sw.lat();
  queryString += ' AND pickup_latitude < ' + ne.lat();
  queryString += ' AND pickup_longitude > ' + sw.lng();
  queryString += ' AND pickup_longitude < ' + ne.lng();
  return queryString;
}

На этом этапе у вас достаточно кода для отправки запроса в BigQuery по всем строкам, содержащимся в прямоугольнике, нарисованном пользователем. Прежде чем добавлять другие методы запроса для кругов и фигур, нарисованных от руки, давайте рассмотрим, как обрабатывать данные, возвращаемые запросом.

ПРИМЕЧАНИЕ . Прямоугольная форма на карте Google или любая карта в проекции Web Mercator не представляет прямоугольную область на земле. Это связано с тем, что линии долготы сходятся к полюсам, где они встречаются (Google Maps не показывают области выше примерно 85 градусов к северу или к югу от экватора). Чем больше зона земли, тем больше будет искажение истинной формы и площади на земле, но эффект должен быть незначительным на короткие расстояния до нескольких километров.

10. Визуализация ответа

Таблицы BigQuery могут быть очень большими - ветвиты данных - и могут расти на сотни тысяч рядов в секунду. Поэтому важно попытаться ограничить объем возвращаемых данных, чтобы их можно было нарисовать на карте. Рисование расположения каждого ряда в очень большом наборе результатов (десятки тысяч рядов или более) приведет к нечитаемой карте. Есть много методов для агрегирования мест как в запросе SQL, так и на карте, и вы можете ограничить результаты, которые вернется.

Полный код для этого шага доступен в step5/map.html .

Чтобы сохранить объем данных, передаваемых на вашу веб -страницу до разумного размера для этого CodeLab, измените функцию rectangleSQL() , чтобы добавить оператор, который ограничивает ответ 10000 строками. В приведенном ниже примере это указано в глобальной переменной, называемой recordLimit , так что все функции запроса могут использовать одно и то же значение.

let recordLimit = 10000;
function rectangleSQL(ne, sw){
  var queryString = 'SELECT pickup_latitude, pickup_longitude '
  queryString +=  'FROM `' + publicProjectId +'.' + datasetId + '.' + tableName + '`'
  queryString += ' WHERE pickup_latitude > ' + sw.lat();
  queryString += ' AND pickup_latitude < ' + ne.lat();
  queryString += ' AND pickup_longitude > ' + sw.lng();
  queryString += ' AND pickup_longitude < ' + ne.lng();
  queryString += ' LIMIT ' + recordLimit;
  return queryString;
}

Чтобы визуализировать плотность местоположений, вы можете использовать тепловую карту. Maps JavaScript API имеет класс Theatmaplayer для этой цели. Тепловой мастер принимает множество широты, координаты долготы, поэтому довольно легко преобразовать ряды, возвращаемые из запроса в тепловую карту.

В функции getQueryResults передайте response.result.rows массив к новой функции JavaScript, называемой doHeatMap() , которая создаст тепловую карту.

Каждая строка будет иметь свойство, называемое f , которое представляет собой массив столбцов. Каждый столбец будет иметь свойство v содержащее значение.

Ваш код должен пройти через столбцы в каждой строке и извлечь значения.

В запросе SQL вы просили только широту и долготу значений такси, поэтому в ответе будет только два столбца.

Не забудьте позвонить setMap() на слое тепловой карты, когда вы назначили массив позиций. Это сделает его видимым на карте.

Вот пример:

function getQueryResults(jobId){
  let request = gapi.client.bigquery.jobs.getQueryResults({
    'projectId': billingProjectId,
    'jobId': jobId
  });
  request.execute(response => doHeatMap(response.result.rows))
}

let heatmap;

function doHeatMap(rows){
  let heatmapData = [];
  if (heatmap != null){
    heatmap.setMap(null);
  }
  for (let i = 0; i < rows.length; i++) {
      let f = rows[i].f;
      let coords = { lat: parseFloat(f[0].v), lng: parseFloat(f[1].v) };
      let latLng = new google.maps.LatLng(coords);
      heatmapData.push(latLng);
  }
  heatmap = new google.maps.visualization.HeatmapLayer({
      data: heatmapData
  });
  heatmap.setMap(map);
}

На этом этапе вы должны быть в состоянии:

• Откройте страницу и санкционируйте против BigQuery
• Нарисуйте прямоугольник где -нибудь в Нью -Йорке
• Смотрите полученные результаты запроса, визуализированные как тепловая карта.

Вот пример результата прямоугольного запроса по сравнению с данными желтого такси в Нью -Йорке 2016 года, нарисованной в качестве тепловой карты. Это показывает распределение пикапов вокруг здания Empire State в субботу в июле:

11. Запрос радиусом вокруг точки

Запросы радиуса очень похожи. Используя Fucty BigQuery SQL Math Functions, вы можете построить SQL -запрос, используя формулу Haversine , которая приближается к круговой области на поверхности Земли.

Используя ту же метод для прямоугольников, вы можете обрабатывать событие OverlayComplete , чтобы получить центр и радиус круга, нарисованного пользователем, и создать SQL для запроса таким же образом.

Рабочий пример кода для этого шага включен в репозиторий кода как step6/map.html .

drawingManager.addListener('circlecomplete', circle => circleQuery(circle));

В вашей копии index.html добавьте две новые пустые функции: circleQuery() и haversineSQL() .

Затем добавьте обработчик событий circlecomplete , который передает центр и радиус к новой функции, называемой circleQuery().

Функция circleQuery() будет вызовать haversineSQL() для построения SQL для запроса, а затем отправить запрос, вызывая функцию sendQuery() в соответствии с следующим примером кода.

function circleQuery(circle){
  let queryString = haversineSQL(circle.getCenter(), circle.radius);
  sendQuery(queryString);
}

// Calculate a circular area on the surface of a sphere based on a center and radius.
function haversineSQL(center, radius){
  let queryString;
  let centerLat = center.lat();
  let centerLng = center.lng();
  let kmPerDegree = 111.045;

  queryString = 'CREATE TEMPORARY FUNCTION Degrees(radians FLOAT64) RETURNS FLOAT64 LANGUAGE js AS ';
  queryString += '""" ';
  queryString += 'return (radians*180)/(22/7);';
  queryString += '"""; ';

  queryString += 'CREATE TEMPORARY FUNCTION Radians(degrees FLOAT64) RETURNS FLOAT64 LANGUAGE js AS';
  queryString += '""" ';
  queryString += 'return (degrees*(22/7))/180;';
  queryString += '"""; ';

  queryString += 'SELECT pickup_latitude, pickup_longitude '
  queryString += 'FROM `' + publicProjectId +'.' + datasetId + '.' + tableName + '` ';
  queryString += 'WHERE '
  queryString += '(' + kmPerDegree + ' * DEGREES( ACOS( COS( RADIANS('
  queryString += centerLat;
  queryString += ') ) * COS( RADIANS( pickup_latitude ) ) * COS( RADIANS( ' + centerLng + ' ) - RADIANS('
  queryString += ' pickup_longitude ';
  queryString += ') ) + SIN( RADIANS('
  queryString += centerLat;
  queryString += ') ) * SIN( RADIANS( pickup_latitude ) ) ) ) ) ';

  queryString += ' < ' + radius/1000;
  queryString += ' LIMIT ' + recordLimit;
  return queryString;
}

Попробуйте!

Добавьте приведенный выше код и попробуйте инструмент «Circle», чтобы выбрать область карты. Результат должен выглядеть примерно так:

12. Запрос произвольных форм

Резюме: SQL не поддерживает запросы с использованием произвольных форм, кроме прямоугольников и кругов. BigQuery не имеет нативного типа данных геометрии, поэтому для запуска запросов с помощью полигонов вам нужен другой подход к простым запросам SQL.

Одна очень мощная функция BigQuery, которую можно использовать для этого, - это пользовательские функции (UDF). UDFS выполняет код JavaScript в SQL -запросе.

Рабочий код для этого шага находится в step7/map.html .

UDFS в BigQuery API

Подход BigQuery API для UDFS немного отличается от веб -консоли: вам нужно будет вызвать jobs.insert method .

Для стандартных запросов SQL через API требуется только один оператор SQL для использования функции, определенной пользователем. Значение useLegacySql должно быть установлено на false . Приведенный ниже пример Javascript показывает функцию, которая создает и отправляет объект запроса для вставки новой задачи, в данном случае запрос с определенной пользовательской функцией.

Рабочий пример этого подхода приведен в step7/map.html .

function polygonQuery(polygon) {
  let request = gapi.client.bigquery.jobs.insert({
    'projectId' : billingProjectId,
      'resource' : {
        'configuration':
          {
            'query':
            {
              'query': polygonSql(polygon),
              'useLegacySql': false
            }
          }
      }
  });
  request.execute(response => checkJobStatus(response.jobReference.jobId));
}

Запрос SQL построен следующим образом:

function polygonSql(poly){
  let queryString = 'CREATE TEMPORARY FUNCTION pointInPolygon(latitude FLOAT64, longitude FLOAT64) ';
  queryString += 'RETURNS BOOL LANGUAGE js AS """ ';
  queryString += 'var polygon=' + JSON.stringify(poly) + ';';
  queryString += 'var vertx = [];';
  queryString += 'var verty = [];';
  queryString += 'var nvert = 0;';
  queryString += 'var testx = longitude;';
  queryString += 'var testy = latitude;';
  queryString += 'for(coord in polygon){';
  queryString += '  vertx[nvert] = polygon[coord][0];';
  queryString += '  verty[nvert] = polygon[coord][1];';
  queryString += '  nvert ++;';
  queryString += '}';
  queryString += 'var i, j, c = 0;';
  queryString += 'for (i = 0, j = nvert-1; i < nvert; j = i++) {';
  queryString += '  if ( ((verty[i]>testy) != (verty[j]>testy)) &&(testx < (vertx[j]-vertx[i]) * (testy-verty[i]) / (verty[j]-verty[i]) + vertx[i]) ){';
  queryString += '    c = !c;';
  queryString += '  }';
  queryString += '}';
  queryString += 'return c;';
  queryString += '"""; ';
  queryString += 'SELECT pickup_latitude, pickup_longitude, dropoff_latitude, dropoff_longitude, pickup_datetime ';
  queryString += 'FROM `' + publicProjectId + '.' + datasetId + '.' + tableName + '` ';
  queryString += 'WHERE pointInPolygon(pickup_latitude, pickup_longitude) = TRUE ';
  queryString += 'LIMIT ' + recordLimit;
  return queryString;
}

Здесь происходит две вещи. Во -первых, код создает оператор CREATE TEMPORARY FUNCTION , который инкапсулирует код JavaScript, чтобы выработать, находится ли данная точка внутри многоугольника. Координаты многоугольника вставляются с использованием вызова метода JSON.stringify(poly) для преобразования массива JavaScript в парах координат y в строку. Объект многоугольника передается как аргумент для функции, которая строит SQL.

Во -вторых, код создает основной оператор SQL SELECT . UDF вызывается в WHERE в этом примере.

Интеграция с API MAPS

Чтобы использовать это с помощью библиотеки чертежей API MAPS, нам нужно сохранить полигон, нарисованный пользователем, и передать его в UDF часть SQL -запроса.

Во -первых, нам нужно обрабатывать событие рисования polygoncomplete , чтобы получить координаты формы в качестве массива паров долготы и широты:

drawingManager.addListener('polygoncomplete', polygon => {
  let path = polygon.getPaths().getAt(0);
  let queryPolygon = path.map(element => {
    return [element.lng(), element.lat()];
  });
  polygonQuery(queryPolygon);
});

Функция polygonQuery может затем построить функции UDF JavaScript как строку, а также оператор SQL, который будет вызывать функцию UDF.

См. Step7/map.html для рабочего примера этого.

Пример вывода

Вот пример результата запроса пикапов из данных желтого такси в NYC 2016 года в Бигкери с использованием полигона от руки, с выбранными данными, нарисованными в качестве тепловой карты.

13. Принимая его дальше

Вот несколько предложений для способов расширить этот коделаб, чтобы посмотреть на другие аспекты данных. Вы можете найти рабочий пример этих идей на step8/map.html в репозитории кода.

Картирование сброса

До сих пор мы только наметили места для забора. Запрашивая столбцы dropoff_latitude и dropoff_longitude и изменяя код HEATMAP, чтобы вместо этого построить их, вы можете увидеть направления поездок такси, которые начались в определенном месте.

Например, давайте посмотрим, где такси, как правило, отбрасывают людей, когда они просят забрать вокруг здания Empire State.

Измените код для оператора SQL в polygonSql() чтобы запросить эти столбцы в дополнение к месту получения.

function polygonSql(poly){
  let queryString = 'CREATE TEMPORARY FUNCTION pointInPolygon(latitude FLOAT64, longitude FLOAT64) ';
  queryString += 'RETURNS BOOL LANGUAGE js AS """ ';
  queryString += 'var polygon=' + JSON.stringify(poly) + ';';
  queryString += 'var vertx = [];';
  queryString += 'var verty = [];';
  queryString += 'var nvert = 0;';
  queryString += 'var testx = longitude;';
  queryString += 'var testy = latitude;';
  queryString += 'for(coord in polygon){';
  queryString += '  vertx[nvert] = polygon[coord][0];';
  queryString += '  verty[nvert] = polygon[coord][1];';
  queryString += '  nvert ++;';
  queryString += '}';
  queryString += 'var i, j, c = 0;';
  queryString += 'for (i = 0, j = nvert-1; i < nvert; j = i++) {';
  queryString += '  if ( ((verty[i]>testy) != (verty[j]>testy)) &&(testx < (vertx[j]-vertx[i]) * (testy-verty[i]) / (verty[j]-verty[i]) + vertx[i]) ){';
  queryString += '    c = !c;';
  queryString += '  }';
  queryString += '}';
  queryString += 'return c;';
  queryString += '"""; ';

  queryString += 'SELECT pickup_latitude, pickup_longitude, dropoff_latitude, dropoff_longitude, pickup_datetime ';
  queryString += 'FROM `' + publicProjectId + '.' + datasetId + '.' + tableName + '` ';
  queryString += 'WHERE pointInPolygon(pickup_latitude, pickup_longitude) = TRUE ';
  queryString += 'LIMIT ' + recordLimit;
  return queryString;
}

Функция doHeatMap может затем использовать значения высадки вместо этого. У объекта результата есть схема, которую можно проверить, чтобы найти местоположение этих столбцов в массиве. В этом случае они будут в индексных позициях 2 и 3. Эти индексы могут быть прочитаны из переменной, чтобы сделать код более управляемым. Nb maxIntensity тепловой карты установлена для того, чтобы показать плотность 20 каплей на пиксель в качестве максимума.

Добавьте несколько переменных, чтобы позволить вам изменить, какие столбцы вы используете для данных Heatmap.

// Show query results as a Heatmap.
function doHeatMap(rows){
  let latCol = 2;
  let lngCol = 3;
  let heatmapData = [];
  if (heatmap!=null){
    heatmap.setMap(null);
  }
  for (let i = 0; i < rows.length; i++) {
      let f = rows[i].f;
      let coords = { lat: parseFloat(f[latCol].v), lng: parseFloat(f[lngCol].v) };
      let latLng = new google.maps.LatLng(coords);
      heatmapData.push(latLng);
  }
  heatmap = new google.maps.visualization.HeatmapLayer({
      data: heatmapData,
      maxIntensity: 20
  });
  heatmap.setMap(map);
}

Вот тепловая карта, показывающая распределение каплей от всех пикапов, непосредственно вокруг здания Empire State в 2016 году. Вы можете увидеть большие концентрации (красные капли) мест в среднем городке, особенно вокруг Таймс -сквер, а также вдоль 5 -й авеню между 23 -й ул. И 14 -й улицей с другими местами высокой плотности, которые не показаны на этом уровне Zoom, включают в аэропорты La Guardia и JFK, в мировую торговую центр и парку.

Стилирование BaseMape

Когда вы создаете карту Google, используя API Maps JavaScript, вы можете установить стиль карты с помощью объекта JSON. Для визуализаций данных может быть полезно отключить цвета на карте. Вы можете создавать и попробовать стили карты, используя мастер стиля Google Maps по адресу mapstyle.withgoogle.com .

Вы можете установить стиль карты при инициализации объекта карты или в любое последующее время после этого. Вот как вы добавите пользовательский стиль в функцию initMap() :

function initMap() {
  map = new google.maps.Map(document.getElementById('map'), {
        center: {lat: 40.744593, lng: -73.990370}, // Manhattan, New York.
  zoom: 12,
  styles: [
    {
        "elementType": "geometry",
          "stylers": [
            {
              "color": "#f5f5f5"
            }
          ]
        },
        {
          "elementType": "labels.icon",
            "stylers": [
              {
                "visibility": "on"
              }
            ]
        },
        {
          "featureType": "water",
            "elementType": "labels.text.fill",
              "stylers": [
                {
                  "color": "#9e9e9e"
                }
              ]
        }
      ]
    });
  setUpDrawingTools();
}

В стиле примера ниже показана карта серого с меткой точек, представляющих интерес.

[
  {
    "elementType": "geometry",
    "stylers": [
      {
        "color": "#f5f5f5"
      }
    ]
  },
  {
    "elementType": "labels.icon",
    "stylers": [
      {
        "visibility": "on"
      }
    ]
  },
  {
    "elementType": "labels.text.fill",
    "stylers": [
      {
        "color": "#616161"
      }
    ]
  },
  {
    "elementType": "labels.text.stroke",
    "stylers": [
      {
        "color": "#f5f5f5"
      }
    ]
  },
  {
    "featureType": "administrative.land_parcel",
    "elementType": "labels.text.fill",
    "stylers": [
      {
        "color": "#bdbdbd"
      }
    ]
  },
  {
    "featureType": "poi",
    "elementType": "geometry",
    "stylers": [
      {
        "color": "#eeeeee"
      }
    ]
  },
  {
    "featureType": "poi",
    "elementType": "labels.text.fill",
    "stylers": [
      {
        "color": "#757575"
      }
    ]
  },
  {
    "featureType": "poi.park",
    "elementType": "geometry",
    "stylers": [
      {
        "color": "#e5e5e5"
      }
    ]
  },
  {
    "featureType": "poi.park",
    "elementType": "labels.text.fill",
    "stylers": [
      {
        "color": "#9e9e9e"
      }
    ]
  },
  {
    "featureType": "road",
    "elementType": "geometry",
    "stylers": [
      {
        "color": "#ffffff"
      }
    ]
  },
  {
    "featureType": "road.arterial",
    "elementType": "labels.text.fill",
    "stylers": [
      {
        "color": "#757575"
      }
    ]
  },
  {
    "featureType": "road.highway",
    "elementType": "geometry",
    "stylers": [
      {
        "color": "#dadada"
      }
    ]
  },
  {
    "featureType": "road.highway",
    "elementType": "labels.text.fill",
    "stylers": [
      {
        "color": "#616161"
      }
    ]
  },
  {
    "featureType": "road.local",
    "elementType": "labels.text.fill",
    "stylers": [
      {
        "color": "#9e9e9e"
      }
    ]
  },
  {
    "featureType": "transit.line",
    "elementType": "geometry",
    "stylers": [
      {
        "color": "#e5e5e5"
      }
    ]
  },
  {
    "featureType": "transit.station",
    "elementType": "geometry",
    "stylers": [
      {
        "color": "#eeeeee"
      }
    ]
  },
  {
    "featureType": "water",
    "elementType": "geometry",
    "stylers": [
      {
        "color": "#c9c9c9"
      }
    ]
  },
  {
    "featureType": "water",
    "elementType": "labels.text.fill",
    "stylers": [
      {
        "color": "#9e9e9e"
      }
    ]
  }
]

Предоставление отзывов пользователя

Несмотря на то, что BigQuery обычно дает ответ в считанные секунды, иногда полезно показать пользователю, что что -то происходит во время запуска.

Добавьте немного пользовательского интерфейса на свою веб -страницу, которая показывает ответ функции checkJobStatus() и анимированную графику, чтобы указать, что запрос находится в процессе.

Информация, которую вы можете отобразить, включает продолжительность запроса, количество возвращенных данных и объем обработанных данных.

Добавьте немного HTML после карты <div> , чтобы создать панель на страницу, на которой будет показано количество строк, возвращаемых запросом, времени, которое потребовалось, и объем обработанных данных.

<div id="menu">
    <div id="stats">
        <h3>Statistics:</h3>
        <table>
            <tr>
                <td>Total Locations:</td><td id="rowCount"> - </td>
            </tr>
            <tr>
                <td>Query Execution:</td><td id="duration"> - </td>
            </tr>
            <tr>
                <td>Data Processed:</td><td id="bytes"> - </td>
            </tr>
        </table>
    </div>
</div>

Внешний вид и положение этой панели контролируются CSS. Добавьте CSS, чтобы поместить панель в верхнем левом углу страницы под кнопками типа карты и панели инструментов чертежа, как в фрагменте ниже.

#menu {
  position: absolute; 
  background: rgba(255, 255, 255, 0.8); 
  z-index: 1000; 
  top: 50px; 
  left: 10px; 
  padding: 15px;
}
#menu h1 {
  margin: 0 0 10px 0;
  font-size: 1.75em;
}
#menu div {
  margin: 5px 0px;
}

Анимированный график может быть добавлен на страницу, но скрыт до тех пор, пока не требуется, а какой -то код JavaScript и CSS использовался, чтобы показать его, когда работает большая работа.

Добавьте немного HTML, чтобы показать анимированную графику. В папке loader.gif в папке img в папке IMG есть файл изображения.

<img id="spinner" src="img/loader.gif">

Добавьте несколько CSS, чтобы позиционировать изображение и скрыть его по умолчанию, пока оно не понадобится.

#spinner {
  position: absolute; 
  top: 50%; 
  left: 50%; 
  margin-left: -32px; 
  margin-top: -32px; 
  opacity: 0; 
  z-index: -1000;
}

Наконец, добавьте немного JavaScript, чтобы обновить панель состояния и отобразить или скрыть графику, когда запускается запрос. Вы можете использовать объект response для обновления панели в зависимости от того, какая информация доступна.

При проверке текущей работы существует response.statistics . Свойство статистики, которое вы можете использовать. Когда задание будет завершено, вы можете получить доступ response.totalRows Totalrows и response.totalBytesProcessed . Пользователю полезно преобразовать миллисекунды в секунды и байты в гигабайты для отображения, как показано в примере кода ниже.

function updateStatus(response){
  if(response.statistics){
    let durationMs = response.statistics.endTime - response.statistics.startTime;
    let durationS = durationMs/1000;
    let suffix = (durationS ==1) ? '':'s';
    let durationTd = document.getElementById("duration");
    durationTd.innerHTML = durationS + ' second' + suffix;
  }
  if(response.totalRows){
    let rowsTd = document.getElementById("rowCount");
    rowsTd.innerHTML = response.totalRows;
  }
  if(response.totalBytesProcessed){
    let bytesTd = document.getElementById("bytes");
    bytesTd.innerHTML = (response.totalBytesProcessed/1073741824) + ' GB';
  }
}

Вызовите этот метод, когда есть ответ на вызов checkJobStatus() и когда результаты запроса будут извлечены. Например:

// Poll a job to see if it has finished executing.
function checkJobStatus(jobId){
  let request = gapi.client.bigquery.jobs.get({
    'projectId': billingProjectId,
    'jobId': jobId
  });
  request.execute(response => {
    //Show progress to the user
    updateStatus(response);

    if (response.status.errorResult){
      // Handle any errors.
      console.log(response.status.error);
      return;
    }
    if (response.status.state == 'DONE'){
      // Get the results.
      clearTimeout(jobCheckTimer);
      getQueryResults(jobId);
      return;
    }
    // Not finished, check again in a moment.
    jobCheckTimer = setTimeout(checkJobStatus, 500, [jobId]); 
  });
}

// When a BigQuery job has completed, fetch the results.
function getQueryResults(jobId){
  let request = gapi.client.bigquery.jobs.getQueryResults({
    'projectId': billingProjectId,
    'jobId': jobId
  });
  request.execute(response => {
    doHeatMap(response.result.rows);
    updateStatus(response);
  })
}

Чтобы переключить анимированную графику, добавьте функцию для управления ее видимостью. Эта функция будет переключать непрозрачность любого элемента HTML DOM, переданного ей.

function fadeToggle(obj){
    if(obj.style.opacity==1){
        obj.style.opacity = 0;
        setTimeout(() => {obj.style.zIndex = -1000;}, 1000);
    } else {
        obj.style.zIndex = 1000;
        obj.style.opacity = 1;
    }
}

Наконец, назовите этот метод перед обработкой запроса, и после того, как результат запроса вернулся из BigQuery.

Этот код вызывает функцию fadeToggle , когда пользователь закончил рисовать прямоугольник.

drawingManager.addListener('rectanglecomplete', rectangle => {
  //show an animation to indicate that something is happening.
  fadeToggle(document.getElementById('spinner'));
  rectangleQuery(rectangle.getBounds());
});

Когда ответ запроса был получен, позвоните fadeToggle() снова, чтобы скрыть анимированную графику.

// When a BigQuery job has completed, fetch the results.
function getQueryResults(jobId){
  let request = gapi.client.bigquery.jobs.getQueryResults({
    'projectId': billingProjectId,
    'jobId': jobId
  });
  request.execute(response => {
    doHeatMap(response.result.rows);
    //hide the animation.
    fadeToggle(document.getElementById('spinner'));
    updateStatus(response);
  })
}

Страница должна выглядеть примерно так.

Посмотрите на полный пример в step8/map.html .

14. Что нужно учитывать

Слишком много маркеров

Если вы работаете с очень большими таблицами, ваш запрос может вернуть слишком много рядов, чтобы эффективно отображаться на карте. Ограничьте результаты, добавив пункт WHERE пункт или LIMIT оператор.

Рисование многих маркеров может сделать карту нечитаемой. Рассмотрите возможность использования HeatmapLayer , чтобы показать плотность или кластерные маркеры, чтобы указать, где лежат многие точки данных, используя один символ на кластер. В нашем учебном пособии по кластеризации есть больше деталей.

Оптимизация запросов

BigQuery сканирует весь стол с каждым запросом. Чтобы оптимизировать использование квот BigQuery, выберите только нужные столбцы, которые вам нужны в вашем запросе.

Запросы будут быстрее, если вы храните широту и долготу в виде поплавков, а не струн.

Экспорт интересных результатов

Примеры здесь требуют, чтобы конечный пользователь был аутентифицирован против таблицы BigQuery, которая не соответствует каждому варианту использования. Когда вы обнаружили некоторые интересные шаблоны, может быть легче поделиться ими с более широкой аудиторией, экспортируя результаты BigQuery и создав статический набор данных с использованием уровня данных Google Maps .

Скучный закон

Имейте в виду, что Условия обслуживания платформы платформы Google Maps. Более подробную информацию о ценах на платформу Google Maps см. В Интернете документацию .

Играйте с большим количеством данных!

В BigQuery есть ряд публичных наборов данных, которые имеют столбцы широты и долготы, например, наборы данных такси в Нью-Йорке с 2009-2016 , Uber и Lyft NYC Trip Data и набор данных GDELT .

15. Поздравляю!

Мы надеемся, что это поможет вам быстро работать с некоторыми гео -запросами против таблиц BigQuery, чтобы вы могли обнаружить шаблоны и визуализировать их на карте Google. Счастливого картирования!

Что дальше?

Если вы хотите узнать больше о платформе Google Maps или BigQuery, посмотрите на следующие предложения.

Посмотрите , что такое BigQuery, чтобы узнать больше о службе Google без сервера, масштабного масштаба Petabyte.

Посмотрите на руководство по праву, чтобы создать простое приложение, используя API BigQuery .

См. Руководство по разработчике для библиотеки чертежей для получения более подробной информации о том, как позволить пользовательскому взаимодействию рисовать формы на карте Google.

Посмотрите на другие способы визуализации данных на карте Google.

См. Руководство по началу работы для клиента JavaScript AP I, чтобы понять основные концепции использования API клиента для доступа к другим API Google.