Gone are the days when people have to get on to their shoes and explore their locality to know what is where nearby. With the advent of GPS, exploring a locality is just one click away.
Today, it’s a basic necessity for any business to let their customers know where they can get its products or services nearby their locality. It’s the friendly way for the business to say ‘we are here nearby to serve you’.
So, it’s a no wonder to see links like ‘Store Finder’, ‘Locate your store’ on any corporate website that run business across a nation or around the globe. How this is being implemented? To know this, read further.
Let us take an example of locating nearby ATM of a popular bank that runs business across a country. The following schema defines the table structure of ATM locations.
Table Name: TBL_ATM_LOCATIONS
| S.No | Field Name | Field Type |
|---|---|---|
| 1 | ATM_ID | Integer |
| 2 | ATM_ADDRESS | Varchar |
| 3 | ATM_CITY | Varchar |
| 4 | ATM_STATE | Varchar |
| 5 | ATM_ZIPCODE | Varchar |
This schema is enough, if the ATM needs to be located by exact zip code. This can be the case, only if the bank has ATMs in each zip code of the country and then users could get the location of the ATM in their zip code. If the Bank doesn’t have ATMs in each zip code, then this table won’t provide information for the users who don’t have the Bank’s ATM in their zip code. Here, we need to add some more information to the table schema to make it show the nearest ATMs for those users.
Next come the question – how do we know the nearest zip code? Here comes the mathematics for our help. Since our earth is a near spherical in shape, it is possible to calculate the approximate distance between two points on the earth. For this, the coordinates of each point are required. So, we need latitude and longitude detail for each zip code. Now we add two new fields to the table “TBL_ATM_LOCATIONS”.
| S.No | Field Name | Field Type |
|---|---|---|
| 1 | ATM_ID | Integer |
| 2 | ATM_ADDRESS | Varchar |
| 3 | ATM_CITY | Varchar |
| 4 | ATM_STATE | Varchar |
| 5 | ATM_ZIPCODE | Varchar |
| 6 | LATITUDE | Numeric |
| 7 | LONGITUDE | Numeric |
Now we added latitude and longitude on the table schema, but where do we get the actual data for these for each zip code? There are many third party zip code databases available on the internet that provide the latitude, longitude details for zip codes of any specific country. Most of them are commercially licensed. A few free databases are also available. One such free zip code database can be found at http://www.free-zipcodes.com/
The zip code table would typically look like the following,
Table Name: TBL_ZIP_CODE
| S.No | Field Name | Field Type |
|---|---|---|
| 1 | ZIP_CODE | Varchar |
| 2 | LATITUDE | Numeric |
| 3 | LONGITUDE | Numeric |
The third party databases might have some other extra fields. The above are the necessary fields for our purpose. Whenever a new ATM record is added to the TBL_ATM_LOCATIONS table, get its latitude and longitude details from the TBL_ZIP_CODE table and store in TBL_ATM_LOCATIONS table.
Now, we have all required details to search the nearby ATMs. Let us try to find a nearby ATM that is within a radius of 5 KM for a zip code XXXXX. First, get the latitude and longitude for the zip code XXXXX from theTBL_ZIP_CODE table.
SELECT LATITUDE, LONGITUDE FROM TBL_ZIP_CODE WHERE ZIP_CODE='XXXXX'
Let us call its latitude and longitude as SRC_LAT and SRC_LONG respectively.
Now, we create a MYSQL Query that will get us the nearest ATMs within 5 KM radius,
SELECT *, (6371* ACOS( COS( RADIANS(SRC_LAT) ) * COS( RADIANS( LATITUDE ) ) * COS( RADIANS( SRC_LONG ) - RADIANS(LONGITUDE) ) + SIN( RADIANS(SRC_LAT) ) * SIN( RADIANS( LATITUDE ) ) ) ) AS DISTANCE FROM TBL_ATM_LOCATIONS HAVING DISTANCE<=5
In this query, we have used the following mathematical formula to calculate the distance,
Distance in KM = 6371 * acos( cos(radians(lat1)) * cos(radians(lat2)) * cos(radians(long1) – radians(long2)) + sin(radians(lat1)) * sin(radians(lat2)) )
This MYSQL query will get the list of ATMs that are within 5 KM radius from the zip code XXXXX. Further, these ATM locations can be shown on google map using google maps API and it would be a good user experience.
A PHP script with a sample database to accomplish the above the example has been given atm_finder. Kindly note that the zip code table in the sample database has only less records on US zipcodes and so the example won’t work for all US zipcodes. Below are the screenshots for this example script.