Eseguire query direttamente sul set di dati

Per accedere direttamente ai dati di Places Insights, scrivi query SQL in BigQuery che restituiscono dati 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 pari a 0, 1, 2, 3 o 4, la riga dei risultati viene omessa dai risultati.

Se devi ottenere conteggi inferiori a 5, valuta l'utilizzo delle funzioni di conteggio dei luoghi invece. Queste funzioni possono restituire qualsiasi conteggio, incluso 0, ma applicano un' area di ricerca minima di 40,0 metri per 40,0 metri (1600 m²). Scopri di più su quando eseguire query direttamente e quando utilizzare le funzioni.

Nozioni di base sulle query

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

Ogni parte della query è descritta in modo più dettagliato di seguito.

Successo: BigQuery fornisce anche documentazione ed esempi per l'esecuzione di query sui dati geospaziali. Per ulteriori informazioni, consulta la sezione 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. In caso contrario, la query non andrà a buon fine.

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

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

Specificare un nome del progetto (facoltativo)

Puoi includere facoltativamente il nome del progetto nella query. Se non specifichi un nome del progetto, la query utilizzerà per impostazione predefinita il progetto attivo.

Potresti voler includere il nome del progetto se hai collegato set di dati 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.

Specificare 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 in 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"

Specificare una limitazione della località

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 cercare un'area specifica. Questa query di esempio specifica una limitazione target centrata sull'Empire State Building di New York, 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 punto centrale del luogo rientra nella limitazione della località. Un luogo, ad esempio un parco, il cui punto centrale si trova al di fuori della limitazione della località non viene incluso nei risultati.

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

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)

Filtrare 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 regular_opening_hours, price_level e rating del cliente.

Fai riferimento a tutti i campi del set di dati definiti dallo schema del set di dati per il 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.

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, vengono restituiti i dati di aggregazione per i luoghi di tipo tourist_attraction con un business_status di OPERATIONAL, che hanno una 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;

Filtrare in base al tipo principale e al tipo di luogo

Ogni luogo nel set di dati può avere:

• Un singolo tipo principale associato ai 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 ai tipi definiti da Tipi di luoghi. 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 dei tipi associati al luogo.

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

Filtrare in base all'ID luogo

L'esempio seguente calcola la valutazione media per 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;

Escludere determinati ID luogo

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 a livello di programmazione utilizzando l'API Places per eseguire una richiesta di ricerca testuale (nuova).

Nell'esempio seguente, la query trova il conteggio dei bar nel codice postale 2000 di Sydney, Australia, che non vengono visualizzati nell'array excluded_cafes. Una query di questo tipo potrebbe essere utile per un proprietario di un'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;

Filtrare 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 in un raggio di 1000 metri dall'Empire State Building di 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)

Filtrare in base agli 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 `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);

Filtrare in base alla regione (componenti dell'indirizzo)

Il nostro set di dati dei 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 a 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

Filtrare in base alla 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

Restituire 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 su postal_code_names in GB.

Ad esempio, la query seguente 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à, 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
  `PROJECT_NAME.places_insights___us.places`
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: