How rabravo/ws2pgdb works
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: Dockerfile PostGIS image · DESCRIPTION ws2pgdb · canopiVoronoi.R Spatial analysis · ws_metadata_span_2_pgdb.R Metadata loaders
Components 20
External sources
Remote and upstream spatial data inputs.
- NOAA · Weather station and observation source used by the package.
- TIGER shapefiles · County and tract boundary data loaded into PostGIS.
R package
Client-side retrieval, transformation, and ingestion logic.
- ws2pgdb · Main orchestration layer for weather retrieval, cleanup, and loading.
DESCRIPTION - Metadata loaders · Fetches and stores station metadata for single dates and spans.
R/ws_metadata_span_2_pgdb.R - Data loaders · Fetches weather observations, including average and NA variants.
R/ws_data_avg_span_2_pgdb.R - Span batching · Iterates NOAA requests across multi-year ranges to respect API limits.
R/ws_metadata_span_2_pgdb.R - DB ingest · Generic table-loading utility used by the specialized loaders.
R/df_2_pgdb.R - Spatial analysis · Builds station-coordinate and Voronoi outputs for coverage analysis.
R/canopiVoronoi.R - Station coords · Collects and prepares station coordinate data for spatial workflows.
R/all_coor_ws.R - Voronoi tools · Creates Voronoi polygons from station locations.
R/createVoronoi.R
Spatial database
PostgreSQL/PostGIS/PL-R runtime and database-side functions.
- PostGIS image · Containerized PostgreSQL/PostGIS/PL-R database runtime.
docker/Dockerfile - Stored functions · Database-side functions loaded into PostgreSQL.
sql/functions.sql - SPI bridge · 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 · Initializes schema and functions when the container starts.
docker/init-db.sh - pg_config.yml · Database credentials and NOAA token used by R and the container.
docker/pg_config.yml - manage.sh · Container lifecycle and setup entry point.
docker/manage.sh - Docker publish · Builds and publishes the container image.
.github/workflows/docker-publish.yml
Consumers
Downstream tools that read the database outputs.
- QGIS · Reads the database for inspection and visualization.
Docs and sample
Operational documentation and bundled example data.
- Watts sample · Bundled dataset used for examples or lightweight testing.
data/Watts.rda - Workflow docs · Explains setup and operational workflow.
docs/workflow.html
Connections 22
- 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.