How to query posts based on lat-lng coordinate as post meta?

admin管理员组

文章数量:1327675

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.

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.

• wp-query
• meta-query
• google-maps

Share Improve this question asked Aug 14, 2012 at 8:31 ifdionifdion 1,47711 gold badge1111 silver badges2323 bronze badges

Add a comment  | 

3 Answers 3

Sorted by: Reset to default 7

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;

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_meta($post->ID, 'city_latitude', true), get_post_meta($post->ID, 'city_longitude', true), 25);

Another solution build upon the other ones:

• Used $wpdb->prepare for parameter escaping
• Used table names from $wpdb
• Added minimal distance
• Added limit
• Added option to switch between miles and km
• Added option to set post type
• Extracted names for meta fields

     /**
     * @param float   $latitude Latitude of the center
     * @param float   $longitude longitude of the center
     * @param int     $min_distance Minimal distance from the center (Default: 0).
     * @param int     $max_distance Maximal distance from the center (Default: 100).
     * @param int     $limit Maximal number of results (Default: 20).
     * @param string  $post_type Post type to filter for (Default: 'pois').
     * @param boolean $use_miles Set to true, if you are using miles instead of kilometers (Default: false).
     * @return array|object
     */
    function my_get_nearby_locations( $latitude, $longitude, $min_distance = 0, $max_distance = 100, $limit = 20, $post_type = 'pois', $use_miles = false ) {
        global $wpdb;
    
        $meta_key_latitude = 'location_lat';
        $meta_key_longitude = 'location_long';
    
        $miles_to_km = $use_miles ? 1 : 1.609344;
    
        $query = "SELECT DISTINCT
            t_lat.post_id,
            t_post.post_title,
            t_lat.meta_value as latitude,
            t_long.meta_value as longitude,
            ((ACOS(SIN(%f * PI() / 180) * SIN(t_lat.meta_value * PI() / 180) + COS(%f * PI() / 180) * COS(t_lat.meta_value * PI() / 180) * COS((%f - t_long.meta_value) * PI() / 180)) * 180 / PI()) * 60 * 1.1515 * {$miles_to_km}) AS distance
            FROM {$wpdb->postmeta} AS t_lat
            LEFT JOIN {$wpdb->postmeta} as t_long ON t_lat.post_id = t_long.post_id
            INNER JOIN {$wpdb->posts} as t_post ON t_post.ID = t_lat.post_id
            WHERE t_lat.meta_key = %s AND t_long.meta_key = %s AND t_post.post_type = %s
            HAVING distance > %d AND distance < %d ORDER BY distance ASC LIMIT %d;";
    
        $prepared_query = $wpdb->prepare( $query, [ $latitude, $latitude, $longitude, $meta_key_latitude, $meta_key_longitude, $post_type, $min_distance, $max_distance, $limit ] );
    
        return $wpdb->get_results( $prepared_query );
    }

本文标签： How to query posts based on latlng coordinate as post meta