Hi
The only way I know is to create a MySQL procedure:
CREATE PROCEDURE geodist (IN mylat decimal(18,12), IN mylon decimal(18,12), IN dist float)
BEGIN
declare lon1 float;
declare lon2 float;
declare lat1 float;
declare lat2 float;
set lon1 = mylon-dist/abs(cos(radians(mylat))*69);
set lon2 = mylon+dist/abs(cos(radians(mylat))*69);
set lat1 = mylat-(dist/69);
set lat2 = mylat+(dist/69);
SELECT p.*, 3956 * 2 * ASIN(SQRT( POWER(SIN((mylat -lat.meta_value) * pi()/180 / 2), 2) +COS(mylat * pi()/180) * COS(lat.meta_value * pi()/180) *POWER(SIN((mylon - lon.meta_value) * pi()/180 / 2), 2) )) as distance FROM wp_posts as p, wp_postmeta lat, wp_postmeta lon WHERE p.ID = lat.post_id and lat.meta_key = 'latitude' and p.ID = lon.post_id and lon.meta_key = 'longitude' and lon.meta_value between lon1 and lon2 and lat.meta_value between lat1 and lat2
and post_status = 'publish'
having distance < dist ORDER BY Distance;
END
Supposing you have a post meta with key ‘latitude’ and other one with key ‘longitude’
Then you should call the procedure:
global $wpdb;
$posts = $wpdb->query($wpdb->prepare('call geodist(?, ?, ?)', $lat, $lon, $dis));
I didn’t test, I just port from a CodeIgniter project to WordPress, but it should
Hope it works
Thanks for your response. It is very useful.
One more thing: what are those coded numbers inside the procedure (like 18,12 or 69 or 3965)?
If as I presume are algorithmic constants, is there any documentation about it?
I’ll probably have to convert in other languages.
Hi
They are constants, I don’t know their meaning, I took the query from internet long time ago
Thank you very much!
Very helpful 🙂
As I know something of geodetics, I can answer. There’s roughly 69 miles per degree latitude or longitude. Because the earth is an ellipsoid and not spherical, this is a gross approximation, but close enough for nearly any application outside of land surveys. Even the earth being an ellipsoid is an approximation. Survey level accuracy is extremely complicated and unwarranted outside of surveying applications.
3956 is the earth’s approximate radius in miles.
Anyone wishing to convert distance units would to know this, so it’s not just an idle curiosity question.
decimal(18,12) specifies floating point accuracy in SQL. 18 total places, 12 of them fractional.
Inquiring minds need to know 🙂
In case someone is interested I developed this code that I’ve tested on MySQL, thanks to previous suggestions.
$sql = "SELECT *, (
6373000 * acos (
cos ( radians( $latitude ) )
* cos( radians( latitude ) )
* cos( radians( longitude ) - radians( $longitude ) )
+ sin ( radians( $latitude ) )
* sin( radians( latitude ) )
)
) AS distance
FROM positions
HAVING distance < $radius";
Where $latitude, $longitude are the input coordinates and $radius is the radius of the area selected.
@dariopellegrini are you using the latitude and longitude from the post meta data? I see that you have two variables $latitude and $longitude.
