Find nearby cities with classifieds (businesses)

Question

Find nearby cities with classifieds (businesses)

I have two data sources 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 searched for suitable cities by name and searched for lists within 25 miles of lat / long without any problem, but now I need to somehow "join" them ... I would like to be able to:

a) when looking for cities by name, return only cities that have lists within 25 miles of them and b) when I look at results for one city (latitude / longitude known), pull out the 3 nearest cities that have lists within 25 miles of them.

Is there a way to create a single sphinx search to do these two searches?

Edit based on the comment thread below:

I updated my city table to include a Point field 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 try to update data to create points from 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 disabled or am I facing some other problem?

+2

php mysql sphinx

Ty W 13 oct. At 13:09

source to share

1 answer

Quassnoi · Accepted Answer · 2009-10-13T13:19:10+0000

You need to do the following:

Create an additional field GEOMETRY

to contain Point(Latitude, Longitude)

, replacing latitude and longitude with metric coordinates for flat earth.
Create an index SPATIAL

on this field

Correct the first query:

SELECT  *
FROM    cities cc
WHERE   EXISTS
        (
        SELECT  NULL
        FROM    listings cp
        WHERE   MBRContains(LineString(Point(cc.latitude - 25, cc.longitude - 25), Point(cc.latitude + 25, cc.longitude + 25)), cp.Coords)
                AND GLength(LineString(cc.Coords, cp.Coords)) <= 25
        )

To find out about the three cities closest, run this query:

SELECT  cp.*
FROM    cities cc
CROSS JOIN
        cities cp
WHERE   cc.id = @id
ORDER BY
        GLength(LinePoint(cc.Coords, cp.Coords))
LIMIT 3

however, note that this will not be very efficient if you have many cities.

To make it effective, you need to create a tessellation table (which will cover the surface of the Earth next to you), calculate the order of proximity of the fragments, and join them.

Here's a simple script to demonstrate:

CREATE TABLE t_spatial (id INT NOT NULL PRIMARY KEY, coords Point) ENGINE=MyISAM;

INSERT
INTO    t_spatial
VALUES
(1, Point(0, 0)),
(2, Point(0, 1)),
(3, Point(1, 0)),
(4, Point(1, 1));

SELECT  s1.id, s2.id, GLength(LineString(s1.coords, s2.coords))
FROM    t_spatial s1
CROSS JOIN
        t_spatial s2

Find nearby cities with classifieds (businesses)

More articles: