Table of Contents
For a 3rd-get together logistics firm like RWI Logistics, geographic knowledge can be a very important software for solving troubles and developing methods for our prospects. We usually need to glimpse up no matter whether we’ve managed any truckloads into or out of a record of geographic parts, but it can be complicated to swiftly match places if they have a various town, state, and postal code from one particular a further.
For illustration, if we wished to know how quite a few masses we’ve sent in the vicinity of Fort Thomas, Kentucky, the most straightforward resolution would be to look for for past transactions with “City” = ‘Fort Thomas’ and “State” = ‘KY’. However this approach would not return transactions for Cincinnati, Ohio despite the reality that Cincinnati is only 5 miles from Fort Thomas, due to the fact the metropolis and state really don’t match our unique look for criteria.
Domo has enabled us to apply an efficient and repeatable resolution to this problem. Our method makes use of Redshift dataflows and the Domo Proportions Connector to match locations within just a specified mileage radius, fairly than relying on drawn boundaries like point out traces.
There are 3 datasets needed for this course of action:
- Location checklist – A postal code checklist for wished-for locations, these kinds of as prospective new client delivery locations
- Transaction heritage – A listing of previous destinations to research, that contains postal codes
- Domo Proportions Connector “cityzip.csv” report – We will use this dataset to glance up the latitude and longitude of each location
Stage 1 – Combination latitude/longitude desk
Some postal codes show up in the Domo Proportions dataset a number of occasions, so we have to use a table completely transform in Redshift to combination this table so there is just one row per postal code to avoid duplicating rows when we sign up for to the other tables.
choose
"Postal",
avg("Latitude") as "Lat",
avg("Longitude") as "Long"
from "city_zip"
team by "Postal"
Phase 2 – Completely transform areas into points on the world
We 1st want to obtain the coordinates of the postal codes in the site record and transaction background by becoming a member of both equally datasets to the aggregated latitude/longitude table by postal code. The st_place() function transforms the latitude and longitude of just about every postal code into a place on the world. Observe that managing SQL previews will not show any details when these details are incorporated in the query.
find |
select |
Step 3 – Join datasets
Now that the place record and transaction history the two incorporate details, we can use the st_DistanceSphere() operate to estimate the length (meters) among points. We use this distance as the requirements for the join in between the two tables. In this illustration, we match places and transactions that are within just 100 miles of just about every other.
choose
a."postal",
a."area name",
b."transaction #",
b."site name" as "transaction area identify"
from "area_coord" as a
left sign up for "background_coord" as b
on st_distancesphere(a."coord",b."coord") <= 1609.34*100
Step 4 – Visualize results
Using the above transform as the output of the Redshift dataflow, we then create a summary card that displays the count of historical transactions by location name. We can also gather more context by drilling to the transaction detail.
Since implementing this process, RWI Logistics has improved the efficiency and consistency of location-matching tasks. Domo has given us the ability to quickly analyze location data for various use cases and share insights across the organization.