Tables description

Information and data are stored in the following tables:

  1. climate_daily: station series of daily meteorological data from BZ-Province and METEOTRENTINO. About 5 million records
  2. metadata: station information
  3. climatologies: station monthly climatologies, 1981 - 2010. About 4000 records

In addition to these 3 tables we have another one with different data policies that we have to maintain accessible only for Earth Observation users. These datasets come from METEOSWISS and HISTALP services. The stations are located in Austria and Switzerland. If interested contact us for details These datasets are collected in the following tables accessible using the DB user climate_eo (ask for the password to us):

  1. stations_data_restricted: data from METEOSWISS and HISTALP services. About 90.000 records
  2. stations_data_join: (join of tables: stations_data and stations_data_restricted)

In metadata table the following information are reported for each station site:

  • Name
  • Site location: longitude, latitude and elevation
  • Quality flag T_use and P_use (0 if low quality series or duplicate, 1 for good quality series)
  • Data availability flag T_length and P_length (0 if < 10 years of data, 1 if < 15 years, 2 if ≥ 15 years of data)
  • Homogenization index (1 for homogenized series, 0 for checked and homogenous series, 2 for possible inhomogeneous but not corrected series)
  • Province
  • Data source

Data Access

The access to the table is possible for all users with different modalities depending on the access to ScientificNet:

  • API: available for all users provides data in json format
  • Direct DB connection: available only for Eurac users and users with access to ScientificNet also by VPN

API - PostgREST API

Access is possible by a RESTfull-API available for all users at this URL:

https://edp-portal.eurac.edu/envdb

The API provide datasets in json format that can be downloaded by many client like a web-browser, linux shell, R, Python, etc. Documenation is available here:

Example of querying data by linux shell:

curl --location --request GET 'https://edp-portal.eurac.edu/envdb/climatologies?station=eq.Cles'

Example of querying data by web-browser to filter results:

https://edp-portal.eurac.edu/envdb/climate_daily?station=eq.Cles

Example using JupyterLab notebook filtering values for station "Cles" and date > 2015-01-01:

# connection and query with filter
import requests
url = 'https://edp-portal.eurac.edu/envdb/climate_daily?station=eq.Cles&date=gte.2015-01-01'
resp = requests. get(url)
data_cles = resp.json()
# print of the json file
data_cles

DB connection (only internal users)

The direct connection to PostgreSQL CDB is possible by many different clients (R, Python, PgAdmin etc.) or by a basic but powerful web interface for data preview and queries. The connection is possible for all users that have access to the ScientificNet also by VPN. Here are the necessary information for CDB connection:

    host: 10.7.18.68
    name: climate_data   (schema: public)
    Port: 5432
    User name: climate_user (read-only)
    Password: write to <andrea.vianello@eurac.edu>

A preview and a query tool is available through this Web interface by ScientificNetwork.

Note that currently access to the DB and also the web interface, is only possible via the ScientificNetwork (i.e. directly within Eurac&co. or with VPN). Use above information (username and password) for connection. Then select server “Climate Data” on the left, select database “climate_data” in main window, select schema “public”, then you shall see the window as shown below. Use “browse” tool to view tables or use the “select” tool to run SQL queries.

tables preview

Web Map Service

The table metadata is a PostGIS layer that can be accessed by a GIS client (or R) to compose maps using the WMS service: http://webgis.eurac.edu/geoserver/ows?SERVICE=WMS& (select layer dpi:metadata) A preview of the station coverage is provided here below or opening this MAP. Here is possible to query the features to get stations metadata.

Using R or python for access

It is possible to access the CDB by direct connection, using R or python. Below you can find some example notebooks showing code examples.

Credit goes to Giulio Genova who actually created the first code to access the CDB. See his notes from the BolzanoR presentation on databases for more information on connecting R to databases, SQL queries and using the RStudio connection panel.