Improving Supply Chain Logistics with Geographic Proximity Matching

For a 3rd-get together logistics company like RWI Logistics, geographic info can be a critical instrument for resolving problems and making options for our consumers. We generally have to have to search up regardless of whether we have managed any truckloads into or out of a checklist of geographic regions, but it can be tough to promptly match destinations if they have a different city, point out, and postal code from 1 a different.

For example, if we needed to know how quite a few hundreds we have delivered in the vicinity of Fort Thomas, Kentucky, the most basic solution would be to look for for past transactions with “City” = ‘Fort Thomas’ and “State” = ‘KY’. However this process would not return transactions for Cincinnati, Ohio despite the point that Cincinnati is only 5 miles from Fort Thomas, because the city and state never match our particular research standards.

Domo has enabled us to put into action an efficient and repeatable remedy to this dilemma. Our approach uses Redshift dataflows and the Domo Proportions Connector to match destinations in just a specified mileage radius, somewhat than relying on drawn boundaries like condition traces.

There are 3 datasets essential for this method:

• Area listing – A postal code record for wanted locations, such as possible new purchaser delivery areas
• Transaction background – A checklist of previous spots to lookup, that contains postal codes
• Domo Dimensions Connector “cityzip.csv” report – We will use this dataset to glance up the latitude and longitude of every site

Action 1 – Aggregate latitude/longitude table

Some postal codes show up in the Domo Proportions dataset numerous periods, so we must use a table transform in Redshift to aggregate this table so there is one particular row for every postal code to keep away from duplicating rows when we sign up for to the other tables.

decide on
  "Postal",
  avg("Latitude") as "Lat",
  avg("Longitude") as "Extended"
from "city_zip"
group by "Postal"

Stage 2 – Transform spots into details on the globe

We 1st need to locate the coordinates of the postal codes in the area record and transaction history by signing up for the two datasets to the aggregated latitude/longitude table by postal code. The st_position() perform transforms the latitude and longitude of each individual postal code into a point on the world. Notice that managing SQL previews will not exhibit any knowledge when these factors are involved in the question.

pick

  a."postal",

  a."area identify",

   b."transaction #",

   b."area title" as "transaction spot identify"

from "site_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.