MySQL: GIS e dati geospaziali

Il supporto introdotto nelle versioni più recenti di MySQL ai Geographic Information Systems (GIS) racchiude un insieme di strumenti concepiti per descrivere, salvare, manipolare e analizzare i dati spaziali o, più propriamente, i dati geografici. Questi strumenti vengono adoperati dagli sviluppatori per dare all'utente la possibilità di accedere a informazioni spaziali di qualsiasi tipo, come per esempio la ricerca di luoghi di interesse a lui più vicini. La possibilità di interagire con i suddetti dati permette agli utenti di effettuare ricerche interattive e dinamiche, tramite il semplice uso di una qualsiasi mappa geografica.

In questo articolo presenteremo come caso di studio la ricerca dei primi cinque ristoranti più vicini alla nostra posizione, in modo da mostrare le potenzialità dei GIS. Supporremo di sfruttare una installazione di MySQL di versione superiore alla 5.6, poiché è  da questa versione in poi che è stato introdotto il supporto a GIS.

OpenStreetMap Data

Per testare i GIS abbiamo bisogno di dati particolari, nello specifico di dati geografici o di mappe cittadine. In nostro aiuto viene il progetto OpenStreetMap, che fornisce una raccolta di dati geografici relativi all'intero globo, a cui è possibile accedere gratuitamente.

Per il nostro esempio, scarichiamo la mappa di New York a questo link.
In questo file saranno presenti degli script che automaticamente ci permetteranno di creare un database MySQL per poter gestire i dati contenenti tutte le coordinate geografiche della città di New York. Questi script racchiudono comandi Perl e per tale motivo è necessario avere installato un interprete Perl per poterli eseguire.
Lanciamo quindi i seguenti comandi per creare il database che ospiterà i nostri dati:

mysql -e "create database nyosm"
bunzip2 new-york.osm.bz2
./bulkDB.pl new-york.osm nyosm

Eseguiti questi comandi, il nostro database pieno di dati geografici su New York è pronto per l'uso. I dati geografici includeranno informazioni relative a luoghi come strade e attività commerciali.
Due delle tabelle più importanti che saranno generate dal precedente script sono le seguenti:

CREATE TABLE node_tags (
    id bigint NOT NULL, -- primary key part 1/3; references nodes(id,version) part 1/2
    version bigint NOT NULL, -- primary key part 2/3; references nodes(id,version) part 2/2
    k VARCHAR(255) DEFAULT '', -- primary key part 3/3
    v VARCHAR(255) DEFAULT ''
);
CREATE TABLE nodes (
    id bigint NOT NULL, -- primary key part 1/2
    latitude integer NOT NULL,
    longitude integer NOT NULL,
    changeset_id bigint NOT NULL, -- references changesets(id)
    visible BOOLEAN NOT NULL,
    `timestamp` timestamp ,
    tile bigint NOT NULL,
    version bigint NOT NULL -- primary key part 2/2
);

Queste due tabelle racchiudono il cuore di OpenStreetMap.
La tabella nodes è una particolare tabella che si occuperà di memorizzare i nodi di una mappa, cioè gli elementi chiave di OpenStreetMape, definiti da latitudine, longitudine e da un ID. I nodi sono adoperati per calcolare le distanze geografiche o per tracciare percorsi.
La tabella node_tags si affianca a quella nodes e definisce per questa un tag. Un tag è composto da una chiave (key, abbreviato in k) e da un valore (value, abbreviato in v) e vengono adoperati per taggare un nodo, e quindi dargli una certa caratterizzazione, come per esempio quella di "ristorante".

Calcolare la distanza tra due nodi

Per calcolare la distanza tra due punti, sappiamo che è necessario utilizzare la formula della distanza euclidea. Quando, però, le distanze da tenere in considerazione sono abbastanza grandi, è necessario considerare anche la curvatura della Terra, e per tale motivo la distanza euclidea non va più bene. A questo proposito, ci viene in aiuto la suite GIS di MySQL, che ci offre una funzione che implementa la formula dell'emisenoverso (nota in inglese come haversine formula), che tiene in considerazione la curvatura terrestre per calcolare la distanza tra due punti.

ST_Distance_Sphere(pt1, pt2 [,radius])

La funzione ST_Distance_Sphere implementa proprio questa formula, richiedendo due punti ed un raggio, che se non specificato è, di default, quello della Terra.

Bounding Box

Per poter cercare i ristoranti più vicini, abbiamo bisogno di impostare un'area di ricerca, e possiamo adoperare la funzione ST_MakeEnvelope, fornita da MySQL GIS per dare vita a un rettangolo (o bounding box) in cui andremo a vedere se sono presenti dei ristoranti.

ST_MakeEnvelope(pt1, pt2)

La funzione richiede come parametri due punti (gli estremi diagonalmente opposti del rettangolo), dai quali si darà vita alla bounding box grazie ai segmenti verticali e orizzontali che si verranno a tracciare.

La prima query geografica

Mettiamo insieme le conoscenze che abbiamo appena visto e, impostando un'area di ricerca (bounding box) di una certa ampiezza (offset), cerchiamo i ristoranti (restaurant) più vicini alla nostra posizione (definita dalle coordinate X e Y):

SELECT id, ST_Distance_Sphere(Point(X, Y), geom) as distance_in_meters, tags, ST_AsText(geom)
FROM nodes
WHERE ST_Contains( ST_MakeEnvelope(
                    Point((X + (Offset)), ( Y + (Offset))),
                    Point((X - (Offset)), ( Y - (Offset)))
                 ), geom )
      AND match(tags) against ("+restaurant" IN BOOLEAN MODE)
ORDER BY distance_in_meters LIMIT 5

Il risultato della query sarà un insieme di coordinate, rappresentanti i ristoranti a noi più vicini. Se tutto è andato per il meglio, inserendo queste coordinate su Google Maps, potremo verificare la correttezza della nostra ricerca.