Reply To: Search contents by latitutine, longitude and radius

• ksikora

• November 12, 2020 at 8:36 am

I’m doing something similar and using this DB call. This is using the Haversine formula.

  

global $wpdb;
  $tablePrefix = $wpdb->prefix;
  $query = $wpdb->prepare("SELECT ".$tablePrefix."posts.*,
  a.meta_value street_address,
  b.meta_value city,
  c.meta_value state,
  d.meta_value zip_code,
  e.meta_value phone,
  f.meta_value website,
  g.meta_value latitude,
  h.meta_value longitude,
  ( 3959 * acos( cos( radians('%s') ) * cos( radians( g.meta_value ) ) * cos( radians( h.meta_value ) - radians(%s) ) + sin( radians('%s') ) * sin( radians( g.meta_value ) ) ) ) AS distance
  FROM ".$tablePrefix."posts
  LEFT JOIN ".$tablePrefix."postmeta a ON ".$tablePrefix."posts.ID = a.post_ID AND a.meta_key='street_address'
  LEFT JOIN ".$tablePrefix."postmeta b ON ".$tablePrefix."posts.ID = b.post_ID AND b.meta_key='city'
  LEFT JOIN ".$tablePrefix."postmeta c ON ".$tablePrefix."posts.ID = c.post_ID AND c.meta_key='state'
  LEFT JOIN ".$tablePrefix."postmeta d ON ".$tablePrefix."posts.ID = d.post_ID AND d.meta_key='zip_code'
  LEFT JOIN ".$tablePrefix."postmeta e ON ".$tablePrefix."posts.ID = e.post_ID AND e.meta_key='phone'
  LEFT JOIN ".$tablePrefix."postmeta f ON ".$tablePrefix."posts.ID = f.post_ID AND f.meta_key='website'
  LEFT JOIN ".$tablePrefix."postmeta g ON ".$tablePrefix."posts.ID = g.post_ID AND g.meta_key='latitude'
  LEFT JOIN ".$tablePrefix."postmeta h ON ".$tablePrefix."posts.ID = h.post_ID AND h.meta_key='longitude'
  WHERE ".$tablePrefix."posts.post_type = 'acf-store' AND ".$tablePrefix."posts.post_status = 'publish'
  HAVING distance < '%s'
  ORDER BY distance LIMIT 0 , 100",
              $center_lat,
              $center_lng,
              $center_lat,
              $radius);

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