# rabravo/ws2pgdb architecture

> Architecture diagram of the GitHub repository rabravo/ws2pgdb, generated by GitDiagram from its file tree, README and a sample of source files (last updated 2026-05-17). It is an AI-made overview: check details against the source.

- Interactive diagram: https://gitdiagram.com/rabravo/ws2pgdb
- Repository: https://github.com/rabravo/ws2pgdb

## Overview

1. **What this repository is**
   - `ws2pgdb` is an R package plus a companion PostgreSQL/PostGIS/PL-R database image.
   - Its purpose is to move weather-station and weather-observation data into a spatially enabled Postgres database, and to support station-based spatial analysis such as Voronoi tessellation.

2. **Primary runtime boundary: R vs. PostgreSQL**
   - The R package provides the client-side orchestration: fetching NOAA data, preparing data frames, and pushing results into the database.
   - The database side provides persistent storage and in-database functions written in SQL/PLpgSQL/PLR, so some processing can happen inside Postgres rather than only in R.

3. **External data sources**
   - NOAA is the main remote source for weather data. The package uses `rnoaa`, and the README notes NOAA’s one-year-per-request limitation, which drives the iterative “span” and “by date” functions.
   - U.S. Census TIGER shapefiles are the spatial base layer used for county/tract boundaries. These are loaded into PostGIS to support spatial joins and map outputs.

4. **R package subsystems**
   - `ws_metadata_*` functions fetch and store station metadata, either for a single date or over a date span, and either from raw retrieval or directly into Postgres.
   - `ws_data_*` functions do the same for weather observations, with variants for averages, missing values (`na`), and date-span iteration.
   - `all_coor_ws*` and `canopiVoronoi`/`createVoronoi` are the spatial-analysis pieces: they work with station coordinates and generate Voronoi polygons for territory/coverage analysis.
   - `df_2_pgdb` is the generic database ingestion utility used by the more specialized loaders.

5. **Database-side subsystems**
   - `sql/functions.sql` and `sql/pg.spi.foo.sql` define the stored functions loaded into PostgreSQL.
   - The README indicates these functions enable communication between the simulator/R code and Postgres, so the DB is not just storage—it also hosts callable logic.
   - The presence of PL/R means some functions can run R code inside the database container, tightening the loop between analytics and persistence.

6. **Data pipeline shape**
   - Typical flow is: NOAA API → R retrieval/cleanup → transformation into tabular form → load into Postgres tables.
   - For spatial workflows: Census tract shapefiles are loaded into PostGIS → weather station coordinates are loaded/derived → Voronoi polygons are computed or stored → GIS tools can query and visualize the results.
   - The iterative “span” functions exist specifically to batch NOAA requests across multiple years while respecting API limits.

7. **Containerized infrastructure**
   - The `docker/` directory defines a self-contained runtime image bundling PostgreSQL, PostGIS, PL/R, GDAL, and related spatial dependencies.
   - `docker/init-db.sh` and the SQL files initialize the database on first run, so the system boots with the required schema/functions already in place.
   - `docker/manage.sh` is the operational entry point for starting/stopping the container and performing common setup tasks.

8. **Configuration boundary**
   - Database credentials and the NOAA token live in `pg_config.yml`, which is intentionally kept outside the repo for safety.
   - Inside the container the config is expected at `/root/pg_config.yml`; locally it is `~/pg_config.yml` or `~/.docker/pg_config.yml` per the README.
   - This config file is the main bridge between the package code and the external database/API environment.

9. **Visualization and downstream consumers**
   - QGIS is an intended consumer of the PostGIS database, not part of the application logic itself.
   - It connects directly to the `us_gis` database to inspect county tracts, station points, and Voronoi geometries.
   - That means the database is the stable integration point for analysis and visualization, while R is mainly the ingestion/processing layer.

10. **Packaging, docs, and distribution**
   - The repo is structured as an R package (`DESCRIPTION`, `NAMESPACE`, `R/`, `man/`, `data/`), with a small bundled dataset (`Watts.rda`) for example use or testing.
   - `docs/` contains workflow and failure-mode documentation, suggesting the project expects operational setup issues around spatial libraries and database connectivity.
   - `.github/workflows/docker-publish.yml` shows the container image is built and published via CI, making the Docker image a first-class deliverable alongside the R package.

11. **Architecture summary for a diagram**
   - Draw three main layers: external data sources (NOAA, Census), an orchestration layer in R, and a spatial database layer in Dockerized Postgres/PostGIS/PL-R.
   - Show QGIS as a read-only visualization client to the database.
   - Show configuration as an external file boundary feeding both R and containerized DB access.
   - The key design idea is that data acquisition happens in R, persistent spatial storage and some functions happen in Postgres, and the whole system is packaged to run locally in a single container or as an installed R package.

Main entry points: [`docker/Dockerfile`](https://github.com/rabravo/ws2pgdb/tree/main/docker/Dockerfile) (PostGIS image), [`DESCRIPTION`](https://github.com/rabravo/ws2pgdb/tree/main/DESCRIPTION) (ws2pgdb), [`R/canopiVoronoi.R`](https://github.com/rabravo/ws2pgdb/blob/main/R/canopiVoronoi.R) (Spatial analysis), [`R/ws_metadata_span_2_pgdb.R`](https://github.com/rabravo/ws2pgdb/blob/main/R/ws_metadata_span_2_pgdb.R) (Metadata loaders)

## Diagram (Mermaid)

```mermaid
flowchart TD

subgraph group_external["External sources"]
  node_noaa(("NOAA<br/>remote API"))
  node_census["TIGER shapefiles<br/>spatial source"]
end

subgraph group_r["R package"]
  node_ws_pkg["ws2pgdb<br/>R package"]
  node_meta_load["Metadata loaders<br/>R ingest"]
  node_data_load["Data loaders<br/>R ingest"]
  node_span_ops["Span batching<br/>R iterator"]
  node_ingest_db["DB ingest<br/>R loader<br/>[df_2_pgdb.R]"]
  node_spatial_ops["Spatial analysis<br/>R geoprocess<br/>[canopiVoronoi.R]"]
  node_coords["Station coords<br/>R transform<br/>[all_coor_ws.R]"]
  node_voronoi["Voronoi tools<br/>R geoprocess<br/>[createVoronoi.R]"]
end

subgraph group_db["Spatial database"]
  node_db_image[("PostGIS image<br/>postgres runtime")]
  node_db_sql["Stored functions<br/>SQL/PLpgSQL<br/>[functions.sql]"]
  node_spi_sql["SPI bridge<br/>SQL/PLR helper<br/>[pg.spi.foo.sql]"]
end

subgraph group_infra["Container and delivery"]
  node_db_init["Init script<br/>bootstrap<br/>[init-db.sh]"]
  node_db_cfg["pg_config.yml<br/>[pg_config.yml]"]
  node_manage["manage.sh<br/>ops script<br/>[manage.sh]"]
  node_ci["Docker publish<br/>CI workflow<br/>[docker-publish.yml]"]
end

subgraph group_consumers["Consumers"]
  node_qgis["QGIS<br/>GIS client"]
end

subgraph group_docs["Docs and sample"]
  node_sample["Watts sample<br/>example data<br/>[Watts.rda]"]
  node_workflow["Workflow docs<br/>documentation<br/>[workflow.html]"]
end

node_noaa -->|"station metadata"| node_meta_load
node_noaa -->|"observations"| node_data_load
node_span_ops -->|"batch requests"| node_noaa
node_ws_pkg -->|"orchestrates"| node_meta_load
node_ws_pkg -->|"orchestrates"| node_data_load
node_ws_pkg -->|"coordinates"| node_spatial_ops
node_meta_load -->|"load table"| node_ingest_db
node_data_load -->|"load table"| node_ingest_db
node_coords -->|"feeds"| node_voronoi
node_spatial_ops -->|"derives"| node_coords
node_spatial_ops -->|"computes"| node_voronoi
node_census -->|"load boundaries"| node_db_image
node_db_init -->|"bootstraps"| node_db_image
node_db_sql -->|"installs"| node_db_image
node_spi_sql -->|"installs"| node_db_image
node_db_cfg -->|"configures"| node_ws_pkg
node_db_cfg -->|"configures"| node_db_image
node_manage -->|"manages"| node_db_image
node_ci -->|"publishes"| node_db_image
node_db_image -->|"serves GIS"| node_qgis
node_sample -->|"example input"| node_ws_pkg
node_workflow -->|"documents"| node_ws_pkg

click node_ws_pkg "https://github.com/rabravo/ws2pgdb/tree/main/DESCRIPTION"
click node_meta_load "https://github.com/rabravo/ws2pgdb/blob/main/R/ws_metadata_span_2_pgdb.R"
click node_data_load "https://github.com/rabravo/ws2pgdb/blob/main/R/ws_data_avg_span_2_pgdb.R"
click node_span_ops "https://github.com/rabravo/ws2pgdb/blob/main/R/ws_metadata_span_2_pgdb.R"
click node_ingest_db "https://github.com/rabravo/ws2pgdb/blob/main/R/df_2_pgdb.R"
click node_spatial_ops "https://github.com/rabravo/ws2pgdb/blob/main/R/canopiVoronoi.R"
click node_coords "https://github.com/rabravo/ws2pgdb/blob/main/R/all_coor_ws.R"
click node_voronoi "https://github.com/rabravo/ws2pgdb/blob/main/R/createVoronoi.R"
click node_db_image "https://github.com/rabravo/ws2pgdb/tree/main/docker/Dockerfile"
click node_db_sql "https://github.com/rabravo/ws2pgdb/blob/main/sql/functions.sql"
click node_spi_sql "https://github.com/rabravo/ws2pgdb/blob/main/sql/pg.spi.foo.sql"
click node_db_init "https://github.com/rabravo/ws2pgdb/blob/main/docker/init-db.sh"
click node_db_cfg "https://github.com/rabravo/ws2pgdb/blob/main/docker/pg_config.yml"
click node_manage "https://github.com/rabravo/ws2pgdb/blob/main/docker/manage.sh"
click node_ci "https://github.com/rabravo/ws2pgdb/blob/main/.github/workflows/docker-publish.yml"
click node_sample "https://github.com/rabravo/ws2pgdb/blob/main/data/Watts.rda"
click node_workflow "https://github.com/rabravo/ws2pgdb/blob/main/docs/workflow.html"

classDef toneNeutral fill:#f8fafc,stroke:#334155,stroke-width:1.5px,color:#0f172a
classDef toneBlue fill:#dbeafe,stroke:#2563eb,stroke-width:1.5px,color:#172554
classDef toneAmber fill:#fef3c7,stroke:#d97706,stroke-width:1.5px,color:#78350f
classDef toneMint fill:#dcfce7,stroke:#16a34a,stroke-width:1.5px,color:#14532d
classDef toneRose fill:#ffe4e6,stroke:#e11d48,stroke-width:1.5px,color:#881337
classDef toneIndigo fill:#e0e7ff,stroke:#4f46e5,stroke-width:1.5px,color:#312e81
classDef toneTeal fill:#ccfbf1,stroke:#0f766e,stroke-width:1.5px,color:#134e4a
class node_noaa,node_census toneBlue
class node_ws_pkg,node_meta_load,node_data_load,node_span_ops,node_ingest_db,node_spatial_ops,node_coords,node_voronoi toneAmber
class node_db_image,node_db_sql,node_spi_sql toneMint
class node_db_init,node_db_cfg,node_manage,node_ci toneRose
class node_qgis toneIndigo
class node_sample,node_workflow toneTeal
```

## Components

### External sources

Remote and upstream spatial data inputs.

- **NOAA**: remote API · Weather station and observation source used by the package.
- **TIGER shapefiles**: spatial source · County and tract boundary data loaded into PostGIS.

### R package

Client-side retrieval, transformation, and ingestion logic.

- [ws2pgdb](https://github.com/rabravo/ws2pgdb/tree/main/DESCRIPTION): R package · Main orchestration layer for weather retrieval, cleanup, and loading. · `DESCRIPTION`
- [Metadata loaders](https://github.com/rabravo/ws2pgdb/blob/main/R/ws_metadata_span_2_pgdb.R): R ingest · Fetches and stores station metadata for single dates and spans. · `R/ws_metadata_span_2_pgdb.R`
- [Data loaders](https://github.com/rabravo/ws2pgdb/blob/main/R/ws_data_avg_span_2_pgdb.R): R ingest · Fetches weather observations, including average and NA variants. · `R/ws_data_avg_span_2_pgdb.R`
- [Span batching](https://github.com/rabravo/ws2pgdb/blob/main/R/ws_metadata_span_2_pgdb.R): R iterator · Iterates NOAA requests across multi-year ranges to respect API limits. · `R/ws_metadata_span_2_pgdb.R`
- [DB ingest](https://github.com/rabravo/ws2pgdb/blob/main/R/df_2_pgdb.R): R loader · Generic table-loading utility used by the specialized loaders. · `R/df_2_pgdb.R`
- [Spatial analysis](https://github.com/rabravo/ws2pgdb/blob/main/R/canopiVoronoi.R): R geoprocess · Builds station-coordinate and Voronoi outputs for coverage analysis. · `R/canopiVoronoi.R`
- [Station coords](https://github.com/rabravo/ws2pgdb/blob/main/R/all_coor_ws.R): R transform · Collects and prepares station coordinate data for spatial workflows. · `R/all_coor_ws.R`
- [Voronoi tools](https://github.com/rabravo/ws2pgdb/blob/main/R/createVoronoi.R): R geoprocess · Creates Voronoi polygons from station locations. · `R/createVoronoi.R`

### Spatial database

PostgreSQL/PostGIS/PL-R runtime and database-side functions.

- [PostGIS image](https://github.com/rabravo/ws2pgdb/tree/main/docker/Dockerfile): postgres runtime · Containerized PostgreSQL/PostGIS/PL-R database runtime. · `docker/Dockerfile`
- [Stored functions](https://github.com/rabravo/ws2pgdb/blob/main/sql/functions.sql): SQL/PLpgSQL · Database-side functions loaded into PostgreSQL. · `sql/functions.sql`
- [SPI bridge](https://github.com/rabravo/ws2pgdb/blob/main/sql/pg.spi.foo.sql): SQL/PLR helper · Database glue for calling between R and PostgreSQL. · `sql/pg.spi.foo.sql`

### Container and delivery

Docker image, startup scripts, config, and CI delivery.

- [Init script](https://github.com/rabravo/ws2pgdb/blob/main/docker/init-db.sh): bootstrap · Initializes schema and functions when the container starts. · `docker/init-db.sh`
- [pg_config.yml](https://github.com/rabravo/ws2pgdb/blob/main/docker/pg_config.yml): config · Database credentials and NOAA token used by R and the container. · `docker/pg_config.yml`
- [manage.sh](https://github.com/rabravo/ws2pgdb/blob/main/docker/manage.sh): ops script · Container lifecycle and setup entry point. · `docker/manage.sh`
- [Docker publish](https://github.com/rabravo/ws2pgdb/blob/main/.github/workflows/docker-publish.yml): CI workflow · Builds and publishes the container image. · `.github/workflows/docker-publish.yml`

### Consumers

Downstream tools that read the database outputs.

- **QGIS**: GIS client · Reads the database for inspection and visualization.

### Docs and sample

Operational documentation and bundled example data.

- [Watts sample](https://github.com/rabravo/ws2pgdb/blob/main/data/Watts.rda): example data · Bundled dataset used for examples or lightweight testing. · `data/Watts.rda`
- [Workflow docs](https://github.com/rabravo/ws2pgdb/blob/main/docs/workflow.html): documentation · Explains setup and operational workflow. · `docs/workflow.html`

## Connections

- NOAA → Metadata loaders: station metadata · Metadata is fetched from NOAA before transformation and load.
- NOAA → Data loaders: observations · Weather observations are pulled from NOAA.
- Span batching → NOAA: batch requests · Span-based functions split long requests into allowed chunks.
- ws2pgdb → Metadata loaders: orchestrates · The package exposes metadata retrieval and load workflows.
- ws2pgdb → Data loaders: orchestrates · The package exposes weather observation retrieval and load workflows.
- ws2pgdb → Spatial analysis: coordinates · The package also drives spatial analysis workflows.
- Metadata loaders → DB ingest: load table · Prepared station metadata can be written into PostgreSQL.
- Data loaders → DB ingest: load table · Prepared observation tables are written into PostgreSQL.
- Station coords → Voronoi tools: feeds · Prepared station coordinates are used to create Voronoi geometry.
- Spatial analysis → Station coords: derives · Coordinate preparation is part of the spatial workflow.
- Spatial analysis → Voronoi tools: computes · Voronoi generation is the main spatial output.
- TIGER shapefiles → PostGIS image: load boundaries · TIGER boundaries are imported into PostGIS.
- Init script → PostGIS image: bootstraps · Initialization script prepares the database on first start.
- Stored functions → PostGIS image: installs · Stored functions are loaded into the database runtime.
- SPI bridge → PostGIS image: installs · PL/R/SPI helper logic is loaded into PostgreSQL.
- pg_config.yml → ws2pgdb: configures · Package code reads database and NOAA credentials from config.
- pg_config.yml → PostGIS image: configures · Container startup uses the same external config file.
- manage.sh → PostGIS image: manages · Operational script starts and manages the containerized database.
- Docker publish → PostGIS image: publishes · CI builds and publishes the database image.
- PostGIS image → QGIS: serves GIS · QGIS connects directly to the spatial database for visualization.
- Watts sample → ws2pgdb: example input · Bundled sample data supports package examples and testing.
- Workflow docs → ws2pgdb: documents · Docs describe the package and container workflow.
