Calculating Commute Distance

Koos van Strien puts together some code to figure out commuting distance using Google Maps data:

Think well about this third step: you can save quite a few dollars annually by just keeping a local cache of travel distances, and only querying when the distance is unknown. My ETL process for this part consists of three global steps:

  1. Add unknown departure/destination pairs to the ‘to be queried’ table (PK of this table is start & end point address in less-structured format, ensuring uniqueness of commutes)

  2. Query Maps API for unknown travel distances. Add retrieved distances (or known unknowns) to the local cache table of travel distances

  3. Use the local cache of travel distance (as complete as it gets at this moment) as the primary lookup for travel distance

The Google Maps API allows free tier users to make about 1000 requests per day.  If you don’t need to pull more than that many data points back (or can queue them to run over the necessary time frame), there’s no marginal cost to calls.  Otherwise, it ends up being a few dollars per thousand calls, so that shouldn’t break your company’s budget.

Related Posts

Compressing Files In SSIS

Mark Broadbent shows how to compress files within Integration Services using a script task: Ok, so before I get started I will caveat this quick post by saying that there is an easier (or preferred) way to perform compression on files in SSIS using the ZipFile Class in the recent version of the .NET framework, […]

Read More

Executing Powershell In SSIS

Daniel Calbimonte shows how to execute Powershell via C# in SQL Server Integration Services: Get started The article will include the following topics: Get the list of services using PowerShell in C#. How to send SSIS Parameters to PowerShell using the script task. How to use the PowerShell.addscript function. How to invoke a PowerShell script […]

Read More

Categories

July 2016
MTWTFSS
« Jun Aug »
 123
45678910
11121314151617
18192021222324
25262728293031