Improving Supply Chain Logistics with Geographic Proximity Matching
Table of Contents
For a third-get together logistics corporation like RWI Logistics, geographic facts can be a vital instrument for resolving complications and making remedies for our buyers. We usually want to glance up whether we’ve managed any truckloads into or out of a checklist of geographic regions, but it can be challenging to swiftly match locations if they have a different town, condition, and postal code from a person an additional.
For instance, if we wished to know how several loads we’ve delivered in the vicinity of Fort Thomas, Kentucky, the easiest remedy would be to research for previous transactions with “City” = ‘Fort Thomas’ and “State” = ‘KY’. On the other hand this strategy would not return transactions for Cincinnati, Ohio regardless of the point that Cincinnati is only 5 miles from Fort Thomas, due to the fact the metropolis and point out do not match our distinct lookup conditions.
Domo has enabled us to carry out an efficient and repeatable solution to this problem. Our approach works by using Redshift dataflows and the Domo Proportions Connector to match areas within a specified mileage radius, fairly than relying on drawn boundaries like point out strains.
There are 3 datasets required for this course of action:
- Locale record – A postal code list for ideal locations, these as potential new purchaser supply areas
- Transaction background – A listing of past spots to search, containing postal codes
- Domo Dimensions Connector “cityzip.csv” report – We will use this dataset to look up the latitude and longitude of each location
Phase 1 – Mixture latitude/longitude desk
Some postal codes show up in the Domo Dimensions dataset numerous occasions, so we should use a table transform in Redshift to aggregate this desk so there is a person row for each postal code to stay away from duplicating rows when we join to the other tables.
pick
"Postal",
avg("Latitude") as "Lat",
avg("Longitude") as "Lengthy"
from "town_zip"
team by "Postal"
Move 2 – Transform places into details on the world
We 1st want to uncover the coordinates of the postal codes in the location record and transaction history by becoming a member of both of those datasets to the aggregated latitude/longitude desk by postal code. The st_level() operate transforms the latitude and longitude of each and every postal code into a position on the globe. Notice that running SQL previews will not display any data when these details are integrated in the query.
pick |
select |
Move 3 – Sign up for datasets
Now that the locale checklist and transaction historical past the two have points, we can use the st_DistanceSphere() purpose to calculate the length (meters) involving factors. We use this distance as the requirements for the be part of in between the two tables. In this case in point, we match spots and transactions that are in just 100 miles of each other.
find
a."postal",
a."locale name",
b."transaction #",
b."spot identify" as "transaction location title"
from "spot_coord" as a
left sign up for "history_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.
