Database Table Changes

Well Table

- 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

- 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

- deleted ~41,000 duplicate New York entries with sparse data. It was noticed that 17-digit entries ending in '-0000' had the most extensive data, so these were kept. Their 12-digit counterparts and ~800 longer APIs, with a combined total number equal to the 17-digit API count, were removed.

- replaced 562306 instances of horizontal_well with NULL to ensure proper operation of well table update script

- added index on pair WELL_COUNTY, WELL_STATE_CODE to speed up well update script (county portion)

- added index on WELL_COUNTY_WELL_MUNICIPALITY, WELL_STATE_CODE to speed up well update script (municipality portion)

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

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

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

Wellpad Table

- deleted row of almost all blanks

- replaced all by NULL in table

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

Production Table

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

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

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

- changed "" to NULL for WELL_API_COUNTY_ID, PERIOD_ID, PRODUCTION_INDICATOR, WELL_STATUS, FARM_NAME, WELL_ID, OPERATOR_NAME, WELL_COUNTY, WELL_MUNICIPALITY, COMMENT_REASON, COMMENT_TEXT, water_bbls, months_in_production

Permit Table

- 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

- 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

- 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

- 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

County Table

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

- deleted 67 duplicate entries to reduce from 3206 to 3139

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)