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 Bases du données « Oiseaux marins » du laboratoire ECOMAR Environnement marin Iles Nom, coordonnées, altitude, surface, substrat, statut de conservation, invasion biologique, … Démographie Capture – marquage – recapture Succès reproducteur Croissance Bathymetrie, SST, Chla, SHA, stratistiques de pêche,… Campagnes en mer Dates, trajectoires, Espèces, nombres, comportements, … Télémétrie Colonies de reproduction Ile, Espèce, coordonnées, dénombrements, … Espèce, colonie, type de plateforme, dates, coordonnées, SST, Comportements,… 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 Fous Paille en queue Frégates Puffin Petrels Exemples Paille en queue à brins rouges Pétrel de Barau Red-tailed tropicbird: annual distribution as revealed by GLS data February June January July Breeding period December Wintering period August Third case: Barau’s petrel Tracked with Argos transmitters and GLS (modified from Pinet et al. submitted) Barau’s petrel: seasonal variations January June Postbreeding migration Wintering period Breeding period July November Prebreeding migration First overview of the seabird hotspots in the tropical Indian Ocean Core foraging areas of all tracked species (kernel 50%, h=1) Wedgetailed shearwater (from Cousin) Red-tailed tropicbird (from Europa) Wedgetailed shearwater (from Aride) Barau’s Petrel (from Réunion) Great frigatebird (from Aldabra) Red-footed and masked boobies Great frigatebird (from Europa) Organisation des données de tracking LAN • PSQL • ODBC • R • … • QGIS • GRASS • … shp2pgsql Shapefiles Internet • Client Web • … POSTGRES + POSTGIS • Apache + PHP • Mapsserver • GeoServer • … POSTGRESQL • système de gestion de base de données relationnelle et objet (SGBDRO) • pratiquement conforme aux normes ANSI SQL 89, SQL 92 (SQL 2), SQL 99 (SQL 3) et SQL:2003 • fonctionne sur diverses plates‐formes matérielles et sous différents systèmes d'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) D • ST_DWithin(A, B, D) ST_Intersects() = TRUE ST_Intersection() = Architecture de la base Seabird_tracking carto Requêtes croisées Public Architecture de la base • Automatisation du formatage POSTGIS CREATE TRIGGER donnees_update BEFORE INSERT ON tracking FOR EACH ROW EXECUTE PROCEDURE format_postgis(); • Synthèse de l’information pour utilisation avec les SIG 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; 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 : pilotes JDBC/JDO, • PHP : phpPgAdmin (interface web d'administration) • pgAdmin (application) • Python : Psycopgzsql/PyGRes (pilotes Python), PoPy/ Zope‐DA (pilotes Zope) • Microsoft Windows : des pilotes ADO, .NET, OLE et ODBC existent 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;