2012-03-27

Quick & easy navigation.

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.

3 comments:

  1. 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.
    I think this and this use that.

    ReplyDelete
    Replies
    1. 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.

      Delete
  2. In 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