Hello!
I have a problem that is probably not unique:

I have a list of about 100,000 customer addresses, and about 1000 store addresses, both all across USA. For each customer, I want a list of the closest 5 stores (based on drive time), with drive time information. I am open to any external add on software (to mappoint) which can make this task simpler.

Finding the distance between each customer address and each store address has been discussed, but I am pretty sure that it would be too expensive and could be too time consuming (100 million distance calculations even at 100 per second would take 277 hours!).
Thanks for looking at my question, hope I get an answer soon!

Straight line calculations are really fast - almost instantaneous, whereas drive time calculations take some time (often 1-2 seconds) so what I'd probably recommend is something first calculating the closest 10 stores based on straight line distance, and then calculate the drive times to each one to determine which are the nearest 5 stores based on drive time. Hope that makes sense.

However, you'd still be talking about a million drive time calculations which - you can do the math, but possibly YEARS to finish, hehe. Are you sure you need that many drive time calculations? Could you get by with straight line distance?

Eric

Trust me, I tried saying that it was the best way, at least to shorten the list to a manageable level before going for the drive time calculation. But it was shot down, based on a few exceptions to the rule - like a customer living on an island but has only one road for access (but the closest stores are not accessible by road) and stuff like that. Even in that case, if I had a list of 100,000 customer addresses and 10 stores for each customer, is there any easy way to get the drive times and distances for this list?

I was wondering if there was any kind of alternate software, which would start from the customer locations, and then calculate the distance and drive time only to the nearest stores? That would not do the same number of calculations.

You can write a macro for instance in Excel that would loop over and do all the calculations. You can do straight line calculations in Excel using trigonometry (which does not need MapPoint) and you can get the drive time calculations by automating MapPoint in Excel VBA. The code is straightforward to do and if you throw me some dollars for a few hours consulting time I'd be glad to help you with it

When you say calculate only to the nearest stores, do you mean nearest straight line distances or nearest drive time?

We developed an excel-AddIn that fits your needs, it´s IC-DistanceAnalysis.
You can define either a radius or a drivetimezone around your 1000 stores.
The program will find all the customers within und will sort them by distance and/or time.

You will get a free 3-month license if you register.

Kind regards
Manfred

Hi Eric,
Thanks for the offer! I use SAS and there is a built in function for finding the distance between zip centroids or lat/longs, so trigonometry wise I'm covered Drive times and distances are my priority now.

Hi Manfred,
Thanks for sharing the info on your software, I will check out the demo!

Hi krish,

The demo-version will not work, because you will need the professional version of IC-DistanceAnalysis to use the perimeter-feature.
Register an I will send you a license-key.
Manfred