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. Se devuelven los resultados del conjunto de datos para los criterios de búsqueda especificados en la consulta.

Información sobre el umbral de agregación

Las consultas en SQL estándar que se ejecutan directamente en las tablas sin procesar aplican un umbral de agregación estricto. Se genera una fila de salida solo si el recuento de agregación calculado es 5 o más.

Si los filtros de tu consulta producen un recuento de 0, 1, 2, 3 o 4, se omite toda la fila de resultados de la respuesta. Si la lógica de tu aplicación requiere recuentos bajos precisos (incluido el 0) o necesita recuperar IDs de lugar individuales, debes consultar el conjunto de datos con las funciones de recuento de lugares.

Conceptos básicos de las consultas

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

A continuación, se describe cada parte de la consulta con más detalle.

Sugerencia: BigQuery también proporciona documentación y ejemplos para ejecutar consultas en datos geoespaciales. Para obtener más información, consulta Introducción a las estadísticas geoespaciales.

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 tienes conjuntos de datos vinculados 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áficas

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

Nota: Un lugar solo se incluye en los resultados si su punto central se encuentra dentro de la restricción de ubicación. No se incluye en los resultados un lugar, como un parque, cuyo punto central se encuentra fuera de la restricción de ubicación.

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)

Filtra por campos del conjunto de datos de lugares

Define mejor tu búsqueda según los campos que define el esquema del conjunto de datos. Filtra 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:

• El esquema principal que es común a los conjuntos de datos de todos los países.
• Un esquema específico del país que define los componentes del esquema específicos de ese país.

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

Sugerencia: Incluye el filtro business_status para asegurarte de obtener el recuento de OPERATIONAL empresas. De lo contrario, el recuento incluye las empresas cerradas.

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 a él de los tipos definidos por Tipos de lugar. Por ejemplo, un restaurante puede tener los siguientes tipos: seafood_restaurant, restaurant, food, point_of_interest y establishment. Usa types en una consulta para filtrar los resultados de 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

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 forma programática con la API de Places para realizar una solicitud de Text Search (nueva).

En el siguiente ejemplo, la consulta encuentra la cantidad 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 por 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

Nota: Por lo general, debes incluir el filtro business_status para asegurarte de obtener el recuento de empresas de OPERATIONAL. De lo contrario, los recuentos incluyen las empresas cerradas.

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

Nota: El campo snapshot_date tiene el formato AAAA-MM-01 (por ejemplo, '2024-01-01'). Aunque es obligatorio incluir un valor de día (01) en la cadena de fecha, cada instantánea representa los datos agregados de todo el mes, en lugar de un día individual.

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.

Nota: No se permite realizar una operación GROUP BY en postal_code_names en GB.

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 ejemplo del resultado de esta consulta:

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 ejemplo del resultado de esta consulta: