String sql = "select name, address, zip, city, country, phone, url"
+ " from retailer"
+ " where latitude is not null and longitude is not null"
+ " order by pow(lat-lat2, 2) + pow(2 * min(abs(lon-lon2), 360-abs(lon-lon2)), 2) asc limit 10";
stmt = con.prepareStatement(sql);
stmt.setDouble(1, Double.parseDouble(request.getParameter("lat")));
stmt.setDouble(2, Double.parseDouble(request.getParameter("lng")));
Honolulu to Los Angeles and San Fransisco in
Python:
>>> lat, long = (21.3069444, -157.8583333)
>>> lat2, lon2 = (34.0522342, -118.2436849)
>>> pow(lat-lat2, 2) + pow(2 * min(abs(lon-lon2), 360-abs(lon-lon2)), 2)
33839.327855007934
>>> lat2, lon2 = (37.7749295, -122.4194155)
>>> pow(lat-lat2, 2) + pow(2 * min(abs(lon-lon2), 360-abs(lon-lon2)), 2)
30952.629658700374
2 * lon is ok for latitude 45 or -45, but
1/cos(radians(lat)) is better.
The haversine formula says that Honolulu (21.3069444, -157.8583333) to Los Angeles (34.0522342, -118.2436849) is 4120 km and to San Fransisco (37.7749295, -122.4194155) is 3854 km.
ReplyDeleteI think this and this use that.
That's twice as slow as the spherical law of cosines, though, which is slower (but more accurate, at least near the poles) than my formula.
DeleteIn SQL Express, it's: square(lat-lat2) + square(2 * case when abs(lon-lon2) < 360 - abs(lon-lon2) then abs(lon-lon2) else 360 - abs(lon-lon2) end)
ReplyDelete