Modle Objet relationnel d'Oracle

1
Modèle Objet relationnel d'Oracle

• Résumé, traduction et explication de
• Oracle Spatial, User's Guide and Reference
• Release 9.2, mars 2002.
• Préparé par
• Suzie Larrivée, professionnelle de recherche
• Dr Yvan Bédard, professeur
• mars 2004

2
Que permet Oracle Spatial ?

• Intégrer complètement les données géométriques et
  les données descriptives.
• Faire des analyses spatiales à l'aide de requêtes
  SQL (sans interface graphique) et donner un
  résultat textuel ou tabulaire (i.e. non
  cartographique).
• Exemple Imprimer la liste de tous les
  propriétaires voisins du parc Victoria.
• Oracle prend donc un rôle traditionnellement
  réservé aux logiciels SIG.

3
Que permet Oracle Spatial ?

• Un simple Viewer permet de voir les cartes
  numériques stockées dans Oracle, de lancer
  graphiquement des requêtes et de voir les
  résultats graphiquement.
• Ceci remplace donc à peu de frais une partie
  importante des fonctions SIG et soumet la gestion
  des données géométriques aux mêmes conditions que
  la gestion des données descriptives.

4
Concepts de base

• Oracle Spatial comprend
• le schéma MDSYS (usager) qui impose le stockage
  de données pouvant être 2D, 3D ou 4D (mesure),
  une syntaxe et des types de géométrie
• un mécanisme d'indexation spatiale (2D
  seulement)
• un ensemble d'opérateurs et fonctions permettant
  d'analyser les données spatialement et
  d'effectuer des jointures spatiales (2D
  seulement)
• des utilités administratives.

5
Concepts de base

• Permet de gérer des géométries simples (point,
  ligne, arc et polygone) et des géométries
  complexes telles les polygones et lignes composés
  de lignes droites et d'arcs.
• Permet de gérer jusqu'à 4 dimensions soit x, y, z
  et mesure (référence linéaire).
• La structure de données géométriques d'Oracle
  n'est pas topologique.
• Il y a duplication des coordonnées lorsque deux
  objets partagent la même limite (exemple lots
  cadastraux).
• Les relations spatiales ne sont pas gérées
  explicitement dans la base de données mais sont
  déduites par traitement.

6
Types de géométrie

• Points et agrégats de points
• Ligne composée de lignes droites
• Polygone composé de lignes droites
• Ligne composée d'arcs
• Polygone composé d'arcs
• Polygone composé delignes droites et arcs
• Ligne composée delignes droites et arcs
• Cercle
• Rectangle

7
Le modèle conceptuelde données
8
Le modèle de données

• Classe d'objets spatiaux Ensemble d'objets de la
  réalité ayant les mêmes caractéristiques et
  pouvant être localisés.
• Ensemble des bâtiments.
• Objet spatial Occurrence d'une classe d'objets
  spatiaux.
• Pavillon Lacerte
• Géométrie géométrie simple ou complexe d'un
  objet.
• Polygone avec un trou.
• Primitives géométriques Géométrie de même type
  pouvant être point, ligne, arc, polygone
  intérieur, polygone extérieur, complexe linéaire,
  complexe polygonal intérieur, complexe polygonal
  extérieur ou autre.
• Prim 1 1,1003,3
• Prim 2 5, 2003,3
• Coordonnées
• 10,10, 20,20, 13,13, 17,17

(20,20)
(10,20)
(13,17)
(17,17)
(13,13)
(17,13)
(10,10)
(20,10)
N.B.On entend par complexe un agrégat de
primitives de type ligne droite et arc. Voir
SDO_ELEMENT_INFO pour détails
9
Modèle d'implantationde SDO_GEOMETRY
10
Implantation de la géométrieSDO_GEOMETRY
11
SDO_GTYPE
d) Correspond à la dimension de l'univers du
système dans lequel est localisé l'objet 2, 3 ou
4. l) Identifie la dimension où sera stockée la
mesure si l'on utilise la référence linéaire 3
ou 4. 1) Pour les polygones avec trou, entrez la
limite extérieure en premier suivie de la limite
intérieure 2) Les polygones d'une collection
peuvent être disjoints.
12
SDO_SRID

• Champ migré dans le type d'objets SDO_GEOMETRY
  pour faire le lien avec la table des systèmes de
  coordonnées. Ce champ identifie de façon unique
  un système de coordonnées. La valeur de ce champ
  peut être nulle.
• Toutes les primitives formant une géométrie
  doivent être du même système de coordonnées.

13
SDO_POINT

• Le champ SDO_POINT est de format SDO_POINT_TYPE.
  Il permet le stockage des données ponctuelles.
  Il est limité à 3 dimensions. Si l'on utilise la
  référence linéaire (mesure), on doit alors
  stocker les points dans les champs SDO_ELEM_INFO
  et SDO_ORDINATES. Ce type de données est défini
  comme suit
• CREATE TYPE sdo_point_type AS OBJECT ( X NUMBER,
  Y NUMBER, Z NUMBER)

• MDSYS.SDO_GEOMETRY(
• 2001,
• NULL,
• MDSYS.SDO_POINT_TYPE
• (12, 14, NULL),
• NULL, NULL))

14
SDO_ELEM_INFO

• Le champ SDO_ELEM_INFO est de format
  SDO_ELEM_INFO_ARRAY. Il nous renseigne sur
  comment interpréter les coordonnées stockées dans
  le champ SDO_ORDINATES. Celui-ci est défini comme
  suit
• CREATE TYPE SDO_ELEM_INFO_ARRAY AS VARRAY
  (1048576) of NUMBER
• On y stocke un ou plusieurs triplets comprenant
  le rang de la coordonnée de départ de la
  primitive (SDO_STARTING_OFFSET), le type de
  géométrie de la primitive (SDO_ETYPE) et
  l'interprétation de ce type (SDO_INTERPRETATION).

15
SDO_ELEM_INFOSDO_ETYPE et SDO_INTERPRETATION

• Ces types sont des géométries simples et
  généralement défini par un seul triplet sauf le
  cas du polygone avec trou.

16
SDO_ELEM_INFOSDO_ETYPE et SDO_INTERPRETATION

• Ces types sont des géométries complexes dont le
  premier triplet décrit le type de géométrie
  complexe et les triplets suivants décrivent la
  géométrie de chaque primitive simple. Si la
  ligne ou le polygone est composé de lignes
  droites et d'arc, ont doit alors utiliser le
  ETYPE 4 pour une ligne et 1005 pour un polygone
  extérieur et 2005 pour un polygone intérieur.

17
SDO_ORDINATES

• Le champ SDO_ORDINATES est de format
  SDO_ORDINATE_ARRAY. Il comprend la liste des
  coordonnées séparées par une virgule. Il permet
  de stocker des données de 2, 3 ou 4 dimensions
  (mesure). Il est défini comme suit
• CREATE TYPE sdo_ordinate_array AS VARRAY
  (1048576) of NUMBER
• SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT est un
  fonction qui permet de valider la géométrie et
  d'indiquer qu'elles sont les erreurs s'il y a.

18
Exemple
SDO_GEOMETRY ( SDO_GTYPE 2003 SDO_SRID
Null SDO_POINT Null SDO_ELEM_INFO (1, 1003,
1, 19, 2003, 1) SDO_ORDINATES (6, 25, 11, 40,
19, 40, 25, 35, 25, 15, 20, 10, 10, 10, 6, 15,
6, 25, 12, 15, 15, 15, 15, 24, 12, 24, 12, 15) )
Position 19
SDO_GEOMETRY SDO_GTYPE Dimension du système
et type de géométrie SDO_SRID Identifiant du
système de coordonnées SDO_POINT Lorsque
l'objet est un point, les coordonnées sont
stockées dans ce champ SDO_ELEM_INFO
SDO_STARTING_OFFSET Rang de la première
coordonnée x composant la primitive
géométrique. SDO_ETYPE Type de géométrie de
la primitive (1003 polygone intérieur et 2003
polygone extérieur) SDO_INTERPRETATION
Précise le type de géométrie (1003 combiné à 1
Polygone simple dont les sommets sont reliés
par une ligne droite). SDO_ORDINATES
coordonnées
19
ExemplePolygone simple

• MDSYS.SDO_GEOMETRY(
• 2003, -- polygone d'un système 2D
• NULL,
• NULL,
• MDSYS.SDO_ELEM_INFO_ARRAY
• (1,1003,1, 19,2003,1), -- polygone avec trou
• MDSYS.SDO_ORDINATE_ARRAY(2,4, 4,3,
• 10,3, 13,5, 13,9, 11,13, 5,13, 2,11, 2,4, 7,5,
• 7,10, 10,10, 10,5, 7,5))

Prendre note que les coordonnées de départs d'un
polygone sont répétées à la fin.
20
ExempleComplexe ligne

• MDSYS.SDO_GEOMETRY(
• 2002,
• NULL,
• NULL,
• MDSYS.SDO_ELEM_INFO_ARRAY
• (1,4,2, 1,2,1, 3,2,2), -- compound line string
• MDSYS.SDO_ORDINATE_ARRAY(10,10, 10,14, 6,10,
  14,10)))

21
ExempleComplexe polygone

• MDSYS.SDO_GEOMETRY(
• 2003, -- 2-dimensional polygon
• NULL,
• NULL,
• MDSYS.SDO_ELEM_INFO_ARRAY
• (1,1005,2, 1,2,1, 5,2,2), -- polygone complexe
• MDSYS.SDO_ORDINATE_ARRAY
• (6,10, 10,1, 14,10, 10,14, 6,10))

22
ExempleMultipolygone

• MDSYS.SDO_GEOMETRY(
• 2007,
• NULL,
• NULL,
• MDSYS.SDO_ELEM_INFO_ARRAY
• (1,1003,1, 11,1003,1, 23,1003,1),
• MDSYS.SDO_ORDINATE_ARRAY
• (2,4, 2,11, 5,10, 5,4, 2,4, 7,5, 7,10,
• 10,10, 10,5, 7,5, 10,3, 11,5, 11,13,
• 13,9, 13,5, 10,3))

23
Chargement des données géométriques
24
Chargement en lot avecSQL Loader

• Tout comme pour les données descriptives, on
  utilise SQL Loader pour charger les données
  géométriques en lot (batch).

25
Chargement en lot avecSQL Loader (polygone)

• LOAD DATAINFILE TRUNCATE CONTINUEIF NEXT(11)
  '' INTO TABLE POLY_4PT FIELDS TERMINATED BY
  ''TRAILING NULLCOLS (GID INTEGER
  EXTERNAL,GEOM COLUMN OBJECT(SDO_GTYPE INTEGER
  EXTERNAL,SDO_ELEM_INFO VARRAY TERMINATED BY
  '/'(elements FLOAT EXTERNAL),SDO_ORDINATES
  VARRAY TERMINATED BY '/'(ordinates FLOAT
  EXTERNAL)))

• Integer External signifie que les chiffres
  composant l'entier sont dans un format lisible
  par l'homme et non binaire
• Chaque identifiant de marché (mkt_id) est précédé
  d'un espace car la fonction CONTINUEIF NEXT (11)
  ' ' occasionne l'ignorance du premier
  caractère de chaque ligne.

• begindata 12003110031/-122.421537.7862-
  122.42237.7869-122.42137.789-122.4237.7866
  -122.421537.7862/ 22003110031/-122.4019
  37.8052-122.402737.8055-122.403137.806-122.4
  01237.8052-122.401937.8052/
  32003110031/-122.42637.803-122.424237.80
  53-122.4235537.8044-122.423537.8025-122.426
  37.803/

26
Chargement en lot avecSQL Loader (point)

• LOAD DATAINFILE TRUNCATECONTINUEIF NEXT(11)
  ''INTO TABLE POINTFIELDS TERMINATED BY
  ''TRAILING NULLCOLS (GID INTEGER
  EXTERNAL,GEOMETRY COLUMN OBJECT(SDO_GTYPE
  INTEGER EXTERNAL,SDO_POINT COLUMN OBJECT(X
  FLOAT EXTERNAL,Y FLOAT EXTERNAL)))

BEGINDATA 12001 -122.4215 37.7862 22001
-122.4019 37.8052 32001 -122.426
37.803 42001 -122.4171 37.8034 52001
-122.416151 37.8027228
27
Chargement avecInsert

• Insère un polygone avec un trou.
• INSERT INTO cola_markets VALUES(10,MDSYS.SDO_GEO
  METRY(2003, NULL,NULL,MDSYS.SDO_ELEM_INFO_ARRA
  Y(1,1003,1, 19,2003,1), MDSYS.SDO_ORDINATE_ARRAY
  (2,4, 4,3, 10,3, 13,5, 13,9, 11,13, 5,13, 2,11,
  2,4, 7,5, 7,10, 10,10, 10,5, 7,5)))

28
Chargement avecInsert

• Insertion d'enregistrements à l'aide d'une
  procédure en PL/SQL
• DECLAREgeom mdsys.sdo_geometry
  mdsys.sdo_geometry (2003, null,
  null, mdsys.sdo_elem_info_array
  (1,1003,3), mdsys.sdo_ordinate_array
  (-109,37,-102,40))BEGIN INSERT_GEOM(geom) CO
  MMITEND

29
Mise à jour des métadonnées
30
Table de métadonnées

• La table de métadonnées associée à la géométrie
  décrit la couverture spatiale pour chacune des
  classes. Celle-ci est délimitée par les limites
  supérieures et inférieures de chaque axe du
  système ainsi que la tolérance.
• Les utilisateurs n'ont pas accès directement à la
  table de métadonnées stockée dans le compte
  MDSYS. Ils ont accès à une vue,
  USER_SDO_GEOM_METADATA, pour charger les
  métadonnées pour chaque table spatiale.

31
Structure de la vueUSER_SDO_GEOM_METADATA

• Cette vue comprend les champs suivants
• TABLE_NAME VARCHAR2(32),
• COLUMN_NAME VARCHAR2(32),
• DIMINFO MDSYS.SDO_DIM_ARRAY,
• SRID NUMBER

32
Table de métadonnées

• Table name Nom de la table ayant un champ
  Geometry
• Column name Nom du champ stockant la géométrie.
• DimInfo Type d'objets permettant le stockage des
  limites supérieures et inférieures de chaque
  dimension nécessaire aux positionnement des
  objets de la classe.
• Create Type SDO_DIM_ARRAY as VARRAY(4) of
  SDO_DIM_ELEMENT
• Le SDO_DIM_ELEMENT est défini
• Create Type SDO_DIM_ELEMENT as OBJECT (
• SDO_DIMNAME VARCHAR2(64),
• SDO_LB NUMBER,
• SDO_UB NUMBER,
• SDO_TOLERANCE NUMBER)
• SRID Identifiant du système de coordonnées dans
  lequel la géométrie de l'ensemble des objets de
  la classe est stockée.

33
Insérer un enregistrement dans la vue
USER_SDO_GEOM_METADATA

• INSERT INTO USER_SDO_GEOM_METADATA VALUES
  ('batiment','geometrie',MDSYS.SDO_DIM_ARRAY(
  MDSYS.SDO_DIM_ELEMENT('X', 0, 2000,
  0.005),MDSYS.SDO_DIM_ELEMENT('Y', 0, 2000,
  0.005)),NULL)

34
Création de l'index spatial
35
Type d'index spatiaux

• Une fois les données chargées dans le base de
  données, un index spatial doit être créé afin
  d'accélérer l'accès aux données. L'index permet
• de trouver les objets ayant une relation spatiale
  avec un point ou une aire de recherche.
• de trouver la paire d'objets interagissant
  spatialement (join spatial)
• Les index peuvent être
• R-Tree
• Quadtree

36
Comparaison duR-tree et Quadtree
37
R-tree

• Le R-tree approxime la géométrie de chaque
  géométrie par un rectangle englobant minimum (REM
  ou MBR (Minimum Bounding Rectangle)).
• Pour une classe d'objets spatiaux donnée, le
  R-tree consiste en un index hiérarchique sur les
  REM.

38
Fonctions permettant de vérifier la qualité d'un
index R-tree

• SDO_TUNE.ANALYZE_RTREE Suggère ou non de
  reconstruire l'index en comparant le pointage
  actuel de qualité de l'index avec celui obtenu
  lors de la création de l'index.
• SDO_TUNE.RTREE_QUALITY Retourne le pointage
  actuel de qualité de l'index.
• SDO_TUNE.QUALITY_DEGRADATION retourne un facteur
  de dégradation de la qualité. Par exemple si
  l'exécution des requêtes est 2 fois plus lente
  que lorsque l'index était nouvellement créé, la
  valeur retournée serait 2.
• Pour reconstruire un index, utilisez la commande
  ALTER INDEX REBUILD.

39
Quadtree

• Le Quadtree consiste à diviser le territoire
  couvert par les données en tuiles régulières
  (tesselation).
• Lorsqu'une tuile devient surchargée en données,
  elle est redivisée en 4 sous-tuiles.

40
Quadtree

• Un Quadtree peut être de dimension fixe ou
  variable.
• On dit qu'il est de dimension fixe lorsque les
  tuiles sont de dimension égale et qu'il est
  contrôlé par la résolution des tuiles
  (SDO_LEVEL), i.e. que la tesselation se termina
  lorsque l'espace aura été décomposé un nombre de
  fois spécifique.
• Il est de dimension variable lorsqu'il est
  contrôlé par un nombre maximum de tuiles par
  géométrie (SDO_NUMTILES). La tesselation se
  terminera lorsque n tuiles seront utilisées pour
  couvrir une géométrie donnée.

41
Quadtree

• Oracle spatial supporte présentement deux types
  d'indexation de type Quadtree, soit
• Fixed indexing Tuiles de dimension fixe où il y
  a une valeur pour le champ SDO_LEVEL et une
  valeur nulle pour le champ SDO_NUMTILES.
• Hybrid indexing Les champs SDO_LEVEL et
  SDO_NUMTILES ont une valeur non nulle. Il
  comprend une série de tuiles de dimension fixe
  qui couvre entièrement le territoire et une série
  de tuiles de dimension variable qui couvre aussi
  entièrement le territoire. Pour la plupart des
  applications cette méthode d'indexation n'est pas
  conseillée.

42
QuadtreeFixed indexing

• Deux géométries sont susceptibles d'être non
  disjointes si elles partagent une même tuile et
  donc passent le premier filtre spatial.
• SELECT DISTINCT ltselect_list for geometry
  identifiersgt
• FROM table1_sdoindex A, table2_sdoindex B
• WHERE A.sdo_code B.sdo_code
• L'exactitude et l'efficacité de cette méthode
  d'indexation dépend du niveau de tuilage et de la
  variation en dimension de la géométrie des objets
  d'une classe.
• Par exemple, si la dimension des tuiles est
  petites, il faudra alors plusieurs tuiles pour
  couvrir un objet de grande dimension mais la
  sélection de petits objets sera précise.
• À l'inverse, si la dimension des tuiles est
  grande, il faudra alors moins de tuiles pour
  couvrir les objets de grandes dimensions mais la
  sélection des petits objets sera imprécise.

43
QuadtreeFixed indexing

• Avec de petites tuiles, plusieurs sont
  nécessaires pour couvrir la géométrie mais une
  fenêtre de requête identifiera facilement les
  géométries A et B comme pouvant être non
  disjointes. et rejettera la géométrie C.

• Avec de grandes tuiles, moins de tuiles sont
  nécessaires pour couvrir un géométrie mais une
  fenêtre de requête identifiera plus de géométries
  comme pouvant être non disjointes. Dans ce
  cas-ci les trois géométries A, B et C sont
  susceptibles d'être non disjointes puisqu'elles
  partagent les mêmes tuiles.

44
QuadtreeFixed indexing
Table SDOINDEX
45
QuadtreeHybrid indexing

• Un Quadtree hybride utilise une combinaison de
  tuiles de dimension fixe et de tuiles de
  dimension variable. Pour chaque géométrie, il y
  aura une série de tuiles de dimension fixe qui
  couvre entièrement la géométrie et une série de
  tuiles de dimension variable qui couvrent
  également entièrement la géométrie.
• L'utilisation n'est pas recommandée pour la
  plupart des applications. Ils peuvent cependant
  être utilisés entre autre lorsque la classe
  d'objets comprend plusieurs géométries de petites
  tailles couvrant un petit secteur et plusieurs
  polygones de grande taille couvrant un grand
  secteur.

46
QuadtreeHybrid indexing
SDO_LEVEL 1 SDO_NUMTILES 4
47
Création d'un index

• Création d'un index R-tree
• CREATE INDEX roads_idx ON roads(shape)INDEXTYPE
  IS MDSYS.SPATIAL_INDEX
• Création d'un index Quadtree fixed indexing
• CREATE INDEX roads_fixed ON roads(shape)INDEXTYP
  E IS MDSYS.SPATIAL_INDEX PARAMETERS
  ('SDO_LEVEL8')
• Création d'un index Quadtree hybrid indexing
• CREATE INDEX roads_hybrid ON roads(shape)INDEXTY
  PE IS MDSYS.SPATIAL_INDEX PARAMETERS
  ('SDO_LEVEL6 SDO_NUMTILES12')

48
Vues permettant de visualiserles informations
des index

• USER_SDO_INDEX_INFO contient les informations de
  base qui ont été fournies lors de la création de
  l'index (nom de l'index, table contenant le
  champs sur laquel est construit l'index, nom du
  champ, type d'index, nom de la table stockant les
  valeurs de l'index, status (deferred ou valid))
• USER_SDO_INDEX_METADATA contient des
  informations détaillées sur l'index telles le nom
  de l'usager, le type, le nom, la table, le nom du
  champ, index primaire ou secondaire, partionné ou
  non, nombre de dimension des objets, nombre de
  noeuds du R-tree, nombre d'enfants maximum par
  noeud, sdo_level, sdo_numtiles, etc.

49
Structure des tables stockantles valeurs des
index spatiaux

• Pour les index de type R-tree, la table stockant
  les valeurs de l'index comprend les champs
  suivants
• Node_id number identifiant unique du noeud de
  l'arbre.
• Node_level number Niveau du noeud dans
  l'arbre.
• Info blob information sur le noeud telle nbr
  d'enfants du noeud, adresses des noeuds enfants.
• Pour les index de type Quadtree, la table
  stockant les valeurs de l'index comprend les
  champs suivants
• Sdo_code raw Valeur de l'index pour l'objet
  traité.
• Sdo_rowid rowid Numéro de l'enregistrement (et
  non la clé primaire) de l'objet spatial traité.
• Sdo_status varchar2 A la valeur I si la tuile
  est à l'intérieure de la géométrie ou a la valeur
  B si la tuile est sur la limite de la géométrie.
• Sdo_groupcode raw Pour index hybride
  seulement valeur de l'index au niveau Sdo_Level.

50
Analyse spatiale
51
Modèle de requête

• Les requêtes et jointures spatiales sont résolues
  par un modèle de requêtes à 2 tiers, composé des
  filtres primaire et secondaire

• Le filtre primaire compare des géométries
  approximatives (REM ou tuiles de l'index) afin de
  réduire le temps de traitement et retourner
  rapidement un ensemble d'objets susceptibles de
  satisfaire la requête.

• Le filtre secondaire compare les géométries
  exactes afin de valider si elles satisfont ou non
  la requête. Ce traitement est complexe et
  coûteux en CPU mais il est appliqué uniquement
  sur un sous ensemble des objets de la classe.

52
Filtre primaireSDO_FILTER

• L'opérateur SDO_FILTER fait usage uniquement du
  filtre primaire.
• SDO_FILTER (geom1 MDSYS.SDO_GEOMETRY, geom2
  MDSYS.SDO_GEOMETRY, params VARCHAR2)
• où geom1 et geom2 sont les géométries des deux
  tables impliquées dans la relation. Un index
  spatial doit exister.
• params sont
• querytype qui peut être de Type
• Windows s'il n'y a qu'une seule géométrie dans la
  table de geom2. L'ensemble des Geom1 seront
  comparées à l'unique Geom2.
• Join Si les tables ont toutes deux plusieurs
  enregistrement.
• idxtab1 et idxtab2 On spécifie le nom de l'index
  dans le cas où il existe plusieurs index pour la
  geom1 ou la géom2.

53
Filtre primaireSDO_FILTER

• Sélectionne le GID des enregistrements de la
  table POLYGONES dont la géométrie est susceptible
  d'être non disjointe de la géométrie de
  l'enregistrement ayant le GID1 de la table
  QUERY_POLYS.
• SELECT A.gidFROM Polygons A, query_polys
  BWHERE B.gid 1AND SDO_FILTER(A.Geometry,
  B.Geometry,'querytype WINDOW ') ' TRUE '

54
Filtre primaireSDO_FILTER

• Sélectionne le GID des enregistrements de la
  table POLYGONES dont la géométrie est susceptible
  d'être non disjointe de la géométrie spécifiée
  dans l'objet mdsys.sdo_geometry.
• Select A.GidFROM Polygons AWHERE
  SDO_FILTER(A.Geometry,mdsys.sdo_geometry(2003,NUL
  L,NULL,mdsys.sdo_elem_info_array(1,1003,3),mdsys
  .sdo_ordinate_array(x1,y1,x2,y2)),
  'querytypeWINDOW') 'TRUE'

55
Filtre primaireSDO_FILTER

• Sélectionne le GID des enregistrements de la
  table POLYGONES dont la géométrie est susceptible
  d'être non disjointe de l'une des géométries de
  la table QUERY_POLYS.
• SELECT A.gidFROM Polygons A, query_polys
  BWHERE SDO_FILTER(A.Geometry,B.Geometry,
  'querytype JOIN') 'TRUE'

56
Analyses spatiales

• Les analyses spatiales requièrent l'usage du
  filtre secondaire afin de sélectionner avec
  exactitude les objets qui satisfont la requête.
• Oracle comprend les opérateurs spatiaux suivants
• SDO_WITHIN_DISTANCE opérateur métrique qui
  détermine si deux objets sont situés à
  lintérieur dune distance spécifique l'un de
  l'autre.
• SDO_NN opérateur métrique qui permet
  d'identifier le plus proche voisin d'un objet.
• SDO_RELATE opérateur topologique.

57
SDO_WITHIN_DISTANCE

• SDO_WITHIN_DISTANCE(geometry1, aGeom, params)
• geometry1 Géométries à évaluer
• AGeom Géométrie qui servira à évaluer si des
  geometry1 se trouvent à une certaine distance de
  celle-ci.
• Params
• distance permet de spécifier la distance
• idxtabl1 spécifie le nom de l'index s'il en
  existe plusieurs.
• querytype indique si uniquement le filtre
  primaire sera lancé ou bien les deux. Par
  défaut, les deux filtres sont exécutés. Pour
  exécuter que le filtre primaire, mettre
  'querytype FILTER'.
• unit permet de spécifier l'unité de mesure. Par
  défaut, c'est le mètre.

58
SDO_WITHIN_DISTANCE

• Sélectionne les GID des polygones situés à
  l'intérieur d'une distance de 10 unités du
  rectangle défini.
• SELECT A.GIDFROM POLYGONS AWHERESDO_WITHIN_DIST
  ANCE(A.Geometry,mdsys.sdo_geometry(2003,NULL,NULL
  ,mdsys.sdo_elem_info_array(1,1003,3),mdsys.sdo_o
  rdinate_array(x1,y1,x2,y2)),'distance 10')
  'TRUE'

59
SDO_NN

• SDO_NN(geometry1, geometry2, param , number)
• où params
• sdo_batch_size spécifie le nombre
  d'enregistrements à considérer comme pouvant
  satisfaire la requête pour chaque geometry2
  (requiert un R-tree)
• sdo_num_res spécifie le nombre de résultat à
  retourner par géométrie. La valeur par défaut
  est 1. Si une valeur pour sdo_batch_size est
  donnée, ce paramètre est ignoré.
• unit spécifie l'unité de mesure.
• Cet opérateur retourne le nombre de sdo_num_res
  de la géométrie1 le plus près de la géométrie2.

60
SDO_NN

• On aimerait connaître les deux restaurants
  italiens les plus près de l'hôtel (la géométrie
  est remplacée par la variable my_hotel). Le
  sdo_batch_size est spécifié en fonction du nombre
  de restaurants qu'Oracle devra considérer afin de
  s'assurer qu'au moins deux d'entre eux seront de
  cuisine italienne.
• SELECT r.name FROM restaurants r WHERE
  SDO_NN(r.geometry, my_hotel, 'sdo_batch_size10
  ') 'TRUE' AND r.cuisine 'Italian' AND ROWNUM
  lt2

61
SDO_RELATE

• La matrice à 9 intersections est utilisée pour
  catégoriser les relations spatiales entre les
  points, lignes et polygones.
• Les catégories retenues sont
• disjoint
• touch
• overlapbydisjoint
• overlapbyintersect
• equal

• contains
• covers
• inside
• coveredby
• on
• anyinteract

62
SDO_RELATE
63
SDO_RELATE

• DISJOINT Les limites et les intérieurs ne
  s'intersectent pas.
• TOUCH Les limites d'intersectent mais pas les
  intérieurs.
• OVERLAPBDYDISJOINT L'intérieur d'un objet
  intersecte la limite et l'intérieur d'un autre
  objet.
• OVERLAPBDYINTERSECT Les limites et les
  intérieurs des objets s'intersectent.
• EQUAL Les objets ont le même intérieur et la
  même limite.
• CONTAINS L'intérieur et la limite d'un objet
  sont inclus entièrement dans l'intérieur d'un
  autre objet.
• COVERS L'intérieur d'un objet est complètement
  inclus dans l'intérieur d'un autre objet et leurs
  limites s'intersectent
• INSIDE L'opposé de CONTAINS. A INSIDE B implique
  que B CONTAINS A.
• COVEREDBY L'opposé de COVERS. A COVEREDBY B
  implique que B COVERS A.
• ON L'intérieur et la limite d'un objet est sur
  la limite d'un autre objet.
• ANYINTERACT Les objets sont non disjoints.

64
SDO_RELATE

• SDO_RELATE(geometry1, geometry2, params)
• params précise le comportement de l'opérateur.
• mask Spécifie la relation topologique a évalué
  (touch, covers, contains, ..., anyinteract)
• querytype Window (si la géométrie2 a un seul
  objet) ou Join (si géométrie2 comprend plusieurs
  objets).
• idxtab1 et idxtbl2 spécifie le nom de l'index
  s'il en existe plusieurs pour les table 1 et 2.

65
SDO_RELATE

• Sélectionne les GID des objets de la table A
  ayant une relation spatiale avec un ou plusieurs
  objets de la table B.
• SELECT A.gidFROM Polygons A, query_polys BWHERE
  SDO_RELATE(A.Geometry,B.Geometry,'maskANYINTERAC
  TquerytypeJOIN') 'TRUE'

66
SDO_RELATE

• Plusieurs masques peuvent être combinés dans une
  requête.
• SELECT a.gidFROM polygons A, query_polys BWHERE
  B.gid 1AND SDO_RELATE(A.Geometry,
  B.Geometry,'maskinsidecoveredby
  querytypeWINDOW') 'TRUE'
• Cette requête donnera un meilleur résultat si
  elle est effectuée avec un UNION
• SELECT a.gidFROM polygons a, query_polys BWHERE
  B.gid 1AND SDO_RELATE(A.Geometry,
  B.Geometry,'maskinside querytypeWINDOW')
  'TRUE'UNION ALLSELECT a.gidFROM polygons a,
  query_polys BWHERE B.gid 1AND
  SDO_RELATE(A.Geometry, B.Geometry,'maskcoveredby
  querytypeWINDOW') 'TRUE'

67
Autres fonctions et méthodes relatives à la
géométrie
68
Autres fonctions et méthodes relatives à la
géométrie

• Méthodes de l'objet SDO_GEOMETRY
• Fonctions géométriques
• Fonctions de transformation de système de
  coordonnées
• Fonctions d'agrégation spatiale

69
Méthode de l'objet SDO_GEOMETRY

• Get_dim()
• Retourne la dimension de l'univers de l'objet
• Get_gtype()
• Retourne la dimension de l'objet
• Get_LRS_Dim
• Retourne la valeur de mesure de la géométrie de
  l'objet

70
Méthode de l'objet SDO_GEOMETRY

• Get_dim()
• SELECT c.mkt_id, c.shape.GET_DIMS()FROM
  cola_markets c WHERE c.name 'cola_d'
• Get_gtype()
• SELECT FROM cola_markets c where
  c.shape.GET_GTYPE()3
• SELECT distinct c.shape.GET_GTYPE()FROM
  cola_markets c
• Get_LRS_Dim
• SELECT a.route_id, a.route_geometry.GET_LRS_DIM()
  FROM lrs_routes a WHERE a.route_id 1

71
Fonctions géométriques

• Elles peuvent être regroupées en quatre
  catégories
• Relation (vrai/faux) entre deux objets
• Relate
• Within_distance
• Validation
• Validate_Geometry
• Validate_layer

72
Fonctions géométriques

• On peut les regrouper en 4 catégories

• Relation (vrai/faux) entre deux objets
• Relate
• Within_distance
• Validation
• Validate_Geometry
• Validate_layer
• Opération sur 2 objets
• Sdo_distance
• Sdo_difference
• Sdo_intersection
• Sdo_union
• Sdo_xor

• Opération sur 1 seul objet
• Sdo_arc_densify
• Sdo_area
• Sdo_buffer
• Sdo_centroid
• Sdo_convexhull
• Sdo_length
• Sdo_Mbr
• Sdo_Pointonsurface

73
Fonctions géométriques
74
Fonctions géométriques
75
Fonctions de transformation de système de
coordonnées

• SDO_CS.TRANSFORM
• Transforme la représentation d'une géométrie d'un
  système de coordonnées vers un autre.
• SDO_CS.TRANSFORM_LAYER
• Transforme la représentation géométrique de
  l'ensemble des objets de la table d'un système de
  coordonnées vers un autre.

76
Fonctions d'agrégation spatiale

• SDO_AGGR_CENTROID
• Retourne le centroïde (centre de gravité) des
  géométries spécifiées.
• SDO_AGGR_CONVEXHULL
• Retourne une géométrie polygonale approximative
  incluant l'ensemble des géométries spécifiées.
• SDO_AGGR_LRS_CONCAT
• Agrège les segments pour former un agrégat .
• SDO_AGGR_MBR
• Retourne le REM des géométries spécifiées.
• SDO_AGGR_MBR SDO_AGGR_UNION
• Retourne la géométrie résultante de l'union des
  géométries spécifiées.

77
Mise à jour duSDO_SRID ou SDO_Gtype

• Si lon laisse le SDO_SRID à Null, on pourra le
  mettre à jour par la suite.
• update gare a set a.geometry.sdo_srid
  8307(Longitude / Latitude (WGS 84))
• Si lon veut modifier le Gtype des
  enregistrements dune table, on procède comme
  suit
• update gare a set a.geometry.sdo_gtype 2001

78
Résumé Exemple dumarché de boissons gazeuses
79
Mise en contexte

• Cette figure illustre l'endroit où pour chaque
  type de boissons gazeuses, il y a eu le plus de
  ventes.

80
Étapes à réaliser

• Création de la table
• Chargement des données
• avec INSERT ou
• avec SQL Loader
• Mise à jour des métadonnées
• Création de l'index spatial
• R-tree
• Quadtree
• Interrogation de la base de données spatiale

81
Création de la table

• CREATE TABLE cola_markets (
• mkt_id NUMBER PRIMARY KEY,
• name VARCHAR2(32),
• shape MDSYS.SDO_GEOMETRY)

82
Chargement des donnéesavec Insert

• Aire d'intérêt du cola a correspondant à un
  rectangle
• INSERT INTO cola_markets VALUES(
• 1,'cola_a',
• MDSYS.SDO_GEOMETRY(
• 2003, NULL, NULL,
• MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),
  MDSYS.SDO_ORDINATE_ARRAY(1,1, 5,7)))
• Aire d'intérêt du cola b correspondant à un
  polygone simple
• INSERT INTO cola_markets VALUES(
• 2,'cola_b',
• MDSYS.SDO_GEOMETRY(
• 2003, NULL, NULL,
• MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),
  MDSYS.SDO_ORDINATE_ARRAY(5,1, 8,1, 8,6, 5,7,
  5,1)))

83
Chargement des donnéesavec Insert

• Aire d'intérêt du cola c correspondant à un
  polygone simple
• INSERT INTO cola_markets VALUES(
• 3,'cola_c',
• MDSYS.SDO_GEOMETRY(
• 2003, NULL, NULL,
• MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),
  MDSYS.SDO_ORDINATE_ARRAY(3,3, 6,3, 6,5, 4,5,
  3,3)))
• Aire d'intérêt du cola d correspondant à un
  cercle de rayon 2
• INSERT INTO cola_markets VALUES(
• 4,'cola_d',
• MDSYS.SDO_GEOMETRY(
• 2003, NULL, NULL,
• MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,4),
  MDSYS.SDO_ORDINATE_ARRAY(8,7, 10,9, 8,11)))

84
Chargement des donnéesavec SQL Loader

• Load
• INFILE
• TRUNCATE
• CONTINUEIF NEXT(11) ''
• INTO TABLE COLA_MARKETS
• FIELDS TERMINATED BY ''
• TRAILING NULLCOLS (
• mkt_id INTEGER EXTERNAL,
• name CHAR,
• shape COLUMN OBJECT
• (SDO_GTYPE INTEGER EXTERNAL,
• SDO_ELEM_INFO VARRAY TERMINATED BY '/'
• (elements FLOAT EXTERNAL),
• SDO_ORDINATES VARRAY TERMINATED BY '/'
• (ordinates FLOAT EXTERNAL)))

• begindata
• 1cola_a
• 2003110033/
• 1157/
• 2cola_b
• 2003110031/
• 5181865751/
• 3cola_c
• 2003110031/
• 3363654533/
• 4cola_d
• 2003110034/
• 87109811/

• Integer External signifie que les chiffres
  composant l'entier sont dans un format lisible
  par l'homme et non binaire
• Chaque identifiant de marché (mkt_id) est précédé
  d'un espace car la fonction CONTINUEIF NEXT (11)
  ' ' occasionne l'ignorance du premier
  caractère de chaque ligne.

85
Mise à jour des métadonnées

• INSERT INTO USER_SDO_GEOM_METADATA
• VALUES (
• 'cola_markets',
• 'shape',
• MDSYS.SDO_DIM_ARRAY( -- 20X20 grid
• MDSYS.SDO_DIM_ELEMENT('X', 0, 20, 0.005),
• MDSYS.SDO_DIM_ELEMENT('Y', 0, 20, 0.005)),
• NULL)
• Étape obligatoire avant la création de l'index
  spatial.

86
Création de l'index spatial

• Création d'un index R-tree
• CREATE INDEX cola_spatial_idx
• ON cola_markets(shape)
• INDEXTYPE IS MDSYS.SPATIAL_INDEX
• Création d'un index Quadtree
• CREATE INDEX cola_spatial_idx
• ON cola_markets(shape)
• INDEXTYPE IS MDSYS.SPATIAL_INDEX
• PARAMETERS('SDO_LEVEL 8')

87
Interrogationde la base de données spatiale

• Retourne l'intersection entre les géométries des
  marchés a et c
• SELECT SDO_GEOM.SDO_INTERSECTION (c_a.shape,
  c_c.shape, 0.005)FROM cola_markets c_a,
  cola_markets c_cWHERE c_a.name 'cola_a'
  ANDc_c.name 'cola_c'
• Réponse
• SDO_GEOMETRY
• (2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1003,
  1), SDO_ORDINATE_ARRAY(4, 5, 3, 3, 5, 3, 5, 5, 4,
  5))

88
Interrogationde la base de données spatiale

• Vérifie s'il existe une relation spatiale entre
  les géométries des marchés b et d
• SELECT SDO_GEOM.RELATE (c_b.shape, 'anyinteract',
  c_d.shape, 0.005)FROM cola_markets c_b,
  cola_markets c_dWHERE c_b.name 'cola_b' AND
  c_d.name 'cola_d'
• Réponse
• False

89
Interrogationde la base de données spatiale

• Calcule la superficie de tous les marchés
• SELECT name, SDO_GEOM.SDO_AREA(shape, 0.005) FROM
  cola_markets
• Réponse
• NAME SDO_GEOM.SDO_AREA(SHAPE,0.005)---
  --------------- ------------------------------col
  a_a
  24cola_b
  16,5cola_c
  5cola_d
  12,5663706

90
Interrogationde la base de données spatiale

• Calcule la distance entre la géométrie des
  marchés b et d
• SELECT SDO_GEOM.SDO_DISTANCE (c_b.shape,
  c_d.shape, 0.005)FROM cola_markets c_b,
  cola_markets c_dWHERE c_b.name 'cola_b' AND
  c_d.name 'cola_d'
• Réponse
• SDO_GEOM.SDO_DISTANCE(C_B.SHAPE,C_D.SHAPE,0.005)-
  -----------------------------------------------
  ,846049894

91
Interrogationde la base de données spatiale

• Retourne les deux objets les plus près du point
  spécifié
• SELECT / INDEX(cola_markets cola_spatial_idx)
  /c.mkt_id, c.name FROM cola_markets c
  WHERESDO_NN(c.shape,mdsys.sdo_geometry(2001,
  NULL,mdsys.sdo_point_type(10,7,NULL),
  NULL,NULL), 'sdo_num_res2') 'TRUE'
• Réponse
• MKT_ID NAME------------ ---------------
  2 cola_b 4 cola_d

92
Interrogationde la base de données spatiale

• Retourne les deux objets les plus près du point
  spécifié ainsi que la distance entre les deux.
• SELECT / INDEX(cola_markets cola_spatial_idx)
  / c.mkt_id, c.name, mdsys.SDO_NN_DISTANCE(1)
  distFROM cola_markets c WHERE SDO_NN(c.shape,
  mdsys.sdo_geometry(2001, NULL, mdsys.sdo_point_typ
  e(10,7,NULL), NULL, NULL), 'sdo_num_res2', 1)
  'TRUE' ORDER BY dist
• Réponse
• MKT_ID NAME DIST
• ------------ -----------------------------
  ----------
• 4 cola_d
  ,828427125
• 2 cola_b
  2,23606798