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 METAR stations file into the new states table
4. Add columns to the existing AMET 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       |
+---------+------------+---------+-----------+------+------+---------+---------+--------------------+-------+---------+--------+-----------+
10 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

Update AMET AQ Database to Include Site Lists by Modeling Domain

The reason for this modification is to enable queries/stats to be run for only sites with in nested modeling domains. It allows you to have consistent statistics when comparing parent and nested modeling results (i.e., states for both runs are calculated using the same list of sites).

First create the list of sites, I did this using output from sitecompare.

Once you have text file with the list of sites, load it into the database. After logging into Mysql:

 mysql> use amet;
 mysql> create table `ladco4_aqs_sites` (`id` varchar(10) NOT NULL, primary key(`id`));
 mysql> load data infile 'ladco4_sites.txt' into table ladco4_aqs_sites;
 mysql> create table `ladco1_aqs_sites` (`id` varchar(10) NOT NULL, primary key(`id`));
 mysql> load data infile 'ladco1_sites.txt' into table ladco1_aqs_sites;

From there you can modify the AMET queries to limit stats to those sites in a particular modeling domain as such.

You'll first need to enable the query in the R_analysis_code:

 qs <- paste("SELECT 
 d.network,d.stat_id,d.lat,d.lon,d.ob_dates,d.ob_datee,d.ob_hour,d.month,d.",species,"_ob,d.",species,"_mod, 
 precip_ob, precip_mod,d.POCode from ",run_names[j]," as d, site_metadata as s, ladco1_aqs_sites as l, 
 ladco4_aqs_sites as m",criteria," ORDER BY network,stat_id",sep="")   # Set the rest of the MYSQL query

Then you can use queries like this in your amet AQ analysis scripts:

  # query to include only sites in the LADCO 1.33km domain
  if ( $zoom == 1k ) setenv QUERY "and(s.stat_id=l.id)"