Запросить набор данных напрямую

Для прямого доступа к данным Places Insights необходимо писать SQL-запросы в BigQuery, которые возвращают агрегированные данные о местах. Результаты возвращаются из набора данных по критериям поиска, указанным в запросе.

Примечание: Результаты агрегирования возвращаются только в том случае, если значение равно 5 или больше. Например, если в результатах поиска отображается значение 0, 1, 2, 3 или 4, то соответствующая строка будет исключена из итогового результата.

Если вам нужно получить значения меньше 5, рассмотрите возможность использования функций подсчета мест . Эти функции могут возвращать любые значения, включая 0, но устанавливают минимальную область поиска 40,0 метров на 40,0 метров (1600 ^м² ). Узнайте больше о том, когда следует выполнять прямые запросы, а когда использовать функции .

Основы запросов

На следующем изображении показан базовый формат запроса:

Каждая часть запроса описана более подробно ниже.

Успех: BigQuery также предоставляет документацию и примеры выполнения запросов к геопространственным данным. Для получения дополнительной информации см. раздел «Введение в геопространственную аналитику» .

Требования к запросу

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

В этом примере указывается, что для запроса к набору данных по Соединенным Штатам используется places_insights___us.places .

SELECT WITH AGGREGATION_THRESHOLD
COUNT(*) AS count
FROM `PROJECT_NAME.places_insights___us.places`

Укажите название проекта (необязательно)

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

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

Например, [project name].[dataset name].places .

Укажите функцию агрегирования

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

SELECT WITH AGGREGATION_THRESHOLD
  COUNT(id) AS place_count,
  APPROX_COUNT_DISTINCT(rating) as distinct_ratings,
  COUNTIF(rating > 4.0) as good_rating_count,
  LOGICAL_AND(rating <= 5) as all_ratings_equal_or_below_five,
  LOGICAL_OR(rating = 5) as any_rating_exactly_five,
  AVG(rating) as avg_rating,
  SUM(user_rating_count) as rating_count,
  COVAR_POP(rating, user_rating_count) as rating_covar_pop,
  COVAR_SAMP(rating, user_rating_count) as rating_covar_samp,
  STDDEV_POP(rating) as rating_stddev_pop,
  STDDEV_SAMP(rating) as rating_stddev_samp,
  VAR_POP(rating) as rating_var_pop,
  VAR_SAMP(rating) as rating_var_samp,
FROM
  `PROJECT_NAME.places_insights___us.places`
WHERE
  ST_DWITHIN(ST_GEOGPOINT(-73.9857, 40.7484), point, 1000)
  AND business_status = "OPERATIONAL"

Укажите ограничение по местоположению

Если ограничение по местоположению не указано, агрегация данных применяется ко всему набору данных. Обычно ограничение по местоположению указывается для поиска в определенной области. В этом примере запроса задано целевое ограничение, центрированное на Эмпайр-стейт-билдинг в Нью-Йорке, с радиусом 1000 метров.

SELECT WITH AGGREGATION_THRESHOLD
COUNT(*) AS count
FROM `PROJECT_NAME.places_insights___us.places`
WHERE
ST_DWITHIN(ST_GEOGPOINT(-73.9857, 40.7484), point, 1000)

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

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

SELECT WITH AGGREGATION_THRESHOLD
COUNT(*) AS count
FROM `PROJECT_NAME.places_insights___us.places`
WHERE
ST_CONTAINS(ST_GEOGFROMTEXT("""POLYGON((-73.985708 40.75773,-73.993324 40.750298,
                                      -73.9857 40.7484,-73.9785 40.7575,
                                      -73.985708 40.75773))"""), point)

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

DECLARE route GEOGRAPHY;

SET route = ST_GEOGFROMTEXT("""LINESTRING(-73.98903537033028 40.73655649223003,
                                          -73.93580216278471 40.80955538843361)""");

SELECT WITH AGGREGATION_THRESHOLD
  COUNT(*) AS count
FROM `PROJECT_NAME.places_insights___us.places`
WHERE
  ST_DWITHIN(route, point, 100)

Фильтрация по полям набора данных места.

Уточните поиск на основе полей, определенных схемой набора данных . Отфильтруйте результаты по таким полям набора данных, как «место», regular_opening_hours , price_level и rating клиента».

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

• Основная схема , общая для всех наборов данных по странам.
• Схема, специфичная для конкретной страны , определяющая компоненты схемы, характерные именно для этой страны.

Например, ваш запрос может включать условие WHERE , определяющее критерии фильтрации для запроса.

Совет: Используйте фильтр business_status , чтобы получить количество OPERATIONAL предприятий. В противном случае в подсчет будут включены закрытые предприятия.

В следующем примере возвращаются агрегированные данные для мест типа tourist_attraction со business_status OPERATIONAL , rating не ниже 4.0 и параметром allows_dogs , установленным в true :

SELECT WITH AGGREGATION_THRESHOLD
COUNT(*) AS count
FROM `PROJECT_NAME.places_insights___us.places`
WHERE
ST_DWITHIN(ST_GEOGPOINT(-73.9857, 40.7484), point, 1000)
AND 'tourist_attraction' IN UNNEST(types)
AND business_status = "OPERATIONAL"
AND rating >= 4.0
AND allows_dogs = true

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

SELECT WITH AGGREGATION_THRESHOLD
  COUNT(*) AS count
FROM
  `PROJECT_NAME.places_insights___us.places`
WHERE
  ev_charge_options.connector_count > 8;

Фильтрация по основному типу места и типу места.

Каждое место в наборе данных может иметь:

• С этим местом связан единственный основной тип из типов, определенных в параметрах типа «Местоположение» . Например, основным типом может быть mexican_restaurant или steak_house . Используйте primary_type в запросе, чтобы отфильтровать результаты по основному типу места.

• С этим объектом могут быть связаны несколько типов, определенных в параметрах типа Place . Например, ресторан может иметь следующие типы: seafood_restaurant , restaurant , food , point_of_interest , establishment . Используйте types в запросе, чтобы отфильтровать результаты по списку типов, связанных с этим местом.

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

SELECT WITH AGGREGATION_THRESHOLD
  COUNT(*) AS count
FROM
  `PROJECT_NAME.places_insights___us.places`
WHERE
  'spa' IN UNNEST(types)
  AND 'skin_care_clinic' = primary_type

Фильтрация по идентификатору места

В приведенном ниже примере вычисляется средний рейтинг для 5 мест. Места идентифицируются по их place_id .

DECLARE place_ids ARRAY<STRING>;
SET place_ids = ['ChIJPQOh8YVZwokRE2WsbZI4tOk', 'ChIJibtT3ohZwokR7tX0gp0nG8U',
                 'ChIJdfD8moVZwokRO6vxjXAtoWs', 'ChIJsdNONuFbwokRLM-yuifjb8k',
                 'ChIJp0gKoClawokR0txqrcaEkFc'];
SELECT WITH AGGREGATION_THRESHOLD
 AVG(rating) as avg_rating,
FROM
  `PROJECT_NAME.places_insights___us.places`,
  UNNEST(place_ids) place_id
WHERE
  id = place_id;

Отфильтровать определенные идентификаторы мест

Также можно исключить из запроса массив идентификаторов мест .

Вы можете найти нужные вам идентификаторы мест, используя инструмент поиска идентификаторов мест , или программно, используя API мест для выполнения запроса текстового поиска (нового) .

В приведенном ниже примере запрос подсчитывает количество кафе в почтовом индексе 2000 Сиднея, Австралия, которые не указаны в массиве excluded_cafes . Такой запрос может быть полезен владельцу бизнеса, который хотел бы исключить свои собственные заведения из подсчета.

WITH excluded_cafes AS (
  -- List the specific place IDs to exclude from the final count
  SELECT * FROM UNNEST([
    'ChIJLTcYGz-uEmsRmazk9oMnP5w', 'ChIJeWDDDNOvEmsRF8SMPUwPbhw',
    'ChIJKdaKHbmvEmsRSdxq_1O05bU'
  ]) AS place_id
)

SELECT WITH AGGREGATION_THRESHOLD
  COUNT(*) AS count
FROM
  `places_insights___au.places` AS places
-- Perform a LEFT JOIN to identify which places are in the exclusion list
LEFT JOIN
  excluded_cafes ON places.id = excluded_cafes.place_id
WHERE
  -- Filter for specific place type and postal code
  places.primary_type = 'cafe'
  AND '2000' IN UNNEST(places.postal_code_names)
  -- Keep only the rows where the join failed (meaning the ID was NOT in the list)
  AND excluded_cafes.place_id IS NULL;

Фильтрация по предопределенным значениям данных

Многие поля набора данных имеют предопределенные значения. Например:

• Поле price_level поддерживает следующие предопределенные значения:

  • PRICE_LEVEL_FREE
  • PRICE_LEVEL_INEXPENSIVE
  • PRICE_LEVEL_MODERATE
  • PRICE_LEVEL_EXPENSIVE
  • PRICE_LEVEL_VERY_EXPENSIVE

• Поле business_status поддерживает следующие предопределенные значения:

  • OPERATIONAL
  • CLOSED_TEMPORARILY
  • CLOSED_PERMANENTLY

Примечание: Обычно для получения количества OPERATIONAL предприятий используется фильтр business_status . В противном случае в подсчет включаются и закрытые предприятия.

В этом примере запрос возвращает количество всех цветочных магазинов со business_status OPERATIONAL в радиусе 1000 метров от Эмпайр-стейт-билдинг в Нью-Йорке:

SELECT WITH AGGREGATION_THRESHOLD
COUNT(*) AS count
FROM `PROJECT_NAME.places_insights___us.places`
WHERE
ST_DWITHIN(ST_GEOGPOINT(-73.9857, 40.7484), point, 1000)
AND business_status = "OPERATIONAL"
AND 'florist' IN UNNEST(types)

Фильтр по часам работы

В этом примере возвращается количество всех мест в географическом районе, где по пятницам действуют «счастливые часы»:

SELECT WITH AGGREGATION_THRESHOLD COUNT(*) AS count
FROM `PROJECT_NAME.places_insights___us.places`,
UNNEST(regular_opening_hours_happy_hour.friday) AS friday_hours
WHERE '17:00:00' BETWEEN friday_hours.start_time AND friday_hours.end_time
AND ST_DWITHIN(ST_GEOGPOINT(-73.9857, 40.7484), point, 1000);

Фильтрация по региону (компоненты адреса)

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

SELECT WITH AGGREGATION_THRESHOLD
  COUNT(*) AS count
FROM
  `PROJECT_NAME.places_insights___us.places`
WHERE
  '10002' IN UNNEST(postal_code_names)
  --- 'ChIJm5NfgIBZwokR6jLqucW0ipg'  IN UNNEST(postal_code_ids) -- same filter as above using postal code ID

Фильтр по типу зарядки электромобилей

В этом примере приведено количество мест, где установлено не менее 8 зарядных устройств для электромобилей:

SELECT WITH AGGREGATION_THRESHOLD
  COUNT(*) AS count
FROM
  `PROJECT_NAME.places_insights___us.places`
WHERE
  ev_charge_options.connector_count > 8;

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

SELECT WITH AGGREGATION_THRESHOLD
  COUNT(*) AS count
FROM
  `PROJECT_NAME.places_insights___us.places`, UNNEST(ev_charge_options.connector_aggregation) as connectors
WHERE
  connectors.type ='EV_CONNECTOR_TYPE_TESLA'
  AND connectors.max_charge_rate_kw >= 50
  AND connectors.count >= 10

Возвращаемые группы результатов

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

Примечание: В Великобритании выполнение операции GROUP BY для полей postal_code_names запрещено.

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

SELECT WITH AGGREGATION_THRESHOLD
  primary_type,
  COUNT(*) AS count
FROM
  `PROJECT_NAME.places_insights___us.places`
WHERE
  ST_DWITHIN(ST_GEOGPOINT(-73.99992071622756, 40.71818785986936), point, 1000)
GROUP BY primary_type

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

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

WITH my_locations AS (
  SELECT 'Location 1' AS name, ST_GEOGPOINT(-74.00776440888504, 40.70932825380786) AS location
  UNION ALL
  SELECT 'Location 2' AS name, ST_GEOGPOINT(-73.98257192833559, 40.750738934863215) AS location
  UNION ALL
  SELECT 'Location 3' AS name, ST_GEOGPOINT(-73.94701794263223, 40.80792954838445)  AS location
)
SELECT WITH AGGREGATION_THRESHOLD
  l.name,
  COUNT(*) as count
FROM
  `PROJECT_NAME.places_insights___us.places`
JOIN
   my_locations l
ON
  ST_DWITHIN(l.location, p.point, 1000)
WHERE
  primary_type = "restaurant"
  AND business_status = "OPERATIONAL"
GROUP BY
  l.name

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