I have two data sources already in Sphinx:
source cities {
...
sql_query = SELECT id, city_name, state_name, state_abbr, latitude,
longitude, population FROM cities;
sql_attr_uint = population
sql_attr_float = latitude
sql_attr_float = longitude
...
}
source listings {
...
sql_query = SELECT entry_id, title, url_title, category_names,
address1, address2, city, state, zip, latitude, longitude,
listing_summary, listing_url, extended_info FROM listings;
sql_attr_float = latitude
sql_attr_float = longitude
...
}
Using the PHP Sphinx API I have done searches for matching cities by name and searches for listings within 25 miles of a lat/long without any problem, but now I need to sort of 'join' them... I'd like to be able to:
a) when searching for cities by name, return only cities having listings within 25 miles of them and b) when I'm viewing results for one city (lat/long is known), pull the 3 nearest cities that have listings within 25 miles of them
Is there a way to build a single sphinx search to accomplish these two lookups?
Edit based on comment chain below:
I've updated my cities table to include a field point of type Point and created a spatial index on it:
> describe cities_copy; +-------------+-----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+-----------------------+------+-----+---------+----------------+ | id | mediumint(7) unsigned | NO | PRI | NULL | auto_increment | | city_name | varchar(64) | NO | MUL | NULL | | | state_name | varchar(64) | NO | | NULL | | | state_abbr | varchar(8) | NO | | NULL | | | county_name | varchar(64) | NO | | NULL | | | county_id | smallint(3) unsigned | NO | | NULL | | | latitude | float(13,10) | NO | MUL | NULL | | | longitude | float(13,10) | NO | | NULL | | | population | int(8) unsigned | NO | MUL | NULL | | | point | point | NO | MUL | NULL | | +-------------+-----------------------+------+-----+---------+----------------+ > show indexes from cities_copy; +-------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +-------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | cities_copy | 0 | PRIMARY | 1 | id | A | 23990 | NULL | NULL | | BTREE | | | cities_copy | 0 | city/state | 1 | city_name | A | NULL | NULL | NULL | | BTREE | | | cities_copy | 0 | city/state | 2 | state_abbr | A | 23990 | NULL | NULL | | BTREE | | | cities_copy | 1 | lat/long | 1 | latitude | A | NULL | NULL | NULL | | BTREE | | | cities_copy | 1 | lat/long | 2 | longitude | A | NULL | NULL | NULL | | BTREE | | | cities_copy | 1 | population | 1 | population | A | NULL | NULL | NULL | | BTREE | | | cities_copy | 1 | point | 1 | point | A | NULL | 32 | NULL | | SPATIAL | | +-------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
But when I attempt to update the data to create the points out of lat/long data I get an error:
> update cities_copy set point = Point(latitude, longitude); Cannot get geometry object from data you send to the GEOMETRY field
Is my syntax off here or am I running into some other problem?