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

June 23, 2015:

- changed WELL_STATE_CODE to NY for wells with APIs beginning with 31. It was noticed that many of these had been previously misassigned as PA.

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

Unconventional Table

Mid-June 2015:

- 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.

June 22, 2015:

- changed default value for WELL_COUNTRY from 'United States' to NULL to comply with asana data standards

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

June 22, 2015:

- changed default value for WELL_COUNTRY from 'United States' to NULL to comply with asana data standards

June 23, 2015:

- removed all WV entries, since they were incredibly sparse. Will repopulate with the most up-to-date data.

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/22, 2015:

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

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

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

- for set instances of proposed_total_depth=0 to NULL

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

- set instances of CONFIGURATION= to NULL

- set instances of WELL_TYPE= to NULL

- set instances of FARM_NAME= to NULL

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

- set instances of OPERATOR_OGO= to NULL

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

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

- changed default value for WELL_COUNTRY from 'United States' to NULL to comply with asana data standards

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

- set instances of WELL_TYPE='NOT AVAILABLE' to NULL (all WV)

Spud Table

June 22, 2015:

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

- set instances of OPERATOR_OGO= to NULL - set instances of OPERATOR_NAME=, OPERATOR_NAME=' No record available in Charleston' (all WV), and OPERATOR_NAME=' unknown' (all WV) to NULL

- set instances of FARM_NAME= to NULL

- set instances of WELL_TYPE= to NULL

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

- set instances of WELL_TYPE='NOT AVAILABLE' to NULL (all WV)

- changed default value for WELL_COUNTRY from 'United States' to NULL to comply with asana data standards

Municipality Table

- backed up to ~/database_backups as municipality_with_demographic_info.sql.gz, then deleted demographic info

Dropped Tables

The following tables are properly backup up in a sql.gz file, and thus are redundant and dropped:

• production_backup
• compliance_backup
• permit_backup
• penalty_backup
• violation_backup
• v_fips_year (view)
• v_compliance (view)
• v_munic_pca (view)
• v_waste_facility (view)
• v_waste_operator (view)