Find the link to Eeshan's original article here:
https://www.linkedin.com/pulse/shortest-distance-between-auto-insurance-claimant-repair-choudhuri
Assumption: It is more likely that both the Collision Center Service Center and the Claimant addresses contain Zip Codes, than Latitude and Longitude. I mean who records Latitude and Longitude Coordinates of Insurance Claimants anyways?
Step 1: Your Insurance Claimant Fact and Service Center Tables may have Zip Code Column only. In such a case download a database file with US Zip Codes and their corresponding Latitude and Longitude.
You may get it from any source:
Listed is one such source: http://federalgovernmentzipcodes.us/
Create and Load this data in the table say “US_LAT_LONG”.
Step 2: Join the Claimant Address Table/ Collision Center Table Zip codes with the “US_LAT_LONG” on the “Zip_Code” Column to extract their corresponding Latitude and Longitude.
You can now get your Claimant & Collision Service Center Latitude and Longitudes as
Step 3: Create all possible scenarios where the Claimant could have brought in his car for Collision Repair. We cross join the two SQL Statements (SQL A & SQL B) to find all possible distances between the Claimant and the Collision Centers in the same City or County or State based on a date range filter.
You can create a Table out of this Cross join using Strategy Logical Table feature for dynamic cases where the date ranges are smaller (E.g. few weeks or few days). For Large date ranges of your data set, create a View or Table depending on database loads. In this Case I have just used a simple cross join.
*Final SQL to Load the Data in the Database or Logical Table should look as follows:
*Note in the above SQL the aliases
CLAIMLAT refers to Claimant Latitude
CLAIMLONG refers to Claimant Longitude
SVCLAT refers to Collision Repair Service Latitude
SVCLONG refers to Collision Repair Service Longitude
Step 4: In Strategy or any BI Tool create Attributes for Claim and Service Center as follows. Make sure you have the appropriate form for each attribute form of Claims and Service Centers using attribute editor from Strategy Business Intelligence Tool.
Example: Claim Attribute
Geography Role for Latitude:
Geography Role for Longitude:
In the next steps Create Simple Metrics for Claim Latitude & Longitude and Service Latitude & Longitude respectively. Example Screenshots created in Strategy is as follows.
Example:
Claim Latitude Metric
Claim Longitude Metric
Step 5: Pull in all Attributes and Metrics into the Sample report template as follows
Step 6: Now using the Claim and Service Center Latitude & Longitude metrics create a derived metric “Distance” (Miles) as shown below
Metric for the Distance between 2 Latitude Longitude coordinates A & B (in Miles)
((6371 * Acos(((Cos(Radians((90 - [CLAIMLAT]))) * Cos(Radians((90 - [SVCLAT])))) + ((Sin(Radians((90 - [CLAIMLAT]))) * Sin(Radians((90 - [SVCLAT])))) * Cos(Radians(([CLAIMLONG] - [SVCLONG]))))))) / 1.609)
Final Report Snapshot should look as follows:
Let us Test the Sample Report using online Tools for some of the Values (Say First and Last entries listed above in the "Final Report Snapshot")
Example: https://www.melissadata.com/lookups/zipdistance.asp
