Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

I'm planning a custom WordPress theme where the Custom Post Type (CPT) will have latitude and longitude coordinate as it's meta value. The latitude and longitude will be displayed as a Marker in a Google Map.

So far I don't have any problem in showing the Google Map and the CPT as it's Marker. That is if I query the CPT using the default order.

The problem occurs when I need n CPTs that is closest to a coordinate whether it is current user position or a location clicked on the map. Another situation is to query all CPTs that is in x km radius of a coordinate.

The question is : How do I query posts based on latitude-longitude value that is saved on the post meta?

I'm not really sure how to do it, but I think the latitude and longitude value should be saved in a separate custom field.

Thank you in advance.

share|improve this question

3 Answers 3

up vote 3 down vote accepted

This is a simple mathimatical problem. You will indeed need access to both your longitude and latitude, so save it in a metafield.

than you will have to query your posts like this as a sql query. Haven't got a chance to test it. and or pour it into wordpress. Don't have access to my test env now. But I guess you could do it yourself :) if not I'll do it later on when I can.

set @latitude = xxx; — center latitude
set @longitude = xxx; — center longitude
set @distance = xx; — search distance

select p.ID, p.post_name, ((ACOS(SIN(@latitude * PI() / 180) * SIN(`latitude.meta_value` * PI() / 180) + COS(@latitude * PI() / 180) * COS(`latitude.meta_value` * PI() / 180) * COS((@longitude – `longitude.meta_value`) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) AS distance
from wp_posts p
left join wp_postmeta latitude on latitude.post_id = p.ID and latitude.meta_key = ‘_latitude’
left join wp_postmeta longitude on longitude.post_id = p.ID and longitude.meta_key = ‘_longitude’
having distance < @distance;
share|improve this answer
    
This is a great start. Thank you very much. I'm not that good on sql queries, I'll respond if i've figured it out. –  ifdion Aug 14 '12 at 10:26
    
+1, looks promising. –  amit Aug 14 '12 at 13:27
    
I was wondering if you managed to get it to work? –  woony Aug 17 '12 at 18:16

For anyone looking for this answer in the future, I took woony's code the extra mile and got everything working within Wordpress' post_meta table structure. This assumes you have two separate custom fields, one for Latitude (city_latitude) and one for longitude (city_longitude). Just pass the latitude, longitude, and distance parameters into a WP function and you should be set.

Here's the WP function. Drop this in your functions.php file:

function get_nearby_cities($lat, $long, $distance){
    global $wpdb;
    $nearbyCities = $wpdb->get_results( 
    "SELECT DISTINCT    
        city_latitude.post_id,
        city_latitude.meta_key,
        city_latitude.meta_value as cityLat,
        city_longitude.meta_value as cityLong,
        ((ACOS(SIN($lat * PI() / 180) * SIN(city_latitude.meta_value * PI() / 180) + COS($lat * PI() / 180) * COS(city_latitude.meta_value * PI() / 180) * COS(($long - city_longitude.meta_value) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) AS distance,
        wp_posts.post_title
    FROM 
        wp_postmeta AS city_latitude
        LEFT JOIN wp_postmeta as city_longitude ON city_latitude.post_id = city_longitude.post_id
        INNER JOIN wp_posts ON wp_posts.ID = city_latitude.post_id
    WHERE city_latitude.meta_key = 'city_latitude' AND city_longitude.meta_key = 'city_longitude'
    HAVING distance < $distance
    ORDER BY distance ASC;"
    );

    if($nearbyCities){
        return $nearbyCities;
    }
}

Return the value in your template file:

$nearbyCities = get_nearby_cities(get_post_meat($post->ID, 'city_latitude', true), get_post_meta($post->ID, 'city_longitude', true), 25);
share|improve this answer

Have a look at the Geo Data Store plugin. It creates an optimized table for your lat/long coordinates and has two template tags to use.

More info and code example in this post

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.