We help IT Professionals succeed at work.

Querying Google Maps API more efficiently - ideas needed

hindersaliva asked
Last Modified: 2018-12-20
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.
Watch Question

Fractional CTO
Distinguished Expert 2019
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)


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).