Avanzado: Consulta el conjunto de datos directamente

Para acceder directamente a los datos de Places Insights, escribe consultas en SQL en BigQuery que devuelvan estadísticas agregadas sobre los lugares. Los resultados se devuelven desde el conjunto de datos para los criterios de búsqueda especificados en la consulta.

Comprende el umbral de agregación

Cuando se consultan las tablas directamente, se aplica un umbral de agregación de 1. Esto significa que se genera una fila de salida solo si agrega al menos 1 lugar. Si los filtros de consulta producen un recuento de 0, se omite toda la fila de resultados de la respuesta. Para mostrar 0 en las filas omitidas, usa las funciones de recuento de Places en su lugar.

Conceptos básicos de las consultas

En la siguiente imagen, se muestra el formato básico de una consulta:

Es el formato básico de una consulta.

Cada parte de la consulta se describe con más detalle a continuación.

Requisitos de las consultas

Las consultas en SQL que se realizan directamente en el conjunto de datos deben especificarlo y, además, incluir WITH AGGREGATION_THRESHOLD en la cláusula SELECT. Sin esto, la consulta fallará.

En este ejemplo, se especifica places_insights___us.places para consultar el conjunto de datos de Estados Unidos.

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

Especifica un nombre para el proyecto (opcional).

De manera opcional, puedes incluir el nombre de tu proyecto en la consulta. Si no especificas un nombre de proyecto, la consulta se establece de forma predeterminada en el proyecto activo.

Te recomendamos incluir el nombre del proyecto si vinculaste conjuntos de datos con el mismo nombre en diferentes proyectos o si consultas una tabla fuera del proyecto activo.

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

Especifica una función de agregación

En el siguiente ejemplo, se muestran las funciones de agregación de BigQuery compatibles. Esta consulta agrega las calificaciones de todos los lugares situados en un radio de 1,000 metros del edificio Empire State en la ciudad de Nueva York para producir estadísticas de calificaciones:

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"

Filtra los resultados con polígonos y líneas geográficos

Si no especificas una restricción de ubicación, la agregación de datos se aplica a todo el conjunto de datos. Por lo general, especificas una restricción de ubicación para buscar en un área específica.

1. Filtra con búferes de búsqueda radial (ST_DWITHIN)

En esta consulta de ejemplo, se especifica una restricción de destino centrada en el edificio Empire State de la ciudad de Nueva York, con un radio 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)

2. Filtra con límites de polígonos (ST_CONTAINS)

Puedes usar un polígono para especificar el área de búsqueda. Cuando se usa un polígono, los puntos de este deben definir un bucle cerrado en el que el primer punto del polígono sea el mismo que el ú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. Filtra a lo largo de los búferes de viaje de la ruta (LINESTRING)

En el siguiente ejemplo, se define el área de búsqueda con una línea de puntos conectados y se establece el radio de búsqueda en 100 metros alrededor de la línea. La línea es similar a una ruta de viaje calculada por la API de Routes. La ruta puede ser para un vehículo, una bicicleta o un peatón:

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)

Cómo filtrar por campos de conjuntos de datos de lugares

Define mejor tu búsqueda según los campos que define el esquema del conjunto de datos. Filtrar los resultados según los campos del conjunto de datos, como el lugar regular_opening_hours, price_level y el cliente rating

Haz referencia a cualquier campo del conjunto de datos definido por el esquema del conjunto de datos para tu país de interés. El esquema del conjunto de datos para cada país consta de dos partes:

Por ejemplo, tu consulta puede incluir una cláusula WHERE que defina los criterios de filtrado para la consulta.

En el siguiente ejemplo, se devuelven datos agregados para lugares del tipo tourist_attraction con un business_status de OPERATIONAL, que tienen una rating mayor o igual que 4.0 y con allows_dogs establecido en 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

La siguiente consulta devuelve resultados para lugares que tienen al menos ocho estaciones de carga de VE:

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

Filtra por tipo principal y tipo de lugar

Cada lugar del conjunto de datos puede tener lo siguiente:

  • Un solo tipo principal asociado con él de los tipos definidos por Tipos de lugares. Por ejemplo, el tipo principal puede ser mexican_restaurant o steak_house. Usa primary_type en una consulta para filtrar los resultados según el tipo principal de un lugar.
  • Varios valores de tipo asociados con él, de los tipos definidos por Tipos de lugar. Por ejemplo, un restaurante podría tener los siguientes tipos: seafood_restaurant, restaurant, food, point_of_interest y establishment. Usa types en una consulta para filtrar los resultados en la lista de tipos asociados con el lugar.

La siguiente consulta devuelve resultados para todos los lugares con un tipo principal de skin_care_clinic que también funcionan 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

Cómo filtrar por ID de lugar

En el siguiente ejemplo, se calcula la calificación promedio de 5 lugares. Los lugares se identifican por su 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;

Excluye IDs de lugar específicos de los resultados de la consulta

También puedes excluir un array de IDs de lugar de una consulta.

Puedes encontrar los IDs de lugar que buscas con el buscador de IDs de lugar o de manera programática con la API de Places para realizar una solicitud de Text Search (nueva).

En el siguiente ejemplo, la consulta encuentra el recuento de cafeterías en el código postal 2000 de Sídney, Australia, que no aparecen en el array excluded_cafes. Esta consulta puede ser útil para el propietario de una empresa que quiera excluir sus propias empresas de un recuento.

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;

Filtra los valores de datos predefinidos

Muchos campos de conjuntos de datos tienen valores predefinidos. Por ejemplo:

  • El campo price_level admite los siguientes valores predefinidos:
    • PRICE_LEVEL_FREE
    • PRICE_LEVEL_INEXPENSIVE
    • PRICE_LEVEL_MODERATE
    • PRICE_LEVEL_EXPENSIVE
    • PRICE_LEVEL_VERY_EXPENSIVE
  • El campo business_status admite los siguientes valores predefinidos:
    • OPERATIONAL
    • CLOSED_TEMPORARILY
    • CLOSED_PERMANENTLY
    • FUTURE_OPENING

En este ejemplo, la consulta devuelve el recuento de todas las florerías con un business_status de OPERATIONAL dentro de un radio de 1,000 metros del edificio Empire State en la ciudad de Nueva 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 horario de atención

En este ejemplo, se devuelve el recuento de todos los lugares de un área geográfica que tienen happy hours los viernes:

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ón (componentes de la dirección)

Nuestro conjunto de datos de lugares también contiene un conjunto de componentes de dirección que son útiles para filtrar los resultados según los límites políticos. Cada componente de dirección se identifica por su nombre interno de código de texto (10002 para el código postal de NYC) o ID de lugar (ChIJm5NfgIBZwokR6jLqucW0ipg) para el 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

Cómo filtrar por parámetros de configuración de carga de VE

En este ejemplo, se proporciona un recuento de la cantidad de lugares con al menos 8 cargadores de VE:

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

En este ejemplo, se cuenta la cantidad de lugares que tienen al menos 10 cargadores de Tesla que admiten la carga rápida:

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 fecha de la instantánea de datos

El conjunto de datos de lugares contiene datos de recuento de lugares para cada mes desde enero de 2024. Para consultar la instantánea de los datos de lugares de un mes determinado, filtra con el campo snapshot_date.

Por ejemplo, la siguiente consulta devuelve resultados agrupados por el tipo principal de cada lugar en el área de búsqueda, en el mes de enero 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;

Devuelve filas de resultados agrupadas

Las consultas que se mostraron hasta ahora devuelven una sola fila en el resultado que contiene el recuento de agregaciones de la consulta. También puedes usar el operador GROUP BY para devolver varias filas en la respuesta según los criterios de agrupación.

Por ejemplo, la siguiente consulta devuelve resultados agrupados por el tipo principal de cada lugar en el área de búsqueda:

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

En esta imagen, se muestra un resultado de ejemplo para esta consulta:

Resultados de la consulta para agrupar los resultados por tipo principal.

En este ejemplo, definirás una tabla de ubicaciones. Luego, para cada ubicación, calculas la cantidad de restaurantes cercanos, es decir, aquellos que se encuentran a menos 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

En esta imagen, se muestra un resultado de ejemplo para esta consulta:

Resultados de la consulta para agrupar los resultados por ubicación.