A Sustainable Supply Chain Template with Geospatial Calculations in Python | by Sabi Horvat | Apr, 2023

By Jessie Hobb On Apr 25, 2023

A supply chain network footprint should include greenhouse gas calculations for both transportation and distribution centers, not just one or the other

Supply chain data scientists often need to be knowledgeable about a variety of fields to support various departments. This breadth contributes to the aspiration to optimize supply chain networks globally rather than to optimize local segments; a combination of local optimizations rarely leads to the best overall solution.

Sustainability is a relatively new area to supply chain analytics. Similar to the aspiration for global-rather-than-local-optimization, we also aspire to apply critical thinking to achieve the most valuable outcomes with sustainability metrics.

The United States Environmental Protection Agency (US EPA) calculates transportation as the largest of five economic sector categories contributing to greenhouse gas emissions in the US [1].

Transportation is a significant source of greenhouse gases, but there are additional sources to consider for supply chain calculations.

A supply chain network that provides goods and services to most residents in the US also relies on distribution centers to store, re-package, and prepare these products for shipment. The following structure shares an approach to combine transportation and facility contributions, with example data and calculations posted on GitHub:

Transportation software calculates greenhouse gas emissions
Data generation with python
Scenarios for Supply Chain GHG Emissions: One Distribution Center (DC)
Scenarios for Supply Chain GHG Emissions: Three DCs
Conclusion
Next Article: Multi-Objective Optimization

Many Transportation Management Systems (TMS) provide a calculation for greenhouse gas (GHG) emissions based on mileage.

The distance traveled, in the US in miles, can be multiplied by a factor such as 0.36 grams of CO2e per mile for a Less-Than-Truckload (LTL) truck. CO2e refers to carbon-dioxide-equivalent units, since CO2 is only one of the greenhouse gases that are biproducts of transportation.
The greenhouse gas emissions may be more specific, such as accounting for the specific type of vehicle rather than industry averages, the amount of weight in the truck’s trailer, and other factors.

Supply chain network design software can similarly include a CO2e amount next to distance and cost.

Unfortunately, only accounting for transportation might lead someone to incorrectly believe that shorter transportation distances imply lower CO2e emissions.

Shorter shipments that reach the consumer faster often require additional facilities such as distribution centers or cross docks. The emissions of a supply chain with lower transportation emissions can have a larger total emissions.

This article is accompanied by a sustainable supply chain template, posted on GitHub, to demonstrate supply chain emissions when accounting for both transportation and facility carbon footprints.

The following are key data points to consider for supply chain sustainability. My approximations were developed in python, but other tools could be similarly used.

The geocoded locations of distribution centers will be the origins and destinations on shipment routes.

I selected the 50 largest US metropolitan areas according to Wikipedia [2] for the data sample. Each metro will serve as a destination and the selected origin(s) for a given scenario will be located in one or more metros.
The next step was to retrieve the latitude and longitude for distance calculations and map creations. This practice is referred to as geocoding. While I prefer to automate this workflow in python, there are other ways to gather geo information using software, spreadsheets, internet resources, et cetera.
My preferred free-and-open-source python method, geopy, was temporarily out of service due to a dependency, but that service was available when I checked again ten days later. For the following script, I instead used a Google Maps API account, which fortunately waived the fee for the $0.32 worth of geo-data that I downloaded for this article. A geopy method is commented out but shown in the script as well.

In the following python script, I provided the city and state and received back the latitude and longitude for each location. I could have instead provided postal codes or full addresses and received more accurate geo-coordinates.

I ran this script for a few locations at a time to ensure that it was easy to validate the data, and then I updated the CSV file with the latest set of cities. There was one instance where I had mistyped an input to the Google Maps API by simply writing “California” while omitting the city name, but this process helped me catch my error immediately.

Enumerate the list of routes between locations

Each route in this simplified model will have one origin location and one destination location. I usually call these routes origin-destination pairs (or OD pairs). Source-destination pair or long-haul route are other common descriptors.
In order to have all of the routes available that I might use for modeling scenarios in the next steps, I want to create routes between all possible origins and all possible destinations. To create scenarios, I will later filter the data frame on subsets of these routes.
Using the cross merge from pandas, the cartesian product of 50 origins and 50 destinations results in 2500 OD pairs.

Calculate the distance between locations

The next step is to add the distances for each route in the origin_destinations_pairs data frame that was just created.
When traveling long distances, the curvature of the Earth is significant in the distance calculation. The haversine formula helper function calculates these Greatest Circle Distances (GCD) [3]. The greenhouse gas calculator I used in the next step also utilized the Greatest Circle Distance. While it is possible to obtain actual trucking distances, using the haversine arc-line distances is typically easier and in this case will ensure that the data formats that I combine in the model will match.
I made one modification to these distance results. The distance from a location to itself (e.g. Houston to Houston) was 0 miles. I changed these 50 occurrences of “selfy” routes to 10 miles, assuming that there may be routes to another point within the same city.

Calculate the greenhouse gas emissions for transportation and distribution centers

Disclaimer: The factors below are assumptions based on limited research. The purpose of using these assumptions is to demonstrate that the results of scenarios can vary. I respect the amount of work required to tune these factors for an organization, but that is beyond the scope of this article.

For the transportation greenhouse gas emissions, after evaluating a few calculators online, I decided to use the approximation / assumption of 0.36 grams of co2e per mile for a Less-Than-Truckload (LTL) truck from a particular route from DHL[3]. In short, I entered an origin and destination, confirmed that the distance for the grams of CO2e were for Greatest Circle Distances (GCD), and then divided by the total miles to obtain the grams per mile.
For the greenhouse gas emissions of an average distribution center, I combined public data on distribution center (DC) energy usage per square foot [4] and the emission impact of that energy [5] to arrive at 443 kilograms co2e per day for my example / assumption DC.
The model will assume that all shipments occur in one day. This way the daily DC emissions and daily shipment emissions can sum to a daily total.

The following scenarios demonstrate the total greenhouse gas emissions under the conditions and assumptions already mentioned. Utilizing the emissions factors, I used the following calculation for scenarios that utilize one distribution center.

For simplification, these calculations make the (incorrect) assumptions that:

All shipments can be completed in one day
Each truck could equally find shipments on their return route to the distribution center

The first function, footprint_for_one_dc(), requires the data filtered on the routes that are relevant for each scenario. For example, if I want to understand the distance and greenhouse gas emissions for one distribution center in Memphis, Tennessee and 50 shipping destinations, then my filtered data frame will have 50 rows.

The second function, map_supply_chain_network(), uses folium to create a map of the distribution centers, the destinations, and the “as the crow flies” line routes among the distribution centers and destinations. These type of OD pair maps are sometimes referred to as spider maps or network analysis maps.

One Distribution Center near the center of the US at Memphis, Tennessee

For the first scenario, I filtered the data frame distance_between_od_pairs (created above) on the origin of Memphis and ran the two functions for the calculations and map creation.

The distribution center location of Memphis yielded daily values of:

467 kg CO2e for DC and transportation emissions combined
24 kg CO2e for transportation emissions

Memphis DC using Leaflet and OpenStreetMap via folium in python (Image by Author)

One Distribution Center near the center of the US at St. Louis, Missouri

For the second scenario, instead of filtering on the origin of Memphis, I filtered on the origin of St. Louis. Then I ran the two functions for the calculations and map creation.

The distribution center location of St. Louis yielded daily values of:

466 kg CO2e for DC and transportation emissions combined
23 kg CO2e for transportation emissions

Since the footprint for each DC was assumed to be the same, we can deduce that the transportation footprint for St. Louis as a single location was better for this data scenario, compared to Memphis, by 1 kg CO2e.

St. Louis DC using Leaflet and OpenStreetMap via folium in python (Image by Author)

One Distribution Center at Seattle, Washington

For the third scenario, I chose Seattle for the DC location to demonstrate results when a single DC is far from the center of the US.

The distribution center location of Seattle yielded daily values of:

494 kg CO2e for DC and transportation emissions combined
51 kg CO2e for transportation emissions

The choice of a single distribution center location in Seattle might be the worst possible choice in terms of the metrics that we’re evaluating given the data for these scenarios.

Yet, 494 total kg CO2e is only about 6% higher than the two previous scenarios. For the data in these scenarios, if the DC was run on lower carbon energy sources, that could enhance the single Seattle distribution center into a more attractive option based on environmental metrics under these assumptions. Yet even with good environmental metrics, the transportation and total facility costs could be other factors that may lead to a decision to locate the DC in another location.

Seattle DC Image by Author using Leaflet and OpenStreetMap via folium in python

Finally, the last scenario chooses three DCs that are relatively equally spaced throughout the US: Riverside- California, Houston-Texas, and Columbus-Ohio.

For how to choose an optimal set of DCs, scroll down to the bottom of this article for the link to the next article about multi-objective optimization (when available).

In order to calculate the greenhouse gas emissions for a scenario with more DCs, the data wrangling process requires a few more steps:

I want each distribution center to single-source the destinations; each destination would only be served by the closest DC. It is a common practice to have primary transportation lanes setups this way, but secondary and tertiary lanes are usually an option.
The distribution centers can be smaller, to serve the same destination demands, so the greenhouse gas emissions per facility will be updated. I will make the assumption that each DC has a similar size, in order to simplify the calculation and model.

To choose the closest DC, first I filtered on the three origin DCs, similar to the earlier scenarios. Next, I filtered the destination on the minimum distance in order to pick the closest DC for each destination.

Since the three DCs would service a subset of the the same destinations as one central DC, each DC would need to carry more inventory to ensure products are available for a sale when needed. While it is possible to ship products from a farther DC when the closer location is out of stock, minimum inventory levels for planning and operations can be estimated by the supply chain square root rule of thumb. Otherwise, a more complex simulation would be required to account for such possibilities.

The supply chain square root rule of thumb estimates how much more inventory we would need to maintain the same service levels. For example, for three DCs versus one DC:

square_root(3 / 1) = 1.73

For three DCs compared to one, we would need 1.73 times as much inventory, or 73% more. Since there are three DCs to cover that quantity, if the DC footprints scale linearly and apply equally to each DC, then the 433 kg CO2e of daily DC scales to 766 kg CO2e, or approximately 256 kg CO2e each for the smaller DCs.

Three Distribution Centers at Riverside-CA, Houston-TX, and Columbus-OH

For the fourth scenario, I chose three DCs that seemed visually spaced apart. These are not necessarily the optimal three to choose, but the process for how to make an optimal choice will be covered in the next article.

The Riverside, Houston, and Columbus DCs combined for:

779 kg CO2e DC and transportation emissions
11 kg CO2e transportation emissions

These three DCs reduced the transportation emissions by more than 50% compared to one of the central DCs. But the total emissions increased by 40% due to the additional DCs.

Three DCs’ Image by Author using Leaflet and OpenStreetMap via folium in python

Under certain conditions, more distribution centers and shorter shipping distances can lead to less greenhouse gas emissions. But this limited example demonstrates that shorter final-shipping destinations may not always be indicative of a supply chain network with a lower carbon footprint.

In order to design a sustainable supply chain, the considerations of both transportation and facility impacts should be considered, along with other factors.

To use the template or contribute to improve the template, visit GitHub.

Although this article focused on sustainability, cost or time are often prioritized for supply chain metrics.

The next article will summarize different ways that Operations Research can balance multiple objectives for complex mathematical optimization problems. The balancing is required, since the minimum cost will rarely also lead to the best service level or sustainability metrics.

If you would like to receive a notification when the next article is available, follow me on Medium and click the subscribe option on my profile. There will also be an (Update:) and link included here.

If you enjoyed this article and would like to read (and write) more articles like this, consider clicking the Follow button by my profile picture and/or subscribing to a medium membership using my referral link: https://sabolch-horvat.medium.com/membership

[1] “Sources of Greenhouse Gas Emissions.” US Environmental Protection Agency, 2011, https://www.epa.gov/ghgemissions/sources-greenhouse-gas-emissions. 18 April 2023.

[2] “Metropolitan statistical area.” Wikipedia, Wikimedia Foundation, 15 March 2023, https://en.wikipedia.org/wiki/Metropolitan_statistical_area.

[3] “Haversine formula.” Wikipedia, Wikimedia Foundation, 7 April 202, 3https://en.wikipedia.org/wiki/Haversine_formula.

[4] “Carbon Calculator.” DHL website, accessed 8 April 2023, https://www.dhl-carboncalculator.com/.

[5] “Electricity consumption totals and conditional intensities by building activity subcategories, 2012.” U.S. Energy Information Administration, Released 2016, https://www.eia.gov/consumption/commercial/data/2012/c&e/cfm/pba4.php.

[6] Ries, J.M., Grosse, E.H. and Fichtinger, J. (2016). Environmental impact of warehousing: a scenario analysis for the United States. International Journal of Production Research, doi: 10.1080/00207543.2016.1211342

Permanent repository link: https://openaccess.city.ac.uk/id/eprint/17118/