Home

Overview

This wiki contains developer documentation for the Piscataqua Watershed Data Explorer. Specifically, this page focuses on specifying how the web application retrieves data from the PREP database and API.

Database API

The PREP database is a PostgreSQL database using the Observations Data Model 2 (ODM2) data model.

The database is accessed through a PostgREST API, which runs on the PREP server. The URL endpoint for this API must is defined in the web app source code using an environmental variable named VITE_API_URL. This variable can be set using a .env file for each environment (e.g. .env.development for development).

Materialized Views

A series of materialized views are used to define which stations, variables, and results are shown on the web app. Although the web app could query the ODM2 tables directly via the PostgREST API (e.g., odm2.samplingfeatures), some stations and variables need to be excluded (e.g., stations located outside the PREP basin, or variables with secondary units). Furthermore, result sets need to be aggregated by station and variable so the web application knows the full time period and number of samples for each unique (station, variable) pair.

The db/prep-db-explorer-views.sql file contains the SQL queries to create these materialized views, along with comments explaining what each view does. Although five views are created, only three are used by the web application (the other two serve as intermediaries):

odm2.prep_variables: list of variables and their primary units
  prep_variableid             unique ID generated for this view (does not relate to any odm2 table)
  variabletypecv              variable type (see odm2.cv_variabletype)
  variablenamecv              variable name (see odm2.cv_variablename)
  unitsid                     units ID (FK to odm2.units.unitsid)
  unitsabbreviation           units abbreviation (see odm2.units)
  variablecore                boolean indicating if variable is considered a "core parameter"

odm2.prep_stations: list of stations (aka samplingfeature)
  samplingfeatureid           unique ID (FK to odm2.samplingfeatures.samplingfeatureid)
  samplingfeaturecode         station code (see odm2.samplingfeatures)
  samplingfeaturename         station name (see odm2.samplingfeatures)
  samplingfeaturedescription  station description (see odm2.samplingfeatures)
  samplingfeaturecore         boolean indicating if station is considered a "core station"
  longitude                   longitude
  latitude                    latitude

odm2.prep_results: list of results (aka available sampling data for each unique (station,variable) pair)
  prep_resultid               unique ID generated for this view (does not relate to any odm2 table)
  samplingfeatureid           sampling feature ID (FK to odem2.prep_stations.samplingfeatureid)
  samplingfeaturecode         sampling feature code
  prep_variableid             variable ID (FK to odm2.prep_variables.prep_variableid)
  start                       date of first measurement
  end                         date of last measurement
  n_values                    number of measurements

Together, these three materialized views provide the web app with a summary of all available stations, variables, and results. All three of these are retrieved from the server upon initialization of the web app. See the associated files in the src/services folder.

Timeseries Queries

When the user selects a station and parameter, the web app retrieves the full set of results and associated timeseriesresultvalues from using the /results API endpoint for the odm2.results table. This table is queriedSee Tables and Views for the API specification.

This query (see src/services/resultValues.ts) contains a long series of parameters to

1. filter the results forthe given variablenamecv and unitsid of the selected parameter, e

GET {API_URL}/results?

  ## FILTER RESULTS
  featureaction.samplingfeatureid=in.(${stationIds})&                              # selected station(s)
  variable.variablenamecv=eq.${encodeURIComponent(variable.variablenamecv)}&       # selected parameter
  variable.variabletypecv=in.(Hydrology,Water quality)&                            # only include variabletypecv=Hydrology or Water Quality
  unitsid=eq.${variable.unitsid}&                                                  # primary units of selected parameter
  timeseriesresults.timeseriesresultvalues.qualitycodecv=neq.Bad&                  # exclude values where qualitycodecv='Bad'
  timeseriesresults.timeseriesresultvalues.datavalue=neq.NaN$&                     # exclude NaN values
  timeseriesresults.timeseriesresultvalues.valuedatetime=gte.${start}&             # start date (only used for bulk downloads)
  timeseriesresults.timeseriesresultvalues.valuedatetime=lte.${end}+23:59:59.999Z& # end date (only used for bulk downloads)

  ## SELECT COLUMNS, NESTED RELATIONS
  select=
    *,                                            # all columns in odm2.results
    variable:variables!inner(*),                  # join odm2.variables
    units:units!inner(*),                         # join odm2.units
    featureaction:featureactions!inner(           # join odm2.featureactions
      *,
      samplingfeature:samplingfeatures(*),        # join odm2.samplingfeatures
      action:actions(                             # join odm2.actions
        *,
        method:methods(*)                         # join odm2.methods
      )
    ),
    timeseriesresults(                            # join odm2.timeseriesresults
      *,
      timeseriesresultvalues(                     # join odm2.timeseriesresultsvalues
        valueid,resultid,datavalue,valuedatetime,valuedatetimeutcoffset,censorcodecv
      )
    )

This query returns a list of rows from odm2.results with the associated station, variable, and timeseries values (among other metadata) as nested objects. The web app then combines the timeseriesresultvalues into a single array to plot the data on the chart.