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