Postgis : une extension de Postgres pour l’analyse spatiale

14/12/2017Journée rBDD 2017 – Postgis pour l’analyse spatiale

http

s://c

reat

ivec

omm

ons.

org/

licen

ses/

by-n

c-sa

/4.0

/C

hris

tine.

plum

ejea

ud-p

erre

au@

univ

-lr.fr

Postgis : une extension de Postgres pour l’analyse spatiale

1

Christine Plumejeaud-Perreau

UMR 7266 Littoral Environnement et Sociétés, LIENSs

2 rue Olympe de Gouges, La Rochelle

[email protected]

Journée « Bonnes pratiques et outils liés à la mise en place

d’une base de données recherche » 14/12/2017


http

s://c

reat

ivec

omm

ons.

org/

licen

ses/

by-n

c-sa

/4.0

/C

hris

tine.

plum

ejea

ud-p

erre

au@

univ

-lr.fr

2

Cette œuvre est mise à disposition selon les termes de la Licence Creative Commons

Attribution - Pas d’Utilisation Commerciale - Partage dans les Mêmes Conditions 4.0

International.

Vous êtes autorisé à :

� Partager — copier, distribuer et communiquer le matériel par tous moyens et

sous tous formats

� Adapter — remixer, transformer et créer à partr du matériel

Selon les conditions suivantes :

� Attribution — Vous devez mentionner le nom de l'auteur de la manière

suivante : « Christine Plumejeaud-Perreau, LIENSs-CNRS, 2017 »

� Pas d’Utilisation Commerciale — Vous n'êtes pas autorisé à faire un usage

commercial de cette Œuvre, tout ou partie du matériel la composant.

� Partage dans les Mêmes Conditions — Si vous modifiez, transformez ou

adaptez cette œuvre, vous n'avez le droit de distribuer votre création que sous

une licence identique ou similaire à celle-ci.

Voir la version intégrale de la licence : http://creativecommons.org/licenses/by-nc-sa/4.0/

Copyright


http

s://c

reat

ivec

omm

ons.

org/

licen

ses/

by-n

c-sa

/4.0

/C

hris

tine.

plum

ejea

ud-p

erre

au@

univ

-lr.fr

3

Plan

� Ecosystème autour de Postgis

� 4 types spatiaux conformes aux standards de l’OGC

� Geography

� Geometry

� Raster

� Topology

� Exemples d’analyses

� Indices de diversité d’îlots sur la France entière

� Intégration d’un modèle de submersion par tsunami

� Prioriser 600 000 zones humides en France


http

s://c

reat

ivec

omm

ons.

org/

licen

ses/

by-n

c-sa

/4.0

/C

hris

tine.

plum

ejea

ud-p

erre

au@

univ

-lr.fr

4

Les + de postgis

� Support de l’information géographique

� http://postgis.refractions.net/documentation/casestudies/ign/

“IGN began exploring storing their data in a seamless database for the entire country

around 2002. They benchmarked several technologies,

including Oracle, DB2 and PostGIS/PostgreSQL, looking for a product that met a number

of requirements:

•The capacity to handle more than 100 million spatial objects;

•The speed to answer queries quickly given such a large database;

•Full transactional integrity to ensure data quality during maintenance operations.

The benchmarking tests showed PostgreSQL/PostGIS with similar performance to the

proprietary alternatives.”

from Frank Fuchs, IGN - 2006


http

s://c

reat

ivec

omm

ons.

org/

licen

ses/

by-n

c-sa

/4.0

/C

hris

tine.

plum

ejea

ud-p

erre

au@

univ

-lr.fr

5

Webographie

� Postgis : http://postgis.refractions.net/

� Site Web français : http://www.postgis.fr/

� Wiki : http://trac.osgeo.org/postgis

� Mailing liste : http://postgis.org/mailman/listinfo/postgis-users

� Forum : http://gis.stackexchange.com/

� https://postgis.net/docs/using_postgis_dbmanagement.html

� http://www.postgis.us/downloads/postgis21_cheatsheet.pdf

� http://www.bostongis.com/

� http://www.postgis.fr/chrome/site/docs/workshop-routing-

foss4g/docs/pgRoutingWorkshop.pdf

� Tuning de la base : http://wiki.openstreetmap.org/wiki/PostgreSQL

� Postgis et GRASS : https://grasswiki.osgeo.org/wiki/PostGIS


http

s://c

reat

ivec

omm

ons.

org/

licen

ses/

by-n

c-sa

/4.0

/C

hris

tine.

plum

ejea

ud-p

erre

au@

univ

-lr.fr

6


� Avec des langages de programmation

�C (GDAL), R (sf et RpostgreSQL),

�Python (psycopg2 et shapely),

�Java (JTS et Geotools)

� Avec des SIG desktop

� QGIS

� uDIG,

� OpenJump

� GRASS

� Avec des serveurs de données

� Qgis server

� Geoserver

� Map server

� Avec des plateformes de Web Mapping

� Lizmap

� GeoCMS

Ecosystème : interconnexion, compatibilité


http

s://c

reat

ivec

omm

ons.

org/

licen

ses/

by-n

c-sa

/4.0

/C

hris

tine.

plum

ejea

ud-p

erre

au@

univ

-lr.fr

7

Plan



� Geography

� Geometry

� Raster

� Topology






http

s://c

reat

ivec

omm

ons.

org/

licen

ses/

by-n

c-sa

/4.0

/C

hris

tine.

plum

ejea

ud-p

erre

au@

univ

-lr.fr

8

Le type geography

create extension postgis;� Fonctions : https://postgis.net/docs/reference.html

� En coordonnées géographiques, projection WGS84 (EPSG = 4326)

Longitude

Latitude

A

B


http

s://c

reat

ivec

omm

ons.

org/

licen

ses/

by-n

c-sa

/4.0

/C

hris

tine.

plum

ejea

ud-p

erre

au@

univ

-lr.fr

� Systèmes de projection (EPSG) : http://www.epsg-registry.org/

9

Geography �•� Geometry

��

� ��

�

St_distance(geometry, geometry)

geometry ST_Transform(geometry g1, integer srid)

select ST_Transform(A, 2154)

-- renvoie la géométrie de A

dans la projection Lambert 93


http

s://c

reat

ivec

omm

ons.

org/

licen

ses/

by-n

c-sa

/4.0

/C

hris

tine.

plum

ejea

ud-p

erre

au@

univ

-lr.fr

10

Le type geometry : standard OGC


http

s://c

reat

ivec

omm

ons.

org/

licen

ses/

by-n

c-sa

/4.0

/C

hris

tine.

plum

ejea

ud-p

erre

au@

univ

-lr.fr

11

� Les trois principaux types de géométries

En résumé, la géométrie


http

s://c

reat

ivec

omm

ons.

org/

licen

ses/

by-n

c-sa

/4.0

/C

hris

tine.

plum

ejea

ud-p

erre

au@

univ

-lr.fr

12

� Le modèle des 9-i [Egenhofer, 1991]

Les relations spatiales

Norme ISO 19107

Implémentée dans

PostgreSQL avec postGIS


http

s://c

reat

ivec

omm

ons.

org/

licen

ses/

by-n

c-sa

/4.0

/C

hris

tine.

plum

ejea

ud-p

erre

au@

univ

-lr.fr

13

Le type raster

� Une grille: le modèle de submersion de padangscenario_2b_tide_subsidence_dsm_inundation1.tif

� Des fonctions adaptées : https://postgis.net/docs/RT_reference.html


http

s://c

reat

ivec

omm

ons.

org/

licen

ses/

by-n

c-sa

/4.0

/C

hris

tine.

plum

ejea

ud-p

erre

au@

univ

-lr.fr

14

� GIST

Index spatial

CREATE INDEX bati_geom_idx ON bati USING gist (geom);


http

s://c

reat

ivec

omm

ons.

org/

licen

ses/

by-n

c-sa

/4.0

/C

hris

tine.

plum

ejea

ud-p

erre

au@

univ

-lr.fr

15

Le type topology

� Le cadastre à Strasbourg : open data sur http://www.strasbourg.eu/fr/ma-

situation/professionnel/open-data/donnees/referentiel-geographique-open-data

Passage d’un ensemble de géométries à un graphe (avec des nœuds et des arcs)

� Economies de place

� Vérification de cohérence facilitée

create extension postgis_topology;


http

s://c

reat

ivec

omm

ons.

org/

licen

ses/

by-n

c-sa

/4.0

/C

hris

tine.

plum

ejea

ud-p

erre

au@

univ

-lr.fr

16

� Exemple : voir et importer le cadastre opensource de Strasbourg

QGIS et DB manager


http

s://c

reat

ivec

omm

ons.

org/

licen

ses/

by-n

c-sa

/4.0

/C

hris

tine.

plum

ejea

ud-p

erre

au@

univ

-lr.fr

17

create extension postgis_topology

alter table parcelles_strasbourg add column polygon geometry

update parcelles_strasbourg set polygon = st_force2D(geom)

select topology.CreateTopology('topoparcelles', 2154)

select topology.addTopoGeometryColumn('topoparcelles', 'public',

'parcelles_strasbourg', 'topogeom', 'POLYGON')

-- 2 est l’identifiant de la couche topologique

insert into parcelles_strasbourg (topogeom)

select topology.toTopoGeom(polygon, 'topoparcelles', 2, 1) from

parcelles_strasbourg where num_comm = '118' and num_sect = '2';

Fabriquer le graphe à partir des polygones


http

s://c

reat

ivec

omm

ons.

org/

licen

ses/

by-n

c-sa

/4.0

/C

hris

tine.

plum

ejea

ud-p

erre

au@

univ

-lr.fr

18

Plan



� Geography

� Geometry

� Raster

� Topology






http

s://c

reat

ivec

omm

ons.

org/

licen

ses/

by-n

c-sa

/4.0

/C

hris

tine.

plum

ejea

ud-p

erre

au@

univ

-lr.fr

19

RGE sur la France entière

� Workflow de l’ANR MAPUCE, volet 1.1

Christine Plumejeaud-Perreau, Cyril Poitevin, Cécilia Pignon-Mussaud, Nathalie Long. Building Local Climate Zones by

using socio-economic and topographic vectorial databases. 9th International Conference on Urban Climate, Jul 2015,

Toulouse, France. Proceeding of the 9th International Conference on Urban Climate, 2015, 〈http://www.meteo.fr/icuc9/index.html〉. 〈hal-01252819〉


http

s://c

reat

ivec

omm

ons.

org/

licen

ses/

by-n

c-sa

/4.0

/C

hris

tine.

plum

ejea

ud-p

erre

au@

univ

-lr.fr

20

� Chargement et transformation de données spatiales

� Lire le manuel en ligne : http://www.gdal.org/ogr2ogr.html

� Installer GDAL

Utilisation de Ogr2Ogr

set PGCLIENTENCODING=latin1

ogr2ogr -f "PostgreSQL" PG:"host=194.214.86.66 port=5432

user=formateur dbname=savoie password=formation

schemas=public" C:\\cheminverslefichier\\SURFACE_EAU.shp

-lco SPATIAL_INDEX=YES -preserve_fid -append

-nln surface_eau


http

s://c

reat

ivec

omm

ons.

org/

licen

ses/

by-n

c-sa

/4.0

/C

hris

tine.

plum

ejea

ud-p

erre

au@

univ

-lr.fr

21

Analyse de diversité du bâti par îlot ou par carreau

� Méthode

1. Projeter des attributs d’une grille (mif/mid) vers des bâtiments

2. Agréger à l’échelle de l’îlot

3. Calculer une grille avec Postgis

4. Agréger à l’échelle du carreau : transfert par surface majoritaire

5. Comparer : analyse avec R des indices de diversité

L’eau, grille de 250 x 250 m

28 % du bâti

à cheval


http

s://c

reat

ivec

omm

ons.

org/

licen

ses/

by-n

c-sa

/4.0

/C

hris

tine.

plum

ejea

ud-p

erre

au@

univ

-lr.fr

22

Résultats analyse

specialisation

niveau• <= 2

• > 2

0.0 –

0.2

0.2 –

0.4

0.4 –

0.6

0.6 -

0.8

0.8 –

1.0

•


http

s://c

reat

ivec

omm

ons.

org/

licen

ses/

by-n

c-sa

/4.0

/C

hris

tine.

plum

ejea

ud-p

erre

au@

univ

-lr.fr

23

� Padang est une ville côtière d’Indonésie qui vit avec un fort aléa de submersion

marine. Thèse Henky Mayaguezz� Vulnérabilité humaine à Padang : intérieurs et extérieurs

� Résistance du bâti, étages, présence d’ouvertures

� Densité de débris, encombrement des voies de communication

� Modéliser les rythmes de vie des habitants

� Etablir une analyse spatio-temporelle du risque : en chaque endroit, à chaque heure, quel est le

nombre de décès possibles ?

� Mesurer l’accessibilité des zones refuges depuis chaque endroit de la ville

� Temps d’accès vers la zone de refuge la plus proche ?

� Couverture des zones refuges ?

� Quel est le meilleur chemin vers le site d’évacuation le plus proche ?

Tsunami à Padang

Henky Mayaguezz, Christine Plumejeaud-Perreau, Frédéric Pouget, Frédéric Leone. Evolution spatio-temporelle de

l'exposition humaine face au tsunami à Padang: diagnostique de la vulnérabilité et des capacités d'évacuations à

l'échelle infra-urbaine. Revue Internationale de Géomatique, Lavoisier, 2016, 26 (3), pp.273-306. 〈hal-01432902〉

14/12/2017Journée rB

DD

2017 –P

ostgispour l’analyse spatiale

https://creativecommons.org/licenses/by-nc-sa/4.0/[email protected]

Les données


http

s://c

reat

ivec

omm

ons.

org/

licen

ses/

by-n

c-sa

/4.0

/C

hris

tine.

plum

ejea

ud-p

erre

au@

univ

-lr.fr

25

Hétérogénéité des données

6782 Polygones596 Polygones 35 Points

150 Linestrings


http

s://c

reat

ivec

omm

ons.

org/

licen

ses/

by-n

c-sa

/4.0

/C

hris

tine.

plum

ejea

ud-p

erre

au@

univ

-lr.fr

26

�L’attribut camc (CApacité Maximale de Charge) arrive

dans une couche bâti différente, non nettoyée… �

Intégration progressive de données

Bati b1

Bati_total_1 b2

x

X

y

Y

z

camc ?

camc _tot

Si (x ∩ X)

where st_intersects(x.geom, X.wkb_geometry)Si (x ∩ X) :


http

s://c

reat

ivec

omm

ons.

org/

licen

ses/

by-n

c-sa

/4.0

/C

hris

tine.

plum

ejea

ud-p

erre

au@

univ

-lr.fr

27

�Import de ‘Bati_total_1’

�Ajout de l’attribut manquant dans ‘bati’

�Calcul de l’attribut manquant ‘camc’

Mise à jour par jointure spatiale

ogr2ogr -f "PostgreSQL" PG:"host=localhost port=5433 user=postgres dbname=padang

password=postgres schemas=raw_data" "C:\Henky_risque\Données\Export\bati_total_1.shp" -nlt

PROMOTE_TO_MULTI

ALTER TABLE bati ADD column camc integer;

UPDATE bati b1

SET camc = b2.camc_tot

FROM

(select camc_tot, wkb_geometry from raw_data.bati_total_1)

AS b2

WHERE st_intersects(b2.wkb_geometry, b1.geom);


http

s://c

reat

ivec

omm

ons.

org/

licen

ses/

by-n

c-sa

/4.0

/C

hris

tine.

plum

ejea

ud-p

erre

au@

univ

-lr.fr

28

� Calcul de la hauteur d’eau moyenne par un bâtiment dans un rayon de 40 m

� Efficace ! 24 min de traitement sur un PC de travail

Exploiter le modèle raster de submersion

raster2pgsql -I -C -s 32747 "D:\Data\scenario_2b_tide_subsidence_dsm_inundation1.tif"

public.hauteur_eau > "D:\Data\import_hauteur_eau.sql"

psql -U postgres -p 5433 -d padang -f "D:\Data\import_hauteur_eau.sql«

-- statistiques sur raster

SELECT round(ST_SummaryStats(rast), 2) FROM hauteur_eau

-- Returns summarystats consisting of count, sum, mean, stddev, min, max for a given raster band of a raster or raster coverage. Band 1 is

assumed is no band is specified.-- (21851049, 2144374.31, 0.10, 0.60, 0, 11.90)

SELECT ST_Height(rast) FROM hauteur_eau

SELECT ST_Width(rast) FROM hauteur_eau

-- 4911 lignes * 5143 colonnes

alter table bati add column hauteur_eau_moy float;

UPDATE bati b1 SET hauteur_eau_moy = (

SELECT (stats).mean

FROM (SELECT ST_SummaryStats(ST_Clip(h.rast, st_buffer(b.geom, 40))) as stats

FROM hauteur_eau as h, bati as b

WHERE b.id=b1.id) as a);

-- La requête a été exécutée avec succés : 6690 lignes modifiées. La requête a été

exécutée en 1436778 ms (24 min)


http

s://c

reat

ivec

omm

ons.

org/

licen

ses/

by-n

c-sa

/4.0

/C

hris

tine.

plum

ejea

ud-p

erre

au@

univ

-lr.fr

29

Des animations, des courbes

Video


http

s://c

reat

ivec

omm

ons.

org/

licen

ses/

by-n

c-sa

/4.0

/C

hris

tine.

plum

ejea

ud-p

erre

au@

univ

-lr.fr

30

Prioriser des zones humides

� 600 000 zones humides en France

� Exemples d’indicateurs

� Surface moyenne après calcul d’un tampon de 50 m.

� Contribution relative aux masses d’eau

https://github.com/cplumejeaud/DISA-forum

-- Créer la table des buffer à 50m autour des ZHU

create table zhu_buffer50 as (

SELECT (

st_dump(ST_Union(ARRAY( select st_buffer(geom, 50) from

fma.zone_humide)))).geom

)

-- La requête a été exécutée avec succès : 78 611 lignes modifiées.

-- La requête a été exécutée en 9410598 ms.

-- 2h36 min


http

s://c

reat

ivec

omm

ons.

org/

licen

ses/

by-n

c-sa

/4.0

/C

hris

tine.

plum

ejea

ud-p

erre

au@

univ

-lr.fr

31

Contribution des Zones Humides aux masses d’eau

Zoom sur le marais poitevin, et

la rivière du Mignon.

Le réseau est décomposé en

segments (=arêtes), avec la

création d’un nœud à chaque

embranchement.

La ZHU 417333 contribue à 3

segments : 273, 277 et 286

La somme des aires des ZHU

participants à ces segments est :

95979 ha

La surface de la ZHU 417333 est

de 0,25 ha

Elle contribue donc pour 0.00026

% à la protection des masses

d’eau avoisinantes

La ZHU 36 Contribue pour 0,35% aux masses d’eau environnantes


http

s://c

reat

ivec

omm

ons.

org/

licen

ses/

by-n

c-sa

/4.0

/C

hris

tine.

plum

ejea

ud-p

erre

au@

univ

-lr.fr

32

� Postgis

� Une extension spatiale très complète : vecteur, raster

� Graphes avec postgis_topology

� Compatible et Interopérable car conforme aux standards OGC

� Analyse spatiale et spatio-temporelle sur des volumes de données

conséquents avec des bonnes performances

� Suivre les mises à jour

� Exporter dans un format ASCII http://www.postgis.net/docs/reference.html#Geometry_Outputs

Conclusion

SELECT PostGIS_Full_Version();

-- "POSTGIS="2.2.2 r14797" GEOS="3.5.0-CAPI-1.9.0 r4090" PROJ="Rel.

4.9.1, 04 March 2015" GDAL="GDAL 2.0.2, released 2016/01/26"

LIBXML="2.7.8" LIBJSON="0.12" TOPOLOGY RASTER"

SELECT st_astext(monpoint) FROM padang.refuges LIMIT 1;

-- “POINT(923770.915469499 6513384.48349125)”

SELECT st_asgeojson(monpoint) FROM padang.refuges LIMIT 1;

-- “{“type”:”Point”,”coordinates”:[923770.915469499,6513384.48349125]}”

Documents

Postgis : une extension de Postgres pour l’analyse spatiale