plan les bases de données spatiales

1

David FAU 1

Les bases de données spatiales

PostgreSQL – PostGISOracle Spatial

David FAU 2

Plan

• Présentation du cours• Module Spatial• Géométrie dans PostGIS

– Indexation de données– Requête avec des données spatiales– Outils

• Géométrie dans Oracle Spatial– Indexation de données– Requête avec des données spatiales– Outils

David FAU 3

Présentation du cours

David FAU 4

La cartographie…c’est

• Lier : – information géométrique

– Information attributaire

Parcelle de monsieur Dupont8 rue des mignotes2000 F/m²

2

David FAU 5

Quelques solutions

• Bases de données spatiales– Stockage de données– Indexation des données– Requêtes spatiales

»PostgreSQL / PostGIS

»Oracle Spatial http://www.oracle.com

http://www.postgis.org

David FAU 6

Module Spatial

Généralités

David FAU 7

A quoi sert-il?Stockage des données

• Stockage – Données géométriques vectorielles– Modèle Relationnel Objet

• Stockage sous forme de champs– Champs géométriques– Champs classiques pour les attributs

David FAU 8

A quoi sert-il?Indexation des données

• Indexation spatiale des données– Récupération de

données par zone– Topologie– Requête

3

David FAU 9

Spatial c’est quoi

• Schéma SQL permettant de faire:– Stockage– Mise à jour– Récupération– Indexation

D’objets géométriques

David FAU 10

Présentation du module spatial PostGIS

PostGIS dans PostgreSQLLes champs géométriques

Quelques mots sur PostgreSQL

• SGBDRO libre (licence BSD)• Multi-plateforme (Solaris, MacOS, Linux,

Unix, Windows en natif)

• Développé à Berkeley (Californie)• Concurrent de MySQL, Firebird (libres) et

Oracle, Microsoft SQL Server, DB2, Sybase (propriétaires)

David FAU 11

Quelques mots sur PostgreSQL

• Support du standard SQL (requêtes complexes, clés étrangères, triggers, vues, intégrité des transactions, etc…)

• Extensible par l’utilisateur (types de données, fonctions, opérateurs, agrégats,

indexage, langages de procédure, etc…)

David FAU 12

4

Présentation de PostGIS

• Extension spatiale de PostgreSQL• Stockage des objets SIG dans une base

• Gestion des index spatiaux (arbres)• Calcul et analyse des objets

géographiques

David FAU 13 David FAU 14

PostGIS contient

• Des tables supplémentaires• Un mécanisme d’indexation

• Une suite d’opérateurs et de fonctions– Zone d’intérêt – Jointure spatiale

PostGIS

• Conforme au standard de l’OpenGIS Consortium (Simple Feature Specifications)

• Méthodes standards de description :» WKB (Well-Known Binary)» WKT (Well-Known Text)

David FAU 15

Description géométrique

• WKT :» 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, MULTILINESTRING, MULTIPOLYGON» GEOMETRYCOLLECTION

• SRID : Spatial Referencing System Identifier» Geometry = GeometryFormText(Text WKT, SRID)

David FAU 16

5

David FAU 17

Présentation PostGIS

• Table SPATIAL_REF_SYSIdentifiants numériques et description textuelle des systèmes de coordonnées

• Table GEOMETRY_COLUMNSCreate Table GEOMETRY_COLUMNS (

F_TABLE_CATALOG VARCHAR(256) NOT NULL,

F_TABLE_SCHEMA VARCHAR(256) NOT NULL,F_TABLE_NAME VARCHAR(256) NOT NULL,

F_GEOMETRY_COLUMN VARCHAR(256) NOT NULL,COORD_DIMENSION INTEGER NOT NULL,

SRID INTEGER NOT NULL,

TYPE VARCHAR(30) NOT NULL,)

David FAU 18

Création d’une table spatiale

• CREATE TABLE ROADS (

ID INTEGER PRIMARY KEY, NAME VARCHAR(256) ) ;

• Ajout de la colonne géométrie

SELECT AddGeometryColumn(

‘ROADS’,‘geom’,423,‘LINESTRING’,2);

David FAU 19

Les formes géométriques

POINTPOINT POLYGONPOLYGONLINESTRINGLINESTRING

MULTIPOINTMULTIPOINT MULTIPOLYGONMULTIPOLYGONMULTILINESTRINGMULTILINESTRING

GEOMETRYCOLLECTIONGEOMETRYCOLLECTION

David FAU 20

ExempleLe carré

• Un rectangle en géométrie PostGIS

POLYGON(12 15,15 15,15 24,12 24,12 15)

)

6

David FAU 21

ExempleLa requête correspondanteINSERT INTO Ma_Table VALUES (

1,'Rectangle1',GeomFromText(

‘POLYGON((12 15,15 15,15 24,12 24,12 15))’,423));

David FAU 22

Exemple de géométrie complexe

INSERT INTO ma_tableVALUES (’OBJ_1’,

GeomFromText(‘POLYGON(

(6 15,10 10,20 10,25 15,25 35,19 40,11 40,6 25, 6 15),(12 15,15 15,15 24,12 24,12 15)

)’,423)

);

David FAU 23

Indexation des données

Index

David FAU 24

Indexation

• Rendre possible l’utilisation avec de grandes quantités de données

• Organisation dans un arbre de recherche

• Trois types :» B-Tree : ordonné sur un axe (nombre, lettres, dates, ...)» R-Tree : rectangles, sous-rectangles, sous-sous-

rectangles, …» GiST : à côté de, qui couvre, incluse, …

� GiST pour les SIG

7

David FAU 25

Requête d’indexation

CREATE INDEX [nom_de_l_index] ON[nom_de_la_table]USING GIST ( [champ_géométrie] GIST_GEOMETRY_OPS );

David FAU 26

Collecte des statistiques de l’index

Optimiser les planifications de requêtes

VACUUM ANALYZE [table_name] [column_name];

VACUUM : récupération de l’espace de stockageANALYZE : Statistiques sur le contenu (pg_statistic)

David FAU 27

Requête spatiale

Modèle d’interrogationOpérateur

David FAU 28

Avant toute requête

• Créer les index• Collecter les statistiques

8

David FAU 29

Jeu de données important

Modèle d’interrogation

• Système à deux filtres

FiltreJeu de

données réduit

Filtre Résultat

David FAU 30


• Premier filtre– Réduction du nombre de données à traiter– Utilisation de boite englobante

• Second filtre– Travail sur un jeu de données réduit

David FAU 31

Exemple

SELECT the_geom FROM geom_tableWHERE

the_geom && box3d('BOX3D(90900 190900, 100100 200100)‘)

AND distance( the_geom, GeomFromText( 'POINT(100000 200000)', -1 ) ) < 100

Opérateurs

• && : si le cadre limite d'un objet géométrique intersecte celui d'un autre

• ~= : teste si deux objets géométriques sont géographiquement identiques

• = : teste uniquement si les cadres limitesdes objets géographiques sont les mêmes

� tirent avantage des index spatiaux GiST

32David FAU 32

9

Relation entre objets

• ST_Relate– Paramètres

• geom1 : premier objet• geom2 : second objet• mask : Relation recherchée

David FAU 3333David FAU David FAU 34


• Différentes relations– ST_Contains– ST_Equals– ST_Disjoint– ST_Intersects

– ST_Touches– ST_Crosses

David FAU 35

Aire d’un objet

• ST_Area

David FAU 36

Retourne le barycentre d’un polygone

• ST_Centroid

10

David FAU 37

Enveloppe convexe

• ST_Convexhull

David FAU 38

Différence entre deux polygones

• ST_Difference– geom1– geom2

David FAU 39

Distance entre objets

• ST_Distance– geom1– geom2

David FAU 40

Intersection

• ST_Intersection– geom1– geom2

11

David FAU 41

Validité de la géométrie

• ST_isvalid

David FAU 42

Point sur une surface

• ST_PointOnSurface

David FAU 43

OUTILS

• Extension :– Bibliothèque GEOS : tests géométrique

(http://geos.refractions.net )– Bibliothèque Proj4 : reprojections

(http://www.remotesensing.org/proj )

• Conversion SHP � PostGIS (shp2pgsql)

Présentation du module spatial Oracle

Spatial dans OracleLes champs géométriques

David FAU 4444David FAU

12

Présentation Oracle Spatial

Multidimension

Context

Option

Spatial Data

Option

Oracle7

Image

Cartridge

Visual Image Retrieval

Cartridge

Time Series

Cartridge

Context

Cartridge

Spatial

Cartridge

Oracle8Intermedia

Image

Audio

Video

Text

Locator

Visual Image Retrieval

Time Series

Spatial

Oracle8 i

4545David FAU

Présentation Oracle Spatial

• Notion de champs géométrique

Id Géométrie Propriétaire Adresse

123

DupontDupuisDumoustier

Rue du quaiRue du pontRue d’auxonne


Spatial contient

• Un schéma (dans MDSYS)• Un Mécanisme d’indexation

• Une suite d’opérateurs et de fonctions– Zone d’intérêt – Jointure spatiale

• Suite d’utilitaires d’administration


Avantage du modèle Objet

• Plus de types géometriques– Arcs, cercles, Polygones composés,

Polylignes composées, Rectangles optimisés

• Utilisation plus simple des indexs

• Maintien de l’index par Oracle• Amélioration des performances

48David FAU 48David FAU

13

Avantage du modèle relationnel

• Possibilité de réplication de bases de données

• Possibilité de bases de données répliquées

• Partitionnement des tables et chargement parallèle de l’index


Choix pour ce cours

• Modèle Objet– Accès plus facile– Modèle relationnel condamné à disparaître


Création d’une table spatiale

• Le type de géométrie est MDSYS.SDO_GEOMETRY

• Exemple

CREATE TABLE Parcelle(

id NUMBER PRIMARY KEY,

Geometry MDSYS.SDO_GEOMETRY);


Le type géométrie de Oracle

• Relationnel objet :– Géométrie dans un objet géométrique

• Champs SDO_GTYPE Type de géométrie• Champs SDO_SRID Système de projection• Champs SDO_POINT point pour les objet

ponctuels• Champs SDO_ELEM_INFO information sur l’objet• Champs SDO_ORDINATES géométrie des

champs


14

53

Les formes géométriques

Chaîne de Chaîne de lignes avec lignes avec intersection intersection

ValideValide

PointPoint

PolygonePolygone Polygone Polygone trouétroué

Polygone avec Polygone avec intersectionintersection

Chaîne de Chaîne de LignesLignes

CompositionCompositiond’arcsd’arcs

CompositionCompositionde lignesde lignes

Polygone Polygone composécomposé

PolygonesPolygonesoptimisésoptimisés

David FAUNon valideNon valide

53David FAU

ExempleLe carré

• Un rectangle en géométrie Oracle


ExempleLa requête correspondante

• INSERT INTO Ma_Table VALUES( 125,

MDSYS.SDO_GEOMETRY(2003,423,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),MDSYS.SDO_ORDINATE_ARRAY(12,15,15,24))

‘Mon Rectangle à moi’);


Différents types de géométrieGéométrie simple

• SDO_GTYPE:– d000 : géométrie inconnue– d001 : Point– d002 : Polyligne– d003 : Polygone


15

Différents types de géométrieGéométrie composée

• SDO_GTYPE:– d004 : Collection– d005 : Liste de points– d006 : Liste de polylignes– d007 : Liste de polygones


Les autres paramètres

• SDO_SRID : Système de projection• SDO_POINT : Point en X, Y et Z.

– Utilisé dans le type POINT

• SDO_ELEM_INFO : Definition du contenu de SDO_ORDINATES

• SDO_ORDINATES : Contient une liste de point :– En 2D : X1,Y1, X2,Y2, X3, Y3, …– En 3D : X1,Y1,Z1 X2,Y2,Z2 X3,Y3,Z3 …


A propos de SDO_ELEM_INFO

• Tableau dynamique– Contient des triplets de nombres

• SDO_STARTING_OFFSET– Indice de début de l’objet

• SDO_ETYPE– Type de l’objet

• SDO_INTERPRETATION– Si type d’objet composé � nombre de sous objet

consécutif après


SDO_ELEM_INFO(Point unique)

– SDO_GTYPE : d001– SDO_INTERPRETATION : 1


16

SDO_ELEM_INFOListe de point

– SDO_ETYPE : 1– SDO_INTERPRETATION : Nombre de points


SDO_ELEM_INFOPolyligne

• SDO_ETYPE : 2• SDO_INTERPRETATION : 1


SDO_ELEM_INFOPolyligne faite d’arcs

• SDO_ETYPE : 2• SDO_INTERPRETATION : 2


SDO_ELEM_INFOPolygone

• SDO_ETYPE : t003• SDO_INTERPRETATION : 1t=1 si on fait une figure pleine (tracer dans le sens trigonométrique)t=2 si c’est un trou (tracer dans le sens horaire)


17

Polygone lié par des arcs

• SDO_ETYPE : t003• SDO_INTERPRETATION : 2t=1 si on fait une figure pleine (tracer dans le sens trigonométrique)t=2 si c’est un trou (tracer dans le sens horaire)


SDO_ELEM_INFORectangle

• SDO_ETYPE : t003• SDO_INTERPRETATION : 3t=1 si on fait une figure pleinet=2 si c’est un trou


SDO_ELEM_INFOCercle

• SDO_ETYPE : t003• SDO_INTERPRETATION : 4t=1 si on fait une figure pleinet=2 si c’est un trou


SDO_ELEM_INFOComposition de polylignes

• SDO_ETYPE : d004• SDO_INTERPRETATION : Nombre de polylignes


18

SDO_ELEM_INFOComposition de polygones

• SDO_ETYPE : d005• SDO_INTERPRETATION : Nombre de polygones


InterprétationEtype Interpretation Meaning

0 (toute valeur) Utilisé pour modéliser toutes géométries non supportées par Oracle Spatialtelles les courbes (autre que les arcs) et splines.

1 1 Point

n>1 Agrégat de n points

2 1 Ligne dont les sommets sont reliés par des lignes droites.

2 Ligne composée d'une séquence d'arcs définis par 3 points (début, sur et fin de l'arc). Un point servant de début et de fin d'un arc n'est pas répété.

1003 (polygone extérieur) ou 2003 (polygone intérieur)

1 Simple polygone dont les sommets sont reliés par des lignes droites. Le sommet de fin du polygone doit être le même que le sommet de début.

2 Polygone composé d'une séquence fermée d'arcs. Le point servant de début et de fin d'un arc n'est pas répété.

3 Rectangle défini par deux points dont l'un est localisé dans le coin inférieur gauche et l'autre dans le coin supérieur droit. Ce type peut être utilisé uniquement avec un système de coordonnées cartésien, sinon définir le rectangle avec 5 points et une interprétation égale à 1.

4 Cercle défini par 3 points localisés sur la circonférence.


InterprétationEtype Interpretation Meaning

4 n>1 Ligne complexe dont certains sommets sont reliés par des lignes droites et d'autres par des arcs. La valeur n du champ interprétation spécifie le nombre de sous-éléments contigus composant la ligne. Les n triplets suivants de la matrice SDO_ELEM_INFO décrivent chacun de ces sous-éléments. Les sous-éléments peuvent uniquement être de type SDO_ETYPE 2. Le dernier point d'un sous-élément est le premier du sous-élément suivant et ne doit pas être répéter .

1005 (polygone extérieur) ou 2005 (polygone intérieur)

n>1 Polygone complexe dont certains sommets sont reliés par des lignes droites et d'autres par des arcs. La valeur n du champ interprétation spécifie le nombre de sous-éléments contigus composant la ligne. Les ntriplets suivants de la matrice SDO_ELEM_INFO décrivent chacun de ces sous-éléments. Les sous-éléments peuvent uniquement être de type SDO_ETYPE 2. Le dernier point d'un sous-élément est le premier du sous-élément suivant et ne doit pas être répéter . Le point de début et de fin d'un polygone doit être le même.


Exemple de géométrie complexe

David FAU 72

INSERT INTO PARKSVALUES (’OBJ_1’,

MDSYS.SDO_GEOMETRY(2003, NULL,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1, 19,2003,3),MDSYS.SDO_ORDINATE_ARRAY(6,15, 10,10, 20,10, 25,15, 25,35, 19,40, 11,40, 6,25, 6,15, 12,15, 15,24)

));

72David FAU

19

Indexation et métadonnées

• Indexation �– Indiquer à ORACLE les données à indexer.– Remplir les table de métadonnées spatial

• USER_SDO_GEOM_METADATA• ALL_SDO_GEOM_METADATA• DBA_SDO_GEOM_METADATA


Indexation des données

MétadonnéesIndex



• Ces tables contiennent :– TABLE_NAME VARCHAR2(32),– COLUMN_NAME VARCHAR2(32),– DIMINFO MDSYS.SDO_DIM_ARRAY,– SRID NUMBER



INSERT INTO USER_SDO_GEOM_METADATAVALUES (

’nom_table’,’nom_colonne’,MDSYS.SDO_DIM_ARRAY(

MDSYS.SDO_DIM_ELEMENT(’X’, 0, 20, 0.005),MDSYS.SDO_DIM_ELEMENT(’Y’, 0, 20, 0.005)

),NULL

);


20

L’indexation des données

Indexation par QUAD TREE


Requête d’indexation

CREATE INDEX nom_spatial_idxON nom_table(nom_colonne)

INDEXTYPE IS MDSYS.SPATIAL_INDEXPARAMETERS(’SDO_LEVEL = 8’);


Estimation de SDO_LEVEL

SDO_LEVEL profondeur de l’arbre d’index• SDO_TUNE.ESTIMATE_TILING_LEVEL

– Nom table– Nom colonne

– Nombre maximum de zones


Requête spatiale

Modèle d’interrogationOpérateur


21

Avant toute requête

• Remplir les métadonnées• Créer les indexs


Jeu de données important


• Système à deux filtres

FiltreJeu de

données réduit

Filtre Résultat



• Premier filtre– Réduction du nombre de données à traiter– Utilisation de boite englobante

• Second filtre– Travail sur un jeu de données réduit


Utilisation de l’index

I

II

I

I

I

I I

II

I

B

I

BB

I

B

B

B

I

B

BBB

B

BB

BBB

I

B

B

B

B

I

B

I

IPas dans la figureDans la figure au second passageDans la figure au premier passageFigure de sélection


22

ExempleSELECT SDO_GEOM.SDO_INTERSECTION(

c_a.shape,m.diminfo, c_c.shape,m.diminfo

)

FROM cola_markets c_a, cola_markets c_c, user_sdo_geom_metadata m

WHERE m.table_name = ’COLA_MARKETS’ AND m.column_name = ’SHAPE’

AND c_a.name = ’cola_a’ AND c_c.name = ’cola_c’;



• SDO_GEOM. RELATE– Paramètres

• geom1 : premier objet• dim1: zone du premier objet• mask : Relation recherchée• geom2 : second objet• dim2 : zone du second objet



• Différentes relations– ANYINTERACT– CONTAINS– COVERS– DISJOINT

– EQUAL– INSIDE



• Différentes relations– OVERLAPBDYDISJOINT– OVERLAPBDYINTERSECT– TOUCH

• Combinaison– ‘INSIDE + TOUCH’


23

Aire d’un objet

• SDO_GEOM.SDO_AREA– geom– dim


Retourne le barycentre d’un polygone

• SDO_GEOM.SDO_CENTROID– geom– dim


Enveloppe convexe

• SDO_GEOM.SDO_CONVEXHULL– geom– dim


Différence entre deux polygones

• SDO_GEOM.SDO_DIFFERENCE– geom1– dim1– geom2– dim2


24

Distance entre objets

• SDO_GEOM.SDO_DISTANCE– geom1– dim1– geom2– dim2


Intersection

• SDO_GEOM.SDO_INTERSECTION– geom1– dim1– geom2– dim2


Validité de la géométrie

• SDO_GEOM.VALIDATE_GEOMETRY– geom– dim


Point sur une surface

• SDO_GEOM.SDO_POINTONSURFACE– geom– dim


25

Opérateurs

• SDO_GEOM.SDO_POLY_DIFFERENCE• SDO_GEOM.SDO_POLY_INTERSECTION• SDO_GEOM.SDO_POLY_UNION• SDO_GEOM.SDO_POLY_XOR

– geom1– dim1– geom2– dim2


OUTILS

• Utilitaire en JAVA– Conversion SHP � Oracle (shp2sdo)– Génération de XML depuis Oracle (java)

• SQL*Loader � Chargement de données de grand volume

• GeoRaptor : extension spatiale de SQL Developer


plan les bases de données spatiales

Documents