# Adding A Distance between (miles) Column in ETL

Member

Is there a way to add a column to the final dataset when combining two data sets? I would like to add a distance column from the employee data zip (home address) to the work location zip (shop address). If it's easier to do this in a beast mode that could also work...

• Coach

Are you looking for driving distance or as the crow flies?

• Member

Miles would be ideal, we keep zip codes of our employee's address and our shop locations and I'd like to map out where techs we retain live compared to techs that don't make it to the 90 day mark live in comparison to their home shop.

We keep full addresses of both home and shops but I figured that may be asking for too much lol

• Coach

Ok, so the more I dug into this the nastier it got. But, there is a way to do it.

I found a walkthrough where a guy put together a MySQL formula for this. You'll need to find the Lat/Long values for each zip code you're wanting to use.

You can then use those with this Haversine formula:

`set @orig_lat=122.4058; set @orig_lon=37.7907;set @dist=10;SELECT *, 3956 * 2 * ASIN(SQRT( POWER(SIN((@orig_lat -abs( dest.lat)) * pi()/180 / 2),2) + COS(@orig_lat * pi()/180 ) * COS( abs(dest.lat) *  pi()/180) * POWER(SIN((@orig_lon – dest.lon) *  pi()/180 / 2), 2) ))as distanceFROM hotels desthaving distance < @distORDER BY distance limit 10;`

Here's a link to the reference:

https://www.scribd.com/presentation/2569355/Geo-Distance-Search-with-MySQL

For each of the values inside SIN an COS functions, you'll need to calculate in a prior transform before calling those. It does work from what I've tested.

Hopefully this will get you started on the right track,

ValiantSpur