PHP，MySQL - 地理定位查询似乎没有返回所有可能的位置

Question

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:

// 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 $_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.

Answer 1

While working on another project I found the answer to this.

Because the longitude and latitude is stored in the meta data it is stored as LONGTEXT. When doing my calculations in the SQL they were not seeing the values as floating point numbers bu instead rounding to whole numbers which was throwing my results way off.

Anyway the way to fix was to use CAST() and cast to DECIMAL().

So the SQL would look something like this instead:

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."'