Skip to content

Instantly share code, notes, and snippets.

@displaynone
Created February 15, 2017 18:49
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
Star You must be signed in to star a gist
Save displaynone/26f52a29730d2094a31a39b2399b9c07 to your computer and use it in GitHub Desktop.
Geolocalization for WordPress queries
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment