Database Table Changes

Well Table

Mid-June 2015:

- deleted indexes FIPS_MUNICIPALITY_2, ..., FIPS_MUNICIPALITY_5

- deleted row where WELL_API=/ and row where well_api was blank

- deleted LATITUDE_UNCONVENTIONAL column (all values were NULL except for one, which was 0)

- deleted LONGITUDE_UNCONVENTIONAL column (all values were NULL except for one, which was 3)

- deleted UNCONVENTIONAL_UNCONVENTIONAL column (all values were NULL except for one, which was O)

- deleted HORIZONTAL_UNCONVENTIONAL column (all values NULL)

- deleted PERMIT_STATUS column (all values NULL)

- Dropped the following columns (specific to new york, exist in other tables): hole_number, objective_formation, producing_formation, quad_section_code, map_quadrangle, field, status_date, well_completion_date, plugging_and_abandonment_date, confidential_expiration_date, confidential_period_type, nysdec_region, subject_to_financial_security, bottom_hole_longitude, bottom_hole_latitude, last_modified_date

- in UNCONVENTIONAL table, changed 236 rows from 'Yes' to 'Y' and 439 rows from 'No' to 'N'. There was some type of return character on the ends of 'Yes' and 'No' making searches hard. Code example:

UPDATE well

SET UNCONVENTIONAL='Y'

WHERE UNCONVENTIONAL LIKE 'Yes%'

- changed all dates '0000-00-00' to NULL

Waste Table

Mid-June 2015:

- deleted row of blanks, nulls, and 0's. WELL_API was simply a blank.

Wellpad Table

Mid-June 2015:

- deleted row of almost all blanks

- replaced all by NULL in table

- changed all dates '0000-00-00' to NULL

Production Table

Mid-June 2015:

- changed all dates '0000-00-00' to NULL

Permit Table

Mid-June 2015:

- there were two equivalent columns : PERMIT_ISSUED_DATE and permit_issue_date. Since a search revealed that the latter was unused, it has been dropped from the table.

- removed ' Well' from the ends of entries in the configuration column, since not all entries had this appended

June 20, 2015:

- replaced 47774 instances of PERMIT_ISSUED_DATE='0000-00-00' with NULL

- replaced 182165 instances of SPUD_DATE='0000-00-00' with NULL

- replaced 47742 instances of permit_application_date='0000-00-00' with NULL

- for WV wells, set 145304 instances of proposed_total_depth=0 to

- set 2 instances of OPERATOR_NAME blank, 87 instances of OPERATOR_NAME=' No record available in Charleston' (all WV), 13835 instances of OPERATOR_NAME=' unknown' (all WV) to NULL

- set 145302 instances of CONFIGURATION= to NULL

- set 5714 instances of WELL_TYPE= to NULL

- set 138845 instances of FARM_NAME= to NULL

- set 80838 instances of LATITUDE_DECIMAL, LONGITUDE_DECIMAL = 0, 0 to NULL, NULL

- set 87412 instances of OPERATOR_OGO= to NULL

- set 1177 instances of UNCONVENTIONAL='No' to 'N' for consistency and since 'N' was the more common of the two

- set 3472 instances of UNCONVENTIONAL='Yes' to 'Y' for reasons previous

Compliance Table

Mid-June 2015:

- in INSP_CATEGORY, changed blank entries to NULL and filled out 319,409 existing erroneous entries 'Primary Fa' to 'Primary Facility'

- changed blank entries to NULL in INSP_ID, INSPECTION_TYPE, INSPECTION_RESULT_DESC, INSP_COMMENT, DB_COMMENTS