Ask your WordPress questions! Pay money and get answers fast! (more info)

Get nearby posts based on the ACF Google Maps field

  • SOLVED

Hello,

On my site I have a custom post type called "locations". Each post has location meta added to it via the Advanced Custom Fields Google Map field (http://www.advancedcustomfields.com/resources/google-map/).

When viewing a single location post, I would like to display additional "nearby locations" based on distance.

The location meta is stored in array that contains address, lat, lng values.

Is this type of query possible? Please let me know if additional information is needed.

Answers (2)

2015-01-13

Dbranes answers:

It's problematic to have the lat+lng in a serialized array.

You could try to go around that problem with:

/**
* Save the lng and lat values into a seperate post meta values for each post on post update.
*
* @see http://www.wpquestions.com/question/showChronoLoggedIn/id/10406
*/

add_action('acf/update_value', 'wpq_update_lng_and_lat', 99, 3 );

function wpq_update_lng_and_lat( $value, $post_id, $field ) {
if( 'google_map' === $field['type'] && 'mymap' === $field['name'] ) {
update_post_meta( $post_id, 'my_lat', $value['lat'] );
update_post_meta( $post_id, 'my_lng', $value['lng'] );
}
}


where you might have to adjust this to your needs.

Then you could try out the <strong>Haversine</strong> formula on the <em>my_lat</em> and <em>my_lng</em> meta values.

Here are some ideas how you could implement it:

[[LINK href="http://stackoverflow.com/questions/574691/mysql-great-circle-distance-haversine-formula"]]This[[/LINK]], [[LINK href="http://wpquestions.com/question/show/id/7926"]]this[[/LINK]] or [[LINK href="http://stackoverflow.com/questions/20795835/wordpress-and-haversine-formula"]]this[[/LINK]] might help you along the way.

2015-01-13

Kyle answers:

You may ultimately have to add to the pot for a total result here, this can get tricky, but to add to Dbranes post the Haversine formula will look something like when adjusted your query:

add_filter( 'posts_fields','my_geo_fields' );
add_filter( 'posts_join','my_geo_join' );

function my_geo_join( $join ) {

global $wpdb;

$sql = "
INNER JOIN $wpdb->postmeta pm1
ON $wpdb->posts.id = pm1.post_id
AND pm1.meta_key = 'my_lat'
INNER JOIN $wpdb->postmeta pm2
ON $wpdb->posts.id = pm2.post_id
AND pm2.meta_key = 'my_long' ";

return $join . $sql;

}

function my_geo_fields( $fields ) {

$post_lat = get_post_meta( $post_id, 'my_lat' );
$post_long = get_post_meta( $post_id, 'my_long' );

return $fields . " ,
pm1.meta_value as lat,
pm2.meta_value as lon,
ACOS(SIN(RADIANS($post_lat))
*SIN(RADIANS(pm1.meta_value))
+COS(RADIANS($post_lat))
*COS(RADIANS(pm1.meta_value))
*COS(RADIANS(pm2.meta_value)
-RADIANS($post_long)))
* 3959 AS distance";
}


Anthony Moore comments:

Hi Kyle,

Thanks for this example. Now will this essentially replace any query I make on the site?

Let's say I just want to use this to display "nearby locations" as part of a sidebar widget when viewing a locations post, is there a way to have greater control of what queries these are used for?


Kyle comments:

Actually this may be even [[LINK href="http://www.bluefinengineering.com/blog/wordpress-geolocation-query-wp_query-lat-lng-post-metadata"]]here[[/LINK]] it adds a class to use instead of WP_Query called WP_Geo_Query so that you can use it as you need it. You'd need to update the examples there with your custom fields. You'll want to then replace the $distance['lat'] in the search arguments with the values from your current post (global $post). See how that works.


Anthony Moore comments:

This does look promising. I seem to be running into some issues.

This is what I currently have:


//Save the lat and lng values into different custom field
add_action('save_post', 'x_update_lng_and_lat');

function x_update_lng_and_lat( $post_id ) {

$location = get_field(x_location', $post_id );
$lat = $location['lat'];
$lng = $location['lng'];

update_post_meta( $post_id, 'x_location_lat', $lat );
update_post_meta( $post_id, 'x_location_lng', $lng );
}

class WP_Query_Geo extends WP_Query {
var $lat;
var $lng;
var $distance;

function __construct( $args=array() ) {
if( !empty( $args['lat'] ) ) {
$this->lat = $args['lat'];
$this->lng = $args['lng'];
$this->distance = $args['distance'];
add_filter('posts_fields', array($this, 'posts_fields'));
add_filter('posts_groupby', array($this, 'posts_groupby'));
add_filter('posts_join_paged', array($this, 'posts_join_paged'));
}

parent::query($args);

remove_filter('posts_fields', array($this, 'posts_fields'));
remove_filter('posts_groupby', array($this, 'posts_groupby'));
remove_filter('posts_join_paged', array($this, 'posts_join_paged'));
}

function posts_fields($fields) {
global $wpdb;
$fields = $wpdb->prepare(" ((ACOS(SIN(%f * PI() / 180) * SIN(mtlat.meta_value * PI() / 180) + COS(%f * PI() / 180) * COS(mtlat.meta_value * PI() / 180) * COS((%f - mtlng.meta_value) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) AS distance", $this->lat, $this->lat, $this->lng);
return $fields;
}

function posts_groupby($where) {
global $wpdb;
$where .= $wpdb->prepare(" HAVING distance < %d ", $this->distance);
return $where;
}

function posts_join_paged($join) {
$join .= " INNER JOIN uc_postmeta AS mtlat ON (IF(mtmaster.meta_value != uc_posts.ID, mtmaster.meta_value, uc_posts.ID) = mtlat.post_id AND mtlat.meta_key = 'lat') ";
$join .= " INNER JOIN uc_postmeta AS mtlng ON (IF(mtmaster.meta_value != uc_posts.ID, mtmaster.meta_value, uc_posts.ID) = mtlng.post_id AND mtlng.meta_key = 'lng') ";
return $join;
}
}


And then my query looks something like this:


$lat = get_post_meta( get_the_ID(), 'x_location_lat', true );
$lng = get_post_meta( get_the_ID(), 'x_location_lng', true );

$args = array(
'post_status' => 'publish',
'post_type' => 'locations',
'posts_per_page' => 2,
'fields' => 'all',
'lat' => $lat,
'lng' => $lng,
'distance' => 5
);

$foo = new WP_Query_Geo( $args );

if ( $foo->have_posts() ) :
while ( $foo->have_posts() ) : $foo->the_post();
get_the_title();
endwhile;
endif;


However I receive the following error:

WordPress database error: [You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'HAVING distance < 5 ORDER BY wp_posts.post_date DESC LIMIT 0, 2' at line 1]

Could there be an issue with my MySQL version? Or is there an obvious issue with my code. Not too sure if I need to change anything in my posts_join_paged($join) function.


Kyle comments:

Try removing the groupby


Anthony Moore comments:

Possibly getting close, my code now looks like:


class WP_Query_Geo extends WP_Query {
var $lat;
var $lng;
var $distance;

function __construct( $args=array() ) {
if( !empty( $args['lat'] ) ) {
$this->lat = $args['lat'];
$this->lng = $args['lng'];
$this->distance = $args['distance'];

add_filter('posts_fields', array($this, 'posts_fields'));
add_filter('posts_join_paged', array($this, 'posts_join_paged'));
}

parent::query($args);

remove_filter('posts_fields', array($this, 'posts_fields'));
remove_filter('posts_join_paged', array($this, 'posts_join_paged'));
}

function posts_fields($fields) {
global $wpdb;
$fields = $wpdb->prepare(" ((ACOS(SIN(%f * PI() / 180) * SIN(mtlat.meta_value * PI() / 180) + COS(%f * PI() / 180) * COS(mtlat.meta_value * PI() / 180) * COS((%f - mtlng.meta_value) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) AS distance", $this->lat, $this->lat, $this->lng);
return $fields;
}

function posts_join_paged($join) {
$join .= " INNER JOIN wp_postmeta AS mtlat ON (IF(mtmaster.meta_value != wp_posts.ID, mtmaster.meta_value, wp_posts.ID) = mtlat.post_id AND mtlat.meta_key = 'lat') ";
$join .= " INNER JOIN wp_postmeta AS mtlng ON (IF(mtmaster.meta_value != wp_posts.ID, mtmaster.meta_value, wp_posts.ID) = mtlng.post_id AND mtlng.meta_key = 'lng') ";
return $join;
}
}


But I receive the following error:

WordPress database error: [Unknown column 'mtmaster.meta_value' in 'on clause']