Querying Google Maps API more efficiently - ideas needed

Looking for ideas.
Post CodesI have created this solution (in Excel) that queries the Google Maps API, and gets the distance and driving duration from the Post Code at the top to the listed Post Codes as shown above.
At the moment the list is about 500 and it takes about 20 seconds - there is a NEW trip to the Maps API for each of the 500.
That's ok, but is there a way to make it faster? I'm thinking ...

  1. I only want to look within the radius 'Max Miles' in the cell max miles. At the moment we are covering the whole country unnecessarily. But how do I programatically exclude the ones further away without knowing (yet, 'cos I haven't gone to the Maps API yet!) what the distance is?
  2. Or is it possible to send Maps API the FROM location and a LIST OF THE TO locations? (I'm thinking that might be quicker as there'll be only one trip to the API.
  3. Or is there a Maps API that will give me what I want more efficiently?

Thanks for any ideas.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

David FavorLinux/LXD/WordPress/Hosting SavantCommented:
The biggest performance win using Google Maps, is to cache results returned from their API.

This way you make a call(s), which takes your 20 seconds, which by the way is very fast.

Then subsequent calls returned cached values, which will be returned in milliseconds.

You can only call the Google Map API based on calling conventions provided by Google, so if the docs support any optimizations, you can only follow the docs + then add caching on top for high speed performance.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
hindersalivaAuthor Commented:
Thanks David. I found that, when searching from a location for the first time it took 40 seconds. Subsequent searches on the same location happened in less than 10 seconds.

But it's false hope, because typically a specific search is unlikely to be repeated.

However, I did reduce the time to 1.2 seconds. This maybe useful to others. I created a table of locations (Post Codes in the UK) in my database, in Eastings and Northings from data publicly available from ONS (Ordinance Survey). These are distances in metres from some origin at the South-East corner of the UK (publicly available). From that I could fire-off a local search that narrows down the search for the Google Maps. So the trip to the Google Maps is only about 5% of the whole country. (Why need Google Maps at all? The solution needs to query the Google Maps web service anyway because the exact distance is needed, as well as the driving time).
hindersalivaAuthor Commented:
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.