Home Forums ACF PRO Search contents by latitutine, longitude and radius Reply To: Search contents by latitutine, longitude and radius

  • 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",
         $store_data = $wpdb->get_results($query);