I’ll just like to add that I have a current working solution in place and works well but I’m looking for ideas of being able to do it better or cleaner if possible.
We have hundreds of properties in a custom post type with metakey/metavalue holding the properties longitude and latitude co-ordinates.
We allow a visitor to type in a location and a radius to search for those properties in that area. Kind of like a store locator. Here is how I’m doing it at the moment:
// Work out square radius
if(!empty($_SESSION['s_property_radius'])) {$dist = $_SESSION['s_property_radius'];}else{$dist = 50;}
$orig_lat = $_SESSION['s_property_address_lat'];
$orig_lon = $_SESSION['s_property_address_lng'];
$lon1 = $orig_lon - $dist / abs( cos( deg2rad( $orig_lat ) ) * 69 );
$lon2 = $orig_lon + $dist / abs( cos( deg2rad( $orig_lat ) ) * 69 );
$lat1 = $orig_lat - ( $dist / 69 );
$lat2 = $orig_lat + ( $dist / 69 );
// Compile a map search query to get all property ID's.
$mapsearchquery = "
SELECT `t`.`ID`
, 3956 * 2 * ASIN( SQRT( POWER( SIN( ( ".$orig_lat." - CAST(`t`.`property_address_lat` AS DECIMAL(9,6)) ) * pi() / 180 / 2), 2 ) + COS( ".$orig_lat." * pi() / 180) * COS( CAST(`t`.`property_address_lat` AS DECIMAL(9,6)) * pi() / 180 ) * POWER( SIN( ( ".$orig_lon." - CAST(`t`.`property_address_lng` AS DECIMAL(9,6)) ) * pi() / 180 / 2 ), 2 ) ) ) AS `distance`
FROM (
SELECT `$wpdb->posts`.`ID`
, MAX(CASE WHEN `$wpdb->postmeta`.`meta_key` = 'chb_homes_for_sale_address_longitude' THEN `$wpdb->postmeta`.`meta_value` END ) AS `property_address_lng`
, MAX(CASE WHEN `$wpdb->postmeta`.`meta_key` = 'chb_homes_for_sale_address_latitude' THEN `$wpdb->postmeta`.`meta_value` END ) AS `property_address_lat`
FROM `$wpdb->posts`
LEFT JOIN `$wpdb->postmeta` ON ( `$wpdb->posts`.`ID` = `$wpdb->postmeta`.`post_id` )
WHERE `$wpdb->posts`.`post_status` = 'publish'
AND `$wpdb->posts`.`post_type` = 'homes-for-sale'
GROUP BY `$wpdb->posts`.`ID`
HAVING CAST(`property_address_lng` AS DECIMAL(9,6)) BETWEEN '".$lon1."' AND '".$lon2."' AND CAST(`property_address_lat` AS DECIMAL(9,6)) BETWEEN '".$lat1."' AND '".$lat2."'
) AS `t`
HAVING `distance` < ".$dist."
";
// Just get the ID's
$mapsearchresults = $wpdb->get_col($mapsearchquery);
This returns an array of all post id’s that are within the users search parameters. I then past that array to WP_Query to only show posts that have the above ID’s using post__in. Also I can do other search criteria on the WP_Query afterwards.
In the above query I do a square radius check and I know this could be easily done in WP_Query using meta_query. But where I get stuck is running the circle radius check. Is there a way I can wrap this in WP_Query?
Basically what I’m after is trying to do this all in one rather than doing two lots of queries.
I would duplicate the coordinates of the posts in a separate table
(post_id, lat, lon), with an index on(lat, lon). With all the joins and the casts I doubt the database can use an efficient index with your query.I once wrote an answer to a similar question using this approach.