Geospatial Tracking using the haversine formula

So today I was given the task of finding locations of store’s, addresses, or just places based on a radius. This is useful to find near by store locations, or searching for things based on a radius. For example, if you want to find the closest dominos from your house you should use this tutorial!

First things first, you need to have some type of familiarity with PHPMyAdmin/MYSQL. If you have a web server, all you have to do is go to PHP My Admin, and create a new table. For this tutorial, I will be using MySQL and not the PHPMyAdmin features.

 
1. Go to MySQL inside your server, and create a new table that has ID, Name, Address, Lat, Lng columns:

CREATE TABLE `test` (
  `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
  `name` VARCHAR( 60 ) NOT NULL ,
  `address` VARCHAR( 80 ) NOT NULL ,
  `lat` FLOAT( 10, 6 ) NOT NULL ,
  `lng` FLOAT( 10, 6 ) NOT NULL
) ENGINE = MYISAM ;

2. After that you need to import into your table, Name’s, Addresses, Latitudes, and Longitudes of the stores/restaurants/addresses that you want to search with. For this example, I use 4 different places.

INSERT INTO `test` (`name`, `address`, `lat`, `lng`) VALUES ('Lindbergh School Park','Lindbergh School Park, Costa Mesa, CA','33.652188','-117.900172');
INSERT INTO `test` (`name`, `address`, `lat`, `lng`) VALUES ('US Post Office','2230 Fairview Rd, Costa Mesa, CA','33.655459','-117.906958');
INSERT INTO `test` (`name`, `address`, `lat`, `lng`) VALUES ('Orange Coast College','2701 Fairview Rd, Costa Mesa, CA','33.671890','-117.912086');
INSERT INTO `test` (`name`, `address`, `lat`, `lng`) VALUES ('LMU','1 LMU Drive, Los Angeles, CA','33.970407','-118.417423');

3. Now after this comes the actual search portion, where you put in your own address to see how far away one of the locations you added above is from your own address.

I am using “33.650800, -117.891729” that lat/lng as my own address.

SELECT name, id, ( 3959 * acos( cos( radians(YOUR LATITUDE) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(YOUR LONGITUDE) ) + sin( radians(YOUR LATITUDE) ) * sin( radians( lat ) ) ) ) AS distance FROM test HAVING distance < 25 ORDER BY distance; 

The 3959 should never be changed as that is the radius of the Earth in MILES. Where it says "YOUR LATITUDE" you replace with your latitude, and where it says "YOUR LONGITUDE" you replace with your longitude. The 25 after "HAVING distance" is the mile radius that you are searching. So if you want to search in a 10 mile radius, you replace 25 with 10.

So since I am using "33.650800, -117.891729" and want to search using a 10 mile radius, the SQl search will look like this:

SELECT name, id, ( 3959 * acos( cos( radians(33.650800) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(-117.891729) ) + sin( radians(33.650800) ) * sin( radians( lat ) ) ) ) AS distance FROM test HAVING distance < 10 ORDER BY distance;

4. Your result shows, LMU was more than a 10 mile radius, so it doesn't show up when I search it:

View this article to see more details/further implementation: https://developers.google.com/maps/articles/phpsqlsearch_v3#findnearsql

 

Leave a Reply