Date de tip spațial în SQL Server

Diana Muntea
Software Developer @ Yardi România

PROGRAMARE

Datele spațiale sunt folosite pentru a reprezenta informații despre locația și forma obiectelor geometrice. Aceste obiecte pot fi centrul unei locații, reprezentat sub forma unui punct, sau obiecte complexe: drumuri, râuri, orașe sau țări.

Începând din 2008 suita de produse SQL Server de la Microsoft oferă suport pentru datele geospațiale. Acest lucru permite stocarea datelor în tabele sub formă de puncte,linii şi poligoane. De asemenea, oferă atât o gamă largă de funcții pentru manipularea lor, cât şi indecși spațiali pentru a permite o rulare eficientă. În SQL Server datele spațiale pot fi de două tipuri:

Geometrice (geometry) - date reprezentate într-un sistem euclidian (plan, 2D),
Geografice (geography) - date care țin cont de curbura Pământului și sunt reprezentate într-un sistem elipsoidal (3D sau 4D).

Ambele tipuri de date sunt implementate folosind .NET common language runtime (CLR). Cele două tipuri se comportă de cele mai multe ori similar, dar între ele există totuși câteva diferențe:

Modul în care sunt conectate două puncte - pentru datele geometrice reprezintă o linie dreaptă în timp ce pentru cele geografice este un arc circular.
Măsurătorile spațiale - în sistemul planar distanța este măsurată folosind aceeași unitate de măsură în care sunt exprimate şi coordonatele punctelor, iar distanța, ca număr de unități, va fi mereu aceeași indiferent de unitatea folosită. În sistemul elipsoidal, care ține cont de curbura Pământului, coordonatele punctelor sunt exprimate folosind latitudinea și longitudinea, în timp ce pentru distanță sau suprafață se folosesc de obicei metri. Unitatea de măsură depinde și de identificatorul SRID.
Orientarea datelor spațiale - Orientarea nu este importantă pentru datele de tip geometric. În schimb datele geografice nu au nici o valoare dacă nu le specificăm orientarea, pentru că nu vom ști dacă sunt în emisfera nordică sau sudică. În SQL Server 2014 orice instanță de datele geografice trebuie să fie cuprinsă într-o singură emisfera. De asemenea și rezultatul operațiilor dintre două date de tip geografic (intersecție, uniune, diferență) trebuie să fie definit într-o singură emisferă.

SRID - Spatial Reference Indentifiers

Este un identificator care corespunde unui sistem spațial de referință și unui anumit tip de elipsoid folosit pentru desenarea hărților. Identificatorul este definit de standardul European Petroleum Survey Group (EPSG). O coloană poate conține date spațiale cu SRID diferite, dar nu se pot realiza operații între date care nu au același SRID, și nu sunt bazate pe aceeași unitate de măsură și aceeași proiecție. Cea mai comună unitate de măsură este metrul sau metrul pătrat. Pentru datele de tip geometric valoarea implicită pentru SRID este zero, iar pentru cele de tip geografic este 4326 (acesta este folosit și de către Google Maps API).

Tipuri de obiecte posibile pentru datele geometrice și geografice

Imagine MSDN

SQL Server ne pune la dispoziție mai multe tipuri de funcții și metode pentru manipularea datelor de tip spațial : pentru importarea datelor (STGeomFromText, STGeomFromWKB), pentru a realiza diferite tipuri de operații(STContains, STOverlaps, STUnion, STIntersection) sau pentru a face diverse măsurători (STArea, STDistance), inclusiv pentru a determina cel mai apropiat vecin(STDistance(\@me)) . Începând cu SQL Server 2012 este definită şi o formă geometrică numită FullGlobe, ea reprezintă un poligon care acoperă tot globul pământesc. Acest poligon are o suprafață, dar nu are margini.

Exemple

Date Geometrice

CREATE TABLE myTable (
    id int IDENTITY (1,1),
    geometryData geometry, 
GO

INSERT INTO myTable (geometryData)
VALUES (geometry::STGeomFromText('LINESTRING (100 100, 20 180, 180 180)', 0));

INSERT INTO myTable (geometryData)
VALUES (geometry::STGeomFromText('POLYGON ((0 0, 150 0, 150 150, 0 150, 0 0))', 0));
GO

SELECT @geom1 = geometryData FROM myTable WHERE id = 1;
SELECT @geom2 = geometryData FROM myTable WHERE id = 2;
SELECT @result = @geom1.STIntersection(@geom2);

Date Geografice

CREATE TABLE myTable (
    id int IDENTITY (1,1),
    geographyData geography, 
GO

INSERT INTO myTable (geographyData)
   VALUES (geography::STPolyFromText('POLYGON((-73.9998722076416 40.726185523600634,-74.00708198547363 40.73860807461818,-73.99824142456055 40.7466717351717,-73.97326469421387 40.74628158055554,-73.97309303283691 40.7269010214160, -73.9998722076416 40.726185523600634))', 4326));

Ce tip de date ar trebui să aleg pentru aplicația mea (geometry vs. geography)?

Tipul de date ales depinde de aplicație și de scopul ei. Din punct de vedere al stocării datelor nu este nici o diferență între cele două tipuri de date spațiale. În schimb, dacă ne uităm la performanță, interogările pe date de tip geometric sunt mult mai rapide. În final cel mai important argument este funcționalitatea. Dacă avem o aplicație în care dorim să realizăm măsurători între diverse locații sau trebuie să ținem cont de forma Pământului, va trebui să folosim date geografice. În alte cazuri, în care dorim doar să vizualizăm diverse poligoane, datele geometrice s-ar putea să fie suficiente.

Aplicații

Radius search

Să presupunem că avem o colecție de puncte definite prin latitudine şi longitudine care reprezintă diverse locații. Acest tip de căutare constă în desenarea unui cerc, definit de un punct central şi o rază reprezentată într-o unitate de măsură (metri). În acest caz nu putem folosi decât date geografice, criteriul de căutare fiind distanța dintre puncte.

Varianta optimă ar fi să salvăm punctele folosind trei coloane: latitudine, longitudine și punctul geografic. În acest fel , înainte de aplica filtrarea spațială, putem filtra datele folosind bounding box-ul cercului.

Punctul geografic

 geoPoint = geography::STGeomFromText('POINT (-96.8501 32.7639)', 4326)

SELECT * from myTable
WHERE latitude < 32.7871617669569 
AND latitude > 32.7500254131114 
AND longitude < -96.8143320623701 
AND longitude > -96.8584966119462 
AND geoPoint.STDistance(
  geography::STGeomFromText(
  'POINT(-96.836414337158146 32.768593590034193)',
   4326)) <= 2067