If you find this page useful and would like to be notified of changes made to this page, start by inputting your email below.
powered by ChangeDetection
(→Well Table: well_api length change) |
|||
| Line 1: | Line 1: | ||
[[Category:Documentation]] | [[Category:Documentation]] | ||
| + | __TOC__ | ||
== Well Table == | == 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, | hole_number, | ||
objective_formation, | objective_formation, | ||
| Line 34: | Line 35: | ||
last_modified_date | 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) | |
| − | + | * FARM_NAME column added | |
| − | + | * in UNCONVENTIONAL, changed 'Yes' to 'Y' and 'No' to 'N' for consistency (previously, all four of these strings were present in large numbers) and to ensure source builder scripts interpret the column correctly. | |
| − | + | * Changed well_api to varchar(12) to help prevent APIs whose length is more than 10 digits. | |
| − | |||
| − | |||
==Unconventional Table== | ==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 | UPDATE well | ||
| Line 64: | Line 63: | ||
WHERE UNCONVENTIONAL LIKE 'Yes%' | WHERE UNCONVENTIONAL LIKE 'Yes%' | ||
| − | + | * changed all dates '0000-00-00' to NULL | |
== Waste Table == | == 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 == | == Wellpad Table == | ||
| − | + | * deleted row of almost all blanks | |
| − | + | * replaced all '' by NULL in table (empty strings) | |
| − | + | * changed all dates '0000-00-00' to NULL | |
== Production Table == | == 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 | |
| − | + | * changed LATITUDE_DECIMAL and LONGITUDE_DECIMAL values of 0 to NULL | |
| − | + | * Changed well_api to varchar(12) to help prevent APIs whose length is more than 10 digits. | |
== Permit Table == | == 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 | |
| − | + | * Changed well_api to varchar(12) to help prevent APIs whose length is more than 10 digits. | |
== Compliance Table == | == 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) | |
| − | + | * Changed well_id_fk to varchar(12) to help prevent APIs whose length is more than 10 digits. | |
==Spud Table== | ==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== | ==Municipality Table== | ||
| − | + | * backed up to ~/database_backups as municipality_with_demographic_info.sql.gz, then deleted demographic info | |
==County Table== | ==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== | ==Dropped 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
UPDATE well
SET UNCONVENTIONAL='Y'
WHERE UNCONVENTIONAL LIKE 'Yes%'
The following tables are properly backup up in a sql.gz file, and thus are redundant and dropped: