# How to do a Proximity Search using Haversine Formula?

Let us consider a table:

`id` bigint(20) NOT NULL AUTO_INCREMENT,
`latitude` DECIMAL(9,4) NULL ,
`longitude` DECIMAL(9,4) 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!