CODEWITHSUNDEEP
mysqlwordpressgeolocationmetadatageocoding
Drew Baker
Drew Baker

Reputation: 14440

Sort WordPress posts by proximity to a latitude and longitude

I have a a bunch of posts in WordPress. These posts are in a category called "Restaurants". Each post has a custom meta key/value stored with it like so:

$post_geocode = get_post_meta($post->ID, 'geocode', true);
// $post_geocode = '34.0510613,-118.244705'

You have to be logged into WordPress to use my site, so each user has a Geocode stored for them, like so:

$user_geocode = get_user_meta( $user->ID, 'geocode', true );
// $user_geocode = '34.043925,-118.2424291'

So I want to be able to get posts and rank them in order to their proximity to the users location.

Ideally it would use the WP Query class. But I am happy to change the way the geocodes are stored if needed.

The best answer would look something like this:

// This is how I manage the type of restaurant you'll see (eg: Mexican, Italian etc)
$cat_query = array(
    'taxonomy'  => 'category',
    'field'     => 'slug',
    'terms'     => $restaurant_category, 
    'operator'  => 'IN'
);

// Go and get the posts
$args = array(
    'post_type'         => 'post',
    'post_status'       => 'publish',
    'posts_per_page'    => -20,
    'geocode'               => $user_geocode, // WOuld love this to work!!!
    'orderby'           => 'geocode', // Would love this to work!!!
    'fields'            => 'ids',
    'tax_query'         => array(
        'relation'      => 'AND',
        $cat_query,
    )
);          

$posts = get_posts($args);

Upvotes: 2

Views: 3332

Answers (2)

postpostmodern
postpostmodern

Reputation: 1986

You're definitely going to need to store your values differently. When I've done this I've used a table something along the lines of

CREATE TABLE `geo_db` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `post_id` int(11) DEFAULT NULL,
  `lat` float DEFAULT NULL,
  `lng` float DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `post_id` (`post_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

You could also use a single column of GEOMETRY type, but this is fine for comparing geo points. Then in your plugin, you'll modify the sql used in WP_Query

function geo_posts_fields( $sql, WP_Query $wp_query ){
    global $wpdb;

    // get your user coords here. explode $user_geocode or store in custom db as well.
    $lat = 45;
    $lng = -122;

    if( $wp_query->query_vars['orderby'] == 'geocode' )
        $sql .= $wpdb->prepare( ", ROUND( ( 3959 * acos( cos( radians(geo_db.lat) ) * cos( radians( %f ) ) 
                               * cos( radians(%f) - radians(geo_db.lng)) + sin(radians(geo_db.lat)) 
                               * sin( radians(%f)))), 1 ) AS `distance` ", $lat, $lng, $lat );

    return $sql;
}

function geo_posts_join( $sql, WP_Query $wp_query ){
    global $wpdb;

    if( $wp_query->query_vars['orderby'] == 'geocode' )
        $sql .= " LEFT JOIN geo_db ON $wpdb->posts.ID = geo_db.post_id ";

    return $sql;
}

function geo_posts_orderby( $sql, WP_Query $wp_query ){
    if( $wp_query->query_vars['orderby'] == 'geocode' )
        $sql = " `distance` ASC, ".$sql;

    return $sql;
}

function geo_posts_request( $sql, WP_Query $wp_query ){
    // just to see whats going on
    var_dump( $sql );
    return $sql;
}


add_filter( 'posts_fields', 'geo_posts_fields', 10, 2 );
add_filter( 'posts_join', 'geo_posts_join', 10, 2 );
add_filter( 'posts_orderby', 'geo_posts_orderby', 10, 2 );
add_filter( 'posts_request', 'geo_posts_request', 10, 2 );

Finally, you'll need to register a query var in order to use orderby as an argument with get_posts, I'd recommend making a new WP_Query

$geo_posts = new WP_Query( array(
    'post_type' => 'any',
    'orderby' => 'geocode'
) );

while( $geo_posts->have_posts() ): $geo_post->the_post(); // etc

Upvotes: 1

david strachan
david strachan

Reputation: 7228

If you save your post data in the database (split your coordinates into two fields latitude and logitude) you can use geodetic form of the law of cosines to find distance between points .Reference

cos(d/R)=cos(lat1)cos(lat2)cos(long2-long1)+sin(lat1)sin(lat2)

Where R = radius of earth (6367 kms,3959 miles)

The following SQL query will provide distance in kms,where $center_lat&$center_lng is the users location.

$postids=$wpdb->get_col( $wpdb->prepare("SELECT  name, lat, lng, 
( 6367 * acos( cos( radians(%f) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(%f) ) + sin( radians(%f) ) * sin( radians( lat ) ) ) ) AS distance FROM mytable HAVING distance < %d ORDER BY distance LIMIT 0 , 5",
$center_lat,$center_lng,$center_lat,$radius));
if ( $postids ) 
{
    foreach ( $postids as $id ) 
    {
       echo $id->name;
       echo $id->distance;
       //etc
    }   
}

The query is based on code I have used here but modified with information found here. I don't have access to WordPress to check.

Upvotes: 2

Related Questions