Like [Pennsylvania_Technical_Information] data is added to the server in the form of CSVs. All data originates from a Microsoft Access Database located at: http://oilandgas.ohiodnr.gov/industry/rbdms
Current data is from 6/19/2015.
WELL_API is in the form 34-XXX-XXXXX, access table in the form 34XXXXXXXXXXXX. Using excel with =LEFT(A2,2) & "-" & MID(A2,3,3) & "-" & MID(A2,6,5) we can convert this.
Well status came from a field titled WLstatus, and was converted to from a shortcode to a full description using the corresponding WLstatus table in the access database.
747 new OH wells added to well table. Some locational, SPUD, and county information updated with latest data from accessDB.
County information was updated using the appropriate County table, and modifying the short codes within the well table accordingly.
Latitude and Longitudes came from TblLocational.
All previous 2014 information was deleted.
51470 production entries for 2014/2015 added. Data for production in OH is actually quite sparse. Operator IDs are the Owner IDs. The only real data provided is the year of production, the quarter of production, gas MCF quantity, oil BBL quantity, Operator ID, Operator Name, comments (rarely) and the number of days of production. The number of days of production was used to fill out both the number of gas producing days and oil producing days.
There is no waste data within RBDMS.
SPUD date comes from the well table. There is no dedicated SPUD table for OH within RBDMS.
Permit date comes from the well table. There is no dedicated Permit table within RBDMS.
Use dates from 2014-01-01 to 2015-06-17
All prior 2014 data was deleted.
delete from compliance where well_id_fk like '34%' and INSPECTION_DATE >= STR_TO_DATE("01/01/2014", '%m/%d/%Y')
All data originated from TblInspection.
INSPECTION_TYPE, INSPECTION_CATEGORY, and TYPENOTIFICATION replaced using appropriate values from codes table. There is no corresponding entry for INSPECTION_RESULT_DESC.
LOAD DATA INFILE 'comp.csv' IGNORE
INTO TABLE compliance FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n' IGNORE 1 ROWS (INSP_ID, WELL_ID_FK, INSPECTION_TYPE, INSP_CATEGORY, @INSPECTION_DATE_TMP, INSP_COMMENT)
SET INSPECTION_DATE = STR_TO_DATE(@INSPECTION_DATE_TMP, '%m/%d/%Y');
~18,000 compliance entries added.
All data came from TblInspection. After compliance data was inserted, using Excel's index and match in correspondence with a CSV of Ohio compliance entries, WELL_ID_FK values were used to obtain the corresponding INSP_ID_PK values.
DT_NOV is date resolved for violation. No INSPECTION_RESULT_DESC. Comments are INSP_COMMENTS. Type_Notification is violation comment. No violation ID. No Violation code.
LOAD DATA INFILE 'viol.csv' INTO TABLE violation
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n' IGNORE 1 ROWS (INSP_ID_FK, @junk, @DATE_VIOLATION_TMP, VIOLATION_COMMENT, @DATE_RESOLVED_TMP)
SET DATE_VIOLATION = STR_TO_DATE(@DATE_VIOLATION_TMP, '%m/%d/%Y'), DATE_RESOLVED = STR_TO_DATE(@DATE_RESOLVED_TMP, '%m/%d/%Y')
655 violation entries added.
There is no penalty data in RBDMS for Ohio.
After the creation / updating of Ohio pages, chemical data from the fracfocus registry should be re-added to select pages. This is done by executing python chemicals.py US from the wikimanager folder. This will generated modified pages within staged_sources. After which, the pages must be committed using python commit_staged_sources.py staged_sources
In order to expediate the process, the sql statement us_well_sql can be modified to only select wells from OH.