Adding A Distance between (miles) Column in ETL
Comments

Are you looking for driving distance or as the crow flies?
0 
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
0 
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 distance
FROM hotels dest
having distance < @dist
ORDER BY distance limit 10;Here's a link to the reference:
https://www.scribd.com/presentation/2569355/GeoDistanceSearchwithMySQL
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
**Please mark "Accept as Solution" if this post solves your problem
**Say "Thanks" by clicking the "heart" in the post that helped you.0
Categories
 All Categories
 1.7K Product Ideas
 1.7K Ideas Exchange
 1.5K Connect
 1.2K Connectors
 292 Workbench
 4 Cloud Amplifier
 8 Federated
 2.8K Transform
 95 SQL DataFlows
 602 Datasets
 2.1K Magic ETL
 3.7K Visualize
 2.4K Charting
 697 Beast Mode
 43 App Studio
 39 Variables
 658 Automate
 170 Apps
 441 APIs & Domo Developer
 42 Workflows
 5 DomoAI
 32 Predict
 12 Jupyter Workspaces
 20 R & Python Tiles
 386 Distribute
 111 Domo Everywhere
 269 Scheduled Reports
 6 Software Integrations
 113 Manage
 110 Governance & Security
 8 Domo University
 30 Product Releases
 Community Forums
 39 Getting Started
 29 Community Member Introductions
 98 Community Announcements
 Domo Community Gallery
 4.8K Archive