CODEWITHSUNDEEP
sqlwordpresscookiesgeolocationmeta-key
Zoe
Zoe

Reputation: 21

Wordpress SQL query determining geolocation and returning relevant results

I am trying to put together a wordpress SQL query that will return results relevant to the user's location.

A geolocation latitude and longitude cookie gets set when the user first visits the site. The 2 cookies are then read back into php variables on a later page: 

$lat = $_COOKIE["lat"];  $long = $_COOKIE["long"];

The following sql code is then run:

$locations = "
SELECT P.ID, P.post_title, P.post_content,
    MAX(IF(PM.meta_key = 'lat', PM.meta_value, NULL)) AS lat,
    MAX(IF(PM.meta_key = 'long', PM.meta_value, NULL)) AS long,
    MAX(IF(PM.meta_key = 'contact_email', PM.meta_value, NULL)) AS contact_email
FROM wp_posts AS P
LEFT JOIN wp_postmeta AS PM on PM.post_id = P.ID
WHERE P.post_type = 'pickup_location' and P.post_status = 'publish'
GROUP BY P.ID
ORDER BY P.post_title ASC;";
$result_locations = $wpdb->get_results($locations);

Everything is working as it should, so far... the cookies are being set and being read. The Query is returning exactly what it should. But I'd like to add the following to my Query to further narrow down the results of the Query.

AND WHERE acos(sin('.$lat.') * sin('lat') + cos('.$lat.') * cos('lat') * cos('long' - ('.$long.'))) * 6371 <= 200;

Upvotes: 0

Views: 235

Answers (2)

vijay pancholi
vijay pancholi

Reputation: 154

/* it is working sql query for search post */

$lat = $_COOKIE["lat"];

$long = $_COOKIE["long"];

$distance_radius = '50'; // set miles distance then convert into km

$miles_to_km = $distance_radius * 1.609344;

$locations = 'SELECT *,(((acos(sin(('.$lat.'*pi()/180)) * sin((lat*pi()/180))+cos(('.$lat.'*pi()/180)) * cos((lat*pi()/180)) * cos((('.$long.'- lng)*pi()/180))))*180/pi())*60*1.1515) as distance
FROM wp_posts, tbl_post_lat_lng WHERE wp_posts.ID = tbl_post_lat_lng.post_id AND wp_posts.post_status = "publish" AND wp_posts.post_type = "pickup_location" AND distance <= '.$miles_to_km.' ORDER BY wp_posts.post_title ASC';

$result_locations = $wpdb->get_results($locations);
foreach ($result_locations as $key ) 
      {
          $results[] = $key->ID;
          $results[] = $key->post_title;
          $results[] = $key->post_content;
      }

      return $results;  // return array with post id,post_title,post_content

Upvotes: -1

Zoe
Zoe

Reputation: 21

Have solved the problem. Here it is for anyone else struggling with geolocation and sql and wordpress and multiple meta_keys.

$lat = $_COOKIE["lat"];
$long = $_COOKIE["long"];
$dist = 200; // This is the maximum distance (in km) away from $lat, $long in which to search
$query = "SELECT
    rl.ID,
    rl.post_title,
    ROUND(6367*2*ASIN(SQRT(POWER(SIN(($lat-lat)*pi()/180/2),2)+
    COS($lat*pi()/180)*COS(lat*pi()/180)*
    POWER(SIN(($long-lng)*pi()/180/2),2))),3) AS distance
FROM
    wp_posts rl
    INNER JOIN (SELECT post_id,CAST(meta_value AS DECIMAL(11,7)) AS lat FROM wp_postmeta lat WHERE lat.meta_key='lat') lat ON lat.post_id = rl.ID
    INNER JOIN (SELECT post_id,CAST(meta_value AS DECIMAL(11,7)) AS lng FROM wp_postmeta lng WHERE lng.meta_key='long') lng ON lng.post_id = rl.ID
WHERE
    rl.post_type='pickup_location' AND rl.post_status='publish' 
    HAVING distance <$dist
ORDER BY
    distance ASC LIMIT 2; ";

$result_locations = $wpdb->get_results( $query );

Upvotes: 0

Related Questions