PSQLIO

Format

This plugin connects to a generic PostgreSQL server to retrieve its meteorological data. The server parameters must be provided as well as the queries to retrieve the stations' data and metadata. In order to compile this plugin, the development package of libpq is required (this is the PostgreSQL c client library) and most probably also postgresql-server-dev-all.

Metadata query

This query is used to retrieve the stations' metadata. This SQL query string should retrieve the following columns as result set (in this very order):

 id (int), name (string), x (easting as double), y (northing as double), altitude (height above sea level as double), epsg (int)

The plugin uses parameterized queries (PQexecParams) with $1 as the parameter placeholder for the station ID to prevent SQL injection. An example for a correct SQL metadata query string is therefore:

 SELECT id, station_name AS name, x_coord AS x, y_coord AS y, z AS altitude, epsg from all_stations WHERE id = $1

Data query

This query is used to retrieve the data for the user selected stations within a given time interval. The SQL query may retrieve the following columns as result set (any order, only date is mandatory):

 date (mandatory, as date), ta (double), rh (double), p (double), vw (double), dw (double), iprec (the PSUM value, double), iswr (double)

The SQL query must retrieve the data for one station only, which has to be specified as $1 (parameter placeholder). To set the upper and lower bounds for the date the SQL query must contain $2 and $3 as parameter placeholders (dates in ISO format, e.g. 2024-01-01 12:00:00). Furthermore the resultset should be ordered by date ascending. An example for a correct SQL data query string is therefore:

 SELECT * FROM all_measurements WHERE id = $1 AND date >= $2 AND date <= $3 ORDER BY date

Units

Units are assumed to be pure SI, except:

• temperatures in °C
• relative humidity in %
• snow height in cm
• pressure in mbar

Keywords

This plugin uses the following keywords:

• COORDSYS: coordinate system (see Coords); [Input] section
• COORDPARAM: extra coordinates parameters (see Coords); [Input] section
• database connection keywords; [Input] and [Output] sections:
  • PSQL_URL: The URL or IP of the database server
  • PSQL_PORT: the port to use to connect
  • PSQL_DB: The name of the database to access
  • PSQL_USER: The username to access the server
  • PSQL_PASS: The password to authenticate the PSQL_USER
• database structure keywords; [Input] section
  • SQL_META: SQL query to use to get the stations' metadata.
  • SQL_DATA: SQL query to use to get the stations' data.
• STATIONS: comma separated list of station ids that the user is interested in; [Input] section

Note

Currently, the output structure is fixed with a hard-coded table name and hard-coded fields so it can not be considered usable by most users...