4

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.

Improve this question

4 Answers 4

Reset to default
4

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.
Improve this answer
2

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?

Improve this answer
2

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.

Improve this answer
1

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.

enter image description here

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.