investigate transfers

[ericnost]

Taking Berthauime’s 2024 paper investigating pollutant transfers as a starting point, investigate how these can be used to describe environmental racism in a relational way. The transfers data tell us where what pollutants went/came from. Perhaps we can use this to describe EJ drivers.

1. places in Canada where Canadian entities send pollutants (should be acessible from npri_offsite table)
2. places in Canada where US entities send pollutants (maybe be accessible from full NPRI database, may need to look at TRI)

[ericnost]

For 2 above:

import requests, zipfile, io, pandas

url = 'https://www3.epa.gov/tri/current/US_2022.zip' # Probably try to compile all TRI years?
r = requests.get(url)
z = zipfile.ZipFile(io.BytesIO(r.content))
z.extractall("/content")

tri = pandas.read_csv("US_3a_2022.txt", delimiter = "\t", encoding='latin1', on_bad_lines='skip') # May have some problems loading

tri.loc[tri["104. OFFSITE COUNTRY ID"]=="CA"]

[ericnost]

For 1 above:

select * from npri_offsite limit 5; -- pull out one offsite location: 715

select * from npri_substance_offsite where "OffsiteID" = 715; -- get substance reports for this offsite ID

select * from npri_report_substance where "SubstanceReportID" = 548114; -- get info about one particular substance report

select * from npri_report where "ReportID" = 104883; -- get the report context

select * from facility where "FacilityId" = 282556; -- get the context for this facility

select * from geographiclocation where "NpriId" = 2670; --lat long for the facility

select * from detail_npri_substanceinfo where "SubstanceID" = 14189; -- get info about the transferred pollutant