NOAA ISD气象站历史数据查询

以ISD数据集为例，展现如何将数据导入数据库中

在线Demo地址： http://demo.pigsty.cc/d/isd-overview

如果您拥有数据库后不知道干点什么，不妨参考作者的另一个开源项目：Vonng/isd

您可以直接复用监控系统Grafana，以交互式的方式查阅近30000个地面气象站过去120年间的亚小时级气象数据。

一个功能完成的数据应用，可以查询全球30000个地表气象站从1901年来的气象观测记录。

项目地址：<https://github.com/Vonng/isd>

安装方式

1. cd isd
2. make all         # 完整安装（会从Github与NOAA下载最新数据）
3. make all2        # 完整安装（会直接使用本地下载好的数据）

更精细的控制：

1. make ui          # 将covid dashboards安装至grafana
2. make sql         # 将covid 数据库表定义创建至metadb中
3. make download    # 下载NOAA最新数据，ISD Parser，字典表
4. make baseline    # 使用下载好的数据初始化最基本的全局大盘功能
5. make reload      # 从NOAA下载最新的每日摘要并解析加载

ISD —— Intergrated Surface Data

这里包含了下载、解析、处理、可视化NOAA ISD数据集所需的所有工具。 能让您查阅近30000个地面气象站过去120年间的亚小时级气象数据。并充分体验PostgreSQL带来的强大的数据分析与处理能力！

SYNOPSIS

Download, Parse, Visualize Intergrated Suface Dataset.

Including 30000 meteorology station, sub-hourly observation records, from 1900-2020.

ISD气象数据查询 - 图1

Quick Started

  1. Clone repo

1. git clone https://github.com/Vonng/isd && cd isd 2. Prepare a postgres database

Connect via something like isd or postgres://user:pass@host/dbname)

``` 1. # skip this if you already have a viable database 2. PGURL=postgres 3. psql ${PGURL} -c 'CREATE DATABASE isd;'

  1. database connection string, something like isd or postgres://user:pass@host/dbname

  2. PGURL='isd'

  3. psql ${PGURL} -AXtwc 'CREATE EXTENSION postgis;'

  4. create tables, partitions, functions

  5. psql ${PGURL} -AXtwf 'sql/schema.sql' ```

  6. Download data

  7. ISD Station: Station metadata, id, name, location, country, etc…

  8. ISD History: Station observation records: observation count per month
  9. ISD Hourly: Yearly archived station (sub-)hourly observation records
  10. ISD Daily: Yearly archvied station daily aggregated summary

1. git clone https://github.com/Vonng/isd && cd isd 2. bin/get-isd-station.sh # download isd station from noaa (proxy makes it faster) 3. bin/get-isd-history.sh # download isd history observation from noaa 4. bin/get-isd-hourly.sh <year> # download isd hourly data (yearly tarball 1901-2020) 5. bin/get-isd-daily.sh <year> # download isd daily data (yearly tarball 1929-2020) 4. Build Parser

There are two ISD dataset parsers written in Golang : isdh for isd hourly dataset and isdd for isd daily dataset.

make isdh and make isdd will build it and copy to bin. These parsers are required for loading data into database.

You can download pre-compiled binary to bin/ dir to skip this phase. 5. Load data

Metadata includes world_fences, china_fences, isd_elements, isd_mwcode, isd_station, isd_history. These are gzipped csv file lies in data/meta/. world_fences, china_fences, isd_elements, isd_mwcode are constant dict table. But isd_station and isd_history are frequently updated. You’ll have to download it from noaa before loading it.

``` 1. # load metadata: fences, dicts, station, history,... 2. bin/load-meta.sh

  1. load a year's daily data to database

  2. bin/load-isd-daily

  3. load a year's hourly data to database

  4. bin/laod-isd-hourly ```

Note that the original isd_daily dataset has some un-cleansed data, refer caveat for detail.

Data

Dataset

数据集 样本 文档 备注
ISD Hourly isd-hourly-sample.csv isd-hourly-document.pdf (Sub-) Hour oberservation records
ISD Daily isd-daily-sample.csv isd-daily-format.txt Daily summary
ISD Monthly N/A isd-gsom-document.pdf Not used, gen from daily
ISD Yearly N/A isd-gsoy-document.pdf Not used, gen from monthly

Hourly Data: Oringinal tarball size 105GB, Table size 1TB (+600GB Indexes).

Daily Data: Oringinal tarball size 3.2GB, table size 24 GB

It is recommended to have 2TB storage for a full installation, and at least 40GB for daily data only installation.

Schema

Data schema definition

Station

1. CREATE TABLE public.isd_station
2. (
3. station    VARCHAR(12) PRIMARY KEY,
4. usaf       VARCHAR(6) GENERATED ALWAYS AS (substring(station, 1, 6)) STORED,
5. wban       VARCHAR(5) GENERATED ALWAYS AS (substring(station, 7, 5)) STORED,
6. name       VARCHAR(32),
7. country    VARCHAR(2),
8. province   VARCHAR(2),
9. icao       VARCHAR(4),
10. location   GEOMETRY(POINT),
11. longitude  NUMERIC GENERATED ALWAYS AS (Round(ST_X(location)::NUMERIC, 6)) STORED,
12. latitude   NUMERIC GENERATED ALWAYS AS (Round(ST_Y(location)::NUMERIC, 6)) STORED,
13. elevation  NUMERIC,
14. period     daterange,
15. begin_date DATE GENERATED ALWAYS AS (lower(period)) STORED,
16. end_date   DATE GENERATED ALWAYS AS (upper(period)) STORED
17. );

Hourly Data

1. CREATE TABLE public.isd_hourly
2. (
3. station    VARCHAR(11) NOT NULL,
4. ts         TIMESTAMP   NOT NULL,
5. temp       NUMERIC(3, 1),
6. dewp       NUMERIC(3, 1),
7. slp        NUMERIC(5, 1),
8. stp        NUMERIC(5, 1),
9. vis        NUMERIC(6),
10. wd_angle   NUMERIC(3),
11. wd_speed   NUMERIC(4, 1),
12. wd_gust    NUMERIC(4, 1),
13. wd_code    VARCHAR(1),
14. cld_height NUMERIC(5),
15. cld_code   VARCHAR(2),
16. sndp       NUMERIC(5, 1),
17. prcp       NUMERIC(5, 1),
18. prcp_hour  NUMERIC(2),
19. prcp_code  VARCHAR(1),
20. mw_code    VARCHAR(2),
21. aw_code    VARCHAR(2),
22. pw_code    VARCHAR(1),
23. pw_hour    NUMERIC(2),
24. data       JSONB
25. ) PARTITION BY RANGE (ts);

Daily Data

1. CREATE TABLE public.isd_daily
2. (
3. station     VARCHAR(12) NOT NULL,
4. ts          DATE        NOT NULL,
5. temp_mean   NUMERIC(3, 1),
6. temp_min    NUMERIC(3, 1),
7. temp_max    NUMERIC(3, 1),
8. dewp_mean   NUMERIC(3, 1),
9. slp_mean    NUMERIC(5, 1),
10. stp_mean    NUMERIC(5, 1),
11. vis_mean    NUMERIC(6),
12. wdsp_mean   NUMERIC(4, 1),
13. wdsp_max    NUMERIC(4, 1),
14. gust        NUMERIC(4, 1),
15. prcp_mean   NUMERIC(5, 1),
16. prcp        NUMERIC(5, 1),
17. sndp        NuMERIC(5, 1),
18. is_foggy    BOOLEAN,
19. is_rainy    BOOLEAN,
20. is_snowy    BOOLEAN,
21. is_hail     BOOLEAN,
22. is_thunder  BOOLEAN,
23. is_tornado  BOOLEAN,
24. temp_count  SMALLINT,
25. dewp_count  SMALLINT,
26. slp_count   SMALLINT,
27. stp_count   SMALLINT,
28. wdsp_count  SMALLINT,
29. visib_count SMALLINT,
30. temp_min_f  BOOLEAN,
31. temp_max_f  BOOLEAN,
32. prcp_flag   CHAR,
33. PRIMARY KEY (ts, station)
34. ) PARTITION BY RANGE (ts);

Update

ISD Daily and ISD hourly dataset will rolling update each day. Run following scripts to load latest data into database.

1. # download, clean, reload latest hourly dataset
2. bin/get-isd-daily.sh
3. bin/load-isd-daily.sh

5. # download, clean, reload latest daily dataset
6. bin/get-isd-daily.sh
7. bin/load-isd-daily.sh

9. # recalculate latest partition of monthly and yearly
10. bin/refresh-latest.sh

Parser

There are two parser: isdd and isdh, which takes noaa original yearly tarball as input, generate CSV as output (which could be directly consume by PostgreSQL Copy command).

1. NAME
2. isdh -- Intergrated Surface Dataset Hourly Parser

4. SYNOPSIS
5. isdh [-i <input|stdin>] [-o <output|st>] -p -d -c -v

7. DESCRIPTION
8. The isdh program takes isd hourly (yearly tarball file) as input.
9. And generate csv format as output

11. OPTIONS
12. -i    <input>        input file, stdin by default
13. -o    <output>    output file, stdout by default
14. -p    <profpath>    pprof file path (disable by default)
15. -v                verbose progress report
16. -d                de-duplicate rows (raw, ts-first, hour-first)
17. -c                add comma separated extra columns

UI

ISD Station

ISD气象数据查询 - 图2

ISD Monthly

ISD气象数据查询 - 图3

最后修改 2022-05-27: init commit (1e3e284)