

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:
`building_id` INT NOT NULL AUTO_INCREMENT ,
`building_street` VARCHAR(200) NULL ,
`building_street_nr` VARCHAR(20) NULL ,
`building_city` VARCHAR(200) NOT NULL ,
`building_latitude` VARCHAR(30) NULL ,
`building_longitude` VARCHAR(30) NULL ,
PRIMARY KEY (`building_id`) )
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 buildingmylat
– latitude of our base buildinglon1
– 1. longitude limitlon2
– 2. longitude limitlat1
– 1. latitude limitlat2
– 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).
SET lon2 = mylon+mydistance/abs(cos(radians(mylat))*111);
SET lat1 = mylat-(mydistance/111);
SET lat2 = mylat+(mydistance/111);
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).
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;
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
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>.