Avançado: consultar o conjunto de dados diretamente

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

Entender o limite de agregação

As consultas SQL padrão executadas diretamente nas tabelas brutas aplicam um limite de agregação estrito. Uma linha de saída é gerada somente se a contagem de agregação calculada for 5 ou mais.

Se os filtros de consulta resultarem em uma contagem de 0, 1, 2, 3 ou 4, toda a linha de resultado será omitida da resposta. Se a lógica do aplicativo exigir contagens baixas e precisas (incluindo 0) ou precisar recuperar IDs de lugar individuais, consulte o conjunto de dados usando as funções de contagem de lugares.

Noções básicas sobre consultas

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

Cada parte da consulta é descrita em mais detalhes abaixo.

Dica: o BigQuery também fornece documentação e exemplos para executar consultas em dados geoespaciais. Para mais informações, consulte Introdução à análise 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 vai falhar.

Neste exemplo, especificamos 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`

Especifique um nome para o projeto (opcional)

Se quiser, inclua o nome do projeto na consulta. Se você não especificar um nome de projeto, a consulta será padronizada para o projeto ativo.

É recomendável incluir o nome do projeto se você 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 compatíveis com o BigQuery. Essa consulta agrega as avaliações de todos os lugares situados em um raio de 1.000 metros do Empire State Building, em Nova York, para produzir estatísticas de avaliaçã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"

Filtrar resultados usando polígonos e linhas geográficas

Se você não especificar uma restrição de local, a agregação de dados será aplicada a todo o conjunto de dados. Normalmente, você especifica uma restrição de local para pesquisar uma área específica.

1. Filtrar usando buffers de pesquisa radial (ST_DWITHIN)

Este exemplo de consulta especifica uma restrição de destino centralizada no Empire State Building, em 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 está fora da restrição de local não é incluído nos resultados.

2. Filtrar usando limites de polígono (ST_CONTAINS)

É possível 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 é igual ao ú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)

3. Filtrar ao longo dos buffers de viagem da rota (LINESTRING)

No próximo exemplo, 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 um trajeto de viagem calculado pela API Routes. O trajeto 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 do conjunto de dados de lugares

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

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 de cada país é composto por duas partes:

• O esquema principal comum aos conjuntos de dados de todos os países.
• Um esquema específico do país que define componentes de esquema específicos para esse 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 empresas OPERATIONAL. Caso contrário, a contagem vai incluir as empresas fechadas.

No exemplo a seguir, você retorna dados agregados para lugares do tipo tourist_attraction com um business_status de OPERATIONAL, que têm um 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 eletropostos:

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, definido pelos 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 pelo tipo principal de um lugar.
• Vários valores de tipo associados a ele, 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 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 de 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;

Excluir IDs de lugar específicos dos resultados da consulta

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, de forma programática, usando a API Places para realizar uma solicitação de Text Search (novo).

No exemplo a seguir, a consulta encontra o número 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 conjuntos de dados têm valores predefinidos. Exemplo:

• O campo price_level aceita os seguintes valores predefinidos:
  • PRICE_LEVEL_FREE
  • PRICE_LEVEL_INEXPENSIVE
  • PRICE_LEVEL_MODERATE
  • PRICE_LEVEL_EXPENSIVE
  • PRICE_LEVEL_VERY_EXPENSIVE
• O campo business_status aceita os seguintes valores predefinidos:
  • OPERATIONAL
  • CLOSED_TEMPORARILY
  • CLOSED_PERMANENTLY
  • FUTURE_OPENING

Observação: geralmente, você inclui o filtro business_status para garantir que receba a contagem de empresas OPERATIONAL. Caso contrário, as contagens vão incluir 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, em 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 na 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 o código postal em NYC) ou pelo ID de lugar (ChIJm5NfgIBZwokR6jLqucW0ipg) para o ID de 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 configurações de carregamento de VE

Este exemplo mostra o número de lugares com pelo menos 8 carregadores de VE:

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

Filtrar por data do snapshot de dados

O conjunto de dados de lugares contém dados de contagem de lugares para cada mês desde janeiro de 2024. Para consultar o snapshot de dados de lugares de um determinado mês, filtre usando o campo snapshot_date.

Observação: o campo snapshot_date é formatado como AAAA-MM-01 (por exemplo, "2024-01-01"). Embora um valor de dia (01) seja obrigatório na string de data, cada snapshot representa dados agregados para o mês inteiro, e não para um dia individual.

Por exemplo, a consulta a seguir retorna resultados agrupados pelo tipo principal de cada lugar na área de pesquisa, no mês de janeiro de 2024:

SELECT WITH AGGREGATION_THRESHOLD
  primary_type,
  COUNT(*) AS count
FROM
  `PROJECT_NAME.places_insights___us.places_historical`
WHERE
  snapshot_date = '2024-01-01'
  AND ST_DWITHIN(ST_GEOGPOINT(-73.9857, 40.7484), point, 1000)
GROUP BY
  primary_type;

Retornar linhas de resultado agrupadas

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 executar uma operação GROUP BY em postal_code_names em GB.

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 em um raio de 1.000 metros:

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` AS p
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: