Mapping Trouble
April 6, 2012 8:47 AM Subscribe
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?
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?
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
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
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
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
posted by zvs at 11:28 AM on April 6, 2012
Response by poster: 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
posted by aheckler at 12:43 PM on April 6, 2012
This thread is closed to new comments.
Radius selection tutorial
Batch geocoder
posted by Hollywood Upstairs Medical College at 8:58 AM on April 6, 2012 [3 favorites]