CODEWITHSUNDEEP
phpmysqlgeolocationlatitude-longitude
Matt M
Matt M

Reputation: 43

Inaccurate distance from geosearch using bounding circle

I have been trying different methods to speed up the location search on a website, I have been taking instructions from http://www.movable-type.co.uk/scripts/latlong-db.html.

The code has been tailored to my tables and have input the variables so I can run it through phpmyadmin

Select id, outcode, lat, lng, acos(sin(57.101)*sin(radians(lat)) + 
cos(57.101)*cos(radians(lat))*cos(radians(lng)-'-2.27')) * 3959 As D
From 
  (Select id, outcode, lat, lng
   From car_postcodes
    WHERE
    lat Between 52.7593142577 And 61.4426857423
    And 
    lng Between -10.2633856968 And 5.72338569684
    ) As FirstCut 
Where acos(sin(57.101)*sin(radians(lat)) + 
cos(57.101)*cos(radians(lat))*cos(radians(lng)-'-2.27)) * 3959 < 7000
Order by D

The problem is that the search results are displaying the minimum distance 5448 (miles?) away from the location, which isn't correct as all postcodes are within the UK. (hence the '< 7000)

This is the code for finding the min / max longitude & latitude:

$lat = 57.101;  // latitude of centre of bounding circle in degrees
$lon = -2.27;  // longitude of centre of bounding circle in degrees
$rad = 300;  // radius of bounding circle in kilometers

$R = 3959;  // earth's mean radius, m

// first-cut bounding box (in degrees)
$maxLat = $lat + rad2deg($rad/$R);
$minLat = $lat - rad2deg($rad/$R);
// compensate for degrees longitude getting smaller with increasing latitude
$maxLon = $lon + rad2deg($rad/$R/cos(deg2rad($lat)));
$minLon = $lon - rad2deg($rad/$R/cos(deg2rad($lat)));

The top three results are showing: 

 ID     postcode      lat      lng      D      
 1143   HS2           58.249   -6.468   5428.525603021315 
 1142   HS1           58.213   -6.381   5432.53243648885
 1144   HS3           57.879   -6.853   5435.933627885293

lng & lat columns in the table are stored as binary and are indexed.

Any advice will be greatly appreciated.

Upvotes: 1

Views: 121

Answers (1)

cmorrissey
cmorrissey

Reputation: 8583

You can run this though with :start_lat, :start_lng, :min_distance, and :max_distance, d is in miles. I haven't had a speed problem with it in my code but let me know how it runs.

SELECT SQL_CALC_FOUND_ROWS
                *
                FROM (SELECT id, outcode, lat, lng, ( 3959*(2*ASIN(SQRT(POW(SIN(((car_postcodes.lat-:start_lat)*(PI()/180))/2),2)+COS(car_postcodes.lat*0.017453293)*COS(:start_lat*0.017453293)*POW(SIN(((car_postcodes.lng-:start_lgn)*(PI()/180))/2),2)))) ) AS d
                    FROM car_postcodes
                WHERE 1) AS tmp_tbl
            WHERE d <= :max_distance AND d >= :min_distance
            ORDER BY d ASC

Upvotes: 0

Related Questions