Distance Search WordPress

Kyle comments:

Hey John, thanks for the reply. I would prefer the standard query if thats possible

John Cotton comments:

<blockquote> I would prefer the standard query if thats possible</blockquote>
OK. But to start with just see if this gives you want you want.



global $wpdb;


$user_address = $_POST['input_1'];
$coords = getLatLong($user_address);
$user_lat = $coords['Latitude'];
$user_long = $coords['Longitude'];

$results = $wpdb->get_results("SELECT p.*, pm1.meta_value as lat, pm2.meta_value as lon,
ACOS(SIN(RADIANS($user_lat))*SIN(RADIANS(pm1.meta_value))+COS(RADIANS($user_lat))*COS(RADIANS(pm1.meta_value))*COS(RADIANS(pm2.meta_value)-RADIANS($user_long))) * 3959 AS distance
FROM $wpdb->posts p
INNER JOIN $wpdb->postmeta pm1 ON p.id = pm1.post_id AND pm1.meta_key = 'wpcf-lat'
INNER JOIN $wpdb->postmeta pm2 ON p.id = pm2.post_id AND pm1.meta_key = 'wpcf-lon'
WHERE post_type = 'guides' AND post_status = 'publish'
ORDER BY distance ASC;");



Kyle comments:

Sounds good.

Can I echo the distance from this somehow?

John Cotton comments:



foreach($results as $result) {
echo $result->distance;
}


John Cotton comments:

If you want it as part of the stand query for your page you have have to use a series of filters to:

a) add the distance and lat/lon fields to the output
b) join the postmeta table for each of the meta keys
c) order the results.

It's entirely possible, just messy. I can show you how it works if you want though!

JC

Kyle comments:

Okay great I appreciate that very much, I always like to learn new stuff and my existing query uses the standard way

I am not getting an output with the SQL

here is my current template in full (I made one correction to yours, you had wpcf-lon instead of wpcf-long):

[[LINK href="http://pastie.org/private/fnd87tdgcwcf3xm9qcora"]]http://pastie.org/private/fnd87tdgcwcf3xm9qcora[[/LINK]]

John Cotton comments:


foreach($results as $result) {
setup_postdata( $result );
the_title();
echo $result->distance;
}
}



Kyle comments:

Hmm I'm not getting any output...

John Cotton comments:

After the $wpdb->get_results stick these lines:



echo $wpdb->last_error . '<br/>';
echo print_r( $results, true ). '<br/>';



Kyle comments:

Okay, here is result:

Incorrect parameter count in the call to native function 'RADIANS'
Array ( )

John Cotton comments:

Either $user_lat or $user_long are not equal to anything so the SQL becomes RADIANS().

Can you check that they are being set?

Kyle comments:

okay, I corrected the user_lat user_long mistake.

Error results are now:

Array( )

John Cotton comments:

well that means there is no SQL error but equally no matching results!

Is the post type name correct? You could try removing the whole of the WHERE clause line to check that...

John Cotton comments:

And, of course, I'm assuming there are actually some posts in there with their lat/lon set!!!!

Kyle comments:

I tried removing the WHERE line and still nothing


I did a quick query for this, and got results, so the query should be ok haha

$myposts = new WP_Query( array(
'post_type' => 'guides',
'post_status' => 'publish'
));

while( $myposts->have_posts() ) : $myposts->the_post();
setup_postdata($post);
global $post;

$new_lat = get_post_meta($post->ID, 'wpcf-lat', true);
$new_long = get_post_meta($post->ID, 'wpcf-long', true);

the_title();
echo $new_lat.' '.$new_long;

endwhile;
wp_reset_postdata();

John Cotton comments:

If that WP_Query works, then my SQL should too. There's something wrong somewhere...

So let's strip back the SQL

Try just

SELECT p.* FROM $wpdb->posts p WHERE post_type = 'guides' AND post_status = 'publish'

and then

SELECT p.*, pm1.meta_value as lat, pm2.meta_value as lon
FROM $wpdb->posts p
INNER JOIN $wpdb->postmeta pm1 ON p.id = pm1.post_id AND pm1.meta_key = 'wpcf-lat'
INNER JOIN $wpdb->postmeta pm2 ON p.id = pm2.post_id AND pm1.meta_key = 'wpcf-lon'
WHERE post_type = 'guides' AND post_status = 'publish'


and see what you get. If both of those are blank then something very odd is going on...

John Cotton comments:

Also, can you check in your database that there are actual records in the postmeta table with wpcf-lat and wpcf-long as the keys and that the meta_value column is stored as a float and not some serialized value.

Kyle comments:

Okay this returned the right results:

$results = $wpdb->get_results("SELECT p.* FROM $wpdb->posts p WHERE post_type = 'guides' AND post_status = 'publish'");

foreach($results as $result) {
setup_postdata( $result );
echo get_the_title($result->ID);
}
}

Worth noting, the_title(); returned current page title, not the result title, not sure if that indicates anything


This however, returned nothing:

$results = $wpdb->get_results("SELECT p.*, pm1.meta_value as lat, pm2.meta_value as lon
FROM $wpdb->posts p
INNER JOIN $wpdb->postmeta pm1 ON p.id = pm1.post_id AND pm1.meta_key = 'wpcf-lat'
INNER JOIN $wpdb->postmeta pm2 ON p.id = pm2.post_id AND pm1.meta_key = 'wpcf-lon'
WHERE post_type = 'guides' AND post_status = 'publish'
");

foreach($results as $result) {
setup_postdata( $result );
echo get_the_title($result->ID);
}
}

Checking for serialize values now

John Cotton comments:

Just spotted and error in the SQL - change to this line:

INNER JOIN $wpdb->postmeta pm2 ON p.id = pm2.post_id AND pm2.meta_key = 'wpcf-long'

Kyle comments:

That was it! Okay getting accurate results

John Cotton comments:

<blockquote>That was it!</blockquote>
Apologies - the bane of typing code too quickly.

Kyle comments:

Thanks for taking the time to spot that

Kyle comments:

Even though that took longer than expected, could you help get me started with the filters for adapting this to the standard query?

John Cotton comments:

<blockquote>could you help get me started with the filters for adapting this to the standard query?</blockquote>

Sure it's just the same code broken up into chunks. You then trigger it with a standard WP_Query or query_posts or whatever.


if( true ) {
add_filter( 'posts_fields', 'my_geo_fields' );
add_filter( 'posts_join', 'my_geo_join' );
add_filter( 'posts_orderby', 'my_geo_orderby' );
}


function my_geo_fields( $fields ) {
$user_address = $_POST['input_1'];

$coords = getLatLong($user_address);

$user_lat = $coords['Latitude'];

$user_long = $coords['Longitude'];


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

function my_geo_join( $join ) {
global $wpdb;

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

return $join . $sql;
}

function my_geo_orderby( $orderby ) {
return 'distance ASC');
}



Assuming you stick this in your functions.php what you need to do is change the if(true) logic to determine when these filters kick in (or else they'll be on every one of your queries!!!!).

So perhaps change to

if( isset($_POST['input_1']) ) {

Kyle comments:

Awesome thank you! This is great, I really appreciate it.