West Virginia Technical Information

General

CSVs are copied to the server. Their group and ownership are changed to 'mysql' using the chgrp and chown commands. They are then moved to /var/lib/mysql/wellsdb_dev/ such that they can be read by mysql.

CSVs should be checked for " \n \ and other special characters prior to being uploaded to the server. These special characters can cause the files to be processed incorrectly, which leads to data going into the wrong columns or not being uploaded at all.

Always make backups prior to destructive operations.

PERMIT:

Cleared out old data since new data is much more extensive:

DELETE FROM permit

WHERE WELL_API LIKE '47%'

Populating permit table from owncomp:

LOAD DATA INFILE 'Wv_permit.csv'

INTO TABLE permit

FIELDS TERMINATED BY ','

ENCLOSED BY '"'

LINES TERMINATED BY '\n'

IGNORE 1 ROWS

(@WELL_API_TMP, WELL_COUNTY, DB_COMMENTS, OPERATOR_OGO, FARM_NAME, OPERATOR_NAME, @PERMIT_ISSUED_DATE_TMP, SPUD_DATE, WELL_TYPE, CONFIGURATION, proposed_total_depth)

SET WELL_API = CONCAT(SUBSTRING(@WELL_API_TMP,1,2),'-',SUBSTRING(@WELL_API_TMP,3,3),'-',SUBSTRING(@WELL_API_TMP,6,5)),

WELL_API_COUNTY_ID = SUBSTRING(@WELL_API_TMP,3,3),

PERMIT_ISSUED_DATE = DATE_FORMAT(STR_TO_DATE(CONCAT(

SUBSTRING(@PERMIT_ISSUED_DATE_TMP,1,2),'-',

SUBSTRING(@PERMIT_ISSUED_DATE_TMP,4,2),'-',

SUBSTRING(@PERMIT_ISSUED_DATE_TMP,7,2)),'%d-%m-%y'),'%Y-%m-%d'),

WELL_STATE_CODE='WV',

WELL_COUNTRY='United States';

Grabbing latitude and longitude from locs (a table is created for this to make operations easier and to make the lat/lon data available to be input into other tables' WV data as well):

CREATE TABLE wv_latlon_loader

(

WELL_API varchar(50),

LATITUDE_DECIMAL double,

LONGITUDE_DECIMAL double

);

LOAD DATA INFILE 'Wv_latlon.csv'

INTO TABLE wv_latlon_loader

FIELDS TERMINATED BY ','

ENCLOSED BY '"'

LINES TERMINATED BY '\n'

IGNORE 1 ROWS

(@WELL_API_TMP, LATITUDE_DECIMAL, LONGITUDE_DECIMAL)

SET WELL_API = CONCAT(SUBSTRING(@WELL_API_TMP,1,2),'-',SUBSTRING(@WELL_API_TMP,3,3),'-',SUBSTRING(@WELL_API_TMP,6,5));

Update permit to include latitude and longitude values:

UPDATE permit, wv_latlon_loader

SET permit.LATITUDE_DECIMAL = wv_latlon_loader.LATITUDE_DECIMAL, permit.LONGITUDE_DECIMAL = wv_latlon_loader.LONGITUDE_DECIMAL

WHERE permit.WELL_API = wv_latlon_loader.WELL_API;