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

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

О практической работе

Последнее обновление: дек. 3, 2021

Авторы: Google Maps Platform Team

1. Обзор

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

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

Что вы будете строить

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

Что вы узнаете

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

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

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

Технология

Большой запрос

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

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

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

Слой рисования Javascript API платформы Google Maps позволяет рисовать фигуры на карте. Их можно преобразовать во входные данные для выполнения запросов к таблицам 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, используйте проект, выбранный или созданный на предыдущем шаге. В этом проекте должна быть включена оплата. После включения выставления счетов вы можете включить BigQuery API.

Примечание. Плата за запросы, выполняемые в этой лаборатории кода, не взимается, но если вы превысите уровень бесплатного пользования BigQuery (1 ТБ запросов данных в месяц) или максимальную бесплатную дневную квоту для Maps 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

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

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

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

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

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

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

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

BigQuery поддерживает две версии SQL: Legacy SQL и Standard SQL . Последний является стандартом ANSI 2011 года. Для целей этого руководства мы будем использовать стандартный 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_tip_pc	avg_fare_mile
2,52	12.03	9,97	22.39	5,97

Кеннеди

avg_tip	avg_fare	среднее_расстояние	avg_tip_pc	avg_fare_mile
9.22	48,49	41.19	22.48	4,36

Радиусные запросы

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

Вот пример инструкции 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 для формулы Haversine выглядит сложным, но все, что вам нужно сделать, это указать координату центра окружности, радиус и имена проекта, набора данных и таблицы для 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_tip_pc	avg_fare_mile
1,17	11.08	45,28	10.53	6,42

Бронкс

avg_tip	avg_fare	среднее_расстояние	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 требует всего одного оператора, но пользовательская функция должна быть определена как временная функция в операторе. Вот пример. Вставьте приведенный ниже оператор 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. Как вы видели, местоположение имеет большое значение для данных результатов запросов к этому набору данных, но если вы не догадываетесь, где выполнять ваши запросы, трудно обнаружить специальные пространственные закономерности, используя только SQL-запросы.

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

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

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

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

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

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

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

Чтобы делать запросы к платформе Google Maps, вам необходимо сгенерировать ключ 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. нажмите Ввод
4. В открывшемся окне щелкните значок веб-сервера. Вы также можете щелкнуть приложение правой кнопкой мыши, чтобы открыть его в обычной или закрепленной вкладке, в полноэкранном режиме или в новом окне. Далее вы увидите это диалоговое окно, которое позволяет настроить локальный веб-сервер:
5. Нажмите «ВЫБЕРИТЕ ПАПКУ» и выберите папку, в которую вы загрузили файлы образцов кода.
6. В разделе «Параметры» установите флажок «Автоматически показывать index.html»:
7. Сдвиньте переключатель с надписью «Веб-сервер: ЗАПУЩЕН» влево, а затем обратно вправо, чтобы остановить, а затем перезапустите веб-сервер.

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

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

Начните с простой HTML-страницы, которая загружает Карту Google с помощью Maps JavaScript API и нескольких строк Javascript. Код из примера простой карты платформы Google Maps — отличное место для начала. Он воспроизведен здесь, чтобы вы могли скопировать и вставить его в текстовый редактор или 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>

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

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

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

Эта лаборатория кода также использует 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 с включенными инструментами 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 через клиентскую библиотеку API Google для 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 Console в меню навигации выберите API и службы > Учетные данные .

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

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

8. В раскрывающемся меню выберите Идентификатор клиента OAuth .
9. В разделе Тип приложения выберите Веб-приложение .
10. В поле Имя приложения введите имя вашего проекта. Например, «BigQuery и карты».
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 загружается на веб-страницу, вам необходимо выполнить следующие шаги:

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

См. step3/map.html для примера того, как будет выглядеть готовая 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>

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

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

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

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

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

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

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

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

Добавьте код в loadApi() для загрузки BigQuery API с помощью 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 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() после загрузки BigQuery API.

8. Принципы API BigQuery

Вызовы BigQuery API обычно выполняются за секунды, но могут не возвращать ответ немедленно. Вам нужна некоторая логика, чтобы опросить 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. Запрос данных о местоположении с помощью BigQuery API

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

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

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

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

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

Прямоугольные запросы

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

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

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

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

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

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

Доступно множество таблиц поездок такси Нью-Йорка. Чтобы увидеть их, перейдите в веб-консоль BigQuery и разверните пункт меню «Общедоступные наборы данных». Найдите набор данных с именем new_york и разверните его, чтобы увидеть таблицы. Выберите таблицу поездок желтого такси: 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. Инициализируйте 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 , которую вы создали на предыдущем шаге.

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

Вторая функция должна называться 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 для всех строк, содержащихся в прямоугольнике, нарисованном пользователем. Прежде чем мы добавим другие методы запроса для кругов и произвольных фигур, давайте посмотрим, как обрабатывать данные, возвращаемые запросом.

Note : a rectangular shape on a Google Map, or any map in the Web Mercator projection, does not represent a rectangular area on the ground. This is because lines of longitude converge towards the poles where they meet (Google Maps doesn't show areas above about 85 degrees north or south of the equator). The larger the ground area covered, the bigger the distortion of the true shape and area on the ground will be, but the effect should be negligible over short distances of up to a few kilometers.

10. Visualizing the response

BigQuery tables can be very large—Petabytes of data—and can grow by hundreds of thousands of rows per second. So it's important to try and limit the amount of data returned so that it can be drawn on the map. Drawing the location of every row in a very large result set (tens of thousands of rows or greater) will result in an unreadable map. There are many techniques for aggregating the locations both in the SQL query and on the map, and you can limit the results a query will return.

Full code for this step is available in step5/map.html .

To keep the amount of data transferred to your web page down to a reasonable size for this codelab, modify the rectangleSQL() function to add a statement that limits the response to 10000 rows. In the example below this is specified in a global variable called recordLimit , so that all query functions can use the same value.

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;
}

To visualize the density of locations you can use a heatmap. The Maps Javascript API has a HeatmapLayer class for this purpose. The HeatmapLayer takes an array of latitude, longitude coordinates so it is quite easy to convert the rows returned from the query into a heatmap.

In the getQueryResults function, pass the response.result.rows array to a new Javascript function called doHeatMap() that will create a heatmap.

Each row will have a property called f which is an array of columns. Each column will have a v property containing the value.

Your code needs to loop through the columns in each row and extract the values.

In the SQL query, you have only asked for the Latitude and Longitude values of the taxi pickups so there will only be two columns in the response.

Don't forget to call setMap() on the heatmap layer when you have assigned the array of positions to it. This will make it visible on the map.

Here's an example:

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);
}

At this point, you should be able to:

• Open the page and authorize against BigQuery
• Draw a rectangle somewhere in NYC
• See the resulting query results visualized as a heatmap.

Here is an example of the result from a rectangle query against the 2016 NYC Yellow Taxi data, drawn as a heatmap. This shows the distribution of pickups around the Empire State Building on a Saturday in July:

11. Querying by radius around a point

Radius queries are very similar. Using BigQuery's Legacy SQL Math functions you can construct a SQL query using the Haversine Formula which approximates a circular area on the earth's surface.

Using the same technique for rectangles, you can handle an OverlayComplete event to get the center and radius of a user-drawn circle, and build up the SQL for the query in the same way.

A working example of the code for this step is included in the code repository as step6/map.html .

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

In your copy of index.html, add two new empty functions: circleQuery() and haversineSQL() .

Then, add a circlecomplete event handler that passes the centre and radius to a new function called circleQuery().

The circleQuery() function will call haversineSQL() to construct the SQL for the query and then send the query by calling the sendQuery() function as per the following example code.

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;
}

Try it!

Add the code above and try the 'Circle' tool to select an area of map. The result should look something like this:

12. Querying arbitrary shapes

Recap: SQL doesn't support querying using arbitrary shapes other than rectangles and circles. BigQuery doesn't have any native geometry data type, so to run queries using polygon shapes you need a different approach to straightforward SQL queries.

One very powerful BigQuery feature that can be used for this is User Defined Functions (UDF). UDFs execute Javascript code inside an SQL query.

Working code for this step is in step7/map.html .

UDFs in the BigQuery API

The BigQuery API approach for UDFs is slightly different to the web console: you'll need to call the jobs.insert method .

For Standard SQL queries via the API, just a single SQL statement is needed to use a User Defined Function. The value of useLegacySql must be set to false . The JavaScript example below shows a function that creates and sends a request object to insert a new job, in this case a query with a User Defined Function.

A working example of this approach is in 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));
}

The SQL query is constructed as follows:

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;
}

There are two things going on here. Firstly the code is creating the CREATE TEMPORARY FUNCTION statement that encapsulates the JavaScript code to work out if a given point is inside a polygon. The polygon coordinates are inserted using the JSON.stringify(poly) method call to convert a JavaScript array of x,y coordinate pairs into a string. The polygon object is passed as an argument to the function that builds the SQL.

Secondly the code builds the main SQL SELECT statement. The UDF is called in the WHERE expression in this example.

Integrating with the Maps API

To use this with the Maps API drawing library, we need to save the polygon drawn by the user and pass this into the UDF part of the SQL query.

First, we need to handle the polygoncomplete drawing event, to get the coordinates of the shape as an array of longitude and latitude pairs:

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

The polygonQuery function can then construct the UDF Javascript functions as a string, as well as the SQL statement which will call the UDF function.

See step7/map.html for a working example of this.

Example output

Here's an example result of querying pickups from The 2016 NYC TLC Yellow Taxi data in BigQuery using a freehand polygon, with the selected data drawn as a heatmap.

13. Taking it Further

Here are some suggestions for ways to extend this codelab to look at other aspects of the data. You can find a working example of these ideas at step8/map.html in the code repository.

Mapping drop offs

So far we've only mapped pick up locations. By requesting the dropoff_latitude and dropoff_longitude columns and modifying the heatmap code to plot these instead, you can see the destinations of taxi journeys that started at a specific location.

For example, let's see where taxis tend to drop people off when they request a pick up around the Empire State Building.

Change the code for the SQL statement in polygonSql() to request these columns in addition to the pickup location.

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;
}

The doHeatMap function can then use the dropoff values instead. The result object has a schema that can be inspected to find the location of these columns in the array. In this case they would be at index positions 2 and 3. These indices can be read from a variable to make the code more manageable. NB the maxIntensity of the heatmap is set to show density of 20 drop offs per pixel as the maximum.

Add some variables to allow you to change which columns you use for the heatmap data.

// 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);
}

Here is a heatmap showing the distribution of drop offs from all pickups immediately around the Empire State Building in 2016. You can see large concentrations (the red blobs) of midtown destinations especially around Times Square, as well as along 5th Avenue between 23rd St and 14th St. Other high density locations not shown at this zoom level include La Guardia and JFK airports, the World Trade Center and Battery Park.

Styling the basemap

When you create a Google Map using the Maps JavaScript API, you can set the map style using a JSON object. For data visualizations it can be useful to mute the colors in the map. You can create and try out map styles using the Google Maps API Styling Wizard at mapstyle.withgoogle.com .

You can set a map style when you initialize a map object, or at any subsequent time afterwards. Here's how you'd add a custom style in the initMap() function:

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();
}

The sample style below shows a greyscale map with points of interest labels.

[
  {
    "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"
      }
    ]
  }
]

Giving the user feedback

Even though BigQuery will usually give a response in seconds, it is sometimes useful to show the user that something is happening while the query is running.

Add some UI to your web page that shows the response of the checkJobStatus() function, and an animated graphic to indicate that the query is in progress.

Information you can display includes query duration, amount of data returned, and amount of data processed.

Add some HTML after the map <div> to create a panel to the page that will show the number of rows returned by a query, the time the query took, and the amount of data processed.

<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>

The appearance and position of this panel is controlled by CSS. Add CSS to position the panel in the top left corner of the page below the map type buttons and the drawing toolbar as in the snippet below.

#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;
}

The animated graphic can be added to the page but hidden until required, and some JavaScript and CSS code used to show it when a BigQuery job is running.

Add some HTML to show an animated graphic. There is an image file called loader.gif in the img folder in the code repository.

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

Add some CSS to position the image and hide it by default until it's needed.

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

Finally add some JavaScript to update the status panel and show or hide the graphic when a query is running. You can use the response object to update the panel depending on what information is available.

When checking a current job, there is a response.statistics property you can use. When the job is complete you can access the response.totalRows and response.totalBytesProcessed properties. It is helpful to the user to convert milliseconds to seconds and bytes to gigabytes for display as shown in the code sample below.

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';
  }
}

Call this method when there is a response to a checkJobStatus() call and when the query results are fetched. Например:

// 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);
  })
}

To toggle the animated graphic, add a function to control its visibility. This function will toggle the opacity of any HTML DOM Element passed to it.

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;
    }
}

Finally, call this method before processing a query, and after the query result has come back from BigQuery.

This code calls the fadeToggle function when the user has finished drawing a rectangle.

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

When the query response has been received, call fadeToggle() again to hide the animated graphic.

// 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);
  })
}

The page should look something like this.

Have a look at the complete example in step8/map.html .

14. Things to Consider

Too Many Markers

If you're working with very large tables, your query may return too many rows to efficiently display on a map. Limit the results by adding a WHERE clause or a LIMIT statement.

Drawing many markers can make the map unreadable. Consider using a HeatmapLayer to show the density, or cluster markers to indicate where many data points lie using a single symbol per cluster. There are more details in our Marker Clustering tutorial .

Optimizing Queries

BigQuery will scan the entire table with every query. To optimize your BigQuery quota usage, only select the columns you need in your query.

Queries will be faster if you store latitude and longitude as floats rather than strings.

Export Interesting Results

The examples here require the end user to be authenticated against the BigQuery table, which won't suit every use case. When you have discovered some interesting patterns, it may be easier to share these with a wider audience by exporting the results from BigQuery and creating a static dataset using the Google Maps Data Layer .

The Boring Legal Bit

Bear in mind the Google Maps Platform Terms of Service . For more details on Google Maps Platform pricing, see the online documentation .

Play With More Data!

There are a number of public datasets in BigQuery that have latitude and longitude columns, for example the NYC Taxi datasets from 2009-2016 , Uber and Lyft NYC trip data , and the GDELT dataset .

15. Congratulations!

We hope this helps you get up and running quickly with some geo queries against BigQuery tables so you can discover patterns and visualize them on a Google Map. Happy mapping!

Что дальше?

If you'd like to learn more about the Google Maps Platform or BigQuery, have a look at the following suggestions.

See What is BigQuery to learn more about Google's serverless, petabyte-scale data warehouse service.

Have a look at the how-to guide to create a simple application using the BigQuery API .

See the developer guide for the drawing library for more details on enabling user interaction to draw shapes on a Google Map.

Have a look at other ways to visualize data on a Google Map.

See the Getting Started guide for the Javascript Client AP I to understand the basic concepts of using the Client API to access other Google APIs.