Scopo del presente post è presentare un formidabile linguaggio di scripting, in cui io e Giovanni Allegri ci siamo imbattuti da un po’ di tempo a questa parte, che presenta delle potenzialità davvero eccezionali nel processamento di dati geospaziali. Il pretesto occasionale che ha riacceso il nostro sopito interesse nei confronti di JEQL é un recente post del blog Lin.ear Th.inking, Visualizing geodetic information with JEQL, in cui l’autore Martin Davis (aka “Dr JTS”, l’attuale designer e lead developer della fantastica libreria JTS …e non solo!) introduce delle nuove funzioni geodetiche di calcolo sulle geometrie all’interno di JTS, illustrandone l’utilizzo mediante un’applicazione in JEQL che ripercorreremo nel seguito.

Cos’è JEQL?

JEQL è un Query Language sviluppato in Java, dove la “E” può assumere i seguenti significati:

• Extended, poiché implementa un numero sempre crescente di estensioni che lo rende più potente nel processamento dei dati rispetto alle tante versioni e dialetti SQL esistenti;
• Embeddable, in quanto il motore di JEQL può essere integrato all’interno di altre applicazioni, in modo da essere utilizzato come query language per modelli di dati tabellari;
• ETL, essendo l’Extract/Transform/Load il caso d’uso ideale di JEQL;
• Efficient, dato che garantisce velocità di sviluppo ed esecuzione.

JEQL è dunque un linguaggio di scripting che consente il processamento di strutture di dati tabellari, compresi quelli geografici vettoriali. Fin qui nulla di nuovo, probabilmente penseranno i lettori esperti dei possenti RDBMS con estensione spaziale (PostgreSQL + PostGIS, Oracle Spatial, MySQL, ecc.) o del più leggero e, al tempo stesso, molto versatile SpatiaLite. Analogamente a quest’ultimo, JEQL non richiede l’installazione di un server database e ciò, assieme al fatto che è un linguaggio di scripting, rappresenta un grosso vantaggio in termini di portabilità. Si pensi ad esempio alla replicazione della configurazione di un DBMS a distanza di parecchio tempo oppure alla condivisione di uno script con un nostro collega. Inoltre, anche la velocità di sviluppo e di esecuzione rappresentano dei non trascurabili punti di forza. Naturalmente, esistono anche dei task non coperti da JEQL per i quali un RDBMS è insuperabile.
Quali tipi di dati è in grado di utilizzare? Oltre ai classici tipi Java (interi, stringhe, double) e le date, JEQL supporta anche le geometrie JTS e un vasto repertorio in continua crescita di funzioni spaziali, costruttori di predicati e funzioni di aggregazione, che lo rendono uno strumento particolarmente adatto per il processamento di dataset spaziali. Inoltre, è in grado di accedere in lettura e scrittura a diversi formati di dati (CSV, DBF, SHP, KML) e database compatibili con JDBC (Java Data Base Connectivity).
L’installazione di JEQL è molto semplice: nei sistemi operativi Windows, basta scompattare il pacchetto di installazione (è appena stata rilasciata la versione 0.9) in una cartella con percorso non contenente spazi ([JEQL_HOME]) ed aggiungere nella variabile PATH di sistema il percorso [JEQL_HOME]\bin. Manca, tuttavia, uno script shell o qualcosa di analogo per Linux, che tuttavia non dovrebbe essere difficile da produrre.
L’apprendimento del linguaggio JEQL è abbastanza rapido, soprattutto per chi possiede già dei rudimenti di SQL, ed è facilitato dall’interprete che guida l’utente nell’individuazione e nella correzione degli errori. Il pacchetto di installazione è inoltre corredato da una lista di unit test, ovvero gli stessi esempi basilari utilizzati in fase di collaudo del codice. La documentazione rappresenta invece una delle poche note dolenti, essendo ancora un work in progress, nonostante la presenza di alcuni illuminanti esempi applicativi, diversi post su Lin.ear Th.inking e una mailing list dedicata, oltre alla indiscutibile cortesia e competenza dello sviluppatore.
Attualmente JEQL è rilasciato con doppia licenza: freeware e “commerciale”. Quest’ultima per consentirne l’integrazione all’interno di software commerciali (…e in quelli open source?).

Un esempio applicativo: worldAirRoutes.jql

Nel seguito, si cercherà di fornire un saggio delle potenzialità di JEQL, proponendoci le stesse finalità del post citato in precedenza, ovvero disegnare le rotte aeree a scala globale secondo linee ortodromiche, piuttosto che linee rette, con tutti gli accorgimenti del caso al fine di ottenere una buona resa grafica. Ci cimenteremo passo dopo passo con le fasi di importazione e normalizzazione dei dati di interesse fino al raggiungimento dello scopo. I dati utilizzati nell’applicazione sono:

• airport.dat
• routes.dat
• TM_WORLD_BORDERS_SIMPL-0.3.zip

In particolare, i primi due sono parte degli open data del progetto OpenFlights, rilasciati con Open Database License (Odbl), mentre lo shapefile semplificato dei world borders (a proposito della loro qualità, si consiglia la lettura di questo post e successivi) è rilasciato da thematicmapping.org con licenza Creative Commons CC-SA.
Dopo aver installato JEQL 0.9, creiamo una cartella, vi collochiamo i dati appena scaricati (scompattando naturalmente lo zip file) e vi definiamo inoltre un semplice file di testo (worldAirRoutes.jql) nel quale andremo a scrivere il nostro codice.
Il primo passo dell’applicazione consiste nell’importazione dei dati relativi agli aeroporti (airport.dat), tenendo presente che i file .dat in esame sono in formato CSV:

// Read data from CSV
CSVReader airports file: "airports.dat";
numAirports = select count(*) from airports;
Print "Number of airports to clean: " + val(numAirports);
Print airports limit: 10;

Salviamo lo script, apriamo il prompt dei comandi e collochiamoci nella directory che lo contiene. Per eseguirlo occorre digitare jeql worldAirRoutes.jql e premere il tasto Invio. Successivamente, nel prompt dei comandi comparirà qualcosa del genere:

Number of airports to clean: 6344
col1:String, col2:String, col3:String, col4:String, col5:String, col6:String, col7:String, col8:String, col9:String, col10:String, col11:String
1 Goroka Goroka Papua New Guinea GKA AYGA -6.081689 145.391881 5282 10 U
2 Madang Madang Papua New Guinea MAG AYMD -5.207083 145.7887 20 10 U
3 Mount Hagen Mount Hagen Papua New Guinea HGU AYMH -5.826789 144.295861 5388 10 U
...

La prima cosa che scopriamo è che le colonne della tabella “airports” sono identificate come “col1, col2 … colN”, sebbene il file CSV sia sprovvisto delle relative intestazioni dei campi (per la descrizione dei quali si rimanda a questa pagina), e che i campi sono considerati tutti di tipo String. Procediamo quindi con la normalizzazione dei dati degli aeroporti, selezionando i dati di nostro interesse:

// Select airports data with valid IATA airport code
airports2 = select
  col2 as name,
  col3 as city,
  col4 as country,
  col5 as code,
  col7 as lat,
  col8 as lon
  from airports
  where RegEx.matches(col5,'[A-Z]{3}');

E’ interessante notare che:

• per definire gli alias dei campi di una tabella è utilizzata la parola chiave as, così come avviene solitamente in molte versioni di SQL, tuttavia si tenga presente che in JEQL il suo uso è a discrezione dell’utente;

• nella clausola where è utilizzata la funzione RegEx.matches (campo, espressione regolare) in modo da selezionare mediante una semplice espressione regolare tutti e soli gli aeroporti con codice IATA composto da tre lettere maiuscole, in base allo standard ufficiale dell’International Air Transport Association (IATA).

Occorre, inoltre, verificare la presenza di eventuali codici IATA duplicati, in modo da essere certi dell’univocità del campo “code”, al fine di utilizzarlo poi come chiave esterna nelle operazioni di join con la tabella delle rotte aeree (routes.dat), come si vedrà nel seguito.

// Find duplicate IATA airport codes in airports data
duplicateCodes = select *
  from (select code, count(code) as numOccurrences
    from airports2
    group by code) as duplicates
  where numOccurrences > 1;
Print duplicateCodes;

A fronte di tale verifica, si riscontra un unico codice duplicato (“TCG”). Avvalendoci dell’ausilio della banca dati ufficiale è possibile risolvere tale ambiguità, scartando l’aeroporto di Tocache (Cina) al quale è stato erroneamente attribuito questo codice. A tal fine, la clausola where della prima select diventa:

  where RegEx.matches(col5,'[A-Z]{3}')
  and col3!="Tocache";

E’ dunque possibile commentare il codice relativo alla ricerca dei duplicati (“//” commenta una singola riga, mentre “/* … */” tutto il codice tra essi compreso – non a caso, come nel linguaggio Java) e verificare la bontà dei risultati fin qui ottenuti, visualizzando eventualmente i primi dieci record della tabella “airports2”.

// Check intermediate results
numAirports2 = select count(*) from airports2;
Print "Number of airports without duplicates: " + val(numAirports2);

Omettendo per brevità la visualizzazione del contenuto delle tabelle, otteniamo:

Number of airports without duplicates: 5023

Successivamente, si procede all’importazione e normalizzazione dei dati relativi alle rotte aeree (routes.dat). In particolare, selezioniamo i codici IATA degli aeroporti di origine e destinazione relativi a ciascuna rotta aerea, e li combiniamo in un’unica stringa in modo da poter escludere le rotte duplicate mediante l’operazione di raggruppamento (group by). Da queste stringhe è poi possibile recuperare nuovamente i codici dai quali sono stati ottenuti. Inoltre, definiamo una chiave primaria “rid” tramite la funzione rownum() che restituisce il numero di riga.

// Read data from CSV
CSVReader routes file: "routes.dat";
numRoutes = select count(*) from routes;
Print "Number of routes to clean: " + val(numRoutes);
	
// Select routes data removing duplicates
routes2 = select
  rownum() as rid,
  String.substring(routeCode, 0, 3) as fromCode,
  String.substring(routeCode, 3, 6) as toCode
  from (select
    //col3 as fromCode,
    //col5 as toCode,
    col3+col5 as routeCode
    from routes
    order by routeCode asc) as routesWithDuplicates
  group by routeCode;
	
// Check intermediate results
numRoutes2 = select count(*) from routes2;
Print "Number of routes without duplicates: " + val(numRoutes2);

ottenendo:

Number of routes to clean: 64114
Number of routes without duplicates: 36004

Il task successivo consiste nell’associare ad ogni rotta aerea le coordinate dell’aeroporto di partenza e di quello di destinazione, scartando le coordinate con valore null e validando quelle che non lo sono, facendo ancora una volta ricorso alle espressioni regolari.

// Extract origin airport data
fromAirport = select
  rid,
  fromCode,
  name as fromName,
  city as fromCity,
  country as fromCountry,
  lat as fromLat,
  lon as fromLon
  from routes2
  left outer join airports2
  on routes2.fromCode == airports2.code;
	
// Extract destination airport data
toAirport = select
  rid,
  toCode,
  name as toName,
  city as toCity,
  country as toCountry,
  lat as toLat,
  lon as toLon
  from routes2
  left outer join airports2
  on routes2.toCode == airports2.code;
	
// Join the last two tables (removing records with null coords)
troute = select fromAirport.*, toAirport.* except rid
  from fromAirport
  join toAirport
  on fromAirport.rid==toAirport.rid
  where not Val.isNull(fromLat) and not Val.isNull(fromLon) and not Val.isNull(toLat) and not Val.isNull(toLon);
	
// Check if coords are valid
troute2 = select *
  from troute
  where RegEx.matches(fromLat,'-?((([0-9]|[1-8][0-9])(\.[0-9]*)?)|(90))')
  and RegEx.matches(fromLon,'-?((([0-9]|[1-9][0-9]|1[0-7][0-9])(\.[0-9]*)?)|(180))')
  and RegEx.matches(toLat,'-?((([0-9]|[1-8][0-9])(\.[0-9]*)?)|(90))')
  and RegEx.matches(toLon,'-?((([0-9]|[1-9][0-9]|1[0-7][0-9])(\.[0-9]*)?)|(180))');
	
// Check intermediate result
numRoutes3 = select count(*) from troute2;
Print "Number of routes without duplicates and with valid coords: " + val(numRoutes3);

che ci restituisce:

Number of routes without duplicates and with valid coords: 35475

Un altro aspetto degno di nota è l’utilizzo della parola chiave except nella select della tabella “troute”. Si tratta di un’estensione solitamente assente nei vari linguaggi SQL che ci consente di selezionare tutti i campi di una particolare tabella ad eccezione di un campo.
Infine, selezioniamo i dati da cui poter derivare le geometrie delle rotte aeree e rappresentarle graficamente (da qui in poi, il codice è quello del Dr JTS con qualche piccolo adattamento):

// Convert coords from string to double
trte = select fromCity, toCity,
  Val.toDouble(fromLon) fromLon, Val.toDouble(fromLat) fromLat,
  Val.toDouble(toLon) toLon, Val.toDouble(toLat) toLat
  from troute2;
	
// Split geodetic arcs and calculate lengths
tlines = select fromCity, toCity, line, len
  with {
    line = Geodetic.split180(Geodetic.arc(fromLon, fromLat, toLon, toLat, 2));
    len = Geom.length(line);
  }
  from trte order by len desc;
	
// Interpolate line color and fix line width
tplot = select line,
  Color.interpolate("ffffff", "00aacc", "0000ff", len / 50.0 ) lineColor,
  0.2 lineWidth
  from tlines;
	
// Import shapefile data
ShapefileReader tworld file: "TM_WORLD_BORDERS_SIMPL-0.3.shp";
	
// Select geometries and define line and fill colors
tworldLine = select GEOMETRY, "222222" lineColor from tworld;
tworldFill = select GEOMETRY, "333333" fillColor from tworld;
	
// Plot routes with world landmasses
width = 1800;
Plot width: width height: width / 2
  extent: LINESTRING(-180 -90, 180 90)
  data: tworldFill
  data: tplot
  data: tworldLine
  file: "routes.png";
	
// Plot routes without world landmasses
Plot width: width height: width / 2
  extent: LINESTRING(-180 -90, 180 90)
  data: tplot
  file: "routes_only.png";

Qui è possibile effettuare il download dell’intero script. Infine, si mostrano le due immagini risultanti in cui sono rappresentate le rotte aeree rispettivamente con e senza i world borders. In particolare, le rotte sono disegnate in ordine di lunghezza decrescente, utilizzando un colore interpolato in base alla lunghezza, per cui spiccano le rotte più brevi con colore più chiaro. Anche nella seconda immagine, è possibile rilevare con buona approssimazione i limiti di molte terre emerse.

Come giustamente afferma Martin Davis, dalla densità delle rotte aeree che si percepisce nelle immagini si evince come agli europei piaccia molto volare. Ma neanche quelli delle East Coast scherzano!

Conclusioni

L’esempio applicativo appena mostrato esprime già molte delle potenzialità offerte da JEQL, e i comandi attualmente disponibili sono molto numerosi (basta digitare jeql -man per scoprirlo!). JEQL offre inoltre anche la possibilità di estendere le sue funzionalità tramite un’interfaccia di programmazione (API), non ancora documentata, tramite la quale creare nuovi comandi e funzioni da poter utilizzare all’interno dei nostri script. Non nascondiamo la speranza, in parte malcelata dallo stesso Martin Davis, che un domani si possa usufruire di una doppia licenza open source/commerciale, che permetta di contribuire alla crescita della libreria con nuovi plugin e una migliore documentazione di tutte le caratteristiche offerte.

Ringraziamenti

Desidero ringraziare Giovanni Allegri, non solo per il proficuo scambio di idee avvenuto dietro le quinte, ma anche per aver sollecitato il rilascio della nuova release (0.9) di JEQL, senza la quale non sarebbe stato possibile riprodurre l’applicazione, e naturalmente Martin Davis, il padre di questo formidabile linguaggio, per aver ispirato questo post.

Pensato e scritto da Antonio Falciano

Ingegnere per l’ambiente e il territorio e master in Sistemi Informativi Geografici e Telerilevamento presso l’Università degli Studi della Basilicata. Svolge attività di libera professione, occupandosi principalmente di Sistemi Informativi Territoriali per la PA e occasionalmente di formazione e ricerca. Modellistica ambientale, sistemi di coordinate e cartografia catastale rappresentano alcuni dei suoi interessi legati all’informazione geografica. E' un forte sostenitore di un noto desktop GIS libero spagnolo.