Category Archives: fracking

Earthquakes: Latest (1898-01 thru 2017-02-21) Reverse-Geocoded Files Posted

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:

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
21 Feb 2017

Status Update 2017-02-15: FrackingData_FracFocusRegistry 2017-02 Files Uploaded

As of 15 Feb 2017, various files (e.g. SQlite, CSV) derived from FracFocus.org’s February 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 06 Feb 2017, about 3weeks later than its previous posting of 17 Jan 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-02-15

Status Update 2017-01-23: FrackingData_FracFocusRegistry 2017-01 Files Uploaded

As of 23 Jan 2017, various files (e.g. SQlite, CSV) derived from FracFocus.org’s January 2017 FracFocusRegistry have been downloaded, extracted, transformed, loaded, archived, and uploaded to the frackingdata.info/downloads site and their respective links also posted to FrackingData’s FracFocus Data Page .

This time, FracFocus posted their SQL Server backup on 17 Jan 2017, about 4 weeks later than its previous posting of 16 Dec 2016.

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.  In addition, the batch script now uses WinSCP to automatically upload the files in question to the http://frackingdata.info/downloads page.

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-01-23

Fracking quakes-It’s time for a focused analysis of the recent temblors in Oklahoma

While FrackingData.org provides fracking and earthquake-centric datasets suitable for most any citizen-scientist or analyst to consume, it does so with a bend towards the generic.  After the latest significant magnitude 5.0 earthquake to hit Oklahoma near the town of Cushing at or about 7:44 PM on November 6, 2016, as well as the magnitude 5.8 earthquake to hit 8 miles northeast of Pawnee on September 3, 2016, I’ve decided to apply my data analysis and mapping skills to making focused datasets concentrating on the State of Oklahoma’s earthquakes and underground injection wells.

When the State of Kansas experienced fracking-operation related earthquakes, reports were that they reduced the volume of wastewater injected into their underground disposal wells, whereas sources have reported that the State of Oklahoma initially changed not the volume of the wastewater disposed, but the depths at which it was injected.  Therefore, it would seem beneficial if a review of the practices of both states were undertaken, as well as any accrued benefits.

With the preceding in mind, I posit the following:

  • Pull all magnitude 0.0 and above earthquakes,from 1898 to present, from the NCEDC website.
  • Reverse-geocode the aforementioned earthquakes, adding the country, state, county, and nearest city/village in the process.
  • Locate the oil well location datasets for the states of Oklahoma and Kansas, if such are available.
  • Locate the underground injection well datasets for the states of Oklahoma and Kansas, if such are available.
  • Extract, transform, and load (ETL) the aforementioned oil and injection well datasets into a standardized layout suitable for singular and multiple state analyses.
  • Locate the volume of wastewater injection datasets for both states, if such exist.
  • ETL the volume of wastewater injection datasets into a standardized layout suitable for singular and multiple state analyses.
  • Publish the subsequent datasets, along with the methodology used to ETL them, for use by the fracking data analysis community.
  • Produce a step-by-step guide of the subsequent analyses, complete with SQL or source code, as an example of using the datasets for research.
  • Submit my study(ies) to the State of Oklahoma as well as various media outlets for their use or commentary, whichever is more appropriate based upon the nature of the receiving entity.

Please note that I’ll post my progress on the bullet points listed above, as well as build out a “living document” of my adventures in doing so at FrackingData.org’s sister site: FrackingData.info.

In the meantime, I hope that no loss of life occurs due to the continued practice of wastewater injection into underground wells.  That being said, given the State of Oklahoma’s economic dependence upon oil as a means of income and its reluctance to date in reining its activities, I fear that loss of life will be inevitable.  “Loss of life” seems such an abstract phrase, especially when it appears in print, but given that I’ve experienced its direct effects more than once, I can assure all that might read this post that it is deeply personal and most certainly not abstract to those that encounter it on a first-hand basis.

Khepry Quixote
7 November 2016

MS Access: Database posted to downloads site

Microsoft Access, while not SQL-92 compliant, is a very popular database program suitable for analytical use by many people that don’t use R, SAS, or Tableau for analysis and reporting purposes.

Concerning FracFocus.org-related data, and back again by popular demand, FrackingData.org is now providing (see link below) a Microsoft Access database in “accdb” format containing various tables as follows:

  • FracFocus.org-related tables
    • dbo_RegistryUpload
    • dbo_RegistryUploadPurpose
    • dbo_RegistryUploadIngredients
  • Earthquakes-related tables
    • NCEDC_earthquakes_reverse_geocoded (worldwide, 1898 to date, magnitude 0 and up)
  • Toxicities-related tables
    • Chemical_Toxicities_Blended_Sorted
    • Chemical_Toxicities_Blended_Grouped
    • Chemical_Toxicities_Blended_Flattened_Boolean
  • Views utilizing the above tables
    • vue_Registry_Upload_Purpose_Ingredients
    • vue_Registry_Upload_Purpose_Ingredients_Toxicities
  • Link(s) to Microsoft Access database(s), compressed with the 7-Zip program:

Henceforth, this database will be available on the same schedule as the CSV, SQLite, and PostgreSQL files and a page holding the latest link can be found on the FracFocus Data page of FrackingData.org’s site (link below):

Khepry Quixote
10 June 2016

Status Update 2016-06-01: FrackingData_FracFocusRegistry 2016-05 Files Uploaded

As of 01 June 2016, various files (e.g. SQlite, CSV, and PgSQL) derived from FracFocus.org’s April 2016 FracFocusRegistry have been downloaded, extracted, transformed, loaded, archived, and uploaded to the frackingdata.info/downloads site and their respective links also posted to FrackingData’s FracFocus Data Page .

This time, FracFocus posted their SQL Server backup on 23 May 2016, almost a month later than its previous posting of 26 April 2016.

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.  In addition, the batch script now uses WinSCP to automatically upload the files in question to the http://frackingdata.info/downloads page.

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 2016-06-01

Earthquakes: Reverse-Geocoded Files Posted to frackingdata.info/downloads

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:

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.  I will post a link to the open-source project shortly once I’ve vetted its license and repository.  The program processes nearly 3 million rows in approximately 240 seconds.