Scrivere una query di Dati dei luoghi

Per accedere ai dati di Places Insights, scrivi query SQL in BigQuery che restituiscono approfondimenti aggregati sui luoghi. I risultati vengono restituiti dal set di dati per i criteri di ricerca specificati nella query.

Nota: i conteggi di aggregazione vengono restituiti solo se il conteggio è pari o superiore a 5. Ad esempio, se i criteri di ricerca restituiscono un conteggio di 1, 2, 3 o 4, l'aggregazione dei risultati viene omessa dal risultato. Le funzioni di conteggio dei luoghi possono restituire qualsiasi conteggio, incluso 0, ma impongono un'area di ricerca minima di 40 metri per 40 metri (1600 m²).

Operazione riuscita: BigQuery fornisce anche documentazione ed esempi per l'esecuzione di query sui dati geospaziali. Per ulteriori informazioni, consulta Introduzione all'analisi geospaziale.

Requisiti delle query

L'istruzione SELECT nella query deve includere WITH AGGREGATION_THRESHOLD e specificare il set di dati. Ad esempio:

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

In questo esempio, utilizzi FROM per specificare il set di dati places_insights___us___sample.places_sample per gli Stati Uniti.

Specificare una limitazione della località

Tieni presente che la query precedente non specifica alcuna limitazione della località, il che significa che l'aggregazione dei dati viene applicata all'intero set di dati. In genere, specifichi una limitazione della località come mostrato di seguito:

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)

In questo esempio, la query specifica una limitazione del target centrata sull'Empire State Building di New York, con un raggio di 1000 metri.

Nota :un luogo viene incluso nei risultati solo se il suo punto centrale rientra nella limitazione della località. Un luogo, ad esempio un parco, il cui punto centrale si trova al di fuori della limitazione della posizione non è incluso nei risultati.

Puoi utilizzare un poligono per specificare l'area di ricerca. Quando utilizzi un poligono, i punti del poligono devono definire un circuito chiuso in cui il primo punto del poligono è uguale all'ultimo punto:

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)

Nell'esempio successivo, definisci l'area di ricerca utilizzando una linea di punti collegati. La linea è simile a un itinerario di viaggio calcolato dall'API Routes. Il percorso potrebbe essere per un veicolo, una bicicletta o per un pedone:

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)

In questo esempio, imposta il raggio di ricerca su 100 metri intorno alla linea.

Filtrare in base ai campi del set di dati sui luoghi

Perfeziona la ricerca in base ai campi definiti dallo schema del set di dati. Filtra i risultati in base ai campi del set di dati, ad esempio luogo regular_opening_hours, price_level e cliente rating.

Fai riferimento a qualsiasi campo nel set di dati definito dallo schema del set di dati per il tuo paese di interesse. Lo schema del set di dati per ogni paese è composto da due parti:

• Lo schema principale comune ai set di dati di tutti i paesi.
• Uno schema specifico per paese che definisce i componenti dello schema specifici per quel paese.

Ad esempio, la query può includere una clausola WHERE che definisce i criteri di filtro per la query. Nell'esempio seguente, vengono restituiti dati di aggregazione per i luoghi di tipo restaurant con un business_status di OPERATIONAL, che hanno un rating maggiore o uguale a 4, 0 e con allows_dogs impostato su 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 query successiva restituisce risultati per i luoghi che hanno almeno otto stazioni di ricarica EV:

SELECT WITH AGGREGATION_THRESHOLD
  COUNT(*) AS count
FROM
  `places_insights___us___sample.places_sample`
WHERE
  ev_charge_options.connector_count > 8;

Filtra in base al tipo principale di luogo e al tipo di luogo

Ogni luogo nel set di dati può avere:

• Un singolo tipo principale associato ai tipi definiti nella tabella A. Ad esempio, il tipo principale potrebbe essere mexican_restaurant o steak_house. Utilizza primary_type in una query per filtrare i risultati in base al tipo principale di un luogo.

• Valori di tipo multipli associati ai tipi definiti dalla tabella A. Ad esempio, un ristorante potrebbe avere i seguenti tipi: seafood_restaurant, restaurant, food, point_of_interest, establishment. Utilizza types in una query per filtrare i risultati nell'elenco dei tipi associati al luogo.

La seguente query restituisce risultati per tutti i luoghi con un tipo principale di bar ma che funzionano anche come restaurant:

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

Filtrare in base ai valori dei dati predefiniti

Molti campi dell'insieme di dati hanno valori predefiniti. Ad esempio:

• Il campo price_level supporta i seguenti valori predefiniti:

  • PRICE_LEVEL_FREE
  • PRICE_LEVEL_INEXPENSIVE
  • PRICE_LEVEL_MODERATE
  • PRICE_LEVEL_EXPENSIVE
  • PRICE_LEVEL_VERY_EXPENSIVE

• Il campo business_status supporta i seguenti valori predefiniti:

  • OPERATIONAL
  • CLOSED_TEMPORARILY
  • CLOSED_PERMANENTLY

Nota: in genere includi il filtro business_status per assicurarti di ottenere il conteggio delle attività OPERATIONAL. In caso contrario, i conteggi includono le attività chiuse.

In questo esempio, la query restituisce il conteggio di tutti i ristoranti con un business_status di OPERATIONAL entro un raggio di 1000 metri dall'Empire State Building a 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)

Filtra per orari di apertura

In questo esempio, restituisci il conteggio di tutti i luoghi in un'area geografica con happy hour il venerdì:

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

Utilizzare una funzione di aggregazione

L'esempio seguente mostra le funzioni di aggregazione BigQuery supportate. Questa query aggrega le valutazioni di tutti i luoghi situati entro un raggio di 1000 metri dall'Empire State Building di New York per produrre statistiche sulle valutazioni:

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"

Restituisce i gruppi di risultati

Le query mostrate finora restituiscono una singola riga nel risultato che contiene il conteggio di aggregazione per la query. Puoi anche utilizzare l'operatore GROUP BY per restituire più righe nella risposta in base ai criteri di raggruppamento.

Nota: l'esecuzione di un'operazione GROUP BY non è consentita sui campi dei componenti dell'indirizzo (ad esempio administrative_area_level, locality e postal_code) nel set di dati Places Insights.

Ad esempio, la seguente query restituisce risultati raggruppati in base al tipo principale di ogni luogo nell'area di ricerca:

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'immagine seguente mostra un output di esempio di questa query:

In questo esempio, definisci una tabella di località. Per ogni località, calcola il numero di ristoranti nelle vicinanze, ovvero quelli entro 1000 metri:

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'immagine seguente mostra un output di esempio di questa query: