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 user is allowed to select stations with the STATIONS keyword (see below). That is why the SQL query has to end with a 'WHERE id_column_name IN' clause, for example:

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

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 STATIONID (this will be dynamically replaced by the plugin). To set the upper and lower bounds for the date the SQL query has to contain DATE_START and DATE_END. These keywords will be dynamically replaced by the plugin with the correct date. 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 = ''STATIONID'' AND date>=''DATE_START'' AND date<=''DATE_END'' 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...