Criar uma consulta de Insights dos lugares

Para acessar os dados do Places Insights, 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.

Observação: as contagens de agregação só são retornadas se forem iguais ou maiores que 5. Por exemplo, se os critérios de pesquisa resultarem em uma contagem de 1, 2, 3 ou 4, a agregação de resultados será omitida. As funções de contagem de lugares podem retornar qualquer contagem, incluindo 0, mas exigem uma área de pesquisa mínima de 40,0 metros por 40,0 metros (1.600 m²).

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.

Requisitos de consulta

A instrução SELECT na consulta precisa incluir WITH AGGREGATION_THRESHOLD e especificar o conjunto de dados. Exemplo:

SELECT WITH AGGREGATION_THRESHOLD
COUNT(*) AS count
FROM `places_insights___us___sample.places_sample`

Neste exemplo, você usa FROM para especificar o conjunto de dados places_insights___us___sample.places_sample dos Estados Unidos.

Especificar uma restrição de local

A consulta anterior não especifica nenhuma restrição de local, o que significa que a agregação de dados é aplicada ao conjunto de dados inteiro. Normalmente, você especifica uma restrição de local como mostrado abaixo:

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

Neste exemplo, a consulta especifica uma restrição de destino centrada no Empire State Building, em Nova York, com um raio de 1.000 metros.

Observação: um lugar só é 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.

É 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 e o último ponto são iguais:

SELECT WITH AGGREGATION_THRESHOLD
COUNT(*) AS count
FROM `places_insights___us___sample.places_sample`
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. A linha é semelhante a um trajeto calculado pela API Routes. O trajeto pode ser para um veículo, uma bicicleta ou para 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 `places_insights___us___sample.places_sample`
WHERE
  ST_DWITHIN(route, point, 100)

Neste exemplo, você define o raio de pesquisa como 100 metros ao redor da linha.

Filtrar por campos do 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 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 seu 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 de um país que define componentes específicos desse país.

Por exemplo, sua consulta pode incluir uma cláusula WHERE que define critérios de filtragem para a consulta. No exemplo a seguir, você retorna dados de agregação para lugares do tipo restaurant 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 `places_insights___us___sample.places_sample`
WHERE
ST_DWITHIN(ST_GEOGPOINT(-73.9857, 40.7484), point, 1000)
AND 'restaurant' 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
  `places_insights___us___sample.places_sample`
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 pela Tabela A. 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 pela Tabela A. 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 bar, mas que também funcionam como um restaurant:

SELECT WITH AGGREGATION_THRESHOLD
  COUNT(*) AS count
FROM
  `places_insights___us___sample.places_sample`
WHERE
  'restaurant' IN UNNEST(types)
  AND 'bar' = primary_type

Filtrar por valores de dados predefinidos

Muitos campos de conjuntos de dados têm valores predefinidos. Por 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

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

Neste exemplo, a consulta retorna a contagem de todos os restaurantes 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 `places_insights___us___sample.places_sample`
WHERE
ST_DWITHIN(ST_GEOGPOINT(-73.9857, 40.7484), point, 1000)
AND business_status = "OPERATIONAL"
AND 'restaurant' 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 `places_insights___us___sample.places_sample`,
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);

Usar uma função de agregação

O exemplo abaixo mostra as funções de agregação do BigQuery compatíveis. Esta consulta agrega as classificações de todos os lugares situados em um raio de 1.000 metros do Empire State Building, em Nova York, para gerar 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
  `places_insights___us___sample.places_sample`
WHERE
  ST_DWITHIN(ST_GEOGPOINT(-73.9857, 40.7484), point, 1000)
  AND business_status = "OPERATIONAL"

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 no Reino Unido ou postal_code_suffix nos EUA.

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
  `places_insights___us___sample.places_sample`
WHERE
  ST_DWITHIN(ST_GEOGPOINT(-73.99992071622756, 40.71818785986936), point, 1000)
GROUP BY primary_type

A imagem a seguir mostra um exemplo de saída para essa consulta:

Neste exemplo, você define uma tabela de locais. Para cada local, calcule 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
  `places_insights___us___sample.places_sample` 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

A imagem a seguir mostra um exemplo de saída para essa consulta: