Warning: Please do not give out any FTP or ssh credentials to anyone, unless you trust them completely. Giving out login details is dangerous.

If the asker does not get an answer then they have 10 days to request a refund.

$50
Distance Search

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

This question has been answered.

Kyle | 01/24/13 at 2:19pm Edit
Tutorial: How to assign prize money

(28) Responses

See a threaded view of answers?

Warning: Please do not give out any FTP or ssh credentials to anyone, unless you trust them completely. Giving out login details is dangerous.

Last edited:
01/24/13
2:35pm

John Cotton says:
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?

Post reply
Last edited:
01/24/13
3:03pm

Dbranes says:
Hi, this plugin might be of interest to you:

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

- 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.

ps: it uses the Haversine formula here:

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

Post reply
Last edited:
01/24/13
2:50pm

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

Post reply

Last edited:
01/24/13
2:54pm

John Cotton says:

I would prefer the standard query if thats possible

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;");

Post reply

Last edited:
01/24/13
2:57pm

Kyle says:
Sounds good.

Can I echo the distance from this somehow?

Post reply

Last edited:
01/24/13
2:58pm

John Cotton says:





foreach($results as $result) {

echo $result->distance;

}

Post reply

Last edited:
01/24/13
3:01pm

John Cotton says:
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

Post reply
Last edited:
01/24/13
3:13pm

Kyle says:
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):

http://pastie.org/private/fnd87tdgcwcf3xm9qcora

Post reply
Last edited:
01/24/13
3:18pm

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

Post reply

Last edited:
01/24/13
4:47pm

John Cotton says:



foreach($results as $result) {

		setup_postdata( $result );

		the_title();

		echo $result->distance;

	}

}

Post reply

Last edited:
01/24/13
5:05pm

Kyle says:
Hmm I'm not getting any output...

Post reply
Last edited:
01/24/13
5:10pm
John Cotton says:
After the $wpdb->get_results stick these lines:
```
echo $wpdb->last_error . '<br/>';

echo print_r( $results, true ). '<br/>';
```
Post reply
Last edited:
01/24/13
5:13pm

Kyle says:
Okay, here is result:

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

Post reply
Last edited:
01/24/13
5:16pm

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

Can you check that they are being set?

Post reply
Last edited:
01/24/13
5:18pm

Kyle says:
okay, I corrected the user_lat user_long mistake.

Error results are now:

Array( )

Post reply
Last edited:
01/24/13
5:20pm

John Cotton says:
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...

Post reply
Last edited:
01/24/13
5:21pm

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

Post reply

Last edited:
01/24/13
5:32pm

Kyle says:

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();

Post reply

Last edited:
01/24/13
5:37pm

John Cotton says:

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...

Post reply

Last edited:
01/24/13
5:38pm

John Cotton says:
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.

Post reply

Last edited:
01/24/13
5:43pm

Kyle says:

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

Post reply

Last edited:
01/24/13
5:47pm
John Cotton says:
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'
```
Post reply
Last edited:
01/24/13
5:49pm

Kyle says:
That was it! Okay getting accurate results

Post reply
Last edited:
01/24/13
5:50pm

John Cotton says:
That was it!

Apologies - the bane of typing code too quickly.

Post reply
Last edited:
01/24/13
5:53pm

Kyle says:
Thanks for taking the time to spot that

Post reply
Last edited:
01/24/13
6:01pm

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

Post reply

Last edited:
01/24/13
6:11pm

John Cotton says:

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

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']) ) {

Post reply

Last edited:
01/24/13
6:39pm

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

Post reply

This question has expired.

Christianto, Kyle voted on this question.

Current status of this question: Completed

Please log in to add additional discourse to this page.

Warning: Please do not give out any FTP or ssh credentials to anyone, unless you trust them completely. Giving out login details is dangerous.

If the asker does not get an answer then they have 10 days to request a refund.

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

$50 Distance Search

(28) Responses

$50
Distance Search