Avancé : Interroger directement l'ensemble de données

Pour accéder directement aux données Places Insights, vous écrivez des requêtes SQL dans BigQuery qui renvoient des insights agrégés sur les lieux. Les résultats sont renvoyés à partir de l'ensemble de données pour les critères de recherche spécifiés dans la requête.

Comprendre le seuil d'agrégation

Les requêtes SQL standards exécutées directement sur les tables brutes appliquent un seuil d'agrégation strict. Une ligne de sortie n'est générée que si le nombre d'agrégations calculé est supérieur ou égal à 5.

Si les filtres de votre requête génèrent un nombre de 0, 1, 2, 3 ou 4, la ligne de résultat entière est omise de la réponse. Si la logique de votre application nécessite des nombres faibles précis (y compris 0) ou doit récupérer des ID de lieu individuels, vous devez interroger l'ensemble de données à l'aide des fonctions de comptage de lieux à la place.

Principes de base des requêtes

L'image suivante montre le format de base d'une requête :

Chaque partie de la requête est décrite plus en détail ci-dessous.

Conseil : BigQuery fournit également de la documentation et des exemples pour exécuter des requêtes sur des données géospatiales. Pour en savoir plus, consultez la page Présentation des analyses géospatiales.

Exigences concernant les requêtes

Les requêtes SQL effectuées directement sur l'ensemble de données doivent spécifier l'ensemble de données et inclure WITH AGGREGATION_THRESHOLD dans la clause SELECT. Sinon, la requête échouera.

Cet exemple spécifie places_insights___us.places pour interroger l'ensemble de données pour les États-Unis.

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

Spécifier un nom de projet (facultatif)

Vous pouvez éventuellement inclure le nom de votre projet dans la requête. Si vous ne spécifiez pas de nom de projet, votre requête utilise par défaut le projet actif.

Vous pouvez inclure le nom de votre projet si vous avez associé des ensembles de données portant le même nom dans différents projets ou si vous interrogez une table en dehors du projet actif.

Par exemple, [project name].[dataset name].places.

Spécifier une fonction d'agrégation

L'exemple ci-dessous montre les fonctions d'agrégation BigQuery compatibles. Cette requête agrège les notes de tous les lieux situés dans un rayon de 1 000 mètres autour de l'Empire State Building à New York pour générer des statistiques sur les notes :

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"

Filtrer les résultats à l'aide de polygones et de lignes géographiques

Si vous ne spécifiez pas de restriction de lieu, l'agrégation des données est appliquée à l'ensemble de l'ensemble de données. En règle générale, vous spécifiez une restriction de lieu pour rechercher une zone spécifique.

1. Filtrer à l'aide de zones de recherche radiales (ST_DWITHIN)

Cet exemple de requête spécifie une restriction cible centrée sur l'Empire State Building à New York, avec un rayon de 1 000 mètres.

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)

Remarque : Un lieu n'est inclus dans les résultats que si son point central se trouve dans la restriction de lieu. Un lieu, tel qu'un parc, dont le point central se trouve en dehors de la restriction de lieu n'est pas inclus dans les résultats.

2. Filtrer à l'aide de limites de polygones (ST_CONTAINS)

Vous pouvez utiliser un polygone pour spécifier la zone de recherche. Lorsque vous utilisez un polygone, ses points doivent définir une boucle fermée où le premier point du polygone est identique au dernier :

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. Filtrer le long des zones de déplacement (LINESTRING)

Dans l'exemple suivant, vous définissez la zone de recherche à l'aide d'une ligne de points connectés et définissez le rayon de recherche sur 100 mètres autour de la ligne. La ligne est semblable à un itinéraire de voyage calculé par l'API Routes. L'itinéraire peut être destiné à un véhicule, à un vélo ou à un piéton :

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)

Filtrer par champs d'ensemble de données de lieux

Affinez votre recherche en fonction des champs définis par le schéma de l'ensemble de données. Filtrez les résultats en fonction des champs de l'ensemble de données, tels que regular_opening_hours, price_level et rating du client.

Faites référence à tous les champs de l'ensemble de données définis par le schéma de l'ensemble de données pour le pays qui vous intéresse. Le schéma de l'ensemble de données pour chaque pays comporte deux parties :

• Le schéma de base commun aux ensembles de données de tous les pays.
• Un schéma spécifique à un pays qui définit les composants de schéma propres à ce pays.

Par exemple, votre requête peut inclure une clause WHERE qui définit les critères de filtrage de la requête.

Conseil : Incluez le filtre business_status pour vous assurer d'obtenir le nombre d'entreprises OPERATIONAL. Sinon, le nombre inclut les entreprises fermées.

Dans l'exemple suivant, vous renvoyez des données d'agrégation pour les lieux de type tourist_attraction avec un business_status de OPERATIONAL, dont la rating est supérieure ou égale à 4, 0 et pour lesquels allows_dogs est défini sur 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 requête suivante renvoie les résultats pour les lieux qui disposent d'au moins huit bornes de recharge pour véhicules électriques :

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

Filtrer par type principal de lieu et type de lieu

Chaque lieu de l'ensemble de données peut avoir :

• Un seul type principal associé aux types définis par les types de lieux. Par exemple, le type principal peut être mexican_restaurant ou steak_house. Utilisez primary_type dans une requête pour filtrer les résultats sur le type principal d'un lieu.
• Plusieurs valeurs de type associées aux types définis par les types de lieux. Par exemple, un restaurant peut avoir les types suivants : seafood_restaurant, restaurant, food, point_of_interest, establishment. Utilisez types dans une requête pour filtrer les résultats sur la liste des types associés au lieu.

La requête suivante renvoie les résultats pour tous les lieux dont le type principal est skin_care_clinic et qui fonctionnent également comme un 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

Filtrer par ID de lieu

L'exemple ci-dessous calcule la note moyenne de cinq lieux. Les lieux sont identifiés par leur 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;

Exclure des ID de lieu spécifiques des résultats de requête

Vous pouvez également exclure un tableau d'ID de lieu d'une requête.

Vous pouvez trouver les ID de lieu que vous recherchez à l'aide de l'outil de recherche d'ID de lieu ou par programmation à l'aide de l'API Places pour effectuer une requête Text Search (nouvelle version).

Dans l'exemple suivant, la requête recherche le nombre de cafés dans le code postal 2000 de Sydney, en Australie, qui n'apparaissent pas dans le tableau excluded_cafes. Une telle requête peut être utile à un propriétaire d'entreprise qui souhaite exclure ses propres entreprises d'un décompte.

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;

Filtrer sur des valeurs de données prédéfinies

De nombreux champs d'ensemble de données ont des valeurs prédéfinies. Par exemple :

• Le champ price_level accepte les valeurs prédéfinies suivantes :
  • PRICE_LEVEL_FREE
  • PRICE_LEVEL_INEXPENSIVE
  • PRICE_LEVEL_MODERATE
  • PRICE_LEVEL_EXPENSIVE
  • PRICE_LEVEL_VERY_EXPENSIVE
• Le champ business_status accepte les valeurs prédéfinies suivantes :
  • OPERATIONAL
  • CLOSED_TEMPORARILY
  • CLOSED_PERMANENTLY
  • FUTURE_OPENING

Remarque : En règle générale, vous incluez le filtre business_status pour vous assurer d'obtenir le nombre d'entreprises OPERATIONAL. Sinon, les nombres incluent les entreprises fermées.

Dans cet exemple, la requête renvoie le nombre de fleuristes dont le business_status est OPERATIONAL dans un rayon de 1 000 mètres autour de l'Empire State Building à New 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)

Filtrer par heures d'ouverture

Dans cet exemple, renvoyez le nombre de lieux dans une zone géographique où des happy hours sont proposées le vendredi :

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

Filtrer par région (composants d'adresse)

Notre ensemble de données de lieux contient également un ensemble de composants d'adresse utiles pour filtrer les résultats en fonction des limites politiques. Chaque composant d'adresse est identifié par son nom de code texte (10002 pour le code postal à New York) ou par son ID de lieu (ChIJm5NfgIBZwokR6jLqucW0ipg) pour l'ID de code postal équivalent.

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

Filtrer par configurations de recharge pour véhicules électriques

Cet exemple fournit le nombre de lieux disposant d'au moins huit bornes de recharge pour véhicules électriques :

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

Cet exemple compte le nombre de lieux disposant d'au moins 10 bornes de recharge Tesla compatibles avec la recharge rapide :

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

Filtrer par date d'instantané de données

L'ensemble de données de lieux contient des données de nombre de lieux pour chaque mois depuis janvier 2024. Pour interroger l'instantané des données de lieux pour un mois donné, filtrez à l'aide du champ snapshot_date.

Remarque : Le champ snapshot_date est au format AAAA-MM-01 (par exemple, « 2024-01-01 »). Bien qu'une valeur de jour (01) soit obligatoire dans la chaîne de date, chaque instantané représente des données agrégées pour l'ensemble du mois plutôt que pour un jour donné.

Par exemple, la requête suivante renvoie les résultats regroupés par type principal pour chaque lieu de la zone de recherche, au mois de janvier 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;

Renvoyer des lignes de résultat groupées

Les requêtes présentées jusqu'à présent renvoient une seule ligne dans le résultat contenant le nombre d'agrégations pour la requête. Vous pouvez également utiliser l'opérateur GROUP BY pour renvoyer plusieurs lignes dans la réponse en fonction des critères de regroupement.

Remarque : L'exécution d'une opération GROUP BY n'est pas autorisée sur postal_code_names au Royaume-Uni.

Par exemple, la requête suivante renvoie les résultats regroupés par type principal de chaque lieu de la zone de recherche :

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

Cette image montre un exemple de résultat pour cette requête :

Dans cet exemple, vous définissez un tableau de lieux. Pour chaque lieu, vous calculez ensuite le nombre de restaurants à proximité, c'est-à-dire ceux situés à moins de 1 000 mètres :

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

Cette image montre un exemple de résultat pour cette requête :