I'm aiming to create a feature in my latest project preferably using PHP. When each user signs up they are going to input their postcode. Then hopefully I will be converting this to lat/long using Open Street Map.
Anyway, I want to be able to find out other users located near the current user. I have seen a lot of people using the Haversine formula, however this would mean that the user queried every other user's details to work out the distance. I could cache this but its soon going to become outdated as new users sign up.
What sort of effect would running the following query have on my system?
sql = "SELECT zipcode, ( 3959 * acos( cos( radians( {$coords['latitude']} ) )
* cos( radians( latitude ) ) * cos( radians( longitude )
- radians( {$coords['longitude']} ) )
+ sin( radians( {$coords['latitude']} ) ) * sin( radians( latitude ) ) ) )
AS distance FROM zipcodes HAVING distance <= {$radius} ORDER BY distance";
That's pulled from someone's blog.
I don't have any figures for the signup rate or the amount of users as it's still in development.
I would appreciate any feedback or other methods that I could use to find matching users within a specific radius.
There are GIS and Spatial Extensions to mySql in version 4.1, see here. From the description you will find, that it is used for problems like you have here:
A GIS (geographic information system) stores and looks up objects which have one or more spatial attributes, such as size and position, and is used to process such objects. A simple example would be a system that stores addresses in a town using geographic coordinates. If this rather static data was then combined with other information, such as the location of a taxi-cab, then this data could be used to find the closest cab to a certain location.
It adds several things to MySql like:
Spacial keys and the POINT type:
CREATE TABLE address ( address CHAR(80) NOT NULL, address_loc POINT NOT NULL, PRIMARY KEY(address), SPATIAL KEY(address_loc) );
Conversion routines
INSERT INTO address VALUES('Foobar street 12', GeomFromText('POINT(2671 2500)'));
GIS calculation functions
SELECT c.cabdriver, ROUND(GLength(LineStringFromWKB(LineString(AsBinary(c.cab_loc), AsBinary(a.address_loc))))) AS distance FROM cab c, address a ORDER BY distance ASC LIMIT 1;
(Examples taken from link above)