BigQuery è il database di Google che permette di interrogare e analizzare velocemente terabyte di dati. Come visto nell’articolo BigQuery: clausola WITH, BigQuery offre molte funzionalità in più rispetto allo standard SQL. Tra queste c’è il supporto nativo alle funzioni del sistema informativo geografico, o più semplicemente GIS, e i loro tipi di dati.
In questo articolo analizzeremo alcune di queste funzioni per estrarre alcune informazioni sul servizio di bike sharing della città di Londra e visualizzarle mediante BigQuery Geo Vis. Inoltre, vedremo l’utilizzo del costrutto STRUCT per raggruppare colonne semanticamente correlate. Introduciamo, quindi, il problema presentando il problema che vogliamo affrontare e i dati in nostro possesso.
Dataset
Il set di dati che utilizzeremo in questo articolo sarà composto di oltre 24 milioni di viaggi in bike sharing nel centro di Londra. Il servizio di bike sharing è ormai popolare in molti paesi del mondo ed in particolare nei grandi centri urbani.
Essenzialmente, una persona può recarsi ad una banchina che si trovano in un angolo di strada della città e noleggiare una bicicletta ad ore. Per terminare il noleggio, la persona deve riportare la bici ad una delle stazioni, non necessariamente quella di partenza. In base al tempo di utilizzo alla persona viene addebitato un importo. In alcuni casi, grazie all’abbonamento annuale, se la durata del noleggio è inferiore ad una certa soglia non viene addebitato nessun costo. Questo servizio è orientato a ridurre il numero di veicoli a motore presenti sulle strade urbane abbattendo di conseguenza le emissioni nocive. Inoltre, l’utilizzo della bicicletta è salutare. Negli ultimi anni, oltre al bike sharing, sono nati altri servizi simili che offrono la possibilità di noleggiare auto o monopattini elettrici.
A parte le questioni di carattere sociale, il gestore del servizio (pubblico o privato) ha necessità di monitorare l’utilizzo dei veicoli. Le analisi dei dati permettono, infatti, di definire strategie di ampliamento della flotta, manutenzione dei veicoli e/o assistenza all’utenza.
Il dataset che utilizzeremo è pubblico e consiste di due tabelle, una contenente tutti i viaggi che le biciclette hanno effettuato, l’altra le posizioni delle stazioni di attracco nella città di Londra.
Esercizio
Vogliamo estrarre alcune statistiche relativamente ai viaggi dei ciclisti. In particolare, vogliamo individuare i percorsi, ossia le coppie di stazioni di inizio e fine noleggio, sui quali i ciclisti sono più veloci.
Per semplicità e dato che non abbiamo i dati GPS di ciascuna corsa, supponiamo che la velocità di percorrenza sia costante durante tutto il tragitto. Inoltre, il percorso viene approssimato ad una linea retta che unisce le due stazioni.
Nella figura è riportata la struttura della tabella relativa alle singole corse.
Per calcolare la velocità media, abbiamo bisogno della distanza percorsa e della durata. Per la durata potremmo fare una differenza tra i timestamp di inizio e fine. Fortunatamente esiste già un campo che fornisce questa informazione (duration). Se osservate però i valori sono molto alti. Cosa rappresentano? Per scoprirlo è sufficiente andare nella scheda Schema dove troverete anche la descrizione di ciascun campo (quando è presente). In questo caso ci fornisce l’informazione che il valore salvato nel campo duration è il tempo di percorrenza espresso in secondi. Per questo motivo è utilissimo aggiungere le descrizioni delle colonne quando si creano le tabelle!!!
Per il calcolo della velocità ci serve, quindi, solo più la distanza. In questo caso però non c’è un campo che fornisca questa informazione. Gli unici dati a nostra disposizione sono la stazione di arrivo e quella di partenza. Abbiamo quindi bisogno di recuperare le informazioni della posizione geografica dall’altra tabella. Quest’ultima, infatti, non solo contiene l’id e il nome della stazione ma anche latitudine e longitudine. Poiché BigQuery supporta nativamente le funzioni GIS, possiamo usare la funzione ST_DISTANCE, che fornirà la distanza (espressa in metri) su una linea retta tra due punti geografici. Esistono anche altre funzioni GIS. Queste iniziano sempre con il prefisso ST_.
Per costruire la nostra query che estrae i percorsi con una velocità media di percorrenza maggiore, dobbiamo inizialmente effettuare un join tra le due tabelle del nostro dataset. In questo modo assoceremo le informazioni di latitudine e longitudine a ciascuna stazione (di partenza e di arrivo) dei tragitti percorsi. Poiché le colonne relative alla latitudine e alla longitudine non sono di un tipo di dati geografici per natura, è necessario convertirli in punti geografici formali prima di poter usare la funzione ST_DISTANCE. Per la conversione si usa la funzione ST_GEOGPOINT.
Dal momento che vorremo disegnare queste linee su una mappa, è necessario includere nel risultato anche la funzione ST_MAKELINE. In questo modo potremmo importare i dati in Geo Vis.
Questa è la prima parte della query. Possiamo usare il costrutto WITH, visto nell’articolo BigQuery: clausola WITH, per salvare questo risultato temporaneo ed usarlo per calcolare funzioni di aggregazione e/o filtrare i dati.
Per rendere più leggibile il risultato e sfruttare le potenzialità di BigQuery si usa spesso il costrutto STRUCT. Nell’esempio che stiamo analizzando, ove la tabella ha poche colonne, non è strettamente necessario, ma in altri casi può risultare molto utile. Un esempio di tabelle con molte colonne è quello relativo a Google Analytics.
Tornando però al nostro esempio, la prima parte della query dovrà effettuare il join con due istanze della tabella delle stazioni. Ciò perché bisogna includere le informazioni geografiche sia della stazione di partenza che di arrivo. Utilizzando il costrutto STRUCT possiamo raggruppare le informazioni di ciascuna stazione associando un prefisso. In questo modo è chiara la distinzione delle coordinate geografiche delle due stazioni. Questo processo di unire diverse tabelle in una singola tabella è chiamato denormalizzazione. È spesso un ottimo modo per memorizzare i dati al fine di velocizzare l’interrogazione da parte di BigQuery senza dover fare join tra le tabelle ogni volta. È anche altamente performante poiché BigQuery memorizza i dati in un formato a colonne invece che in un formato a record, il che significa che non si viene penalizzati per avere schemi molto ampi.
Ora che abbiamo finito di pre-elaborare tutti i nostri dati, è il momento di scrivere la query per calcolare le statistiche ci interessano. Utilizziamola tabella definita con la clausola WITH. È sufficiente inserire il suo nome nella clausola FROM. Estraiamo, quindi, i nomi delle stazioni, arrotondiamo la distanza del viaggio, la convertiamo da metri a chilometri e contiamo tutti i viaggi tra due stazioni. Poiché vogliamo avere delle statistiche sulla velocità media affidabili, filtriamo solo le coppie di stazioni che hanno almeno 100 corse mediante la clausola HAVING. Infine, ordiniamo il risultato in base alla velocità media e limitiamo la visualizzazione ai primi 100 risultati.
Il risultato che otteniamo è quello riportato qui sotto.
La query finale è la seguente.
WITH staging AS (
SELECT
STRUCT(
start_stn.name,
ST_GEOGPOINT(start_stn.longitude, start_stn.latitude) AS point,
start_stn.docks_count,
start_stn.install_date
) AS starting,
STRUCT(
end_stn.name,
ST_GEOGPOINT(end_stn.longitude, end_stn.latitude) AS point,
end_stn.docks_count,
end_stn.install_date
) AS ending,
STRUCT(
rental_id,
bike_id,
duration, -- seconds
ST_DISTANCE(
ST_GEOGPOINT(start_stn.longitude, start_stn.latitude),
ST_GEOGPOINT(end_stn.longitude, end_stn.latitude)
) AS distance, -- meters
ST_MAKELINE(
ST_GEOGPOINT(start_stn.longitude, start_stn.latitude),
ST_GEOGPOINT(end_stn.longitude, end_stn.latitude)
) AS trip_line, -- straight line (for GeoViz)
start_date,
end_date
) AS bike
FROM `bigquery-public-data.london_bicycles.cycle_stations` AS start_stn
LEFT JOIN `bigquery-public-data.london_bicycles.cycle_hire` AS b
ON start_stn.id = b.start_station_id
LEFT JOIN `bigquery-public-data.london_bicycles.cycle_stations` AS end_stn
ON end_stn.id = b.end_station_id
)
-- Find the fastest avg biking pace for rides over 30 mins
SELECT
starting.name AS starting_name,
ending.name AS ending_name,
ROUND(bike.distance/1000,2) distance_km,
ST_UNION_AGG(bike.trip_line) AS trip_line,
COUNT(bike.rental_id) AS total_trips,
ROUND(
AVG(
(bike.distance / 1000) -- meters --> km
/ (bike.duration / 60 / 60) -- seconds --> hours
)
,2)
AS avg_kmph
FROM staging
WHERE bike.duration > (30 * 60) -- at least 30 minutes = 1800 seconds
GROUP BY
starting.name,
ending.name,
bike.distance
HAVING total_trips > 100
ORDER BY avg_kmph DESC
LIMIT 100; Per visualizzare i nostri risultati su una mappa utilizziamo BigQuery Geo Vis. Geo Vis è un’applicazione web che permette di eseguire query in BigQuery e visualizzare i punti dei dati geografici. Si possono anche modificare gli stili grafici dei dati rappresentati e basare le loro rappresentazioni sui dati stessi.
Usando la query precedente e giocando con le impostazioni di stile, possiamo evidenziare i percorsi con una velocità media maggiore in modo rapido e intuitivo. Un possibile risultato è mostrato qui.
