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.
Share Improve this question asked Aug 14, 2012 at 8:31 ifdionifdion 1,4771 gold badge11 silver badges23 bronze badges3 Answers
Reset to default 7This 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
版权声明:本文标题:How to query posts based on lat-lng coordinate as post meta? 内容由网友自发贡献,该文观点仅代表作者本人, 转载请联系作者并注明出处:http://www.betaflare.com/web/1742223822a2435639.html, 本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容,一经查实,本站将立刻删除。
发表评论