1.1 La base de données RACE est accessible depuis le réseau Internet en étant sur le réseau ULiège via le VPN
Voici 3 grands types d’utilisation de requêtes pour une exploitation sous forme de tableaux, de cartes ou de graphiques.
1.1.1 Requêtes depuis pgAdmin pour une utilisation sous forme de tableau
1.1.2 Requêtes sous forme de « view » pour une utilisation dans QGIS
1.1.3 Requêtes pour préparer des graphiques dans « R »
1.1.1 Requêtes depuis pgAdmin
Extraction des données du mât météo (INSJD) entre deux dates
SELECT
data.daytime,
data.dataparam_codeparam,
data.datavalue,
station.codesta
FROM
public.station,
public.timeseries,
public.data
WHERE
station.codesta = timeseries.tssta_codesta AND
timeseries.codeds = data.dasa_codeds AND
data.daytime >= '2011-10-08 00:00:00' AND
data.daytime <= '2011-10-20 00:00:00' AND
station.codesta = 'INSJD'
ORDER BY
data.daytime ASC,
data.dataparam_codeparam ASC;
.jpg)
Moyennes journalières des données du mât météo (INSJD) entre deux dates sous forme de tableau en utilisant des "views" prédéfinies
SELECT
date_part('YEAR',view_matmeteo_windspeed.daytime) as year,
date_part('month',view_matmeteo_windspeed.daytime) as month,
date_part('DAY',view_matmeteo_windspeed.daytime) as day,
count(view_matmeteo_windspeed.daytime),
round(cast(avg(view_matmeteo_windspeed.windspeed)as numeric),3) as windspeed,
round(cast(avg(view_matmeteo_windgust.windgust)as numeric),3) as windgust,
round(cast(mod(CAST(degrees(atan2((sum(sin(radians(view_matmeteo_winddir.winddir)))),(sum(cos(radians(view_matmeteo_winddir.winddir))))))+360 AS NUMERIC),360.0)as numeric),3) as winddir,
round(cast(avg(view_matmeteo_airtemp.airtemp)as numeric),3) as airtemp,
round(cast(avg(view_matmeteo_relhum.relhum)as numeric),3) as relhum,
round(cast(avg(view_matmeteo_atmpress.atmpress)as numeric),3) as atmpress,
round(cast(avg(view_matmeteo_solrad.solrad)as numeric),3) as solrad
FROM
public.view_matmeteo_windspeed,
public.view_matmeteo_windgust,
public.view_matmeteo_winddir,
public.view_matmeteo_airtemp,
public.view_matmeteo_relhum,
public.view_matmeteo_atmpress,
public.view_matmeteo_solrad
WHERE
view_matmeteo_windspeed.daytime = view_matmeteo_windgust.daytime AND
view_matmeteo_windgust.daytime = view_matmeteo_winddir.daytime AND
view_matmeteo_winddir.daytime = view_matmeteo_airtemp.daytime AND
view_matmeteo_relhum.daytime = view_matmeteo_windspeed.daytime AND
view_matmeteo_relhum.daytime = view_matmeteo_atmpress.daytime AND
view_matmeteo_atmpress.daytime = view_matmeteo_solrad.daytime AND
view_matmeteo_windspeed.daytime > '2013-04-30 23:59:59' AND
view_matmeteo_windspeed.daytime < '2014-06-30 23:59:59'
GROUP by year, month, day
ORDER by year, month,day ;
Moyennes journalières et autres mesures statistiques des données de courants pour une timeserie
select
date_part('year',daytime) as my_year, date_part('month',daytime) as my_month, date_part('day',daytime) as my_day,
tssta_codesta, depth,
max(speed) as max_speed,min(speed) as min_speed, round(cast(avg(speed)as numeric),3) as avg_speed,
count(speed) as Nb_valeurs_speed, max(direction) as max_direction,min(direction) as min_direction,
round(cast(mod(CAST(degrees(atan2((sum(sin(radians(direction)))),
(sum(cos(radians(direction))))))+360 AS NUMERIC),360.0)as numeric),3) as avg_direction, count(direction) as Nb_valeurs_direction
from
(SELECT
data_an.daytime,
timeseries.tssta_codesta,
timeseries.depth,
data_an.datavalue as speed,
cast(NULL as real) as direction
FROM
public.data_an,
public.timeseries
WHERE
timeseries.codeds = data_an.dasa_codeds AND
timeseries.tssta_codesta = 'AN004' AND
data_an.dataparam_codeparam = 'waterspeed'
UNION SELECT
data_an.daytime,
timeseries.tssta_codesta,
timeseries.depth,
cast(NULL as real) as speed,
data_an.datavalue as direction
FROM
public.data_an,
public.timeseries
WHERE
timeseries.codeds = data_an.dasa_codeds AND
timeseries.tssta_codesta = 'AN004' AND
data_an.dataparam_codeparam = 'waterdir') tabletempo
GROUP BY
my_year, my_month, my_day, tssta_codesta, depth
ORDER BY
my_year, my_month, my_day;
1.1.2 Requêtes sous forme de « view » pour une utilisation dans QGIS
Nombre de mesures de vitesse du courant par station
CREATE OR REPLACE VIEW public.s012915_viewmaster_number_measures_waterspeed_data_an_by_statio AS
SELECT timeseries.tssta_codesta, data_an.dataparam_codeparam,
count(data_an.datavalue) AS count, timeseries.the_geom
FROM data_an, timeseries
WHERE timeseries.codeds = data_an.dasa_codeds AND data_an.dataparam_codeparam::text = 'waterspeed'::text
GROUP BY timeseries.tssta_codesta, data_an.dataparam_codeparam, timeseries.the_geom
ORDER BY timeseries.tssta_codesta;
ALTER TABLE public.s012915_viewmaster_number_measures_waterspeed_data_an_by_statio
OWNER TO master;
.jpg)
1.1.3 Requêtes pour préparer des graphiques dans « R »
Certains "packages" dont «openair», « RPostgreSQL », «DBI» doivent installés puis chargés pour se connecter à la BD RACE et pour faire ces graphiques.
Rose des vents pour la station (INSJD) entre deux dates
select daytime as date, speed as ws, direction as wd
from (
select daytime, tssta_codesta, max(speed) as speed, max(direction) as direction
from (
SELECT data.daytime, timeseries.tssta_codesta, data.datavalue as speed, NULL as direction FROM public.data, public.timeseries
WHERE timeseries.codeds = data.dasa_codeds AND timeseries.tssta_codesta = 'INSJD' AND data.dataparam_codeparam = 'windspeed'
UNION SELECT data.daytime, timeseries.tssta_codesta, NULL as speed, data.datavalue as direction
FROM public.data, public.timeseries
WHERE timeseries.codeds = data.dasa_codeds AND timeseries.tssta_codesta = 'INSJD' AND data.dataparam_codeparam = 'winddir')
tabletempo GROUP BY daytime, tssta_codesta ORDER BY daytime)
tabletempo2 where daytime BETWEEN '2014-02-01 00:00:00' AND '2014-03-01 00:00:00';
##Établir une connexion (sous VPN)
## Il est nécessaire d'avoir un identifiant et un login ULg pour l'établir
library(RPostgreSQL)
## loads the PostgreSQL driver
drv <- dbDriver("PostgreSQL")
## Open a connection
con <- dbConnect(drv, user= "master", password="xxxxxx", dbname="race_db_v2", host="139.165.57.180", port="5432" )
Sys.setenv(TZ = "UTC")
## loads openair library
library("openair")
##Importation dans R
datarose <- dbGetQuery(con, "select daytime as date, speed as ws, direction as wd from (select daytime, tssta_codesta, max(speed) as speed, max(direction) as direction from (SELECT data.daytime, timeseries.tssta_codesta, data.datavalue as speed, NULL as direction FROM public.data, public.timeseries WHERE timeseries.codeds = data.dasa_codeds AND timeseries.tssta_codesta = 'INSJD' AND data.dataparam_codeparam = 'windspeed' UNION SELECT data.daytime, timeseries.tssta_codesta, NULL as speed, data.datavalue as direction FROM public.data, public.timeseries WHERE timeseries.codeds = data.dasa_codeds AND timeseries.tssta_codesta = 'INSJD' AND data.dataparam_codeparam = 'winddir') tabletempo GROUP BY daytime, tssta_codesta ORDER BY daytime) tabletempo2 where daytime BETWEEN '2014-02-01 00:00:00' AND '2014-03-01 00:00:00'")
windRose(datarose, type="season", angle=22.5, paddle= FALSE)
Menu
- Données de la Région wallonne
- Données de l'IGN Belgique
- Données de l'IGN France
- Données du SPF Economie (INS)
- Données de l'Atlas national de Belgique
- Données de l'UE
- Données pour exercices
Nouvelles
Plus de nouvelles