base de données tracking des oiseaux marins de...

Base de données  Tracking des oiseaux marins de l’océan Indien tropical

Atelier de travail sur les bases de données et bases de connaissances environnementales à La Réunion

Campagnes en mer Dates, trajectoires, Espèces, nombres, comportements, …

Télémétrie Espèce, colonie, type de plateforme, dates, coordonnées, SST, Comportements,…

Colonies de reproduction Ile, Espèce, coordonnées, dénombrements, …

Environnement marin Bathymetrie, SST, Chla, SHA, stratistiques de pêche,…

Iles Nom, coordonnées, altitude, surface, substrat, statut de conservation, invasion biologique, …

Démographie Capture – marquage – recapture Succès reproducteur Croissance

Bases du données « Oiseaux marins » du laboratoire ECOMAR

Programme régional de suivi télémétrique des oiseaux marins pour identifier les

secteurs océaniques à protéger dans l’océan Indien

Petrels Puffin Frégates Paille en queue Fous

Exemples

Paille en queue à brins rouges Pétrel de Barau

Wintering period

Breeding period

Red-tailed tropicbird: annual distribution as revealed by GLS data

December

January

February June

August

July

(modified from Pinet et al. submitted)

Third case: Barau’s petrel Tracked with Argos transmitters and GLS

Wintering period

Breeding period

Postbreeding migration

Prebreeding migration

November

January

July

June

Barau’s petrel: seasonal variations

Core foraging areas of all tracked species (kernel 50%, h=1)

First overview of the seabird hotspots in the tropical Indian Ocean

Wedgetailed shearwater (from Cousin)

Wedgetailed shearwater (from Aride)

Great frigatebird (from Aldabra)

Great frigatebird (from Europa)

Red-tailed tropicbird (from Europa)

Barau’s Petrel (from Réunion)

Red-footed and masked boobies

Wedgetailed shearwater (from Cousin)

Wedgetailed shearwater (from Aride)

Great frigatebird (from Aldabra)

Great frigatebird (from Europa)

Red-tailed tropicbird (from Europa)

Barau’s Petrel (from Réunion)

Red-footed and masked boobies

Organisation des données de tracking

LAN •  PSQL •  ODBC

•  R •  …

•  Apache + PHP

Internet •  Client Web •  …

Shapefiles

shp2pgsql

POSTGRES +

POSTGIS •  QGIS •  GRASS • …

•  Mapsserver •  GeoServer • …

POSTGRESQL

•  systèmedegestiondebasededonnéesrelationnelleetobjet(SGBDRO)

•  pratiquementconformeauxnormesANSISQL89,SQL92(SQL2),SQL99(SQL3)etSQL:2003

•  fonctionnesurdiversesplates‐formesmatériellesetsousdifférentssystèmesd'exploitation

•  Licence BSD

Caractéristiques (1/2) •  Transactions ▫  BEGIN ; ▫  UPDATE salaires SET montant = montant * 1.10 WHERE

trig<>’jpa’ ; UPDATE salaires SET montant = montant * 2 WHERE trig=’jpa’ ; ▫  COMMIT

•  Vues (Fondées sur une requête) ▫  Interface cohérente vers les données, même si les tables évoluent ▫  Pas de vues matérialisées à ce jour dans PostgreSQL

•  Schémas (espaces de noms dans une base de données ) ▫  grouper les objets d’une base de données ▫  séparer les utilisateurs entre eux ▫  contrôler plus efficacement les accès aux données ▫  d’éviter les conflits de noms dans les grosses bases de données

Caractéristiques (2/2) •  Triggers ▫ Exécutés avant (BEFORE) ou après (AFTER) un

INSERT, un UPDATE ou un DELETE ▫ Soit pour l’ensemble de la requête (FOR STATEMENT) ▫ Soit pour chaque ligne impactée (FOR EACH ROW) ▫ Peuvent être écrits dans n’importe lequel des langages

de procédure supportés par PostgreSQL (C, PL/PgSQL, PL/Perl, etc. )

•  Héritages ▫ Sur les tables ▫ Sur la structure ▫ Sur l’administration

POSTGIS

•  Module spatial pour PostgreSQL •  Conforme aux spécifications de l’OpenGIS

Consortium • Compatible avec Mapserver / GeoServer •  PostGIS permet le traitement d'objets spatiaux

dans les serveurs PostgreSQL, autorisant le stockage en base de données pour les SIG

Les Objets SIG •  Exemples de représentation en WKT d'objets spatiaux ▫  POINT(0 0) ▫  LINESTRING(0 0,1 1,1 2) ▫  POLYGON((0 0,4 0,4 4,0 4,0 0),(1 1, 2 1, 2 2, 1 2,1 1)) ▫ MULTIPOINT(0 0,1 2) ▫ MULTILINESTRING((0 0,1 1,1 2),(2 3,3 2,5 4)) ▫ MULTIPOLYGON(((0 0,4 0,4 4,0 4,0 0),(1 1,2 1,2 2,1 2,1 1)),

((-1 -1,-1 -2,-2 -2,-2 -1,-1 -1))) ▫  GEOMETRYCOLLECTION(POINT(2 3),LINESTRING((2

3,3 4))) •  les objets géographiques inclue un identifiant du système

de références spatiales ("spatial referencing system identifier", SRID).

• ST_Intersects(A, B)

• ST_Contains(A, B) • ST_Within(B, A)

• ST_Touches(A, B)

• ST_Crosses(A, B)

• ST_DWithin(A, B, D) D

ST_Intersects() = TRUE

ST_Intersection() =

Architecture de la base Seabird_tracking

carto Public

Requêtes croisées

CREATE TRIGGER donnees_update BEFORE INSERT ON tracking FOR EACH ROW EXECUTE PROCEDURE format_postgis();

•  Automatisation du formatage POSTGIS

CREATE VIEW seabird_postgis AS SELECT tracking.id_track AS id, tracking.lon, tracking.lat, tracking.date, site.island, site.colony, meta_data.tag_type, bird.band, bird.sex, species.name AS "species name", tracking.geom FROM tracking JOIN meta_data ON meta_data.id_meta_data = tracking.id_meta_data JOIN bird ON bird.id_bird = meta_data.id_bird JOIN species ON species.id_species = bird.id_species JOIN site ON meta_data.id_site = site.id_site;

•  Synthèse de l’information pour utilisation avec les SIG

Architecture de la base

Exemples d’utilisation

•  SIG ▫ Quantum QGIS --> connexion directe au serveur

POSTGRES/POSTGIS ▫ Uniquement les tables portant des informations

géographiques --> utilité des vues ▫ Visualisation rapide/Possibilité d’éditer des clauses

WHERE dans la table affichée •  R ▫ Utilisation du package RODBC (implémentation ODBC

sur R) ▫ Edition des requêtes SQL (accès à toute la base de

données) ▫ Stockage des résultats de la requête

QGIS

QGIS

Utilisation dans R -- Exemple de Script •  require(RODBC) # database connectivity •  require(maps) # map package

•  ### ouverture de la connection avec la base de donnée ##### •  channel <- odbcConnect("seabird", uid= »xxxx", pwd= »xxxxxx", case="postgresql")

•  ######## la requêtes ######### •  land=c("SELECT tracking.date,tracking.time,tracking.lat,tracking.lon FROM

tracking , carto.lowres WHERE ST_ Within(tracking.geom,lowres.the_geom);")

•  ## exécution de la requête, stockage des résultats et fermeture de la connexion ## •  data=sqlQuery(channel, land) •  odbcCloseAll()

•  #### visualisation des resultats #### •  plot(data$lon,data$lat,asp=1,pch=19) •  map(add=T)

Utilisation dans R

Interface

•  Psql(console)•  Java:pilotesJDBC/JDO,•  PHP:phpPgAdmin(interfacewebd'administration)•  pgAdmin(application)•  Python:Psycopgzsql/PyGRes(pilotesPython),PoPy/Zope‐DA(pilotesZope)

• MicrosoftWindows:despilotesADO,.NET,OLEetODBCexistent

A && B = TRUE A && B = FALSE

A && B = TRUE _ST_Intersects(A && B) = FALSE

-- Function: format_postgis()

-- DROP FUNCTION format_postgis();

CREATE OR REPLACE FUNCTION format_postgis() RETURNS "trigger" AS $BODY$ DECLARE BEGIN NEW.the_geom = setSRID(MakePoint(NEW.lon,NEW.lat),4326);

RETURN NEW; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE; ALTER FUNCTION format_postgis() OWNER TO gael;