How can I figure out a users postal code if I have their latitude / longitude location? Need help writing a query.

Question

I'm using HTML5 geolocation to collect the users lat / long and I need to figure out what their postal code is as well. I have a database of all the lat / long for each postal code in the US & Canada. How can I write a query to find out what their postal code is? Below, is an example of how the data is structured in the 'zips' table.

Country  PostalCode  Latitude  Longitude
USA      0051        40.813078 -73.046388
USA      00616       18.426456 -66.673779

I can't do a 'SELECT PostalCode FROM zips WHERE Latitude = user.lat AND Longitude = user.long'. I believe I need to find the nearest lat / long. Any suggestions on how I can write this?

Answer 1

You'll have to go through the entire zip code table, calculating the distance between the postal code latitude and longitude, and the users latitude and longitude.

Since the distances are relatively short, you can use the distance formula: d = SQRT(((PCLA - ULA) ** 2) + ((PCLO - ULO) ** 2)). The answer is in degrees, not kilometers or miles.

Whichever postal code is the shortest distance from your user is probably the postal code.

Otherwise, you could just ask the user. :-)

Look up the postal code they give you, calculate the distance, and see if it's a reasonable distance.

Here's a more precise formula for calculating the distance between two earth coordinates.