Querying Google Maps API more efficiently - ideas needed

hindersaliva
hindersaliva used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Fractional CTO
Distinguished Expert 2018
Commented:
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.

Author

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

Author

Commented:
Thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial