Finding ZIP codes/coordinates for listings within a radius of another ZIP code using Long/Lat coordinates

Question

I want to find all listings within an arbitrary radius of a ZIP code. e.g. 10 miles from 90210, 100 miles from 00603, etc.

If my query is "10 miles from 90210", I want to return all the listings that are within this 10-mile radius. The listings are also ZIP codes, so I essentially want to find all the other ZIP codes within this 10-mile radius.

I think once I have collected all the ZIP codes in the radius, then I add them into an array, and then use SQL to find all listings that have the ZIP codes in this array. However, I am not sure if this is the best way it's done.

I have tried to do research, but it gets very complex for me; I think it may have something to do with a Great Circle. I have been looking into the Aviation formulas too at http://williams.best.vwh.net/avform.htm, but still can't work anything out.

I have a database of ZIP codes containing the longitude and latitude of all ZIP codes, along with city and state info. This is enough info for what I need. I guess I just need to know the math; I don't want to use an API.

I am absolutely lost here, on how I go about calculating such radii.

http://stackoverflow.com/questions/8658730/what-is-the-conversion-of-latitude-longitude-to-a-1-mile — Jorge Campos, Jul 10 '14 at 04:33
how about something like this? https://zipcodedistanceapi.redline13.com/API#radius — Ryan, Jul 10 '14 at 04:34
I don't want to use an API, I have a database with all the necessary info, I just don't know the math to achieve what I want. — user2909486, Jul 10 '14 at 04:36
So, the basics are: Get the longitute and latitude for the zip code, calculate the distance in miles of it than calculate what is the radius of that distance then search all the zips on your database based on the lat and lon inside the calculated radius converted to lat and lon again. — Jorge Campos, Jul 10 '14 at 04:48
"calculate the distance in miles" of what? The radius doesn't need to be calculated, it is set e.g. 100 miles. — user2909486, Jul 10 '14 at 04:51
If your typical radius is in the order of miles or tens of miles you could simplify your calculations by neglecting the spherical nature of the Earth. Over a distance of, say, 20 miles (a 10-mile radius) the Earth is effectively flat. You could further optimise by using a square bounding-box which would allow you to use a simple `BETWEEN` clause. If you really need to use a radius you can then calculate the straight-line distance of just the selected items with Pythagoras. — , Jul 10 '14 at 05:07
Possible duplicate of [Calculate distance between two latitude-longitude points? (Haversine formula)](https://stackoverflow.com/q/27928) — Makyen, Aug 06 '19 at 18:35
See also [this question](https://stackoverflow.com/questions/5031268/algorithm-to-find-all-latitude-longitude-locations-within-a-certain-distance-fro) and [this question, though it's in Java](https://stackoverflow.com/questions/22063842/check-if-a-latitude-and-longitude-is-within-a-circle) — TylerH, Aug 06 '19 at 18:37

score 2 · Answer 1 · answered Jul 10 '14 at 05:08

2

You can get all listing of zip codes corresponding to the entered zip code by calling the following api below:-

$zipcode=90210;
$distancekm=10;

$curl = curl_init();
   curl_setopt_array($curl, array( 
   CURLOPT_RETURNTRANSFER => 1,

   CURLOPT_URL => 
       'http://zipcodedistanceapi.redline13.com/rest/2odywUYrQJOEEO0vJdlcH5Qd8Lf6EGKG4YfBTd2JXQoCFo7pBsBiysvGdLNSsyzw/radius.json/'.$zipcode.'/'.$distancekm.'/mile'));

   $result = curl_exec($curl);

   $decoded_result = json_decode($result, true);

$decoded_result returns you all zipcodes that are in distance of 10 miles nearer to zipcode 90210.

may this will help You.

answered Jul 10 '14 at 05:08

TBI

2,789
1
17
21

thanks, ideally I didn't want to use and API, but if I can't work out how to do it with mysql I may not have any choice. So this would return a list of zip codes in an array, and then I would filter results only with those zip codes? Using IN()? – user2909486 Jul 10 '14 at 05:17
yes ,you can do it using IN(). $zipcode='zipcode that you want to search for'; Like:- "select * from table_name where $zipcode IN($zipcodes)"; $zipcodes is an array of all returned zipcodes. – TBI Jul 10 '14 at 05:27

jynus · Answer 2 · 2014-07-10T04:57:28.430

There are 3 separate issues that you mention:

"Miles" and and "geographical coordinates" do not have a direct translation. As you may have heard, the Earth is not flat :-), which means that 1º of longitude at Equator and at LA latitudes is going to have a different vale in miles. The formula that you link, is the application of the spherical law of cosines that allows you to translate longitude and latitude difference in a faster approximation
Assuming that you may have a lot of addresses, your second issue is that regular MySQL indexes (B-Trees) can be very inefficient for filtering by the closest coordinates. Some database vendors (Oracle, Postgress) have "GIS extension" that allow to perform quick lookups on geographical coordinates. While MySQL has those specific data types and functions, earlier versions were very limited on scope and performance. Newest version (5.6 and 5.7) have improved that in some way, and you can see here an example of what you do with MySQL for filtering places near a coordinate:

http://mysqlserverteam.com/mysql-5-7-and-gis-an-example/

It involves filtering by using a bounding box, which makes it easier from MySQL capabilities. The previous link has all formulas and queries in a step-by-step guide.
You intend to perform a search of points and then execute a second query to retrieve information for each found location. That is unnecessary and probably inefficient in network bandwidth, you can do probably do everything in a single query by performing a self join (JOINin the table search for a particular zipcode with the search for records that are geografically close).

Here it is another link about GIS and MySQL: http://www.slideshare.net/henrikingo/spatial-functions-in-mysql-56-mariadb-55-postgis-20-and-others

So what about doing a table join for each listing, adding an extra column of distance from zip code? Then filtering all distances < 100 miles? — user2909486, Jul 10 '14 at 04:55
Certainly it will simplify the calculations (it is a common denormalization/optimization trick) but beware, because the size of that table will be n^2 records- maintainable if you intend to store 20 zips, but probably not for a million. — jynus, Jul 10 '14 at 04:59
There are about 250,000 listings. I want to create the ability to sort them by nearest, and filter them by radius. Is this way feasible? — user2909486, Jul 10 '14 at 05:05
That is an auxiliary 62500000000 row table! I would recommend you calculating it dynamically as I suggested in my answer solution. — jynus, Jul 10 '14 at 05:08

score 0 · Answer 3 · answered Jun 01 '16 at 20:06

Maybe this Java project can help. The project is configured in kilometers though. You can modify these in CityDAO.java

public List<City> findCityInRange(GeoPoint geoPoint, double distance) {
    List<City> cities = new ArrayList<City>();
    QueryBuilder queryBuilder = geoDistanceQuery("geoPoint")
            .point(geoPoint.getLat(), geoPoint.getLon())
            //.distance(distance, DistanceUnit.KILOMETERS) original
            .distance(distance, DistanceUnit.MILES)
            .optimizeBbox("memory")
            .geoDistance(GeoDistance.ARC);

    SearchRequestBuilder builder = esClient.getClient()
            .prepareSearch(INDEX)
            .setTypes("city")
            .setSearchType(SearchType.QUERY_THEN_FETCH)
            .setScroll(new TimeValue(60000))
            .setSize(100).setExplain(true)
            .setPostFilter(queryBuilder)
            .addSort(SortBuilders.geoDistanceSort("geoPoint")
                    .order(SortOrder.ASC)
                    .point(geoPoint.getLat(), geoPoint.getLon())
                    //.unit(DistanceUnit.KILOMETERS)); Original
                    .unit(DistanceUnit.MILES));

    SearchResponse response = builder
            .execute()
            .actionGet();


    SearchHit[] hits = response.getHits().getHits();

    scroll:
    while (true) {

        for (SearchHit hit : hits) {
            Map<String, Object> result = hit.getSource();
            cities.add(mapper.convertValue(result, City.class));
        }

        response = esClient.getClient().prepareSearchScroll(response.getScrollId()).setScroll(new TimeValue(60000)).execute().actionGet();
        if (response.getHits().getHits().length == 0) {
            break scroll;
        }
    }

    return cities;
}

The "LocationFinder\src\main\resources\json\cities.json" file contains all cities from Belgium. You can delete or create entries if you want too. As long as you don't change the names and/or structure, no code changes are required.

Make sure to read the README https://github.com/GlennVanSchil/LocationFinder

Finding ZIP codes/coordinates for listings within a radius of another ZIP code using Long/Lat coordinates

3 Answers3