Il tipo di dati geography su SQL Server 2008

Come abbiamo mostrato negli articoli precedenti, una delle novità più significative di SQL Server 2008 riguarda il supporto a nuovi tipi di dati (come hierarchyid e filestream), pensati per venire incontro alle esigenze di chi lavora quotidianamente con i database.

In questo articolo ci occuperemo del tipo di dati geography, che consente di memorizzare la posizione di un punto sulla superficie terrestre utilizzando coordinate di latitudine e longitudine GPS, su cui è anche possibile eseguire calcoli, ad esempio richiedere la distanza tra due punti all'interno di una normale query SQL, per mezzo dei nuovi operatori messi a disposizione.

Il tipo di dati geography è supportato da tutte le versioni di SQL Server 2008, compresa l'edizione Express. Per utilizzarlo con i linguaggi di programmazione .NET, è necessario utilizzare Visual Studio 2008 aggiornato al Service Pack 1.

Utilizzo del tipo di dati geography

Il modo migliore per prendere dimestichezza con la gestione dei dati geografici in SQL Server 2008 è sicuramente partire da un esempio pratico. Eseguiamo innanzi tutto il seguente script SQL all'interno del Management Studio:

CREATE DATABASE GeographyTest
GO

USE [GeographyTest]
GO

CREATE TABLE Cities(
  IDCity INT IDENTITY(1,1) NOT NULL,
  Name VARCHAR(50) NOT NULL,
  Position GEOGRAPHY NOT NULL
) 
GO

In questo script creiamo un database di nome GeographyTest e al suo interno definiamo la tabella Cities. La sua colonna POSITION è di tipo geography: useremo questo campo per memorizzare le coordinate di latitudine e longitudine delle varie città.

Il primo problema che abbiamo, nell'utilizzo dei dati geografici, è recuperare le posizioni esatte di longitudine e latitudine, per poter inserire i valori nella tabella e, ad esempio, provare ad effettuare calcoli sulle distanze. Per questo scopo, possiamo sfruttare il servizio messo a disposizione dal sito Get Lat Lon, che consente di recuperare le coordinate GPS di un punto visualizzato nelle mappe di Google. Con questo supporto, possiamo scrivere qualche dato:

INSERT INTO Cities(Name, Position) VALUES('Pisa', geography::Point(43.71, 10.40, 4326))
INSERT INTO Cities(Name, Position) VALUES('Taggia', geography::Point(43.85, 7.85, 4326))
INSERT INTO Cities(Name, Position) VALUES('Genova', geography::Point(44.42, 8.92, 4326))
INSERT INTO Cities(Name, Position) VALUES('Milano', geography::Point(45.47, 9.17, 4326))
INSERT INTO Cities(Name, Position) VALUES('Roma', geography::Point(41.90, 12.47, 4326))
INSERT INTO Cities(Name, Position) VALUES('Palermo', geography::Point(38.13, 13.34, 4326))
INSERT INTO Cities(Name, Position) VALUES('New York', geography::Point(40.72, -74.01, 4326))
INSERT INTO Cities(Name, Position) VALUES('Dallas', geography::Point(32.84, -96.81, 4326))
INSERT INTO Cities(Name, Position) VALUES('Los Angeles', geography::Point(34.07, -118.26, 4326))
INSERT INTO Cities(Name, Position) VALUES('Rio de Janeiro', geography::Point(-22.89, -43.22, 4326))
INSERT INTO Cities(Name, Position) VALUES('Sydney', geography::Point(-33.84, 151.19, 4326))
INSERT INTO Cities(Name, Position) VALUES('Tokyo', geography::Point(35.70, 139.69, 4326))

Per inserire le coordinate nella tabella, utilizziamo il metodo statico geography::Point, che prende tre argomenti, rispettivamente la latitudine, la longitudine e l'identificativo del sistema di riferimento utilizzato per esprimere le coordinate (SRID). SQL Server utilizza l'identificatore predefinito SRID di 4326.

Il tipo geography internamente è memorizzato in formato binario, quindi, per recuperare i valori di latitudine e longitudine in una query SQL è necessario utilizzare i relativi metodi Lat e Long:

SELECT Name, Position.Lat AS Latitudine, Position.Long As Longitudine
FROM Cities

Come è facile intuire, questo interrogazione produce il seguente risultato:

Eseguire operazioni sui dati geografici

Come accennato prima, poiché i dati geografici sono supportati nativamente dal SQL Server 2008, il database engine mette a disposizione anche una serie di metodi che permettono di effettuare operazioni e calcoli su di essi.

Cominciamo dal caso più semplice, ovvero determinare la distanza tra due città:

DECLARE @origin NVARCHAR(50) = 'Pisa'
DECLARE @dest NVARCHAR(50) = 'New York'

-- Calcola la posizione di origine
DECLARE @g GEOGRAPHY
SET @g = (SELECT Position FROM Cities WHERE Name = @origin) 

-- Calcola la distanza in chilometri
SELECT Position.STDistance(@g) / 1000 AS [Distance (KM)]
FROM Cities
WHERE Name = @dest

In questo esempio, vogliamo calcolare la distanza tra Pisa (@origin) e New York (@dest). Innanzi tutto, individuiamo la posizione della città di origine, recuperando il relativo valore della colonna POSITION. Nella seconda query, poi ,utilizziamo il metodo Position.STDistance, che restituisce la distanza più breve con il punto definito dall'istanza di geography passata come argomento. Eseguendo queste istruzioni, quindi, otteniamo il valore 6658,41485219854, che rappresenta effettivamente la distanza in chilometri da Pisa a New York.

Questo calcolo può essere realizzato anche senza utilizzare il tipo geography, ma richiede di scrivere una lunga formula che utilizza funzioni matematiche e trigonometriche, quindi l'approccio basato su geography risulta di gran lunga più semplice.

Un altro metodo interessante è geography::AsGml, che restituisce la rappresentazione GML (Geography Markup Language) di un'istanza del tipo geography. Ad esempio:

SELECT Position.AsGml() FROM Cities WHERE Name = 'Taggia'

Il cui risultato è un documento XML che contiene le coordinate della città specificata:

<Point xmlns="http://www.opengis.net/gml">
  <pos>43.85 7.85</pos>
</Point>

Questo formato, essendo uno standard, consente lo scambio di informazioni basate su coordinate GPS in tutte le applicazioni che lo supportano.

Conclusioni

Grazie al tipo geography, uno dei nuovi tipi di dati introdotti con SQL Server 2008, possiamo memorizzare latitudine e longitudine di un punto sulla superficie terrestre e sfruttare una serie di metodi per calcolare distanze. Un valido aiuto per chi vuole manipolare informazioni geografiche con agilità e utilizzando formati standard.