15 years of excellence
Get a QuoteReady to get started on your project?

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 here. 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.

Screenshot showing the list of ATM locations for a given zipcode



Screenshot showing the ATM Locations in google map for a given zipcode.



articles



Please fill in the fields below and click the "Send" button.
We will e-mail a link of this article.

Your Name
From Email Address
To Email Address
Subject
Please note: We will not spam or share your Email Address
Quick Enquiry

Related Readings

Verified Company

Inc.com Verified

Case Studies

Website details: A blog site with social networking features and user..

Challenge: This is a social community website, with..

More Case studies..

Recent Blog

What our clients Think

CLIENTS

  • todd
  • RIDGE WEB DESIGN
  • DUNKIN BRANDS
  • Store Point
  • HARVEST
  • Open Solutions

Expertise

  • WordPress
  • Joomla
  • Prestashop
  • CodeIgniter
  • Laravel
  • Responsive Web Design
  • Mobile Apps
  • HTML5