Geolocalització i càlcul de distancies entre dues coordenades amb Mysql

Aquests darrers dies estic acabant de pol·lir un aplicació que treballa sobre coordenades de geolocalització. He tingut la necessitat de fer el càlcul de distància entre dues coordenades, i desprès de buscar una mica d’informació i recordar alguns conceptes de matemàtiques he pogut confeccionar un petita formula per poder calcular la distancia.

Per exemple, si volem calcular la distancia entre Barcelona i Begur, el primer que necessitem conèixer és la latitud i longitud de cada una d’aquestes dues poblacions.

Barcelona        : (41.385589 / 2.168745)
Begur                  : (41.951670 / 3.206115)

Saben les coordenades podem aplicar la següent formula :

((ACOS(SIN(41.385589 * PI() / 180) * SIN(41.951670* PI() / 180) + COS(41.385589 * PI() / 180) *  COS(41.951670* PI() / 180) * COS((2.1687453.206115) * PI() / 180)) * 180 / PI()) * 60 * 1.1515)

El resultat d’aquest formula ens donarà 66.30 milles de distancia entre les dues poblacions. Podem convertir les milles amb Km amb el factor de conversió de 1 milla = 1.609344 Km. Si el resultat el multipliqueu per  1.609344 tindrem com a resultat 106.70 Km.

taller informatic - annubis - Geolocalitzacio entre dos punts amb MYSQL

Si tenim una taula de MYSQL amb les poblacions i les corresponents coordenades de geolocalització, podem crear una consulta de SQL que ens faci ja el càlcul directament entre les dues poblacions.

Comencem amb fer una consulta simple perquè puguem recuperar en una única fila les coordenades de Barcelona i Begur :

Select coord1.poblacio, coord1.lat, coord1.lng, coord2.poblacio, coord2.lat, coord2.lng From poblacions as coord1, poblacions as coord2 where coord1.poblacio=’barcelona‘ and coord2.poblacio=’begur

Podeu observar en la consulta que faig dos alias (coord1, coord2) que apunten a la mateixa taula i només volem la població de Barcelona i Begur. El següent pas serà aplicar la formula per poder calcular la distancia :

Select (((ACOS(SIN(coord1.lat * PI() / 180) * SIN(coord2.lat * PI() / 180) + COS(coord1.lat * PI() / 180) *  COS(coord2.lat * PI() / 180)  *  COS((coord1.lngcoord2.lng) * PI() / 180)) * 180 / PI()) * 60 * 1.1515)) * 1.609344  From poblacions as coord1, poblacions as coord2 where coord1.poblacio=barcelonaand coord2.poblacio=begur

En aquest cas he substituït les constants per els valors de la base de dades. Això ens dona la llibertat de modificar només les poblacions i calcular la distancia entre qualsevol població.

Per acabar-ho de “rematar” podem  fer una sentencia de SQL per calcular la distancia entre una població i la resta. L’exemple seria :

Select coord2.poblacio,(((ACOS(SIN(coord1.lat * PI() / 180) * SIN(coord2.lat * PI() / 180) + COS(coord1.lat * PI() / 180) * COS(coord2.lat * PI() / 180) * COS((coord1.lngcoord2.lng) * PI() / 180)) * 180 / PI()) * 60 * 1.1515)) * 1.609344  From poblacions as coord1, poblacions as coord2 where coord1.poblacio=barcelona

En aquest cas, ens donaria la distancia entre Barcelona i la resta de poblacions que tinguem dins de la nostra base de dades.

Per facilitar-vos la feina us podeu descarregar  aquest ZIP amb una copia SQL de totes les poblacions de Catalunya amb la seva geolocalització.