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 (Download CSV file prepared by LADCO)
2. Create a new table in the AMET database called "states"
3. Load the updated MADIS stations file into the new 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)

Error creating thumbnail: File missing Map of MADIS monitors with state tags

Error creating thumbnail: File missing Map of MADIS inland (green) vs shoreline monitors

Error creating thumbnail: File missing Southern Lake MI shoreline MADIS monitors

Error creating thumbnail: File missing MADIS sites with NLCD2011 landcover categories

Why the NLCD2011 codes? Why not? In case we want to evaluate performance at monitors with different land cover classifications (e.g., urban vs agricultural vs forest); link to the NLCD 2011 legend