I have some databases with geo-location data in it, and wanted it displayed on my iPhone using Layar. Doing some research on this, I found a great presentation on Geo/Spatial search in MySQL. Reading this presentation shows that doing a search of geo-locations in a radius around a geo-location, using a mysql version of the Haversine formula is doable, but is not the fastest solution very slow.

In the presentation, Alexander Rubin suggests using Sphinx search for doing the geo/spatial search. I found this the way to go, since I already use sphinx for full-text searching large text tables. Sphinx uses a magic function @geodist for calculation of points of interest (POI) within range. But setup of the source/index and use within PHP is not documented that well.

SO what do you need:

• Some database supported by Sphinx.
• Database filled with longitude / latitude data.

I assume you have a working copy of Sphinx installed and that the searchd listens to port 3312.

To let the Sphinx indexer do it’s magic create a new Sphinx source and index. Add the following code to your sphinx.conf

source geo
{
type                 = mysql
sql_host             = 127.0.0.1
sql_user             = sphinx_user
sql_pass             = sphinx_password
sql_db               = geo_database
sql_port             = 3306
sql_query_pre        = set names utf8
sql_query_pre        = set session query_cache_type=OFF

sql_query            = select id, radians(longitude) as longitude, radians(latitude) as latitude from locations

sql_attr_float       = longitude
sql_attr_float       = latitude

sql_ranged_throttle  = 0

sql_query_info       = select * from locations where id = $id
}

The above fragment creates the Sphinx source. It’s pretty self-explanatory. Adjust the settings to match your MySQL setup.
Note that the sql_query uses the radians function to convert degrees to radians. sql_attr_float tells Sphinx that the longitude/latitude are floats. THis is needed for the magic @geodist function. The sql_query_info is handy if you debugging on the command line.

Now for the Sphinx index.

index geo
{
source                  = geo
path                    = /var/lib/data/geo
docinfo                 = extern
mlock                   = 0
morphology              = none
min_word_len            = 1
charset_type            = utf-8
charset_table           = 0..9, A..Z->a..z, _, a..z, U+410..U+42F->U+430..U+44F, U+430..U+44F
ignore_chars            = U+00AD
html_strip              = 0
enable_star             = 0
}

Copy-paste this fragment to your sphinx.conf. Note that the source should be the set to your just-created source.

When the configuration is in place, build the index files:

indexer --rotate geo

If you create a new index the --rotate is optional, but is harmless. If you want to rebuild the index, the rotate option will send a SIGHUP to searchd, thus restarting the daemon.

With the geo-location data, in radians, in your sphinx index, we need to create a PHP script to get query the dataset.

This is not a full featured php file but only the code for connecting to searchd.

You need a copy of sphinxapi.php in your include_path. This is the PHP API class for Sphinx and is in the Sphinx distribution source file. Get a recent version, because there were some bugs using @geodist in older versions.

<?php
require_once 'sphinxapi.php';

$_longitude = $_GET['longitude'];
$_latitude = $_GET['latitude'];
$_radius = $_GET['radius'];

$search = new SphinxClient();
$search->SetServer("localhost", 3312);
$search->SetMatchMode(SPH_MATCH_ALL);
$search->SetArrayResult(true);
$search->SetLimits(0, 100);
$search->SetGeoAnchor('latitude', 'longitude', (float) deg2rad($_latitude), (float) deg2rad($_longitude));

$circle = (float) $_radius * 1.61;
$search->SetFilterFloatRange('@geodist', 0.0, $circle);

$result = $search->Query('', 'geo');
?>

Longitude and latitude is passed via GET.
$cl->SetLimits limits the result-set to 100.
$cl->SetGeoAnchor set’s the centre location.
Notice that the $_longitude & $_latitude is converted to radians.
$_radius is in meters.

Now apply a filter $cl->SetFilterFloatRange('@geodist', 0.0, $circle); call the @geodist method and only return POI’s within the range of 0 meters to $_range meters.

$result = $cl->Query('', 'geo'); Do the actual query in the geo source, and put the result in $result.

The result is an array, that easy…

Tags: geo, mysql, php, sphinx

This entry was posted on Tuesday, October 20th, 2009 at 23:30 and is filed under php. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.