8

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.

3 Answers 3

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;
2
  • 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
    Commented Aug 14, 2012 at 10:26
  • 1
    I was wondering if you managed to get it to work?
    – woony
    Commented Aug 17, 2012 at 18:16
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_meta($post->ID, 'city_latitude', true), get_post_meta($post->ID, 'city_longitude', true), 25);
6
  • hey @mread ... can you tell me that the result we get is in miles or kilometers Commented Feb 15, 2017 at 6:44
  • The result is in miles, I'm not sure what the formula is for kilometers, but I'm sure you can find that somewhere. You would need to update this line ((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)
    – mread1208
    Commented Feb 16, 2017 at 14:05
  • Jsut for future reference - this ist the code for kilometers: (6371 * acos(cos(radians( $lat )) * cos(radians( city_latitude.meta_value )) * cos(radians( city_longitude.meta_value ) - radians( $long )) + sin(radians( $lat )) * sin(radians( city_latitude.meta_value )))) AS distance, Commented Jan 18, 2021 at 18:24
  • Okay I am rreally lost here. Can anyone help? I have a post with custom fields t_lat and t_long. How can I use the above function to check if the values of t_lat and t_long are nearby a given lat and lng value?
    – RobbTe
    Commented Dec 23, 2021 at 14:00
  • 1
    @RobbTe you should be able to add the get_nearby_cities() function in your functions.php file. Then you'll need to update all instances of city_latitude to match your custom field name t_lat. Same with city_longitude => t_long. That SQL method should grab all the post ID's that have a t_lat and t_long within the radius provided. To test it... execute the function in your template file by hard coding some values for lat, long and distance for say a 25 mile radius in Chicago IL: get_nearby_cities(41.8781, 87.6298, 25);
    – mread1208
    Commented Mar 1, 2022 at 15:00
1

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

5
  • Nicely done. Is there any way to include this into an existing wp_query or I am better to just include additionnal (optional) parameters into this? Commented Jan 21, 2021 at 15:16
  • @PatricePoliquin Not sure, but there is a "post_clauses" filter which might work for this. developer.wordpress.org/reference/hooks/posts_clauses
    – Adrian
    Commented Jan 25, 2021 at 8:21
  • 1
    Hi, you stated 2 times $latitude in the $prepared_query is that correct? Also, any chance you can make this code working for advanced custom fields, maps field? It stores the lat and lng in an array like $array['lat']. How to incorporate this into your solution? Thanks a million!
    – RobbTe
    Commented Dec 22, 2021 at 14:49
  • 1
    @RobbTe, yes $latitude is used 2 times (at the beginning in SIN and COS...). It might be improved using named parameter ... I'm using ACF OpenStreetMap field and are storing the coordinates in seperates fields within a save hook. If both parameters are stored as an array, you can't use the sinus /cosinus i.e.
    – Adrian
    Commented Dec 24, 2021 at 7:54
  • Hi @Adrian, thanks for getting back to me. For some reason I can't get this to work. I have a CPT called locations. I define this like $post_type = 'locations';. My location posts have now the custom fields location_lat and location_long. The variables $latitude and $longitude hold the center lat and long values to search for locations nearby right? For some reason the returned array stays empty.. Any ideas?
    – RobbTe
    Commented Dec 24, 2021 at 9:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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