EDIT This search engine appears quite often, and I wrote an article on it.
http://www.plumislandmedia.net/mysql/haversine-mysql-nearest-loc/
Original publication
Let's start by looking at the Haversin formula once for everyone, putting it in a stored function so that we can forget about its gross details. NOTE. All this decision is in mile laws.
DELIMITER $$ CREATE FUNCTION distance(lat1 FLOAT, long1 FLOAT, lat2 FLOAT, long2 FLOAT) RETURNS FLOAT DETERMINISTIC NO SQL BEGIN RETURN (3959 * ACOS(COS(RADIANS(lat1)) * COS(RADIANS(lat2)) * COS(RADIANS(long1) - RADIANS(long2)) + SIN(RADIANS(lat1)) * SIN(RADIANS(lat2)) )); END$$ DELIMITER ;
Now match the query that searches in the bounding box and then refine the search using our distance function and distance orders
Based on PHP code in your question:
Suppose $radius is your radius, $center_lat , $center_lng is your breakpoint.
$sqlsquareradius = " SELECT post_id, lat, lng FROM ( SELECT post_id, lat, lng, distance(lat, lng, " . $center_lat . "," . $center_lng . ") AS distance FROM wp_geodatastore WHERE lat >= " . $center_lat . " -(" . $radius . "/69) AND lat <= " . $center_lat . " +(" . $radius . "/69) AND lng >= " . $center_lng . " -(" . $radius . "/69) AND lng <= " . $center_lng . " +(" . $radius . "/69) )a WHERE distance <= " . $radius . " ORDER BY distance ";
Pay attention to a few things about this.
The first is the calculation of the bounding box in SQL, not in PHP. There is no good reason for this, with the exception of saving all the calculations in one environment. (radius / 69) - the number of degrees in the radius metric tables.
Secondly, it does not bother with the size of the longitudinal bounding box based on latitude. Instead, it uses a simpler but too large bounding box. This bounding box catches a few extra entries, but the remote measurement gets rid of them. For your typical zip / store search application, the performance difference is not significant. If you searched for many more records (for example, a database of all utility poles), this may not be so trivial.
Thirdly, it uses a nested query to eliminate the distance in order to avoid the need to run the distance function more than once for each element.
Fourth, he orders by distance ASCENDING. This means that zero distance results must be displayed first in the result set. It usually makes sense to list the closest things first.
Fifth, it uses FLOAT , not DOUBLE . There is a good reason for this. The Haversin distance formula is not ideal, because it makes approximation by the earth an ideal sphere. This approximation occurs with approximately the same level of accuracy than epsilon for FLOAT numbers. So DOUBLE is a deceptive numerical excess for this problem. (Do not use this haversin formula for construction work, such as parking drainage, or you will get large puddles for a couple of inches of epsilon, I promise.) This is great for store apps.
Sixth, you will definitely want to create an index for the lat column. If your location table does not change very often, this will help to create an index for your lng column. But your lat index will give you most of your query performance increase.
Finally, I checked the stored procedure and SQL, but not PHP.
Link: http://www.scribd.com/doc/2569355/Geo-Distance-Search-with-MySQL Also, my experience with a bunch of sensors for medical institutions.
--------------- EDIT --------------------
Unless you have a user interface that allows you to define a stored procedure, this is a nuisance. Anyway, PHP allows you to use numbered parameters in a sprintf call, so you can generate an entire nested statement like this. NOTE. You might need% $ 1f, etc. You will need to experiment with this.
$sql_stmt = sprintf (" SELECT post_id, lat, lng FROM ( SELECT post_id, lat, lng, (3959 * ACOS(COS(RADIANS(lat)) * COS(RADIANS(%$1s)) * COS(RADIANS(lng) - RADIANS(%$2s)) + SIN(RADIANS(lat)) * SIN(RADIANS(%$1s)) )) AS distance FROM wp_geodatastore WHERE lat >= %$1s -(%$3s/69) AND lat <= %$1s +(%$3s/69) AND lng >= %$2s -(%$3s/69) AND lng <= %$2s +(%$3s/69) )a WHERE distance <= %$3s ORDER BY distance ",$center_lat,$center_lng, $radius);