Identifying the ZIP code of a location using Latitude and Longitude
I am required to identify the Postal/Zip code for each record in a dataset which contains location information in the following format hosted in an SQL server DB,
For identifying Postal/Zip codes for a location I'm using the information provided by Geo Names which is in the format Country code, Postal code, Place, data1, data2, data3, data4, latitude, longitude and accuracy as follows in a text file,
How can I identify the zip code for each record in the first dataset (checking whether a locations lat lang falls within the lat lang of a zip code). I have attempted to identify any previous problems related to this topic but I could only find this question where the answer does not elaborate on the procedure or calculation required to arrive to the answer (only provides a language specific solution).
Ps. This is a requirement that arose for my undergrad final year project.
One method that will work, though it's a bit of brute force, is to apply the Haversine Formula to the the pair of (Lat,Lon) in your initial dataset (your first picture) to each of (Lat,Lon) pairs in your GeoNames database.
The Haversine formula gives you the great circle distance (remember, the Earth is a globe) between two lat/lon pairs. If you take your first example row, lat 40.769, lon -73.9545, and cycle through each of the records in your GeoNames database calculating the great circle distance (for North Bergan, Weehawken, Union City, etc.), keeping track of the closest distance you've found, that should give you a good indicator of the correct zip code.