MySQL geolocalització

La geolocalització ha pres molta importància en les aplicacions mòbils. Des de fa temps els diversos SGBD han anat incorporant funcions per facilitar el càlcul de distàncies amb les dades provinents del GPS dels mòbils.

Amb aquestes dades i funcions podem fer:

• Localització de punts en mapes.
• Càlcul de distàncies des dels usuaris a altres punts.
• Ubicació d'un mòbil en un país o zona.
• etc.

Introducció

El motor de base de dades MySQL incorpora funcions de càlcul geoespaial. En concret des de les versions:

• Classes geomètriques: POINT, MULTIPOINT, LINE, etc. des de la versió 5.5
• Funcions geoespacials: ST_Distance, ST_Distance_Sphere, etc. des de la versió 5.6(incomplet) / 5.7

Referències:

• GIS data formats v 5.7
• Funcions geoespacials v.5.7
• Presentació sobre distàncies a la superfície terrestre

La classe POINT

Des de MySQL 5.5 s'incorpora un tipus de dades geomètriques genèriques. S'emmagatzemen en un format intern que costa de llegir en una query des de la consola, tot i que la majoria d'interfícies gràfiques com phpmyadmin ens faciliten la visualització. Caldrà utilitzar algunes funcions per visualitzar correctament les dades.

El punt més bàsic es pot crear amb:

mysql> select Point(1,2);
+---------------------------+
| Point(1,2)                |
+---------------------------+
|     ��         �?       @  |
+---------------------------+
1 row in set (0.00 sec)

Les coordenades (1,2) són floats i podem referir-se a qualsevol referència: distància en metres, km, angles, sistema normalitzat, geoespacial, etc.

Si volem veure les coordenades de forma llegible hem d'utilitzar algunes funcions (no les trobareu en MySQL 5.5, només a partir de la 5.6):

mysql> select ST_X(Point(1,2)) , ST_Y(Point(1,2));
+------------------+------------------+
| ST_X(Point(1,2)) | ST_Y(Point(1,2)) |
+------------------+------------------+
|                1 |                2 |
+------------------+------------------+
1 row in set (0.00 sec)

Creant taules amb POINT

Podem crear una taula amb el tipus POINT fàcilment:

mysql> CREATE TABLE geo (comentari TEXT, punt POINT);

I introduir dades com us podeu imaginar:

mysql> INSERT INTO geo VALUES("punt 1",Point(1.313,2.087));

Format WKT

En aquesta referència de MySQL i en d'altres llocs, però, trobareu que s'utilitza un format alternatiu conegut com a WKT (Well-Known Text), i necessitem d'algunes funcions per transformar d'un format a l'altre.

Funcions espaials: http://dev.mysql.com/doc/refman/5.7/en/spatial-function-reference.html

Per exemple, per crear un punt a partir de WKT:

mysql> SELECT ST_GeomFromText('POINT (1.7578125 41.396484375)');

O per visualitzar de forma llegible la nostra taula:

mysql> SELECT AsWKT(punt) FROM geo;
+--------------------+
| AsWKT(punt)        |
+--------------------+
| POINT(1 2)         |
| POINT(1.313 2.087) |
+--------------------+
2 rows in set (0.00 sec)

Càlculs de distància entre punts

Si llegeixes aquesta presentació sobre geolocalització igual et sembla un galimatíes però entendràs ràpidament que el càlcul de distàncies en metres des de dos punts de geolocalització tenen una certa complexitat. Doncs bé, aquestes funcions (des de MySQL v.5.7) ens faciliten aquests càlculs.

La funció de càlcul de distància bàsica en espai euclidià seria ST_Distance:

mysql> SELECT ST_Distance( Point(0,0) , Point(10,10) );
+--------------------------------------+
| ST_Distance(Point(0,0),Point(10,10)) |
+--------------------------------------+
|                   14.142135623730951 |
+--------------------------------------+
1 row in set (0.00 sec)

Si us fixeu, des del punt (0,0) al punt (10,10) la distància seria 10*SQRT(2) (arrel de 2). Ho calculem amb la comanda bc (basic calculator) per comprovar-ho:

$ bc
10*sqrt(2.0000)
14.1420

Coordenades GPS

Arribem per fi al què ens serà realment útil per les nostres aplicacions web amb geolocalització.

Les coordenades GPS es donen en LATITUD i LONGITUD, que son els angles que es tracen a la recta des del centre de la terra al punt (0,0) que és el meridià de Greenwich quan passa per l'Equador (mireu els dibuixos de la presentació).

A partir d'aquests angles calcular la distància entre 2 punts per a una esfera de 40.000 km de perímetre (10.000 km des del pol a l'equador) és un càlcul feixuc, encara més tenint en compte que la Terra no és ben bé esfèrica. Per tant, les funcions següents són una benedicció per poder fer els càlculs. La funció en qüestió és ST_Distance_Sphere(punt1,punt2)

Per comprovar si ens fa bé el càlcul, podríem mirar quant ens dóna la distància del pol nord (0,90º) a l'equador (0,0), agafades al meridià de Greenwich per simplicitat:

mysql> select ST_Distance_Sphere( Point(0,0) , Point(90,0) );
+--------------------------------------------+
| ST_Distance_Sphere(Point(0,0),Point(90,0)) |
+--------------------------------------------+
|                          10007521.40686171 |
+--------------------------------------------+
1 row in set (0.00 sec)

...que son (aprox) els 10.000 km que dèiem abans.

Prenem 2 ciutats, per exemple, Manresa i Viladecans i busquem les seves coordenades. Trobarem que son aquestes:

• Manresa: 41.7285513,1.813047
• Viladecans: 41.3040543,1.9962166

Per calcular la distància en metres (en línia recta, no per carretera!) seria:

mysql> select ST_Distance_Sphere(Point(41.7285513,1.813047),Point(41.3040543,1.9962166));
+----------------------------------------------------------------------------+
| ST_Distance_Sphere(Point(41.7285513,1.813047),Point(41.3040543,1.9962166)) |
+----------------------------------------------------------------------------+
|                                                          51384.64947816014 |
+----------------------------------------------------------------------------+
1 row in set (0.00 sec)

...que venen a ser els 51 km que hi ha entre les dues cities ;)