Geo/Spatial search using Sphinx search and php

14

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…

Share.

About Author

14 Comments

  1. Hi I’m struggling to get this working – my indexes are fine, but for some reason I can’t get it to recognise the GeoAnchor.

  2. You mean the SetGeoAnchor function? Be sure to really send floats to that function. The sphinxapi.php class is relay strict in variable types.

    Also I noticed that if you create an index with only geo data, the search fails. It needs some text field (that is filled on every row), otherwise the search will not return any results. I’ll will update the article with this ‘bug’.

  3. Any tips on sorting by distance? Closest first…

    I manged to get this functional in a Codeigniter application I’ve been working on… Just need to figure out the pagination!

    Thanks!

  4. Hi Mike,

    You can easily sort the results:

    Just add

    $search->SetSortMode ( SPH_SORT_EXTENDED, "@geodist asc");
  5. Does this geodist function search as if it was searching on a globe or is it searching on a flat map?
    Or is this a setting?
    Can’t find this anywhere.

  6. Querying simply id, lat, long results in a empty index when i try this. I fixed it by adding the Field 1 to the field list so it actualy has content to index. It seems it doesn’t count lat and lon as content and thinks the dataset is empty. Has anybody a better solution for this Problem?

  7. This is correct. Like I replied in the second comment, you need a field with some content, otherwise the result is empty.

    This could easily be a fake value. While building the index you can just insert in ‘a’ in a field.

    I don’t know when this is fixed.

  8. Many thanks for this concise example. While my source is much more complicated, its nice that doing the actual filter and sort is so easy.

  9. I’ve been using @geodist search for a while now with great success, however recently something in my environment must have changed because I now always get 0 results.

    I want to use the command line ‘search’ command to see if I can figure out what is wrong.

    My question is how do I perform a geo search from the command line tool (search)? I’m having trouble understanding the terse ‘help’ offered by the search command.

    Terry

  10. Pingback: Geo/Spatial search using Sphinx search and php | My Knowledge Base

  11. Roberto Henríquez on

    I think there is a mistake in the example. Where you say that $_radius is in meters, you probably mean “$_radius is in miles and $circle is in meters”. Otherwise, the conversion

    $circle = (float) $_radius * 1.61;

    would make no sense.

  12. Hi,

    I’m trying to make this work in WordPress using the Sphinx plugin for WordPress. I have all my posts geolocated with meta data and my Sphinx index is configured to use that data. If I do a search in the terminal I can see how the latitude and longitude data is there, for example:

    displaying matches:
    1. document=267965, weight=2, comment_id=0, post_id=133982, ispost=0, iscomment=0, ispage=1, post_type=1, date_added=Tue Sep 4 16:41:45 2012, city_id=2509463, longitude=-0.015075, latitude=0.674252

    I’ve changed the Sphinx plugin for WordPress so it looks where the user is located (I know the city and looking at my database I can have the latitude and longitude easily) and then do the search only around that position. Using your code more or less this way:

    if ( isset($_GET[‘cerca’]) && $_GET[‘cerca’] == ‘1’ && isset( $_SESSION[‘multi_city’] ) ) {
    global $wpdb;
    $sql = ‘SELECT radians(lat) AS lat, radians(lng) AS lng FROM wp_multicity WHERE city_id = %s';
    $myrows = $wpdb->get_results($wpdb->prepare($sql, $_SESSION[‘multi_city’]));
    if (sizeof($myrows) != 0) {
    $_latitude = $myrows[0]->lat;
    $_longitude = $myrows[0]->lng;
    $_radius = 10;
    $circle = (float) $_radius * 1.61; // Miles
    $sphinx->SetGeoAnchor(‘latitude’, ‘longitude’, (float) deg2rad($_latitude), (float) deg2rad($_longitude));
    $sphinx->SetFilterFloatRange(‘@geodist’, 0.0, $circle);
    }
    }

    If I run this code doesn’t find anything, but if I comment the SetGeoAnchor and SetFilterFloatRange lines it works ok, only that it does not do the search in the area I’m interested in.

    My Sphinx is version 0.9.9-release (r2117). Any clue what is happening? Thank you.

  13. Pingback: Sphinx的GEO距离搜索问题备忘 | 阿龙的博客

Leave A Reply