How to use Haversine Formula for location (distance) searches with MySQL?

Problem Statement
Find all locations that are within a certain radius from a particular location(point).

Solution Approach
We will demonstrate that with pseudo-code.
Refer the post titled: How to use Geo Spatial with MySQL
Everything else remains same we will modify the function findGeoLocations(…) and call it findGeoLocationsWithHaversine(…).
NOTE that it does not use geo spatial capabilities of MySQL at all.

This is what the function looks like:

public void findGeoLocationsWithHaversine(double lat, double lon,
double radiusInMiles)
{

Connection con = null;

try {
    con = Constants.getConnection();

    //Statement stmt = con.createStatement();
    String query="";
    double radius=radiusInMiles/69;

    double x1 = lat - radius; double y1 = lon - radius;
    double x2 = lat + radius; double y2 = lon - radius;
    double x3 = lat + radius; double y3 = lon + radius;
    double x4 = lat - radius; double y4 = lon + radius;
    String polygonStr = x1 + " " + y1 + "," + x2 + " " + y2 + "," +
    x3 + " " + y3 + "," + x4 + " " + y4 + "," + x1 + " " + y1;
    String pointStr = lat + " " + lon;

    query = "select g.id, g.latitude, g.longitude, (3956 * 2 *
    ASIN ( SQRT ( POWER(SIN(("+lat+" - g.latitude)*pi()/180 / 2),";
    query = query + " 2) + COS(" + lat + "* pi()/180) *
    COS(g.latitude * pi()/180) * POWER(SIN((" + lon +
    "- g.longitude)     *";
    query = query + " pi()/180 / 2), 2) ) ))/69 as distance" ;
    query = query + " from geoinfo g";
    query = query + " where g.longitude between (" + lon +
    " - " + radius + "/abs(cos(radians(" + lat + "))))";
    query = query + " and (" + lon + " + " + radius +
    "/abs(cos(radians(" + lat + ")))";
    query = query + " ) and g.latitude between (" + lat +
    " - " + radius + ") and (" + lat + "+" + radius + ")";
    query = query + " having distance < " + radius;
    query = query + " order by distance";

    System.out.println("Query to use: " + query);
    PreparedStatement pStmt = con.prepareStatement(query);
    ResultSet rs;

    long startTime = Calendar.getInstance().getTimeInMillis();
    rs = pStmt.executeQuery();
    long timeTaken = Calendar.getInstance().getTimeInMillis()
    - startTime;

    System.out.println("Time Taken: " + timeTaken + " milliseconds");

    int rows = 0;
    while (rs.next())
    {
        rows++;
        /*
        System.out.println ("RS: Id: " + rs.getInt("id") +
        " Lat: " + rs.getDouble("latitude") + " Lon: " +
        rs.getDouble("longitude") + " Dist: " +
        rs.getDouble("distance"));
        */
    }
    System.out.println("Total Rows: " + rows);

} // end try

catch (SQLException e)
{
    e.printStackTrace();
}
finally
{
    con=null;
}
}

Thats It!

Advertisement