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

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.

  • avatar
    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?


  • avatar
    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

  • avatar
    Last edited:
    01/24/13
    2:50pm
    Kyle says:

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

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

  • avatar
    Last edited:
    01/24/13
    2:57pm
    Kyle says:

    Sounds good.

    Can I echo the distance from this somehow?

  • avatar
    Last edited:
    01/24/13
    2:58pm
    John Cotton says:



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

  • avatar
    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

  • avatar
    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

  • avatar
    Last edited:
    01/24/13
    3:18pm
    Kyle says:

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

  • avatar
    Last edited:
    01/24/13
    4:47pm
    John Cotton says:


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

  • avatar
    Last edited:
    01/24/13
    5:05pm
    Kyle says:

    Hmm I'm not getting any output...

  • avatar
    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/>';

  • avatar
    Last edited:
    01/24/13
    5:13pm
    Kyle says:

    Okay, here is result:

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

  • avatar
    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?

  • avatar
    Last edited:
    01/24/13
    5:18pm
    Kyle says:

    okay, I corrected the user_lat user_long mistake.

    Error results are now:

    Array( )

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

  • avatar
    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!!!!

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

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

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

  • avatar
    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

  • avatar
    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'

  • avatar
    Last edited:
    01/24/13
    5:49pm
    Kyle says:

    That was it! Okay getting accurate results

  • avatar
    Last edited:
    01/24/13
    5:50pm
    John Cotton says:

    That was it!

    Apologies - the bane of typing code too quickly.

  • avatar
    Last edited:
    01/24/13
    5:53pm
    Kyle says:

    Thanks for taking the time to spot that

  • avatar
    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?

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

  • avatar
    Last edited:
    01/24/13
    6:39pm
    Kyle says:

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

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.