estrazione dati di interesse con google Sheets

Per estrarre i dati di congestione presi al punto di controllo desiderato e magari farsi spedire una email o altro qualora il livello di congestione arrivi a 10 ad esempio, si può usare la funzione IMPORTDATA di google sheets.

Nel proprio GoogleDrive aprire un nuovo Sheets (in italiano: Fogli).

Nella prima casella (cioè A1) scrivere
https://traffic.api.here.com/traffic/6.3/flow/xml/15/17242/11734?app_id=pippo&app_code=pluto"
con pippo e pluto sostituiti con il propri codici di HERE

nella casella A3 ad esempio , scrivere
=IMPORTDATA(A1)
cosi' da importare la risposta

poi occorre spezzare la risposta in base ai <FI> , perciò si usa SPLIT
ma SPLIT lavora su un carattere soltanto non 4 come in <FI>, quindi scegliamo "^":
mettiamo in A4
=SOSTITUISCI(A3,"<FI>","^")

e poi in A5
=split(A4, "^", VERO, VERO)

si otterrà qualcosa del genere, dove si vedono le informazioni suddivise in colonne, cioè ad ogni colonna corrisponde un punto di rilevamento:

Ora occorre estrarre le congestioni , assumendo che le colonne saranno sempre quelle, ma che i campi <SSS> potranno esserci oppure no a seconda che venga precisata la congestione a dettaglio maggiore.

se JF maggiore della soglia, allora allarme altrimenti fluisce.

Non resta che fare lo script che manda una email se una delle caselle segnala l'allarme. Nella email verrà riportata l'informazione su dove come e quando.

Ad esempio se l'allarme è un colonna G, allora verrà spedito il contenuto della cella G5.

L'altro metodo è importare XML (=IMPORTXML(A1)) ma prima occorre tenere presente un pasticcio nella risposta in formato XML: https://stackoverflow.com/questions/58175223/importxml-in-google-sheets-returns-n-d-when-i-make-slightly-complex-requests

Per fare questo import occorre sbarazzarsi della stringa incriminata xmlns="http://traffic.nokia.com/trafficml-flow-3.2" che è un errore (voluto?) nel formato XML.

Quindi occorre fare un salto triplo.

1. IMPORTDATA in una casella di tutto il file XML
2. SOSTITUISCI la stringa incriminata con "", cioè nulla
3. pubblicare il solo foglio con i dati sostituiti in formato tsv (NON csv! poiché mette doppi apici a tutti i doppi apici)
4. IMPORTXML del foglio pubblicato, ora in formato senza errori, su cui possono essere fatte xquery che funzionano

esegesi della risposta

l'esegesi della risposta si può fare scaricando la documentazione con il solito metodo :
https://traffic.api.here.com/traffic/6.0/xsd/flow.xsd?app_id= etc
(mettendo il proprio appid e codeid)
e leggendosela. Qui si evidenziano le cose più importanti.

[All'inizio c'è un campo con l'ora internazionale (PBT) a cui si riferiscono le informazioni della risposta: ricordarsi che d'estate siamo avanti di 2 ore , le 18 sono in realtà le 20. PBT="2019-10-04T18:15:28Z" ]


Prima di tutto la posizione a cui si riferisce il numerello della congestione è identificata da PC= , ad esempio il semaforo di Conterico è <TMC PC="42182" />.

qui un po' di punti di rilevamento traffico tanto per capire

invece qui un esempio di file json per umap , comodo per vedere dove stanno tutti i tmc sulla mappa.

L'altra cosa importante è la direzione della coda , identificata con QD e che può essere + o -, a seconda del senso di marcia.

Infine ciò che interessa è il livello di congestione , indentificato da JF="numerello da 0 a 10",
dove 10 significa che è tutto bloccato e 0 che "non c'è nessuno".

Un'ultima cosa è l'indicatore SS che serve a precisare la congestione delle corsie se ce n'è più d'una. La congestione media può essere 1.89 , ma su una corsia magari non c'è coda , 0.46, (perchè è solo una canalizzazione di svolta a sinistra) mentre magari sull'altra, la tua, c'è 3.14 !.

<FI>
 <TMC PC="42182" DE="SP161-Paullo" QD="+" LE="1.90462"/>
 <CF CN="0.94" FF="60.7" JF="1.89268" SP="43.34" SU="43.34" TY="TR">
 <SSS>
  <SS FF="60.67" JF="0.4604" LE="1.49274" SP="57.17" SU="57.17"/>
  <SS FF="60.63" JF="3.14159" LE="0.41188" SP="27.47" SU="27.47"/>
 </SSS>
 </CF>
</FI>

Le info relative ad un PC sono racchiuse fra separatori <FI> permettendo ai computer di capire dove cominciano quelle del prossimo PC presente nella risposta.

esempio postazioni TMC con file json

in questo formato viene esportato da umap , pertanto è pure il formato ideale per caricarlo. Comunque csv su 3 colonne va bene lo stesso (intesta colonne con : name, long e lat), i marcatori saranno allora di default.

{"type":"FeatureCollection","features":[","_umap_options":
{"type":"Feature","properties":{"name":"10957","_umap_options":{"showLabel":true,"labelInteractive":true,"labelDirection":"right"}},"geometry":{"type":"Point","coordinates":[9.37515,45.42904]}},","_umap_options":
{"type":"Feature","properties":{"name":"10958","_umap_options":{"showLabel":true,"labelInteractive":true,"labelDirection":"right"}},"geometry":{"type":"Point","coordinates":[9.43238,45.41573]}},","_umap_options":
{"type":"Feature","properties":{"name":"13914","_umap_options":{"showLabel":true,"labelInteractive":true}},"geometry":{"type":"Point","coordinates":[9.27398,45.42529]}},","_umap_options":
{"type":"Feature","properties":{"name":"13915","_umap_options":{"showLabel":true,"labelInteractive":true}},"geometry":{"type":"Point","coordinates":[9.2894,45.42342]}},","_umap_options":
{"type":"Feature","properties":{"name":"13916","_umap_options":{"showLabel":true,"labelInteractive":true}},"geometry":{"type":"Point","coordinates":[9.32105,45.42796]}},","_umap_options":
{"type":"Feature","properties":{"name":"13917","_umap_options":{"showLabel":true,"labelInteractive":true}},"geometry":{"type":"Point","coordinates":[9.36271,45.43027]}},","_umap_options":
{"type":"Feature","properties":{"name":"13919","_umap_options":{"showLabel":true,"labelInteractive":true}},"geometry":{"type":"Point","coordinates":[9.42632,45.41893]}},","_umap_options":
{"type":"Feature","properties":{"name":"25315","_umap_options":{"showLabel":true,"labelInteractive":true}},"geometry":{"type":"Point","coordinates":[9.4324,45.41554]}},","_umap_options":
{"type":"Feature","properties":{"name":"25316","_umap_options":{"showLabel":true,"labelInteractive":true}},"geometry":{"type":"Point","coordinates":[9.43222,45.4118]}},","_umap_options":
{"type":"Feature","properties":{"name":"26606","_umap_options":{"showLabel":true,"labelInteractive":true}},"geometry":{"type":"Point","coordinates":[9.30913,45.42663]}},","_umap_options":
{"type":"Feature","properties":{"name":"26617","_umap_options":{"showLabel":true,"labelInteractive":true}},"geometry":{"type":"Point","coordinates":[9.35384,45.43081]}},","_umap_options":
{"type":"Feature","properties":{"name":"26626","_umap_options":{"showLabel":true,"labelInteractive":true}},"geometry":{"type":"Point","coordinates":[9.40386,45.42532]}},","_umap_options":
{"type":"Feature","properties":{"name":"26730","_umap_options":{"showLabel":true,"labelInteractive":true,"labelDirection":"left"}},"geometry":{"type":"Point","coordinates":[9.40064,45.41604]}},","_umap_options":
{"type":"Feature","properties":{"name":"26731","_umap_options":{"showLabel":true,"labelInteractive":true}},"geometry":{"type":"Point","coordinates":[9.40845,45.41686]}},","_umap_options":
{"type":"Feature","properties":{"name":"26797","_umap_options":{"showLabel":true,"labelInteractive":true}},"geometry":{"type":"Point","coordinates":[9.37484,45.42862]}},","_umap_options":
{"type":"Feature","properties":{"name":"42182","_umap_options":{"showLabel":true,"labelInteractive":true}},"geometry":{"type":"Point","coordinates":[9.40375,45.42532]}}],"_umap_options":
{"displayOnLoad":true,"browsable":true,"name":"layerumappaullesetmclabels.json","id":1014539,"remoteData":{}}}

esempi di coordinate CCISS per farci la mano

un po' d coordinate dei punti di rilevamento ( traffic message channel) per farci la mano:

tmc			latx10000	longx10000
26797	https://www.google.it/maps/@45.42862,9.37484,17z		4542862	937484
13915	https://www.google.it/maps/@45.42342,9.2894,17z		4542342	928940
3701	https://www.google.it/maps/@45.41032,9.26788,17z		4541032	926788
26731	https://www.google.it/maps/@45.41686,9.40845,17z		4541686	940845
14115	https://www.google.it/maps/@45.40681,9.26138,17z		4540681	926138
10957	https://www.google.it/maps/@45.42904,9.37515,17z		4542904	937515
25315	https://www.google.it/maps/@45.41554,9.4324,17z		4541554	943240
14118	https://www.google.it/maps/@45.41462,9.25947,17z		4541462	925947
26607	https://www.google.it/maps/@45.39683,9.32581,17z		4539683	932581
2380	https://www.google.it/maps/@45.41144,9.26212,17z		4541144	926212
26645	https://www.google.it/maps/@45.39156,9.28715,17z		4539156	928715
14117	https://www.google.it/maps/@45.41384,9.26115,17z		4541384	926115
26626	https://www.google.it/maps/@45.42532,9.40386,17z		4542532	940386
10958	https://www.google.it/maps/@45.41573,9.43238,17z		4541573	943238
26625	https://www.google.it/maps/@45.42309,9.40258,17z		4542309	940258
42182	https://www.google.it/maps/@45.42532,9.40375,17z	semaforo di conterico!	4542532	940375
26616	https://www.google.it/maps/@45.43152,9.3479,17z		4543152	934790
13917	https://www.google.it/maps/@45.43027,9.36271,17z		4543027	936271
42742	https://www.google.it/maps/@45.42585,9.42072,17z		4542585	942072
2379	https://www.google.it/maps/@45.40705,9.26142,17z		4540705	926142
13919	https://www.google.it/maps/@45.41893,9.42632,17z		4541893	942632
3700	https://www.google.it/maps/@45.39227,9.28716,17z		4539227	928716
26623	https://www.google.it/maps/@45.43355,9.32436,17z		4543355	932436
26617	https://www.google.it/maps/@45.43081,9.35384,17z		4543081	935384
25309	https://www.google.it/maps/@45.39929,9.38421,17z		4539929	938421
26730	https://www.google.it/maps/@45.41604,9.40064,17z		4541604	940064
26615	https://www.google.it/maps/@45.4335,9.32442,17z		4543350	932442
1112	https://www.google.it/maps/@45.40683,9.2613,17z		4540683	926130
1113	https://www.google.it/maps/@45.39233,9.26096,17z		4539233	926096
13916	https://www.google.it/maps/@45.42796,9.32105,17z		4542796	932105
13914	https://www.google.it/maps/@45.42529,9.27398,17z		4542529	927398
26606	https://www.google.it/maps/@45.42663,9.30913,17z		4542663	930913
26732	https://www.google.it/maps/@45.42287,9.43191,17z		4542287	943191
14116	https://www.google.it/maps/@45.41139,9.26218,17z		4541139	926218
25316	https://www.google.it/maps/@45.4118,9.43222,17z		4541180	943222
14019	https://www.google.it/maps/@45.41462,9.25954,17z		4541462	925954

Fonti dei dati

Posizioni dei punti di rilevamento:
https://www.cciss.it/web/cciss/database-rds-tmc

scaricare il file zippato chiamato Database RDS-TMC (l'ultima versione)
all'interno del file zippato le coordinate geografiche sono in POINTS.DAT.

Ad esempio il punto 42182 si trova a 9.40375 di longitudine e 45.42532 di longitudine

25;1;42182;P;1;11;;45436;29592;;41884;;;836;1;1;1;1;1;1;;;+00940375;+4542532;;0;



Sito dove attingere i dati in tempo reale: https://www.here.com
Build your app with 250k transactions per month, free. Get your free API key

Bisogna registrarsi (gratis)  per ottenere la YOUR_APP_ID e la YOUR_APP_CODE da mettere nelle richieste di informazioni. Tipologia : REST.

I dati vengono inviati al proprio dispositivo (browser) inoltrando la richiesta ad esempio come di seguito per la casella 134/86 a zoom 8:
per gli incidenti, esempio di richiesta con dati voluti in formato xml:

https://traffic.api.here.com/traffic/6.3/incidents/xml/8/134/86
  ?app_id={YOUR_APP_ID}
  &app_code={YOUR_APP_CODE}

per il flusso di traffico (congestione), esempio di richiesta con dati voluti in formato json:

https://traffic.api.here.com/traffic/6.2/flow/json/8/134/86
  ?app_id={YOUR_APP_ID}
  &app_code={YOUR_APP_CODE}

Ora resta il problema di come trasformare latitudine e longitudine nei numerini (134/86 nell'esempio) richiesti dal sito HERE per identificare la casella terrestre della quale si vogliono avere informazioni di traffico o incidente. Ecco il foglio di calcolo.
https://docs.google.com/spreadsheets/d/e/2PACX-1vTkaP0ZHr6mQPNyaQ14Vaxqq3qZhodPugzeQrj-QYiutKDLLxASmESR72hw93oQLPkgW8O5z9UT62Vg/pubhtml?gid=1402560637&single=true

.

formule

risultati

A questo punto di può provare (una volta messe nella casella A20 i codici ottenuti iscrivendosi a HERE, senza le graffe!) a copiare come url per il browser la richiesta e vedere se HERE risponde coi dati, ad esempio:

esempio risposta

I numerelli dopo i JF (jam factor) saranno i valori di congestione che ci interessano .

Per l'esegesi completa della risposta : vedere qui.

Per vedere come estrarre i campi che interessano : vedere qui.




EDIT del 26 gennaio 2020: scoperto un dettaglio non irrilevante sul sistema di coordinate geografiche usato da CCISS:

https://github.com/napo/italian_tmc/blob/master/Find%20TMC%20in%20Rovereto%20and%20Trento.ipynb