Mapping Trouble

April 6, 2012 8:47 AM

MapFilter: How can I narrow down a list of ~1000 essentially random cities to only those that are within a given radius of another list of ~50 cities?

My supervisor has given me a large Excel spreadsheet of potential clients we'd like to market to. It's a list of 1200 or so, and it includes the city and state for each one (e.g., Columbus, OH).

However, for other reasons I won't go into, we only want to market to those potential customers that are within a 50 mile radius of a major airport. So I've also obtained an Excel file that lists city, state info for the top 50 airports in the U.S.

My question is: how can I combine the two? How can I use Google Maps API or some other similar system to effectively cull the large list of clients that are >50 miles distant from one of those airports? Is this even possible?

posted by aheckler to computers & internet (6 answers total)

You can do this using GIS software, like the open source qGIS. Batch geocode the addresses, create a shapefile. Batch geocode the airport addresses, create another shapefile, then select any points within a 50 mile radius. Converting a CSV file with lat/long data into a shapefile is fairly simple.

Radius selection tutorial
Batch geocoder
posted by Hollywood Upstairs Medical College at 8:58 AM on April 6, 2012 [3 favorites]

I think a google api call combined with a great circle calculator is the easiest approach. How to get the human readable data from excel to google or the Lat Long data from google to excel is not obvious to me though.
posted by bessel functions seem unnecessarily complicated at 8:58 AM on April 6, 2012

Yes, it's a standard GIS query. You could use geocoder.us to get the locations of the two tables, then find all of the clients within a 50 mile buffer using a spatial database like spatialite-gis. There may be easier ways ...
posted by scruss at 9:01 AM on April 6, 2012

I don't have a suggestion for another system, but this would be fairly simple to do with ArcMap using just the buffer and intersect tools. You'd need the cities and airports as separate points files.

QGIS or one of the other open source gis packages might have this feature.

On preview, Hollywood Upstairs Medical College has it.
posted by graxe at 9:01 AM on April 6, 2012

The Google API is handy, but I like this batch one. Then all you need is the Great Circle calculator above.
posted by zvs at 11:28 AM on April 6, 2012

Thanks all! Was able to figure it out with qGIS and the help of my neighbor who is, fortunately enough, a GIS student.
posted by aheckler at 12:43 PM on April 6, 2012