AMET Notes

Update the MET database to include state IDs, NCLD 2011 landcover categories, and shoreline distance

Steps:

1. Download a METAR stations file and convert it to a CSV with three columns: Line number (id), 2-digit state code, METAR 4-digit station ID, and use a GIS to add in distance from shoreline and NLCD 2011 codes
2. Create a new table in the AMET database called "states"
3. Load the process stations.csv file into the states table
4. Add columns to the existing stations table for the NLCD and Shoreline data
5. Join the states table to the stations table on the station ID field to add in the new fields

Here's the MySQL code, assuming the database is named amet

 mysql> use amet;
 mysql> create table states (id INT NOT NULL AUTO_INCREMENT, CODE VARCHAR(4) NOT NULL, 
        ST  VARCHAR(2) NOT NULL, NLCD11 VARCHAR(2) NOT NULL, SHORELINE VARCHAR(6) NOT NULL, PRIMARY KEY (id));
 mysql> alter table stations add NLCD11 varchar(2);
 mysql> alter table stations add SHORELINE varchar(6);
 mysql> load data local infile 'MADIS_Stations_NLCD11_Shoreline_cut.csv' into table states fields terminated by ',' 
        lines terminated by '\n' ignore 1 rows (id, CODE, ST, NLCD11, SHORELINE);
 mysql> update stations join states on stations.stat_id=states.CODE set stations.state=states.ST, 
        set stations.NLCD11=states.NLCD11, set stations.SHORELINE=states.SHORELINE;
 mysql> select * from stations limit 10;

+---------+------------+---------+-----------+------+------+---------+---------+--------------------+-------+---------+--------+-----------+
| stat_id | ob_network | lat     | lon       | i    | j    | elev    | landuse | common_name        | state | country | NLCD11 | SHORELINE |
+---------+------------+---------+-----------+------+------+---------+---------+--------------------+-------+---------+--------+-----------+
| KOLS    | METAR      | 31.4200 | -110.8500 | NULL | NULL | 1206.00 |    NULL |                    | AZ    | NULL    | 52     | Inland    |
| KALK    | METAR      | 31.6000 | -110.4200 | NULL | NULL | 1433.00 |    NULL |                    | AZ    | NULL    | 52     | Inland    |
| KTUS    | RAOB       | 32.2300 | -110.9600 | NULL | NULL |  751.00 |    NULL | NULL               | AZ    | NULL    | 22     | Inland    |
| KRYN    | METAR      | 32.1400 | -111.1700 | NULL | NULL |  737.00 |    NULL |                    | AZ    | NULL    | 52     | Inland    |
| KAVQ    | METAR      | 32.4000 | -111.2100 | NULL | NULL |  619.00 |    NULL |                    | AZ    | NULL    | 82     | Inland    |
| KNRS    | METAR      | 32.5700 | -117.1200 | NULL | NULL |    7.00 |    NULL |                    | CA    | NULL    | 23     | 1.0       |
| KSDM    | METAR      | 32.5700 | -116.9800 | NULL | NULL |  160.00 |    NULL |                    | CA    | NULL    | 71     | Inland    |
| KCZZ    | METAR      | 32.6200 | -116.4700 | NULL | NULL |  802.00 |    NULL |                    | CA    | NULL    | 71     | Inland    |
| KNZY    | METAR      | 32.7000 | -117.2200 | NULL | NULL |    8.00 |    NULL |                    | CA    | NULL    | 24     | 2.0       |
| KSAN    | METAR      | 32.7300 | -117.1700 | NULL | NULL |    9.00 |    NULL | SAN_DIEGO/LINDBERG | CA    | NULL    | 24     | 1.5       |
+---------+------------+---------+-----------+------+------+---------+---------+--------------------+-------+---------+
12 rows in set (0.00 sec)