Avanzato: esegui query direttamente sul set di dati

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

Informazioni sulla soglia di aggregazione

Le query SQL standard eseguite direttamente sulle tabelle non elaborate applicano una soglia di aggregazione rigida. Viene generata una riga di output solo se il conteggio di aggregazione calcolato è pari o superiore a 5.

Se i filtri delle query restituiscono un conteggio di 0, 1, 2, 3 o 4, l'intera riga dei risultati viene omessa dalla risposta. Se la logica dell'applicazione richiede conteggi bassi precisi (incluso 0) o deve recuperare singoli ID luogo, devi eseguire query sul set di dati utilizzando le funzioni di conteggio di Places.

Nozioni di base sulle query

L'immagine seguente mostra il formato di base di una query:

Ogni parte della query è descritta più dettagliatamente di seguito.

Suggerimento: BigQuery fornisce anche documentazione ed esempi per l'esecuzione di query su dati geospaziali. Per saperne di più, consulta Introduzione all'analisi geospaziale.

Requisiti delle query

Le query SQL eseguite direttamente sul set di dati devono specificare il set di dati e includere WITH AGGREGATION_THRESHOLD nella clausola SELECT. Senza questo, la query non andrà a buon fine.

Questo esempio specifica places_insights___us.places per eseguire query sul set di dati per gli Stati Uniti.

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

(Facoltativo) Specifica un nome per il progetto

Se vuoi, puoi includere il nome del progetto nella query. Se non specifichi un nome del progetto, per impostazione predefinita la query viene eseguita sul progetto attivo.

Potresti voler includere il nome del progetto se hai set di dati collegati con lo stesso nome in progetti diversi o se stai eseguendo una query su una tabella al di fuori del progetto attivo.

Ad esempio, [project name].[dataset name].places.

Specifica una funzione di aggregazione

L'esempio seguente mostra le funzioni di aggregazione di 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
  `PROJECT_NAME.places_insights___us.places`
WHERE
  ST_DWITHIN(ST_GEOGPOINT(-73.9857, 40.7484), point, 1000)
  AND business_status = "OPERATIONAL"

Filtra i risultati utilizzando linee e poligoni geografici

Se non specifichi una limitazione della località, l'aggregazione dei dati viene applicata all'intero set di dati. In genere, specifichi una limitazione della località per eseguire ricerche in un'area specifica.

1. Filtra utilizzando i buffer di ricerca radiale (ST_DWITHIN)

Questa query di esempio specifica una limitazione del target incentrata sull'Empire State Building di New York City, con un raggio di 1000 metri.

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 luogo viene incluso nei risultati solo se il suo punto centrale rientra nella limitazione di località. Un luogo, ad esempio un parco, il cui punto centrale si trova al di fuori della limitazione di località non è incluso nei risultati.

2. Filtra utilizzando i confini dei poligoni (ST_CONTAINS)

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

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 lungo i buffer di viaggio del percorso (LINESTRING)

Nell'esempio successivo, definisci l'area di ricerca utilizzando una linea di punti collegati e imposti il raggio di ricerca su 100 metri intorno alla linea. La linea è simile a un percorso di viaggio calcolato dall'API Routes. Il percorso potrebbe essere per un veicolo, una bicicletta o 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 `PROJECT_NAME.places_insights___us.places`
WHERE
  ST_DWITHIN(route, point, 100)

Filtra in base ai campi del set di dati dei 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 tutti i campi del set di dati definiti 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 di base, 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.

Suggerimento: includi il filtro business_status per assicurarti di ottenere il conteggio delle attività OPERATIONAL. In caso contrario, il conteggio include le attività chiuse.

Nell'esempio seguente, restituisci i dati di aggregazione per i luoghi di tipo tourist_attraction 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 `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 query successiva restituisce i risultati per i luoghi che hanno almeno otto stazioni di ricarica EV:

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

Filtra in base al tipo principale e al tipo di luogo

Ogni luogo nel set di dati può avere:

• Un singolo tipo principale associato tra i tipi definiti da Tipi di luoghi. 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.
• Più valori di tipo associati tra quelli definiti dai tipi di luogo. 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 in base all'elenco di tipi associati al luogo.

La seguente query restituisce i risultati per tutti i luoghi con un tipo principale di skin_care_clinic che funzionano anche come 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

Filtra per ID luogo

L'esempio seguente calcola la valutazione media di 5 luoghi. I luoghi sono identificati dal relativo 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;

Escludi ID luogo specifici dai risultati delle query

Puoi anche escludere un array di ID luogo da una query.

Puoi trovare gli ID luogo che stai cercando utilizzando lo strumento di ricerca degli ID luogo o, in modo programmatico, utilizzando l'API Places per eseguire una richiesta di ricerca testuale (nuova).

Nell'esempio seguente, la query trova il numero di bar nel codice postale 2000 di Sydney, in Australia, che non compaiono nell'array excluded_cafes. Una query di questo tipo potrebbe essere utile per un proprietario dell'attività che vuole escludere le proprie attività da un conteggio.

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 in base ai valori dei dati predefiniti

Molti campi del set 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
  • FUTURE_OPENING

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 fioristi con un business_status di OPERATIONAL entro un raggio di 1000 metri dall'Empire State Building di New York City:

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)

Filtra per orario di apertura

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

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

Filtra per regione (componenti dell'indirizzo)

Il nostro set di dati sui luoghi contiene anche un insieme di componenti dell'indirizzo utili per filtrare i risultati in base ai confini politici. Ogni componente dell'indirizzo è identificato dal nome in codice di testo (10002 per il codice postale di New York) o dall'ID luogo (ChIJm5NfgIBZwokR6jLqucW0ipg) per l'ID codice postale 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

Filtra per configurazioni di ricarica EV

Questo esempio fornisce un conteggio del numero di luoghi con almeno 8 caricabatterie EV:

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

Questo esempio conta il numero di luoghi che hanno almeno 10 caricabatterie Tesla che supportano la ricarica rapida:

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

Filtra per data dello snapshot dei dati

Il set di dati dei luoghi contiene i dati sul numero di luoghi per ogni mese a partire da gennaio 2024. Per eseguire una query sullo snapshot dei dati dei luoghi per un determinato mese, filtra utilizzando il campo snapshot_date.

Nota: il campo snapshot_date è formattato come AAAA-MM-01 (ad esempio, "2024-01-01"). Anche se nella stringa della data è obbligatorio un valore per il giorno (01), ogni istantanea rappresenta i dati aggregati per l'intero mese anziché per un singolo giorno.

Ad esempio, la seguente query restituisce i risultati raggruppati per tipo principale per ogni luogo nell'area di ricerca, nel mese di gennaio 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;

Restituisci righe di risultati raggruppate

Le query mostrate finora restituiscono una singola riga nel risultato che contiene il conteggio dell'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 su postal_code_names in GB.

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

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

Questa immagine mostra un esempio di output per questa query:

In questo esempio, definisci una tabella di località. Per ogni località, calcoli poi 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
  `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

Questa immagine mostra un esempio di output per questa query: