Table of Contents
For a 3rd-bash logistics firm like RWI Logistics, geographic knowledge can be a essential instrument for resolving problems and building methods for our clients. We often need to seem up whether or not we’ve managed any truckloads into or out of a record of geographic parts, but it can be hard to immediately match destinations if they have a various metropolis, state, and postal code from a person another.
For instance, if we needed to know how numerous masses we’ve delivered in the vicinity of Fort Thomas, Kentucky, the easiest option would be to lookup for past transactions with “City” = ‘Fort Thomas’ and “State” = ‘KY’. Even so this technique would not return transactions for Cincinnati, Ohio regardless of the fact that Cincinnati is only 5 miles from Fort Thomas, mainly because the city and condition really don’t match our particular look for standards.
Domo has enabled us to carry out an productive and repeatable alternative to this dilemma. Our strategy uses Redshift dataflows and the Domo Dimensions Connector to match spots within just a specified mileage radius, relatively than relying on drawn boundaries like condition lines.
There are three datasets needed for this procedure:
- Location checklist – A postal code listing for preferred places, such as prospective new buyer supply spots
- Transaction background – A record of previous destinations to search, containing postal codes
- Domo Proportions Connector “cityzip.csv” report – We will use this dataset to search up the latitude and longitude of each locale
Stage 1 – Combination latitude/longitude table
Some postal codes surface in the Domo Dimensions dataset many moments, so we have to use a desk renovate in Redshift to mixture this table so there is a single row for each postal code to prevent duplicating rows when we be a part of to the other tables.
pick
"Postal",
avg("Latitude") as "Lat",
avg("Longitude") as "Extensive"
from "town_zip"
group by "Postal"
Stage 2 – Completely transform spots into details on the world
We first will need to discover the coordinates of the postal codes in the site record and transaction record by signing up for equally datasets to the aggregated latitude/longitude desk by postal code. The st_issue() purpose transforms the latitude and longitude of each individual postal code into a position on the world. Notice that operating SQL previews will not show any knowledge when these factors are provided in the query.
choose |
pick |
Phase 3 – Be part of datasets
Now that the area listing and transaction background both of those consist of points, we can use the st_DistanceSphere() purpose to compute the length (meters) involving details. We use this distance as the conditions for the be part of among the two tables. In this case in point, we match areas and transactions that are inside 100 miles of every single other.
pick out
a."postal",
a."spot title",
b."transaction #",
b."location name" as "transaction site name"
from "location_coord" as a
left join "heritage_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.