PHP, MySQL - Geo location query doesnt seem to be returning all possible locations

admin

Administrator
Staff member
I have 100 property listings as wordpress posts and each post has two custom fields attached holding the property co-ordinates; property_address_lat and property_address_lng.

I'm providing a way for our website visitors to type in an address or location of their choice where the co-ordinates are returned and also they can enter a radius in miles. And what should happen is the properties that are within those paremeters are returned. So here is the code Im using:

Code:
// 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." - `t`.`property_address_lat` ) * pi() / 180 / 2), 2 ) + COS( ".$orig_lat." * pi() / 180) * COS( `t`.`property_address_lat` * pi() / 180 ) * POWER( SIN( ( ".$orig_lon." - `t`.`property_address_lng` ) * pi() / 180 / 2 ), 2 ) ) ) AS `distance` 
            FROM (

            SELECT `$wpdb->posts`.`ID`
            , MAX(CASE WHEN `$wpdb->postmeta`.`meta_key` = 'property_address_lng' THEN `$wpdb->postmeta`.`meta_value` END ) AS `property_address_lng`
            , MAX(CASE WHEN `$wpdb->postmeta`.`meta_key` = 'property_address_lat' 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` = 'property' 
            GROUP BY `$wpdb->posts`.`ID` 
            HAVING `property_address_lng` BETWEEN '".$lon1."' AND '".$lon2."' AND `property_address_lat` BETWEEN '".$lat1."' AND '".$lat2."'

            ) AS `t`
            HAVING `distance` < ".$dist."
        ";          
// Just get the ID's
$mapsearchresults = $wpdb->get_col($mapsearchquery);

The
Code:
$_SESSION
data holds the users chosen paremeters. What I'm doing first is working out a rough square box around the chosen area. This does an initial query on the data to make sure that the posts are within that box. Then once that runs I run over the top of the results a distance checker to make sure each property is inside a circle radius. Im sure I dont need to explain why Im doing the two steps and not one.

So I come to put it to the test. I set the centerpoint to London and set a 50Mile radius. I get returned about 16 properties which seems about right. I checked them all and yes they are within the radius. So great I think this is working but then I go and set the radius to 1000Miles which should cover all of the properties and I should expect all properties to be returned. Well when I run this only about half of the properties are returned...

So what has got me thinking is that this code is just not right and it seems to fail when the radius is ramped up. Can any of you see if I've done something stupid with my code? all the mathematics to this should be done so working in miles and not KM.

If anyone can shed some light onto this I'd really apreciate it.