Pay money and get answers for your WordPress questions (more info)

Distance Search

  • SOLVED

I would like to be able to order query results by distance. I already have the user submitted address and cpts geocoded, so I just need some help pulling the lat and long for both into an sql function:


The Haversine function to calculate distance (in miles) is something like this:

( 3959 * acos( cos( radians( User Latitude ) )
* cos( radians( CPT Latitude ) )
* cos( radians( CPT Longitude )
- radians( User Longitude ) )
+ sin( radians( User Latitude ) )
* sin( radians( CPT Latitude ) ) ) ) AS distance


The general query is simply for the 'guides' post type that are published. The custom field for Latitude is 'wpcf-lat' and for Longitute is 'wpcf-long' .

The code in place for retrieving the user lat/long is:

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


I've tested that, and the vars are accurate.

So I need to pull those two variables into the cpt query and apply the above formula and spit out the results.

I would like to have distance var returned too

Answers (2)

2013-01-24

John Cotton answers:

Are you happy doing the query with a custom piece of SQL or would you like to go the whole way and filter the standard query?



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.

2013-01-24

Dbranes answers:

Hi, this plugin might be of interest to you:

http://wordpress.org/extend/plugins/wp-geoposts/

<blockquote>- Adds location, latitude, and longitude meta + metaboxes to any content type.
- Provides an easy to use interface for selecting which content types to apply the above meta values. Note: this allows selection of
built in types: page and post as well as any registered custom post types.
- Provides WP_GeoQuery an extended WP_Query class for doing distance based and geo-aware queries.
</blockquote>

ps: it uses the Haversine formula here:

http://plugins.svn.wordpress.org/wp-geoposts/trunk/query.php


Kyle comments:

Hi Dbranes, I didn't see that! Thanks I will definitely take a look