How to do a Proximity Search using Haversine Formula?

Let us consider a table:

create table `geoaddress` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`latitude` DECIMAL(9,4) NULL ,
`longitude` DECIMAL(9,4) NULL,
‘address’ VARCHAR(1000) NULL,
PRIMARY KEY (`ID`) )
ENGINE = InnoDB DEFAULT CHARSET=utf8

The above table must ofcourse be populated with appropriate data.
Here is a simple java code snippet to insert some random data:

...
Statement stmt = con.createStatement();
Random rand = new Random();
for (int i = 0; i < 100000; i++) {
double lat = rand.nextDouble() * 180 - 90;
BigDecimal bd1 = new BigDecimal(lat).setScale(2, RoundingMode.HALF_EVEN);
lat = bd1.doubleValue();

double lon = rand.nextDouble() * 360 - 180;
BigDecimal bd = new BigDecimal(lon).setScale(2, RoundingMode.HALF_EVEN);
lon = bd.doubleValue();

String query = "INSERT INTO test.geocode(latitude, longitude, address)
VALUES (" + lat + "," + lon + ", 'some addr')";
System.out.println(query);
int res = stmt.executeUpdate(query1);
// System.out.println("res: " + res);
}
....

Given a latitude/longitude, we want to find all records from geoaddress table that are within a certain radius from a given latitude/longitude.

Here is java code snippet for that:

//Update lat, lon, radius variables with whatever values you want! The lat/lon together form the point from where search is performed.
double lat=20;
double lon=55;
double radius=1; //Note: 1 unit (or degree) is equivalent to 69 miles. So for a 100 mile search, this should be set to 1.45

String query;

//The query below is implementation of Haversine Formula!
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 geocode 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";

....
PreparedStatement pStmt = con.prepareStatement(query);
ResultSet rs = pStmt.executeQuery();
...

Thats it! The query will return all records from geoaddress table that are within 69 mile radius (1 unit) from lat/long of 20/55!

Reference: http://www.scribd.com/doc/2569355/Geo-Distance-Search-with-MySQL

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: