Wordpress Distance Search SQL

Question

I have two tables, one with all of the postcodes in the UK together with their latitudes and longitudes. The second is a table with store locations.

I need to create a fast search query that returns all stores(posts) within a user specified radius, together with their distances, in order from nearest to furthest away.

The table structure is:

Postcodes Table Fields: Id, Postcode, Latitude, Longitude

Locations Table Fields: Store Name, PostID, Lat, Lng

The search will return the post name and an extract of information relating to that store.

A query that gets the postcode the user submitted, gets the latitude and longitude of that from the Postcodes table, then runs a distance calculation between the Postcodes table latitude/longitude, and each of the Lat/Long in the locations, returning those within the user specified distance is what i imagine is required, but no luck on creating the distance calculations between the two sets of lat/lng.

Distances required are up to 10 miles, up to 20 miles, up to 50 miles, up to 100 miles, and over 100 miles.

Any help much appreciated.

Many thanks,

Dan

Welcome to SO! What have you tried? Where have you failed? Show us some code! — vzwick
You're looking for something called the haversine formula, also known as the great circle distance formula. In the UK, you're far enough from the equator that ordinary planar distance computations won't be usable. — O. Jones

eggyal eggyal · Accepted Answer · 2012-09-16T11:32:15

As @OllieJones commented, you need to use the haversine formula to calculate great circle distance. Adapting from Google Code's Creating a Store Locator with PHP, MySQL & Google Maps in order to cache as much as possible:

ALTER TABLE Postcodes
  ADD tc DOUBLE COMMENT 'cosine of latitude',
  ADD ts DOUBLE COMMENT   'sine of latitude',
  ADD gr DOUBLE COMMENT 'longitude in radians',
  ADD UNIQUE INDEX (Postcode);

CREATE TRIGGER Postcode_insert BEFORE INSERT ON Postcodes FOR EACH ROW SET
  NEW.tc = cos(radians(NEW.Latitude)),
  NEW.ts = sin(radians(NEW.Latitude)),
  NEW.gr =     radians(NEW.Longitude);

CREATE TRIGGER Postcode_update BEFORE UPDATE ON Postcodes FOR EACH ROW SET
  NEW.tc = cos(radians(NEW.Latitude)),
  NEW.ts = sin(radians(NEW.Latitude)),
  NEW.gr =     radians(NEW.Longitude);

UPDATE Postcodes SET tc = NULL; -- trigger will do the rest

CREATE VIEW distance AS
  SELECT s.Id AS PostID, u.Postcode,
         acos(s.tc*u.tc*cos(s.gr-u.gr) + s.ts*u.ts) AS radii
  FROM   Postcodes s, Postcodes u;

Then all you need to do is:

SELECT   Locations.*, 3959 * distance.radii AS miles
FROM     Locations JOIN distance USING (PostID)
WHERE    distance.Postcode = ?
HAVING   miles < ?

See it on sqlfiddle.

Wordpress Distance Search SQL

1 Answers