Écrire une requête Places Insights

Pour accéder aux données Places Insights, vous devez écrire 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.

Remarque : Les nombres d'agrégation ne sont renvoyés que si le nombre est supérieur ou égal à 5. Par exemple, si vos critères de recherche renvoient un nombre de 1, 2, 3 ou 4, l'agrégation des résultats est omise. Les fonctions de nombre de lieux peuvent renvoyer n'importe quel nombre, y compris 0, mais imposent une zone de recherche minimale de 40 mètres sur 40 mètres (1 600 m²).

Bon à savoir : 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 Présentation des analyses géospatiales.

Exigences concernant les requêtes

L'instruction SELECT de la requête doit inclure WITH AGGREGATION_THRESHOLD et spécifier l'ensemble de données. Exemple :

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

Dans cet exemple, vous utilisez FROM pour spécifier l'ensemble de données places_insights___us___sample.places_sample pour les États-Unis.

Spécifier une restriction en fonction de l'emplacement

Notez que la requête précédente ne spécifie aucune restriction géographique, ce qui signifie que l'agrégation des données est appliquée à l'ensemble des données. En général, vous spécifiez une restriction géographique comme indiqué ci-dessous :

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)

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

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

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

Dans l'exemple suivant, vous définissez la zone de recherche à l'aide d'une ligne de points connectés. Cette 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 `places_insights___us___sample.places_sample`
WHERE
  ST_DWITHIN(route, point, 100)

Dans cet exemple, vous définissez le rayon de recherche sur 100 mètres autour de la ligne.

Filtrer par champs de l'ensemble de données sur les 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 du jeu de données, tels que le lieu regular_opening_hours, price_level et le client rating.

Référencez 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 se compose de deux parties :

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

Par exemple, votre requête peut inclure une clause WHERE qui définit les critères de filtrage pour la requête. Dans l'exemple suivant, vous renvoyez des données d'agrégation pour les lieux de type restaurant avec un business_status de OPERATIONAL, qui ont un rating supérieur ou égal à 4, 0 et dont le allows_dogs est défini sur 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

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
  `places_insights___us___sample.places_sample`
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 lui est associé parmi les types définis par le tableau A. Par exemple, le type principal peut être mexican_restaurant ou steak_house. Utilisez primary_type dans une requête pour filtrer les résultats en fonction du type principal d'un lieu.

• Plusieurs valeurs de type lui sont associées à partir des types définis par le tableau A. 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 bar, mais qui fonctionnent également comme restaurant :

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

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

De nombreux champs de l'ensemble de données ont des valeurs prédéfinies. 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

Remarque : En général, vous incluez le filtre business_status pour vous assurer d'obtenir le nombre d'entreprises OPERATIONAL. Sinon, les chiffres incluent les établissements fermés.

Dans cet exemple, la requête renvoie le nombre de restaurants 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 `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)

Filtrer par horaires d'ouverture

Dans cet exemple, renvoyez le nombre de lieux dans une zone géographique proposant des happy hours le vendredi :

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

Utiliser 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
  `places_insights___us___sample.places_sample`
WHERE
  ST_DWITHIN(ST_GEOGPOINT(-73.9857, 40.7484), point, 1000)
  AND business_status = "OPERATIONAL"

Renvoyer des groupes de résultats

Les requêtes affichées jusqu'à présent renvoient une seule ligne dans le résultat, qui contient 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'opération GROUP BY n'est pas autorisée sur postal_code au Royaume-Uni ni sur postal_code_suffix aux États-Unis.

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

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

L'image suivante montre un exemple de résultat pour cette requête :

Dans cet exemple, vous définissez une table d'emplacements. Pour chaque emplacement, 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
  `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

L'image suivante montre un exemple de résultat pour cette requête :