Hledání vzdálenosti GPS souřadnic v MySQL a PHP

Máte v DB uložené objekty s GPS souřadnicemi (pokud ne, GPS souřadnice můžete získat jednoduše) a chcete mezi němi vyhledávat na základě vzájemných vzdáleností? Tak právě vám by se mohla hodit následující MySQL procedura, kterou jsem upravil/opravil a připravil na použití s metrickým systémem.

Tak jdeme na to…

Ukázka bude pracovat s nádledující tabulkou:

Vytvoříme si proceduru s názvem gpsdistance a dvěma vstupními čísly mybuilding_id a mydistance.

mybuilding_id představuje ID číslo výchozí budovy, v jejímž okolí budeme hlegpdat další budovy, které se k ní nacházejí ve vzdálenosti předávané v druhém parametru mydistance (v kilometrech!).

Následně si deklarujeme tyto vnitřní proměnné:

• mylon – zeměpisná délka naší výchozí budovy (longitude)
• mylat – zeměpisná šířka naší výchozí budovy (latitude)
• lon1 – 1. hranice zeměpisné délky
• lon2 – 2. hranice zeměpisné délky
• lat1 – 1. hranice zeměpisné šířky
• lat2 – 2. hranice zeměpisné šířky

Další příkaz načítá aktuální hodnoty naší výchozí budovy do proměnných mylon a mylat:

A následuje vypočítání hraničních souřadnic na základě vzdálenosti zadané v kilometrech ve vstupní hodnotě mydistance. Číslo 111 představuje kolik kilometrů měří přibližně jeden stupeň (1°) zeměpisné šířky (pro míle je to 69). Význam vzorců si můžete dohledat v originálním anglickém dokumentu (odkaz níže).

A motorem celého příkladu je hlavní select vyhledávající budovy, které se nacházejí od výchozí budovy ve vzdálenosti do hodnoty mydistance. Tento skript navíc vrací sloupec s názvem distance, který představuje skutečnou vzdálenost (v kilometrech) od výchozí budovy. Číslo 6372.795 je poloměr země (pro míle by to bylo 3956). Pokud vás zajímá podrobnější význam použitých vzorců, opět vás odkáži na originální dokument (odkaz níže).

Hotová MySQL procedura

Příkaz DELIMITER // nastavuje oddělovač SQL příkazů na dvě lomítka. Děje se tak proto, že implicitně je jako oddělovač nastaven středník. Středníky se však objevují uvnitř procedury, což by způsobilo chybu při vytváření procedury. Proto pozor, pokud budete vytvářet proceduru v phpMyAdmin (budete si muset pod polem pro SQL příkaz nastavit jiný oddělovač než právě defaultní středník).

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
DELIMITER //

CREATE PROCEDURE `gpsdistance` (IN mybuilding_id INT, IN mydistance INT)

BEGIN

DECLARE mylon DOUBLE;

DECLARE mylat DOUBLE;

DECLARE lon1 FLOAT;

DECLARE lon2 FLOAT;

DECLARE lat1 FLOAT;

DECLARE lat2 FLOAT;



SELECT building_latitude, building_longitude INTO mylat, mylon FROM buildings WHERE building_id = mybuilding_id;



SET lon1 = mylon-mydistance/abs(cos(radians(mylat))*111);

SET lon2 = mylon+mydistance/abs(cos(radians(mylat))*111);

SET lat1 = mylat-(mydistance/111);

SET lat2 = mylat+(mydistance/111);



SELECT buildings.*, 6372.795 * 2 * ASIN(SQRT(POWER(SIN((mylat-ABS(building_latitude))*pi()/180/2), 2) +

COS(mylat * pi()/180) * COS(ABS(building_latitude) * pi()/180) * POWER(SIN((mylon - building_longitude) * pi()/180 / 2), 2))) AS distance

FROM buildings WHERE building_latitude IS NOT NULL AND building_longitude IS NOT NULL AND

building_longitude BETWEEN lon1 AND lon2 AND building_latitude BETWEEN lat1 AND lat2 HAVING distance < mydistance ORDER BY distance LIMIT 50;



END//

Volání procedury v PHP

Velmi důležité je vědět, že volat procedury můžete pouze přes vrstvu MySQLi (která je již objektově orientovaná). Samotná procedura se volá SQL příkazem CALL gpsdistance($mybuilding_id, $mydistance). Zde je krátká ukázka:

1
2
3
4
5
6
7
8
9
10
11
12
$link = mysqli_connect('localhost', 'db', 'user', 'passwd');

if (!$link) {

    printf("Can't connect to MySQL Server. Errorcode: %s\n", mysqli_connect_error());

    exit;

}

if ($result = mysqli_query($link, "call gpsdistance($mybuilding_id, 5)")) {

    while($row = mysqli_fetch_array($result)) {

        ...

    }

    mysqli_free_result($result);

    mysqli_close($link);

}

A to je vše, snad vám článek byl k nečemu užitečný

Použitá literatura

• RUBIN, Alexander. Geo/Spatial Search with MySQL. Scribd [online]. 2008 [cit. 2009-11-04]. Dostupný z WWW: <http://www.scribd.com/doc/2569355/Geo-Distance-Search-with-MySQL>.