Consultar o conjunto de dados diretamente

Para acessar os dados do Insights de Lugares diretamente, você escreve consultas SQL no BigQuery que retornam insights agregados sobre lugares. Os resultados são retornados do conjunto de dados para os critérios de pesquisa especificados na consulta.

Observação: as contagens de agregação só são retornadas se a contagem for 5 ou mais. Por exemplo, se os critérios de pesquisa resultarem em uma contagem de 0, 1, 2, 3 ou 4, a linha de resultado será omitida do resultado.

Se você precisar receber contagens menores que 5, use as funções de contagem de lugares. Essas funções podem retornar qualquer contagem, incluindo 0, mas aplicam uma área de pesquisa mínima de 40,0 metros por 40,0 metros (1.600 m²). Saiba mais sobre quando consultar diretamente e quando usar funções.

Noções básicas sobre consultas

A imagem a seguir mostra o formato básico de uma consulta:

Cada parte da consulta é descrita com mais detalhes abaixo.

**Sucesso**: o BigQuery também oferece documentação e exemplos para executar consultas em dados geoespaciais. Para mais informações, consulte Introdução à análise geoespacial geoespacial.

Requisitos de consulta

As consultas SQL feitas diretamente no conjunto de dados precisam especificar o conjunto de dados e incluir WITH AGGREGATION_THRESHOLD na cláusula SELECT. Sem isso, a consulta falhará.

Este exemplo especifica places_insights___us.places para consultar o conjunto de dados dos Estados Unidos.

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

Especificar um nome de projeto (opcional)

Você pode incluir o nome do projeto na consulta. Se você não especificar um nome de projeto, a consulta será definida como o projeto ativo.

Talvez você queira incluir o nome do projeto se tiver conjuntos de dados vinculados com o mesmo nome em projetos diferentes ou se estiver consultando uma tabela fora do projeto ativo.

Por exemplo, [project name].[dataset name].places.

Especificar uma função de agregação

O exemplo abaixo mostra as funções de agregação do BigQuery com suporte. Essa consulta agrega as classificações de todos os lugares situados em um raio de 1.000 metros do Empire State Building, na cidade de Nova York, para produzir estatísticas de classificação:

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"

Especificar uma restrição de local

Se você não especificar uma restrição de local, a agregação de dados será aplicada ao conjunto de dados inteiro. Normalmente, você especifica uma restrição de local para pesquisar uma área específica. Esta consulta de exemplo especifica uma restrição de destino centrada no Empire State Building, na cidade de Nova York, com um raio de 1.000 metros.

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)

Observação: um lugar só será incluído nos resultados se o ponto central dele estiver dentro da restrição de local. Um lugar, como um parque, cujo ponto central esteja fora da restrição de local, não será incluído nos resultados.

Você pode usar um polígono para especificar a área de pesquisa. Ao usar um polígono, os pontos dele precisam definir um loop fechado em que o primeiro ponto do polígono seja o mesmo que o último:

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)

No exemplo a seguir, você define a área de pesquisa usando uma linha de pontos conectados e define o raio de pesquisa como 100 metros ao redor da linha. A linha é semelhante a uma rota de viagem calculada pela API Routes. A rota pode ser para um veículo, uma bicicleta ou um pedestre:

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)

Filtrar por campos de conjunto de dados de lugar

Refine sua pesquisa com base nos campos definidos pelo esquema do conjunto de dados. Filtre os resultados com base em campos de conjunto de dados, como regular_opening_hours, price_level e rating do cliente.

Faça referência a todos os campos no conjunto de dados definidos pelo esquema do conjunto de dados para o país de interesse. O esquema do conjunto de dados para cada país é composto por duas partes:

• O esquema principal que é comum aos conjuntos de dados de todos os países.
• Um esquema específico do país que define componentes de esquema específicos desse país.

Por exemplo, sua consulta pode incluir uma cláusula WHERE que define critérios de filtragem para a consulta.

Dica: inclua o filtro business_status para garantir que você receba a contagem de OPERATIONAL empresas. Caso contrário, a contagem inclui empresas fechadas.

No exemplo a seguir, você retorna dados de agregação para lugares do tipo tourist_attraction com um business_status de OPERATIONAL, que têm uma rating maior ou igual a 4, 0 e com allows_dogs definido como 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

A próxima consulta retorna resultados para lugares que têm pelo menos oito estações de carregamento de veículos elétricos:

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

Filtrar por tipo principal e tipo de lugar

Cada lugar no conjunto de dados pode ter:

• Um único tipo principal associado a ele dos tipos definidos por Tipos de lugar. Por exemplo, o tipo principal pode ser mexican_restaurant ou steak_house. Use primary_type em uma consulta para filtrar os resultados no tipo principal de um lugar.

• Vários valores de tipo associados a ele dos tipos definidos por Tipos de lugar. Por exemplo um restaurante pode ter os seguintes tipos: seafood_restaurant, restaurant, food, point_of_interest, establishment. Use types em uma consulta para filtrar os resultados na lista de tipos associados ao lugar.

A consulta a seguir retorna resultados para todos os lugares com um tipo principal de skin_care_clinic que também funcionam como um 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

Filtrar por ID do lugar

O exemplo abaixo calcula a classificação média de cinco lugares. Os lugares são identificados pelo 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;

Filtrar determinados IDs de lugar

Também é possível excluir uma matriz de IDs de lugar de uma consulta.

Você pode encontrar os IDs de lugar que está procurando usando o Localizador de IDs de lugar ou programaticamente usando a API Places para realizar uma solicitação de Pesquisa de texto (nova).

No exemplo abaixo, a consulta encontra a contagem de cafés no código postal 2000 de Sydney, Austrália, que não aparecem na matriz excluded_cafes. Essa consulta pode ser útil para um proprietário de empresa que gostaria de excluir as próprias empresas de uma contagem.

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;

Filtrar por valores de dados predefinidos

Muitos campos de conjunto de dados têm valores predefinidos. Por exemplo:

• O campo price_level oferece suporte aos seguintes valores predefinidos:

  • PRICE_LEVEL_FREE
  • PRICE_LEVEL_INEXPENSIVE
  • PRICE_LEVEL_MODERATE
  • PRICE_LEVEL_EXPENSIVE
  • PRICE_LEVEL_VERY_EXPENSIVE

• O campo business_status oferece suporte aos seguintes valores predefinidos:

  • OPERATIONAL
  • CLOSED_TEMPORARILY
  • CLOSED_PERMANENTLY
  • FUTURE_OPENING

Observação: normalmente, você inclui o filtro business_status para garantir que você receba a contagem de empresas OPERATIONAL. Caso contrário, a contagem inclui empresas fechadas.

Neste exemplo, a consulta retorna a contagem de todas as floriculturas com um business_status de OPERATIONAL em um raio de 1.000 metros do Empire State Building, na cidade de Nova York:

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)

Filtrar por horário de funcionamento

Neste exemplo, retorne a contagem de todos os lugares em uma área geográfica com happy hours de sexta-feira:

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

Filtrar por região (componentes de endereço)

Nosso conjunto de dados de lugares também contém um conjunto de componentes de endereço que são úteis para filtrar resultados com base em limites políticos. Cada componente de endereço é identificado pelo codinome de texto (10002 para código postal em Nova York) ou pelo ID de lugar (ChIJm5NfgIBZwokR6jLqucW0ipg) para o ID do código postal equivalente.

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

Filtrar por carregamento de veículos elétricos

Este exemplo fornece uma contagem do número de lugares com pelo menos oito carregadores de veículos elétricos:

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

Este exemplo conta o número de lugares que têm pelo menos 10 carregadores Tesla que oferecem suporte ao carregamento rápido:

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

Retornar grupos de resultados

As consultas mostradas até agora retornam uma única linha no resultado que contém a contagem de agregação da consulta. Também é possível usar o operador GROUP BY para retornar várias linhas na resposta com base nos critérios de agrupamento.

Observação: Não é permitido realizar uma operação GROUP BY em postal_code_names na Grã-Bretanha.

Por exemplo, a consulta a seguir retorna resultados agrupados pelo tipo principal de cada lugar na área de pesquisa:

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

Esta imagem mostra um exemplo de saída para essa consulta:

Neste exemplo, você define uma tabela de locais. Para cada local, você calcula o número de restaurantes próximos, ou seja, aqueles que estão a 1.000 metros de distância:

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

Esta imagem mostra um exemplo de saída para essa consulta: