As I promised earlier, I’ve downloaded earthquakes from NCEDC’s web site (1898 to date), reverse-geocoded them via GeoNames and K-D Trees (thereby obtaining their country, state, county, and city/village values), archived the resulting files via 7-ZIP and uploaded both the CSV and SQLite datasets to:

• FrackingData Reverse-Geocoded Earthquakes from 1898-01-01 to date (updated on an approximately monthly basis).
  • NCEDC_earthquakes_18980101_thru_20170518_reverse_geocoded_y_header.csv.7z
    • comma-separated values, approximately 75 MB in size. compressed, 391 MB expanded
  • NCEDC_earthquakes_18980101_thru_20170518_reverse_geocoded.sqlite.7z
    • SQLite 3 database, approximately 162 MB in size, compressed, 1.1 GB expanded

I have authored a program in Python 3 that reverse-geocodes (via GeoNames and K-D Trees) the lat/longs into their respective countries, states, counties, and cities/villages.  This is the link to the open-source Python 3 reverse-geocoder project.  The program processes nearly 3 million rows in approximately 240 seconds.

Khepry Quixote
19 May 2017

As of 18 May 2017, various files (e.g. SQlite, CSV) derived from FracFocus.org’s May 2017 FracFocusRegistry have been downloaded, extracted, transformed, loaded, archived, and their respective links posted to FrackingData’s FracFocus Data Page .

This time, FracFocus posted their SQL Server backup on 15 May 2017, about 4 weeks later than its previous posting of 17 Apr 2017.

Once again, of significance this time was that the download of the files from the FracFocus.org website and their subsequent extract, transform, load, archiving, and exporting to CSV, SQLite, and PostgreSQL files was performed by a Windows batch script without human intervention. This automated method shaved hours from the extract, transform, load, archive, and export process.

When this Windows batch file is sufficiently stable, and I’ve soft-coded the data-cleansing views into the script itself,  I’ll post a link to it in the Source Code section of this blog.  Soft-coding of the data-cleansing views is the last hurdle to publishing this script.

Khepry Quixote 2017-05-18

Presently, some states have a downloadable Address Points file (e.g. Arkansas), while others don’t.  An Address Points dataset, coupled with a Well Location database would allow spatial queries such as “Fetch those addresses with X feet of any well’s location.”  This would be a useful feature in the auditing of flowlines that may pass near a any residence’s location, allowing an agency, auditor, journalist, or non-governmental organization (NGO) to verify that audits of the flowlines have been done on a periodic basis as specified by law or regulation, provided that the audit files are made public as well.

As this is a “night-job” task for me, it’s going to take a while for it to happen as my “day-job” allows.

Khepry Quixote
2017-05-10

Well Data Sources page has been updated for Arkansas hyperlinks that have been changed at some time in the past.

Khepry Quixote
2017-05-10

Because of the recent events in Colorado due to an uncapped flowline feeding gas from a well 178 feet away into the basement of a house and the subsequent explosion resulting in the deaths of two and the injury of one, there’s renewed interest in the locations of oil wells  in the State of Colorado, especially those that might be near residential buildings (or any buildings for that matter).

To this end, I’ve extracted a shapefile from the COGCC’s GIS downloads site, tweaked it a bit to add an explanation column for the status of the well, and then exported the resulting datasets to both a CSV and SQLITE file.  These CSV and SQLITE files were then compressed into a 7-ZIP archive suitable for download and inflation.

The 7-ZIP archive can be found at the following URL:

• https://s3.amazonaws.com/frackinganalysis-s3-01/downloads/Colorado_Wells_with_Facility_Status.7z

You can use:

• 7-Zip to inflate the compressed archive.
• SQLiteStudio to query the SQLITE file.
• LibreOffice Calc or Microsoft Excel Viewer for the CSV file.

The State of Colorado’s COGCC data download URL has been tweaked to http://cogcc.state.co.us/data2.html#/downloads.

This was done after researching Colorado’s well locations related to the recent residential fatalities and injuries due to an unplugged flowline feeding gas into the basement of a recently built house 178 feet away from the well in question, to wit, per EENews: Abandoned gas line caused Colo. home explosion.

Khepry Quixote
2017-05-04

As of 03 May 2017, various files (e.g. SQlite, CSV) derived from FracFocus.org’s April 2017 FracFocusRegistry have been downloaded, extracted, transformed, loaded, archived, and their respective links posted to FrackingData’s FracFocus Data Page .

This time, FracFocus posted their SQL Server backup on 17 Apr 2017, about 4 weeks later than its previous posting of 17 Mar 2017.

Once again, of significance this time was that the download of the files from the FracFocus.org website and their subsequent extract, transform, load, archiving, and exporting to CSV, SQLite, and PostgreSQL files was performed by a Windows batch script without human intervention. This automated method shaved hours from the extract, transform, load, archive, and export process.

When this Windows batch file is sufficiently stable, and I’ve soft-coded the data-cleansing views into the script itself,  I’ll post a link to it in the Source Code section of this blog.  Soft-coding of the data-cleansing views is the last hurdle to publishing this script.

Khepry Quixote 2017-05-03