I'm looking for a small tool (incl source code) that calculates the distance people travelled on a certain day.
The source is an excel file that has the following per row:
- ActivityID
- UserID
- Date & Start time
- Time the job should take to complete
- Start Postalcode
- PostalCode of the location
When there is more than two hours between two tasks, we assume the person has returned to the starting point. It can happen that a person does a few tasks in the morning and a few tasks in the afternoon. The tool should determine at least 2 different stints in this case
In order to speed up the tool and keep costs down, the tool should (persistently) cache its requests. So when it calculate a distance between postalcode 1033WB and 1033AB once, it should know it for the next time.
Postal codes can be formatted as NNNN CC or NNNNCC, it should be sanitized before it gets cached.
The input for the tool should be a fixed format Excel file, as should the output of the file. (Cache can be stored in text or excel, whatever works best for you)
- Postalcodes are always in The Netherlands
- Tool should display a log file with warnings (e.g. when a postalcode can not be resolved and therefore the distance can niet be calculated)
- Tool should be able to handle at least 100K lines per file
Api can be either Azure (preferable), Distancematrixapi or Google (or if you know a good free one, thats fine too ;-))
Free to choose the language to develop this tool (Command-line is fine) e.g. C#, PowerShell, Python as long as the tool is fairly easy to run on a pc.