Geek Answers Handbook

Zipcodes & distance

I have a zipcoded list in a MySQL database along with my Latitude and Longitude data (column names: ZipCode, Lat, Lon).

Now I need to do search queries (zipcode search) to retrieve information from the website. When I do this search query, the results include all the information within a radius of 50 km from zipcode.

Now I do not want to do an unnecessarily large number of search queries, so I would like to minimize the number of zipcodes. Therefore, I am looking for a way to filter all zipcodes, so I only have zipcodes where the distance between them is> 50 km.

Unfortunately, I have no idea how to do this. Can someone help me solve this problem?

+4
source share
6 answers

You may be interested in learning the following presentation:

The author describes how you can use the Haversine Formula in MySQL to limit your searches to a specific range. It also describes how to avoid a full table scan for such queries using traditional indexes in latitude and longitude columns.

+1
source

You can use google geocoding api , it allows you to get distances between two locations (lat / long, it also allows you to get zip with lat / long). From this, you should be able to get the distance between each of your zipcodes and put them in a table, then you can only search for these.

0
source

Well, I see no other way to repeat all the lines in each request and filter them, calculating the distance between the selected zipcode and the others (all of them) based on Lat and Lon.

I use something similar ... http://webarto.com/googlemaps http://webarto.com/izrada-web-stranica/belgrade

PHP function for the distance between two LL ...

function distance($lat1, $lon1, $lat2, $lon2){ $theta = $lon1 - $lon2; $dist = sin(deg2rad($lat1)) * sin(deg2rad($lat2)) + cos(deg2rad($lat1)) * cos(deg2rad($lat2)) * cos(deg2rad($theta)); $dist = acos($dist); $dist = rad2deg($dist); $miles = $dist * 60 * 1.1515; return round($miles * 1.609344,3); }

I count it that way ...

 $sql = mysql_query("SELECT * FROM geoip WHERE city = '$city'"); while($row = mysql_fetch_array($sql)){ $ll = explode(",",$row["ll"]); $x = distance(44.5428009033,18.6693992615,$ll[0],$ll[1]); $road = intval($x+($x/3)); echo "Distance between ".$row["city"]." and Tuzla is ".$x." kilometers of airline, that about ".$road." kilometers of road way."; }
0
source

Daniel refers to the selection of all zip codes within 50 km of a given latitude / longitude. Once you do this, you can create a filtered zipcodes list similar to this ...

  • Choose a zip code in random order and add it to the filtered list
  • Delete all zip codes that are within 50 km of the selected zip code.
  • Choose a new zip code at random from the rest of the zip codes, repeat until there are no more.

You know that you only select zip codes that are> 50 km away from the ones already selected, and you know that as soon as the source table is empty, it should be because all zip codes are within 50 km of at least one postcodes of your choice.

This does not guarantee the smallest possible list of postal codes, and the size of the result will depend on random options. However, I think that this simple algorithm is likely to be “good enough,” and that saving a few searches does not justify the extra effort involved in finding a really optimal solution.

0
source

The issue was discussed earlier here on SO with various solutions.

0
source

I had a similar problem and I used this solution to find the answer. Not sure if you use java or any other language, but the logic can be used in any programming language Geo Location API and search for a user in radius

0
source

More articles:


All Articles