AMET Notes

Update the MET database to include state IDs

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
2. Create a new table in the AMET database called "states"
3. Load the process stations.csv file into the states table
4. Join the states table to the stations table on the station ID field to add in the state IDs

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

 mysql> use amet;
 mysql> create table states (id INT NOT NULL AUTO_INCREMENT, ST VARCHAR(2) NOT NULL, CODE VARCHAR(4) NOT NULL, PRIMARY KEY (id));
 mysql> load data local infile '/data/apps/AMET_v14/obs/MET/point/metar/metar_lookup.csv' into table states fields terminated by ',' lines terminated by '\n' ignore 1 rows (id, ST, CODE);
 mysql> update stations join states on stations.stat_id=states.CODE set stations.state=states.ST;
 mysql> mysql> select * from stations limit 10;
 +---------+------------+---------+-----------+------+------+---------+---------+--------------------+-------+---------+
 | stat_id | ob_network | lat     | lon       | i    | j    | elev    | landuse | common_name        | state | country |
 +---------+------------+---------+-----------+------+------+---------+---------+--------------------+-------+---------+ 
 | KOLS    | METAR      | 31.4200 | -110.8500 | NULL | NULL | 1206.00 |    NULL |                    | AZ    | NULL    |
 | KALK    | METAR      | 31.6000 | -110.4200 | NULL | NULL | 1433.00 |    NULL |                    | AZ    | NULL    |
 | KTUS    | RAOB       | 32.2300 | -110.9600 | NULL | NULL |  751.00 |    NULL | NULL               | AZ    | NULL    |
 | KRYN    | METAR      | 32.1400 | -111.1700 | NULL | NULL |  737.00 |    NULL |                    | AZ    | NULL    |
 | KAVQ    | METAR      | 32.4000 | -111.2100 | NULL | NULL |  619.00 |    NULL |                    | AZ    | NULL    |
 | KNRS    | METAR      | 32.5700 | -117.1200 | NULL | NULL |    7.00 |    NULL |                    | CA    | NULL    |
 | KSDM    | METAR      | 32.5700 | -116.9800 | NULL | NULL |  160.00 |    NULL |                    | CA    | NULL    |
 | KCZZ    | METAR      | 32.6200 | -116.4700 | NULL | NULL |  802.00 |    NULL |                    | CA    | NULL    |
 | KNZY    | METAR      | 32.7000 | -117.2200 | NULL | NULL |    8.00 |    NULL |                    | CA    | NULL    |
 | KSAN    | METAR      | 32.7300 | -117.1700 | NULL | NULL |    9.00 |    NULL | SAN_DIEGO/LINDBERG | CA    | NULL    |
 +---------+------------+---------+-----------+------+------+---------+---------+--------------------+-------+---------+
 10 rows in set (0.00 sec)