Improving Supply Chain Logistics with Geographic Proximity Matching

For a third-party logistics enterprise like RWI Logistics, geographic data can be a crucial tool for resolving issues and generating options for our prospects. We usually have to have to glance up no matter whether we’ve managed any truckloads into or out of a record of geographic regions, but it can be tough to quickly match destinations if they have a various town, state, and postal code from 1 a further.

For case in point, if we required to know how lots of masses we have sent in the vicinity of Fort Thomas, Kentucky, the most straightforward answer would be to lookup for earlier transactions with “City” = ‘Fort Thomas’ and “State” = ‘KY’. Even so this technique would not return transactions for Cincinnati, Ohio in spite of the fact that Cincinnati is only 5 miles from Fort Thomas, since the town and condition really do not match our particular search conditions.

Domo has enabled us to put into action an successful and repeatable answer to this dilemma. Our strategy makes use of Redshift dataflows and the Domo Proportions Connector to match places inside of a specified mileage radius, somewhat than relying on drawn boundaries like condition lines.

There are 3 datasets required for this procedure:

• Location listing – A postal code checklist for preferred destinations, this kind of as possible new consumer shipping areas
• Transaction record – A checklist of past places to research, that contains postal codes
• Domo Proportions Connector “cityzip.csv” report – We will use this dataset to look up the latitude and longitude of each individual place

Move 1 – Combination latitude/longitude desk

Some postal codes look in the Domo Proportions dataset a number of situations, so we need to use a table rework in Redshift to combination this desk so there is a single row per 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 "Very long"
from "metropolis_zip"
group by "Postal"

Step 2 – Change spots into details on the globe

We very first need to obtain the coordinates of the postal codes in the site checklist and transaction historical past by becoming a member of equally datasets to the aggregated latitude/longitude table by postal code. The st_stage() function transforms the latitude and longitude of each individual postal code into a point on the world. Notice that operating SQL previews will not exhibit any facts when these points are incorporated in the question.

select

  a."postal",

  a."locale title",

   b."transaction #",

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

from "area_coord" as a

  left be part of "historical past_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.