Title: <Insert Picture Here>
1ltInsert Picture Heregt
5 Recherches et Analyses Spatiales
2Points couverts
- Modèle d'exécution des requêtes spatiales
- Recherches basées sur les relations spatiales
- Recherches basées sur la distance
- Recherches basées sur la proximité
- Jointures spatiales
- Fonctions spatiales
- Agrégations spatiales
3Modèle d'exécution des requêtes
4Modèle d'exécution optimisé
5Écriture des requêtes spatiales
- Contiennent un prédicat spatial (clause WHERE) -
par exemple - Trouver les parcelles qui longent une rivière
- Trouver les clients à moins de 5 km d'un entrepôt
- Trouver l'agence la plus proche d'un client
- Exprimés au moyen d'opérateurs SQL spécifiques
- SDO_RELATE, SDO_INSIDE, SDO_TOUCH
- SDO_WITHIN_DISTANCE
- SDO_NN
- L'index spatial DOIT exister sinon
ORA-13226 interface not supported without a
spatial index ORA-06512 at "MDSYS.MD", line
1723 ORA-06512 at "MDSYS.MDERR", line
8 ORA-06512 at "MDSYS.SDO_3GL", line 387
6Prédicats topologiques
- On sélectionne les objets selon leur relation
topologique avec un autre objet - SDO_INSIDE
- SDO_CONTAINS
- SDO_COVERS
- SDO_COVEREDBY
- SDO_OVERLAPS
- SDO_TOUCH
- SDO_EQUAL
- SDO_ANYINTERACT
WHERE SDO_INSIDE ( ltgeometry-1gt, ltgeometry-2gt )
'TRUE
7Opérateur topologique générique
- Opérateur générique SDO_RELATE avec un masque
spécifique - Le masque peut étre 'INSIDE', 'CONTAINS',
'TOUCH', etc. - Ou une combinaison 'INSIDECOVEREDBY'
WHERE SDO_RELATE ( ltgeometry-1gt,
ltgeometry-2gt, 'MASKxxxx' ) 'TRUE
8Relations topologiques
B
A
A
A
B
B
A Contains B B Inside A
A Covers B B Coveredby A
A Touch B
A
A
A
B
B
B
OverlapBdyIntersect
OverlapBdyDisjoint
A Equal B
A
B
Disjoint
9Exemples
- Quels parcs sont entièrement contenus dans l'état
du Wyoming ? - Équivalent de
SELECT p.name FROM us_parks p, us_states s
WHERE s.state 'Wyoming' AND SDO_INSIDE
(p.geom, s.geom) 'TRUE'
AND SDO_RELATE(p.geom,s.geom,'MASKINSIDE')
'TRUE'
10Exemples
- Quels états contiennent tout ou partie du parc de
Yellowstone ?
SELECT s.state FROM us_states s, us_parks p
WHERE SDO_ANYINTERACT (s.geom, p.geom) 'TRUE'
AND p.name 'Yellowstone NP'
11Exemples
- Dans quels territoires concurrents se trouve mon
client ?
SELECT s.id, s.name FROM customers c,
competitors_sales_regions s WHERE c.id 5514
AND SDO_CONTAINS (s.geom, c.location) 'TRUE'
12Exemples
- Trouvez tous les comtés autour du comté Passaic
(NJ)
SELECT c1.county, c1.state_abrv FROM
us_counties c1, us_counties c2 WHERE
c2.state 'New Jersey' AND c2.county
'Passaic' AND SDO_TOUCH (c1.geom, c2.geom)
'TRUE'
13Requête avec une fenêtre "constante"
- Trouvez tous les clients de type "Platinum" dans
une zone rectangulaire
SELECT name, category FROM customers WHERE
SDO_INSIDE ( location,
sdo_geometry (2003, 8307, null,
sdo_elem_info_array (1,1003,3),
sdo_ordinate_array ( -122.413,
37.785,-122.403, 37.792)) )'TRUE' AND
customer_grade 'PLATINUM'
14Requête avec une fenêtre "constante"
- Dans quels territoires de ventes concurrents se
trouve un point géographique ?
SELECT id, name FROM competitors_sales_regions
WHERE SDO_CONTAINS ( geom,
SDO_GEOMETRY(2001, 8307,
SDO_POINT_TYPE(-122.41762, 37.7675089, NULL),
NULL, NULL) ) 'TRUE'
15Recherches basées sur la distance
- On sélectionne les objets selon la distance qui
les sépare d'un autre objet - Opérateur SDO_WITHIN_DISTANCE
- La distance peut être exprimée dans n'importe
quelle unité de mesure - Si aucune unité n'est spécifiée, la distance est
exprimée dans l'unité du système de coordonnées
(si projeté) - Pour des données en Longitude/Latitude, ce sont
des mètres.
SDO_WITHIN_DISTANCE( ltgeometry-1gt,
ltgeometry-2gt, 'DISTANCEdistance UNITunit'
) 'TRUE
16Exemple de recherche sur distance
- Quelles agences sont à moins de 1km de ce client ?
SELECT b.id, b.phone_number FROM customers c,
branches b WHERE c.id 8314 AND
SDO_WITHIN_DISTANCE( b.location,
c.location, 'distance1 unitkm')
'TRUE'
17Exemple de recherche sur distance
- Combien de clients de chaque catégorie y-a-t'il
dans un rayon d'¼ de mile de mon agence numéro 77
SELECT customer_grade, COUNT() FROM branches
b, customers c WHERE b.id77 AND
SDO_WITHIN_DISTANCE ( c.location,
b.location, 'DISTANCE0.25
UNITMILE')'TRUE' GROUP BY customer_grade
18Recherches basées sur la proximité
- On sélectionne les N objets les plus proches d'un
autre objet - Opérateur SDO_NN avec le nombre d'objets à
retourner - On peut aussi utiliser ROWNUM pour limiter les
résultats - A utiliser en cas de filtrage additionnel
- Utiliser SDO_NN_DISTANCE() pour classer les
réponses par distance
WHERE SDO_NN ( ltgeometry-1gt,
ltgeometry-2gt, 'SDO_NUM_RESn') 'TRUE
WHERE SDO_NN ( ltgeometry-1gt,
ltgeometry-2gt) 'TRUE AND ROWNUM ltn
19Une recherche de proximité
- Quelle est l'agence la plus proche de ce client ?
SELECT b.id, b.phone_number FROM customers c,
branches b WHERE c.id 8314 AND
SDO_NN( b.location, c.location,
'sdo_num_res1') 'TRUE'
20Exemple simple
- Quels sont mes cinq clients les plus proches de
ce concurrent ? - Ceci ne fonctionne que si aucun autre critère de
sélection n'est présent !
SELECT c.id, c.name, c.customer_grade FROM
competitors co, customers c WHERE co.id1 AND
SDO_NN ( c.location, co.location,
'SDO_NUM_RES5')'TRUE'
809 LINCOLN SUITES
GOLD 1044 MUSEUM OF THE THIRD DIMENSION
SILVER 1526 INTERNATIONAL FINANCE
SILVER 1538 MCKENNA AND CUNEO
SILVER 8792 DESTINATION HOTEL
RESORTS GOLD
21Classement des résultats par distance
- Quels sont mes cinq clients les plus proches de
ce concurrent ?
SELECT c.id, c.name, c.customer_grade,
SDO_NN_DISTANCE(1) distance FROM competitors
co, customers c WHERE co.id1 AND SDO_NN (
c.location, co.location,
'SDO_NUM_RES5', 1)'TRUE' ORDER BY distance
1538 MCKENNA AND CUNEO SILVER
88 809 LINCOLN SUITES
GOLD 95 1044 MUSEUM OF THE THIRD
DIMENSION SILVER 139 8792 DESTINATION
HOTEL RESORTS GOLD 145 1526
INTERNATIONAL FINANCE SILVER
215
22Autres critères de sélection présentsLes
résultats peuvent être incorrects!
- Quels sont mes cinq clients 'GOLD' les plus
proches de ce concurrent ? - On lit les 5 clients les plus proches, et on en
sélectionne ceux de type 'GOLD'!
SELECT c.id, c.name, c.customer_grade FROM
competitors co, customers c WHERE co.id1 AND
SDO_NN ( c.location, co.location,
'SDO_NUM_RES5')'TRUE' AND c.customer_grade
'GOLD'
809 LINCOLN SUITES
GOLD 8792 DESTINATION HOTEL RESORTS
GOLD
23Autres critères de sélection présentsLa bonne
méthode
- Quels sont mes cinq clients 'GOLD' les plus
proches de ce concurrent ?
SELECT c.id, c.name, c.customer_grade FROM
competitors co, customers c WHERE co.id1 AND
SDO_NN ( c.location, co.location)'TRUE'
AND c.customer_grade 'GOLD' AND ROWNUM lt
5
809 LINCOLN SUITES
GOLD 8792 DESTINATION HOTEL RESORTS
GOLD 810 HOTEL LOMBARDY
GOLD 7821 RENAISSANCE MAYFLOWER HOTEL
GOLD 6326 HOTEL LOMBARDY
GOLD
24Jointures Spatiales SDO_JOIN()
- Permet de trouver des corrélations entre deux
tables - Topologie ou distance
- Permet de comparer tous les objets d'une table
avec tous ceux d'une autre table. - Nécessite un index R-Tree sur chaque table
- Techniquement, implémenté sous forme de fonction
retournant une table
25La fonction SDO_JOIN
SDO_JOIN( table_name-1, column_name-1,
table_name-2, column_name-2
,'parameters' , preserve_join_order) RETURN
SDO_ROWIDSET
SQLgt DESC sdo_rowidset SDO_ROWIDSET TABLE OF
MDSYS.SDO_ROWIDPAIR Name Null?
Type -------------- -------- ----------- ROWID1
VARCHAR2(24) ROWID2
VARCHAR2(24)
26Exemple
- Associer chaque client GOLD au territoire
commercial dans lequel il se trouve
SELECT s.id, c.id, c.name FROM customers c,
sales_regions s, TABLE(SDO_JOIN(
'customers', 'location',
'sales_regions', 'geom',
'maskinside')) j WHERE j.rowid1 c.rowid
AND j.rowid2 s.rowid AND c.customer_grade
'GOLD' ORDER BY s.id, c.id
27Exemple d'utilisation distance
- Trouver tous les clients GOLD qui se trouvent à
moins de 500 mètres d'une de nos agences de San
Francisco
SELECT DISTINCT c.id, c.name, b.id FROM
customers c, branches b,
TABLE(SDO_JOIN( 'CUSTOMERS',
'LOCATION', 'BRANCHES', 'LOCATION',
'DISTANCE500 UNITMETER')) j WHERE
j.rowid1 c.rowid AND j.rowid2 b.rowid
AND c.customer_grade 'GOLD' AND b.city 'SAN
FRANCISCO'
28Fonctions Spatiales
- Package SDO_GEOM
- Calculs de distance, longueur, surface
- Génération d'objets buffer, centroïde, enveloppe
convexe, MBR, - Combinaison d'objets union, intersection, etc.
29Fonctions Spatiales
- Opérations sur un objet
- SDO_AREA
- SDO_LENGTH
- SDO_CENTROID
- SDO_CONVEXHULL
- SDO_POINTONSURFACE
- SDO_BUFFER
- Opérations sur deux objets
- SDO_DISTANCE
- SDO_DIFFERENCE
- SDO_INTERSECTION
- SDO_UNION
- SDO_XOR
Résultat numérique
Résultat nouvel objet
Les objets doivent être dans le même système de
coordonnées!
30Calculs de longueur, surface et distance
- SDO_AREA (g)
- Calcule la surface d'un polygone
- SDO_LENGTH (g)
- Calcule la longueur d'une ligne (ou du périmètre
d'un polygone) - SDO_DISTANCE (g1,g2)
- Calcule la distance entre deux objets
- On peut spécifier l'unité de mesure du résultat.
31Exemples
- Quelle est la surface totale du parc de
Yellowstone ? - Quelle est la longueur du Mississipi ?
- Quelle est la distance entre Los Angeles et San
Francisco
SELECT sdo_geom.sdo_area(geom,0.005,'unitsq_km')
FROM us_parks WHERE name 'Yellowstone NP'
SELECT sdo_geom.sdo_length(geom,0.005,'unitkm')
FROM us_rivers WHERE name 'Mississippi'
SELECT sdo_geom.sdo_distance(a.location,
b.location, 0.005, 'unitmile') FROM us_cities
a, us_cities b WHERE a.city 'Los Angeles'
AND b.city 'San Francisco'
32Génération d'objets
- SDO_BUFFER (g, size)
- Génère un buffer de la dimension choisie
- La dimension (size) peut être négative pour un
buffer interne - SDO_CENTROID (g)
- Calcule le centre de gravité d'un polygone
- Peut être en dehors du polygone!
- SDO_CONVEXHUL (g)
- Génère l'enveloppe convexe de l'objet (ligne ou
polygone) - SDO_MBR (g)
- Génère le rectangle d'encombrement de l'objet
(ligne ou polygone)
33Combinaison d'objets UNION
- SDO_UNION (g1, g2)
- Produit un objet qui représente l'union
géométrique des deux objets donnés
34Combinaison d'objets INTERSECTION
- SDO_INTERSECTION (g1, g2)
- Produit un objet qui représente l'intersection
géométrique des deux objets donnés.
35Combinaison d'objets DIFFERENCE
- SDO_DIFFERENCE (g1, g2)
- Produit un objet qui représente la différence
géométrique (g1-g2) des deux objets donnés.
36Combinaison d'objets XOR
- SDO_XOR (g1, g2)
- Produit un objet qui représente la différence
symmetrique des deux objets donnés. - Equivalent à l'union moins l'intersection
37Exemples
- Quelle est la surface occupée par le parc de
Yellowstone dans les états qu'il occupe ?
SELECT s.state, sdo_geom.sdo_area (
sdo_geom.sdo_intersection (s.geom, p.geom, 0.5),
0.5, 'unitsq_km') area FROM us_states
s, us_parks p WHERE SDO_ANYINTERACT (s.geom,
p.geom) 'TRUE' AND p.name 'Yellowstone NP'
STATE
AREA --------------------------
---------- Wyoming
8100.75346 Montana
640.295989 Idaho 154.659879
38Agrégations Spatiales
- Fonctions d' agrégation (comme SUM, COUNT, AVG
...) - Fonctionnent sur des ensemble d'objets
- SDO_AGGR_MBR
- Retourne le rectangle d'encombrement autour d'un
ensemble d'objets. - SDO_AGGR_UNION
- Calcule l'union géométrique d'un ensemble
d'objets - SDO_AGGR_CENTROID
- Calcule le centroïde d'un ensemble d'objets
- SDO_AGGR_CONVEXHULL
- Calcule l'enveloppe convexe autour d'un ensemble
d'objets.
39Exemples d'agrégations
- Trouver le point central de tous nos clients à
Daly City - Calculer le nombre de clients dans chaque code
postal, et calculer le point central pour ces
clients.
SELECT SDO_AGGR_CENTROID(SDOAGGRTYPE(location,0.5)
) center FROM customers WHERE city 'DALY
CITY'
SELECT COUNT(), postal_code,
SDO_AGGR_CENTROID(SDOAGGRTYPE(location,0.5))
center FROM customers GROUP BY postal_code
40En résumé
- Modèle d'exécution des requêtes spatiales
- Recherches basées sur les relations spatiales
- Recherches basées sur la distance
- Recherches basées sur la proximité
- Jointures spatiales
- Fonctions spatiales
- Agrégations spatiales
41(No Transcript)