PhpRiot
News Archive
PhpRiot Newsletter
Your Email Address:

More information

Spatial Indexes: MySQL

Note: This article was originally published at Planet PHP on 20 April 5440.
Planet PHP

Spatial Indexes: MySQL

London, UK Tuesday, April 12th 2011, 09:04 BST

In two previous articles I introduced The spherical Earth model and importing data into SQLite for querying geographical data. In this article we're going to have a look at importing the data into MySQL and finding out how to best store and query spatial data in the databases.

MySQL

MySQL has some support for Spatial Extensions, but it's not particularly useful. For example, there is no way to query anything within the radius around a specific point. Their community pages list a method of implementing it, but it only calculates for a flat Earth model.

Instead, we'll have to implement our own algorithms again. But first of all, let us import the data into MySQL. First we create the MySQL database:

derick@whisky:~$ mysqladmin -u root -p create poi mysql CREATE TABLE poi(id int, type int, lat float, lon float, name char(255), address char(255), cuisine char(64), phone char(18));

And then we take the script from the previous article on importing data, and change the third line from:

$d = ezcDbFactory::create('sqlite://' . dirname(__FILE__) . '/pois.sqlite');

to:

$d = ezcDbFactory::create('mysql://root:root@localhost/poi');

Of course, substitute the username and password (root, root) and the database name (poi) to one that suits yourself. We then run again:

php parseoi.php.txt great_britain_pubs.osm

And check that our import is complete:

mysql SELECT count(*) from poi\G *************************** 1. row *************************** count(*): 28147

Now that we have all the POIs imported, we can query the database. Because MySQL doesn't have working spatial extensions, nor the register-function capabilities from SQLite such as we saw in the previous article, we have to come up with a new solution. The most obvious one is writing a stored procedure for the task, another (non-explored option) would be to write a user defined function. Writing the stored procedure is simple enough; we just have to convert the distance() function to SQL. On the MySQL command line you can enter:

delimiter // CREATE FUNCTION distance (latA double, lonA double, latB double, LonB double) RETURNS double DETERMINISTIC BEGIN SET @RlatA = radians(latA); SET @RlonA = radians(lonA); SET @RlatB = radians(latB); SET @RlonB = radians(LonB); SET @deltaLat = @RlatA - @RlatB; SET @deltaLon = @RlonA - @RlonB; SET @d = SIN(@deltaLat/2) * SIN(@deltaLat/2) + COS(@RlatA) * COS(@RlatB) * SIN(@deltaLon/2)*SIN(@deltaLon/2); RETURN 2 * ASIN(SQRT(@d)) * 6371.01; END//

Fetching all the pubs within a 250 meter radius around 51.5375AN, 0.1933AW is than as easy as running:

mysql SELECT name, address, phone FROM poi WHERE DISTANCE(lat, lon, 51.5375, -0.1933)

With the result being:

| name | address | phone | +-----------------+--------------------------------+-----------------+ | Mrs Betsy Smith | Kilburn High Road 77, NW6 6HY | +44 20 76245793 | | The Cock Tavern | Kilburn High Road 125 | NULL | | The Old Bell | NULL | NULL | | The Westbury | Kilburn High Road 34, NW6 5UA | +44 20 76257500 | +-----------------+--------------------------------+-----------------+ 4 rows in set (0.72 sec)

If we want to also include the distance from the centre point in the result, we need to modify the query to:

mysql SELECT name, DISTANCE(lat, lon, 51.5375, -0.1933) AS dist FROM poi HAVING dist

with as result:

| name | dist | +-----------------+---------------------+ | Mrs Betsy Smith | 0.00825473345748987 | | The Cock Tavern | 0.2420193460511 | | The Old Bell | 0.103123484090313 | | The Westbury | 0.150294300836645 | +------

Truncated by Planet PHP, read more at the original (another 5165 bytes)