HavlenaBlog

Do you have objects with GPS coordinates stored in your DB (if not, you can get GPS coordinates simply) and do you want to search among them on the basis of mutual distances? Yes? So it’s you who can appreciate following MySQL procedure.

We can start…

The sample is going to work with this table:

We will create the procedure called gpsdistance with two input numbers mybuilding_id and mydistance.

mybuilding_id represents ID number of our base building in which surroundings we will search further buildings (which are situated inside the distance handed in the second parameter mydistance) (in kilometers!).

Sequentially we will declare these internal variables:

• mylon – longitude of our base building
• mylat – latitude of our base building
• lon1 – 1. longitude limit
• lon2 – 2. longitude limit
• lat1 – 1. latitude limit
• lat2 – 2. latitude limit

Next command loads actual values of our base building into variables mylon and mylat:

Follow calculating of limiting coordinates on the basis of mydistance. Number 111 represents length of 1° of latitude in kilometres (69 miles). The meaning of formulas you can find in the original document (link below).

And engine of the whole sample is a select which searches buildings located from the base building up to the value of mydistance. In addition this script returns column called distance which represent real distance (in kilometres) from the base building. Number 6372.795 is earth radius in kilometres (3956 miles). Again, the meaning of formulas you can find in the original document (link below).

Complete MySQL procedure

Command DELIMITER // set separator on two slashes. It’s happening because the default separator is semicolon. Semicolons are emerged inside the procedure and it would cause the fault during procedure creation. That’s why you should pay attention as long as you create procedure in phpMyAdmin (you will have to set separator different from semicolon – under the SQL command field).

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//

Calling the procedure in PHP

Very important is to know that you can call procedures only through MySQLi layer (which is object-oriented). The procedure can be call by this SQL command: call gpsdistance($mybuilding_id, $mydistance). Here is a short sample:

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);

}

That’s all. I hope the article is useful for somebody

Used literature

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

Matouš Havlena (e-mail, web)

Přečteno 3483x

Publikováno dne 04.11.2009 v %I:%M %p a zařazeno do kategorie MySQL, PHP 5, Tips and tricks. Reakce na článek můžete sledovat přes RSS 2.0. Můžete také zanechat reakci nebo trackback z vašich stránek.