Create a gist now

Instantly share code, notes, and snippets.

What would you like to do?
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