Skip to content

Carbon footprinting of the 2019 NCAA Men's Basketball Tournament to analyze the 2020 tournament's cancellation impacts.

License

Notifications You must be signed in to change notification settings

cooperjaXC/covid_marchmadness

Folders and files

NameName
Last commit message
Last commit date

Latest commit

 

History

25 Commits
 
 
 
 
 
 
 
 
 
 
 
 
 
 

Repository files navigation

Carbon Footprinting the 2019 NCAA Men's Basketball Tournament

These scripts are the ones run by J. A. Cooper to roughly estimate the quantity of greenhouse gas (GHG) emissions associated with the 2019 NCAA Men's Basketball Tournament, i.e. "March Madness," in the United States.

This project is associated with peer-reviewed publications in the Journal of Cleaner Production and Tourism Geographies, and it draws heavily from a previous publication in the Journal of Sport & Tourism with a similar methodology (see that GitHub Repo).

References
----------
Cooper, J. A. (2020). Making orange green? A critical carbon footprinting of Tennessee football gameday
    tourism. Journal of Sport & Tourism, 24(1): 31-51.
Cooper, J. A. & Derek H. Alderman (2020). Cancelling March Madness exposes opportunity for more 
    sustainable sports tourism economy. Tourism Geographies, 22(3): 525-535.
Cooper, J. A. & Brian M. McCullough (2021). Bracketing sustainability: Carbon footprinting March Madness 
    to rethink sustainable tourism approaches and measurements. Journal of Cleaner Production, 318.

Included here are basic instructions for understanding the process and reproducing it if necessary. Note that the following steps were run in a Windows 10 environment using the git bash shell; please make necessary adjustments if using different configurations or versions of PostgreSQL.

Prerequisites

Install

python -m pip install psycopg2

Personal Machine Setup

Connections to PostgreSQL Databases

  • This uses individualized roles created for the author's personal workflow. A user looking to reproduce this work may need to adopt these role names or change role names referenced downstream in .sql (and other) files.
  • Setup a pgpass.conf file in your App Data \ postgresql directory so you can access these databases.
~\AppData\postgresql\pgpass.conf
  • Make sure these items are included
pgpass.conf
localhost:5432:covid:adminname:adminpass
localhost:5432:covid:username:userpass
  • Change permissions for that file.
$ chmod 0600 pgpass.conf
  • Set the pgpass.conf file as the PGPASSFILE environment variable.
$ export PGPASSFILE='~\AppData\postgresql\pgpass.conf'
  • Also set up the pg_hba.conf file in your Program Files \ PostgreSQL directory
~\ProgramFiles\postgresql\11\data\pg_hba.conf
  • Make sure these items are included
# TYPE DATABASE USER ADDRESS METHOD
# IPv4 local connections:
host all all 127.0.0.1/32 md5
# IPv6 local connections:
host all adminname ::1/128 trust
host all postgres ::1/128 md5
host all username ::1/128 trust
# Allow replication connections from localhost, by a user with the replication privilege.
host replication all 127.0.0.1/32 md5
host replication all ::1/128 md5
  • Then, make sure your postgresql system is started

    • Run these commands as an administrator
    $ /c/Program\ Files/PostgreSQL/11/bin/pg_ctl.exe start -D /c/Program\ Files/PostgreSQL/11/data
    
    • Remember, anytime you change configurations, you have to reload the re-load that configuration file
    $ /c/Program\ Files/PostgreSQL/11/bin/pg_ctl.exe reload -D /c/Program\ Files/PostgreSQL/11/data
    

Database Creation

  • Run each line of the ~/bash/createpsqldb_fromcmdline.bash to create the covid database and enable all PostGIS features.
  • Connect to the database in pgAdmin 4

Loading Initial Data

The data for this project is in .csv format in the ~\data_init_forgit directory.

The .sql file associated with this process is ~\sql\admin0_initdb_importdata.sql

  • Team data was originally uploaded via KML → QGIS → PostgreSQL covid database midway through processes laid out in ~\sql\admin0_initdb_importdata.sql.
    • However, some work has already been completed for you. Find a .csv copy of this data in the ~\data_init_forgit directory. Then, upload it manually to the marchmad SCHEMA of covid DATABASE using pgAdmin 4.
  • Session location data is uploaded via running ~\sql\admin0_initdb_importdata.sql. A shell table marchmad.locats_ncaat19 is created with the code in the file.
    • After this, manually import the 2019ncaatourneylocatdata.csv from the ~\data__init__forgit directory using pgAdmin 4.

Merging team and session data

After successfully loading the TABLES marchmad.teams_ncaat19 and marchmad.locats_ncaat19, run ~/sql/admin1_mergeteamgamelocats.sql.

This will result in a marchmad.locats_ncaat19 table that contains attendance and locational data for each session site and all 4 teams from that session.

Carbon Footprinting the 2019 Data

  1. Run ~/sql/admin2_environcalcs1.sql to set up the environ SCHEMA and import hotel emissions data from Ricaurte & Jagarajan (2019).

  2. Next is the only python step of the project. Run ~/python/admin3_environcalcs2.py for quick psycopg2-bsaed PSQL commands to actually footprint the tournament at the per-session level.

  3. Run ~/sql/admin4_environcalcs3.sql to tabulate the data in different ways by creating tables in the environ SCHEMA at the following levels:

    • per-team-per-round
    • per-trip
    • per-team
    • per-host site
    • the whole tournament.

Exploring the Data

Use the user_.sql files in ~/sql/ directory

These user files will give you a head start in exploring your new data in a safer user rather than admin querying arena that will ensure you don't destroy your hard-created database.

Also, you can import the data with its spatial attributes in the geometry columns into your GIS viewer like ArcGIS or QGIS.

About

Carbon footprinting of the 2019 NCAA Men's Basketball Tournament to analyze the 2020 tournament's cancellation impacts.

Topics

Resources

License

Stars

Watchers

Forks

Releases

No releases published

Packages

No packages published