Prise en charge de données de géolocalisation avec Oracle
Dans les billets précédents, on a pu déterminer la latence de propagation des événements de crues/décrues de la Loire entre différents points de son lit.
Assez naturellement, on constate que cette latence s’accroît à mesure qu’augmente la distance entre le point de référence (Nevers) et le point de mesure.
La difficulté est d’estimer numériquement ce lien. En effet, le cours du fleuve n’étant pas rectiligne, il est difficile de quantifier les distances séparant les divers points de mesures. On peut obtenir un balisage assez précis du tracé du fleuve en s’intéressant aux positions des différents ponts qui l’enjambent.
A partir de la page Wikipédia « Liste des ponts de la Loire », on peut retrouver les coordonnées GPS de ces ponts. Ces données étant stockées dans un tableau au format « wikitable », j’ai utilisé le site http://wikitables.geeksta.net/ pour en extraire le contenu au format CSV.
Les données ont ensuite été manuellement retravaillées:
- seules les lignes en rapport avec le tronçon Nevers/Tours ont été conservées
- une colonne SEQ permettant d’ordonner l’enchainement des ponts suivant le cours du fleuve a été ajoutée
- une colonne SECTION a été ajoutée. Celle-ci permet d’indiquer à quel tronçon les points appartiennent:
| Tronçon | SECTION |
| Nevers/Saint Satur | 1 |
| Saint Satur/Orléans | 2 |
| Orléans/Blois | 3 |
| Blois/Tours | 4 |
Le fichier résultant est accessible ici: Ponts-Loire-Nevers-Tours
L’étape suivante a consisté à charger ces données dans une table – cf http://blog.tiran.info/chargement-de-donnees-au-format-excel-dans-une-table-oracle :
SQL> set SQLFORMAT ansiconsole SQL> SQL> SELECT * FROM LOC_PONTS_LOIRE ORDER BY seq DESC FETCH FIRST 5 ROWS ONLY; SEQ PONT RIVE_GAUCHE RIVE_DROITE LATITUDE LONGITUDE SECTION 43 Pont Wilson Tours Tours 47.399097 0.685248 4 42 Passerelle Saint-Symphorien Tours Tours 47.398756 0.692975 4 41 Pont Mirabeau Tours Tours 47.399526 0.700348 4 40 Pont de l'A10 sur la Loire Saint-Pierre-des-Corps Tours 47.399362 0.70997 4 39 Pont Charles-de-Gaulle Montlouis-sur-Loire Vouvray 47.402706 0.799678 4 SQL>
Pour transformer les informations de latitude/longitude en points géo-localisés, j’ai eu recours à Oracle Locator. Il s’agit d’une série de fonctions géographiques disponibles dans toutes les éditions du SGBD. A noter qu’il existe aussi une option d’Oracle EE dénommée « Spatial » qui permet de réaliser des traitements de données géographiques beaucoup plus complexes. Dans le cas présent, les fonctions Locator sont amplement suffisantes!
Locator est installé avec le composant Oracle Multimédia ORDIM :
SQL> col COMP_NAME format a35 SQL> SQL> SELECT comp_name, version, status FROM dba_registry WHERE comp_id = 'ORDIM'; COMP_NAME VERSION STATUS ----------------------------------- ------------------------------ ----------- Oracle Multimedia 12.1.0.2.0 VALID SQL>
Locator & Spatial introduisent de nouveaux types dont le principal est SDO_GEOMETRY.
Son constructeur est défini ainsi:
SQL> desc SDO_GEOMETRY Name Null? Type ------------- ----- --------------------------- SDO_GTYPE NUMBER SDO_SRID NUMBER SDO_POINT MDSYS.SDO_POINT_TYPE() SDO_ELEM_INFO MDSYS.SDO_ELEM_INFO_ARRAY() SDO_ORDINATES MDSYS.SDO_ORDINATE_ARRAY() SQL>
Dans notre cas, on travaille avec des points autonomes dans un référentiel à deux dimension (latitude/longitude):
- l’attribut SDO_GTYPE sera valorisé à la valeur 2001 (géométrie 2D « 2 », de type point « 1 »).
- l’attribut SDO_SRID sera valorisé à 8307 qui correspond au système de positionnement géodésique WGS84 utilisé par les GPS.
- l’attribut SDO_POINT permettra d’indiquer les coordonnées du point d’intérêt. Pour cela on utilise un autre type spécifique à Locator/Spatial – à savoir SDO_POINT_TYPE:
SQL> desc SDO_POINT_TYPE Name Null? Type ---- ----- ------ X NUMBER Y NUMBER Z NUMBER SQL>
A l’aide d’une vue, on réalise les transformations suivantes sur les données de LOC_PONTS_LOIRE:
- pour chaque pont, on détermine le suivant à l’aide de la fonction analytique LEAD
- on convertit les coordonnées GPS en objet SDO_GEOMETRY
SQL> CREATE OR REPLACE VIEW loc_ponts_loire_geom 2 AS 3 SELECT section, 4 seq, 5 sdo_geometry (2001, 6 8307, 7 sdo_point_type (longitude, latitude, NULL), 8 NULL, 9 NULL) 10 pont_loc, 11 CASE 12 WHEN next_longitude IS NULL 13 THEN 14 NULL 15 ELSE 16 sdo_geometry ( 17 2001, 18 8307, 19 sdo_point_type (next_longitude, next_latitude, NULL) 20 NULL, 21 NULL) 22 END 23 next_pont_loc 24 FROM (SELECT section, 25 seq, 26 rive_gauche, 27 rive_droite, 28 longitude, 29 latitude, 30 LEAD (longitude) OVER (ORDER BY seq) 31 next_longitude, 32 LEAD (latitude) OVER (ORDER BY seq) 33 next_latitude 34 FROM loc_ponts_loire); View loc_ponts_loire_geom created. SQL> SQL> SELECT * FROM LOC_PONTS_LOIRE_GEOM FETCH FIRST 5 ROWS ONLY; SECTION SEQ PONT_LOC NEXT_PONT_LOC 1 1 oracle.sql.STRUCT@4fa1c212 oracle.sql.STRUCT@6ea2bc93 1 2 oracle.sql.STRUCT@3116c353 oracle.sql.STRUCT@f627d13 1 3 oracle.sql.STRUCT@4e928fbf oracle.sql.STRUCT@352ff4da 1 4 oracle.sql.STRUCT@3224a577 oracle.sql.STRUCT@2e32ccc5 1 5 oracle.sql.STRUCT@748741cb oracle.sql.STRUCT@3e44f2a5 SQL>
La fonction SDO_GEOM.SDO_DISTANCE permet de calculer la distance entre deux points SDO_GEOMETRY. On peut alors faire une somme roulante (via la forme analytique de SUM) des distances pour déterminer la longueur du fleuve depuis Nevers jusqu’à chaque fin de tronçon.
SQL> CREATE OR REPLACE VIEW dist_depuis_nevers 2 AS 3 SELECT section, MAX (distance_km) km 4 FROM (SELECT section, 5 SUM (SDO_GEOM.sdo_distance (pont_loc, 6 next_pont_loc, 7 0.05, 8 'unit=KM')) 9 OVER (ORDER BY seq) 10 distance_km 11 FROM loc_ponts_loire_geom) 12 GROUP BY section; View dist_depuis_nevers created. SQL> SQL> SELECT * FROM dist_depuis_nevers ORDER BY section; SECTION KM 1 140.006661912267928 2 242.4190566567324561 3 333.8738342860986281 4 388.9095340744237351 SQL>
Pour ces 4 tronçons, on a calculé le délai moyen de propagation des phénomènes de crues/décrues dans un billet précédent. On les stocke dans la table DELAI_PROPAG:
SQL> CREATE TABLE delai_propag 2 ( 3 section NUMBER PRIMARY KEY, 4 delai_heures NUMBER 5 ); Table delai_propag created. SQL> SQL> INSERT INTO delai_propag VALUES (1, 16); 1 row inserted. SQL> INSERT INTO delai_propag VALUES (2, 37); 1 row inserted. SQL> INSERT INTO delai_propag VALUES (3, 47); 1 row inserted. SQL> INSERT INTO delai_propag VALUES (4, 54); 1 row inserted. SQL> SQL> COMMIT; Commit complete. SQL>
On peut alors déterminer une vitesse moyenne d’écoulement du fleuve entre Nevers et Tours en utilisant une régression linéaire simple:
SQL> SELECT ROUND (REGR_SLOPE (km, delai_heures), 2) vitesse_moyenne_ecoulement_kmh 2 FROM dist_depuis_nevers NATURAL JOIN delai_propag; VITESSE_MOYENNE_ECOULEMENT_KMH 6.52 SQL>