Using a list of geographic locations, and determining the X closest locations to a user-defined location

I've got a list of geographical locations in an Excel spreadsheet. I want users to be able to search for a user-defined location, and then have the X number of closest locations from the list pulled up and displayed, sorted by nearest to farthest by travel time via car. I know Excel alone obviously can't do this.

I have minimal/no programming knowledge. Just an Excel tinkerer. I figure that, at a minimum, this would require utilization of a mapping service like Google Maps or Mapquest.

-

I found an article called Calculate distance in Google spreadsheet that may be of use.

It would take some tweaking to do exactly what you want, I think -- and Google's daily API caps could hinder you, but it's an interesting approach.

-

It looks like the Google Maps API has a service for this: http://code.google.com/apis/maps/documentation/javascript/distancematrix.html

Limit of 25 destinations, though.

You might also be able to leverage the api form http://www.mapnificent.net/, though it's based on public transportation.

-

You say you are a "Excel tinkerer". "Mapping" in Excel is possible to some extent. I once saw a very involved Excel dashboard application that used a line chart to plot out boundaries of a study area and then VBA to determine what portion of the study area a given point was located within - all based on X\Y locations of both the study are line vertices and point locations. It was pretty impressive. Also, these articles might be of interest:

I don't necessarily condone or encourage these as solutions (as there are much better true GIS ones such as mrg's answer to this question), but like I said earlier, you said you were a Excel tinkerer with no-programming knowledge - which begs the question: What's your GIS knowledge level?

-

A possible solution would be to use postgresql, postgis, pgrouting and osm2pgrouting.

• Insert your fixed locations in a postgis database.
• Insert the real road network in your database for the area that you need with an import of OSM data using osm2pgrouting.
• Optional: find the closest point on your road network from the user defined location.
• Use pgrouting to calculate the distance by car from user defined location to all your predefined locations ordered on distance limited by X.

My assumptions:

• You need the real road network to be able to calculate the driving distance from a user defined location to your fixed locations.
• Distance from A to B is a good substitute for driving time. This is obviously not always the case but i do not know your specific use case.

Needed skills:

• What you mainly need with this solution is SQL knowledge.
-