{"id":580,"date":"2016-05-01T20:52:54","date_gmt":"2016-05-01T20:52:54","guid":{"rendered":"http:\/\/blog.tiran.info\/?p=580"},"modified":"2016-05-01T20:52:54","modified_gmt":"2016-05-01T20:52:54","slug":"prise-en-charge-de-donnees-de-geolocalisation-avec-oracle","status":"publish","type":"post","link":"https:\/\/blog.tiran.stream\/?p=580","title":{"rendered":"Prise en charge de donn\u00e9es de g\u00e9olocalisation avec Oracle"},"content":{"rendered":"<p style=\"text-align: justify;\">Dans les billets pr\u00e9c\u00e9dents, on a pu d\u00e9terminer la latence de propagation des \u00e9v\u00e9nements de crues\/d\u00e9crues de la Loire entre diff\u00e9rents points de son lit.<\/p>\n<p style=\"text-align: justify;\">Assez naturellement, on constate que cette latence s\u2019accro\u00eet \u00e0 mesure qu&rsquo;augmente la distance entre le point de r\u00e9f\u00e9rence (Nevers) et le point de mesure.<\/p>\n<p style=\"text-align: justify;\">La difficult\u00e9 est d&rsquo;estimer num\u00e9riquement ce lien. En effet, le cours du fleuve n&rsquo;\u00e9tant pas rectiligne, il est difficile de quantifier les distances s\u00e9parant les divers points de mesures.\u00a0On peut obtenir un balisage assez pr\u00e9cis du trac\u00e9 du fleuve en s&rsquo;int\u00e9ressant aux positions des diff\u00e9rents ponts qui l&rsquo;enjambent.<\/p>\n<p style=\"text-align: justify;\">A partir de la <a href=\"https:\/\/fr.wikipedia.org\/wiki\/Liste_des_ponts_sur_la_Loire\" target=\"_blank\">page Wikip\u00e9dia \u00ab\u00a0Liste des ponts de la Loire\u00a0\u00bb<\/a>, on peut retrouver les coordonn\u00e9es GPS de ces ponts. Ces donn\u00e9es \u00e9tant stock\u00e9es dans un tableau au format \u00ab\u00a0wikitable\u00a0\u00bb, j&rsquo;ai utilis\u00e9 le site <a href=\"http:\/\/wikitables.geeksta.net\/\" target=\"_blank\">http:\/\/wikitables.geeksta.net\/<\/a> pour en extraire le contenu au format CSV.<\/p>\n<p style=\"text-align: justify;\">Les donn\u00e9es ont ensuite \u00e9t\u00e9 manuellement retravaill\u00e9es:<\/p>\n<ul style=\"text-align: justify;\">\n<li>seules les lignes en rapport avec le tron\u00e7on Nevers\/Tours ont \u00e9t\u00e9 conserv\u00e9es<\/li>\n<li>une colonne SEQ permettant d&rsquo;ordonner l&rsquo;enchainement des ponts suivant le cours du fleuve a \u00e9t\u00e9 ajout\u00e9e<\/li>\n<li>une colonne SECTION a \u00e9t\u00e9 ajout\u00e9e. Celle-ci permet d&rsquo;indiquer \u00e0 quel tron\u00e7on les points appartiennent:<\/li>\n<\/ul>\n<p>&nbsp;<\/p>\n<table style=\"border-color: #000000; border-style: solid; width: 300px;\" border=\"1\">\n<tbody>\n<tr>\n<td><strong>Tron\u00e7on<\/strong><\/td>\n<td><strong>SECTION<\/strong><\/td>\n<\/tr>\n<tr>\n<td>Nevers\/Saint Satur<\/td>\n<td>1<\/td>\n<\/tr>\n<tr>\n<td>Saint Satur\/Orl\u00e9ans<\/td>\n<td>2<\/td>\n<\/tr>\n<tr>\n<td>Orl\u00e9ans\/Blois<\/td>\n<td>3<\/td>\n<\/tr>\n<tr>\n<td>Blois\/Tours<\/td>\n<td>4<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p style=\"text-align: left;\">Le fichier r\u00e9sultant est accessible ici:\u00a0<a href=\"https:\/\/blog.tiran.stream\/wp-content\/uploads\/2016\/04\/Ponts-Loire-Nevers-Tours.xlsx\" rel=\"\">Ponts-Loire-Nevers-Tours<\/a><\/p>\n<p style=\"text-align: justify;\">L&rsquo;\u00e9tape suivante a consist\u00e9 \u00e0 charger ces donn\u00e9es dans une table &#8211; cf\u00a0<a href=\"http:\/\/blog.tiran.info\/chargement-de-donnees-au-format-excel-dans-une-table-oracle\" target=\"_blank\">http:\/\/blog.tiran.info\/chargement-de-donnees-au-format-excel-dans-une-table-oracle<\/a>\u00a0:<\/p>\n<pre class=\"brush: sql; ruler: true; highlight: 5\">SQL&gt; set SQLFORMAT ansiconsole\nSQL&gt;\nSQL&gt; SELECT * FROM LOC_PONTS_LOIRE ORDER BY seq DESC FETCH FIRST 5 ROWS ONLY;\n\nSEQ  PONT                         RIVE_GAUCHE             RIVE_DROITE  LATITUDE   LONGITUDE  SECTION\n43   Pont Wilson                  Tours                   Tours        47.399097  0.685248   4\n42   Passerelle Saint-Symphorien  Tours                   Tours        47.398756  0.692975   4\n41   Pont Mirabeau                Tours                   Tours        47.399526  0.700348   4\n40   Pont de l&#039;A10 sur la Loire   Saint-Pierre-des-Corps  Tours        47.399362  0.70997    4\n39   Pont Charles-de-Gaulle       Montlouis-sur-Loire     Vouvray      47.402706  0.799678   4\n\n\nSQL&gt;\n<\/pre>\n<p style=\"text-align: justify;\">Pour transformer les informations de latitude\/longitude en points g\u00e9o-localis\u00e9s, j&rsquo;ai eu recours \u00e0 <a href=\"https:\/\/docs.oracle.com\/database\/121\/SPATL\/GUID-EC6DEA23-8FD7-4109-A0C1-93C0CE3D6FF2.htm#SPATL340\" target=\"_blank\">Oracle Locator<\/a>.\u00a0<span style=\"line-height: 1.5;\">Il s&rsquo;agit d&rsquo;une s\u00e9rie de fonctions g\u00e9ographiques disponibles dans toutes les \u00e9ditions du SGBD.\u00a0<\/span><span style=\"line-height: 1.5;\">A noter qu&rsquo;il existe aussi une option d&rsquo;Oracle EE d\u00e9nomm\u00e9e \u00ab\u00a0Spatial\u00a0\u00bb qui permet de r\u00e9aliser des traitements de donn\u00e9es g\u00e9ographiques beaucoup plus complexes.\u00a0<\/span><span style=\"line-height: 1.5;\">Dans le cas pr\u00e9sent, les fonctions Locator sont amplement suffisantes!<\/span><\/p>\n<p style=\"text-align: justify;\"><span style=\"line-height: 1.5;\">Locator est install\u00e9 avec le composant Oracle Multim\u00e9dia ORDIM :<\/span><\/p>\n<pre class=\"brush: sql; ruler: true;\">SQL&gt; col COMP_NAME format a35\nSQL&gt;\nSQL&gt; SELECT comp_name, version, status FROM dba_registry WHERE comp_id = &#039;ORDIM&#039;;\n\nCOMP_NAME                           VERSION                        STATUS\n----------------------------------- ------------------------------ -----------\nOracle Multimedia                   12.1.0.2.0                     VALID\n\nSQL&gt;\n<\/pre>\n<p style=\"text-align: justify;\">Locator &amp; Spatial introduisent de nouveaux types dont le principal est <a href=\"https:\/\/docs.oracle.com\/database\/121\/SPATL\/GUID-C66EF8CE-8E5D-47BB-B0E0-2F049C5F9B26.htm#SPATL501\" target=\"_blank\">SDO_GEOMETRY<\/a>.<\/p>\n<p style=\"text-align: justify;\">Son constructeur est d\u00e9fini ainsi:<\/p>\n<pre class=\"brush: sql; ruler: true;\">SQL&gt; desc SDO_GEOMETRY\nName          Null? Type\n------------- ----- ---------------------------\nSDO_GTYPE           NUMBER\nSDO_SRID            NUMBER\nSDO_POINT           MDSYS.SDO_POINT_TYPE()\nSDO_ELEM_INFO       MDSYS.SDO_ELEM_INFO_ARRAY()\nSDO_ORDINATES       MDSYS.SDO_ORDINATE_ARRAY()\nSQL&gt;\n<\/pre>\n<p style=\"text-align: justify;\">Dans notre cas, on travaille avec des points autonomes dans un r\u00e9f\u00e9rentiel \u00e0 deux dimension (latitude\/longitude):<\/p>\n<ul style=\"text-align: justify;\">\n<li>l&rsquo;attribut SDO_GTYPE sera valoris\u00e9 \u00e0 la valeur 2001 (g\u00e9om\u00e9trie 2D \u00ab\u00a02\u00a0\u00bb, de type point \u00ab\u00a01\u00a0\u00bb).<\/li>\n<li>l&rsquo;attribut SDO_SRID sera valoris\u00e9 \u00e0 8307 qui correspond au syst\u00e8me de <a href=\"https:\/\/fr.wikipedia.org\/wiki\/WGS_84\" target=\"_blank\">positionnement g\u00e9od\u00e9sique WGS84<\/a> utilis\u00e9 par les GPS.<\/li>\n<li>l&rsquo;attribut SDO_POINT permettra d&rsquo;indiquer les coordonn\u00e9es du point d&rsquo;int\u00e9r\u00eat. Pour cela on utilise un autre type sp\u00e9cifique \u00e0 Locator\/Spatial &#8211; \u00e0 savoir SDO_POINT_TYPE:<\/li>\n<\/ul>\n<pre class=\"brush: sql; ruler: true;\">SQL&gt; desc SDO_POINT_TYPE\nName Null? Type\n---- ----- ------\nX          NUMBER\nY          NUMBER\nZ          NUMBER\nSQL&gt;\n<\/pre>\n<p style=\"text-align: justify;\">A l&rsquo;aide d&rsquo;une vue, on r\u00e9alise les transformations suivantes sur les donn\u00e9es de LOC_PONTS_LOIRE:<\/p>\n<ul style=\"text-align: justify;\">\n<li>pour chaque pont, on d\u00e9termine le suivant \u00e0 l&rsquo;aide de la fonction analytique LEAD<\/li>\n<li>on convertit les coordonn\u00e9es GPS en objet SDO_GEOMETRY<\/li>\n<\/ul>\n<pre class=\"brush: sql; ruler: true; highlight: 41\">SQL&gt; CREATE OR REPLACE VIEW loc_ponts_loire_geom\n  2  AS\n  3     SELECT section,\n  4            seq,\n  5            sdo_geometry (2001,\n  6                          8307,\n  7                          sdo_point_type (longitude, latitude, NULL),\n  8                          NULL,\n  9                          NULL)\n 10               pont_loc,\n 11            CASE\n 12               WHEN next_longitude IS NULL\n 13               THEN\n 14                  NULL\n 15               ELSE\n 16                  sdo_geometry (\n 17                     2001,\n 18                     8307,\n 19                     sdo_point_type (next_longitude, next_latitude, NULL)\n 20                     NULL,\n 21                     NULL)\n 22            END\n 23               next_pont_loc\n 24       FROM (SELECT section,\n 25                    seq,\n 26                    rive_gauche,\n 27                    rive_droite,\n 28                    longitude,\n 29                    latitude,\n 30                    LEAD (longitude) OVER (ORDER BY seq)\n 31                       next_longitude,\n 32                    LEAD (latitude) OVER (ORDER BY seq)\n 33                       next_latitude\n 34               FROM loc_ponts_loire);\n\nView loc_ponts_loire_geom created.\n\nSQL&gt;\nSQL&gt; SELECT * FROM LOC_PONTS_LOIRE_GEOM FETCH FIRST 5 ROWS ONLY;\n\nSECTION  SEQ  PONT_LOC                    NEXT_PONT_LOC\n1        1    oracle.sql.STRUCT@4fa1c212  oracle.sql.STRUCT@6ea2bc93\n1        2    oracle.sql.STRUCT@3116c353  oracle.sql.STRUCT@f627d13\n1        3    oracle.sql.STRUCT@4e928fbf  oracle.sql.STRUCT@352ff4da\n1        4    oracle.sql.STRUCT@3224a577  oracle.sql.STRUCT@2e32ccc5\n1        5    oracle.sql.STRUCT@748741cb  oracle.sql.STRUCT@3e44f2a5\n\n\nSQL&gt;\n<\/pre>\n<p style=\"text-align: justify;\">La fonction <a href=\"https:\/\/docs.oracle.com\/database\/121\/SPATL\/GUID-EC93D8DA-F207-460F-A85A-AA50B92385EC.htm#SPATL1117\" target=\"_blank\">SDO_GEOM.SDO_DISTANCE<\/a> 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\u00e9terminer la longueur du fleuve depuis Nevers jusqu&rsquo;\u00e0 chaque fin de tron\u00e7on.<\/p>\n<pre class=\"brush: sql; ruler: true; highlight: 19\"> \nSQL&gt; CREATE OR REPLACE VIEW dist_depuis_nevers\n  2  AS\n  3       SELECT section, MAX (distance_km) km\n  4         FROM (SELECT section,\n  5                      SUM (SDO_GEOM.sdo_distance (pont_loc,\n  6                                                  next_pont_loc,\n  7                                                  0.05,\n  8                                                  &#039;unit=KM&#039;))\n  9                      OVER (ORDER BY seq)\n 10                         distance_km\n 11                 FROM loc_ponts_loire_geom)\n 12     GROUP BY section;\n\nView dist_depuis_nevers created.\n\nSQL&gt;\nSQL&gt; SELECT * FROM dist_depuis_nevers ORDER BY section;\n\nSECTION  KM\n1        140.006661912267928\n2        242.4190566567324561\n3        333.8738342860986281\n4        388.9095340744237351\n\n\nSQL&gt;\n<\/pre>\n<p style=\"text-align: justify;\">Pour ces 4 tron\u00e7ons, on a calcul\u00e9 le d\u00e9lai moyen de propagation des ph\u00e9nom\u00e8nes de crues\/d\u00e9crues dans un <a href=\"http:\/\/blog.tiran.info\/analyse-de-correlation-avec-oracle\" target=\"_blank\">billet pr\u00e9c\u00e9dent<\/a>. On les stocke dans la table DELAI_PROPAG:<\/p>\n<pre class=\"brush: sql; ruler: true;\">SQL&gt; CREATE TABLE delai_propag\n  2  (\n  3     section        NUMBER PRIMARY KEY,\n  4     delai_heures   NUMBER\n  5  );\n\nTable delai_propag created.\n\nSQL&gt;\nSQL&gt; INSERT INTO delai_propag VALUES (1, 16);\n\n1 row inserted.\n\nSQL&gt; INSERT INTO delai_propag VALUES (2, 37);\n\n1 row inserted.\n\nSQL&gt; INSERT INTO delai_propag VALUES (3, 47);\n\n1 row inserted.\n\nSQL&gt; INSERT INTO delai_propag VALUES (4, 54);\n\n1 row inserted.\n\nSQL&gt;\nSQL&gt; COMMIT;\n\nCommit complete.\n\nSQL&gt;\n<\/pre>\n<p style=\"text-align: justify;\">On peut alors d\u00e9terminer une vitesse moyenne d&rsquo;\u00e9coulement du fleuve entre Nevers et Tours en utilisant une r\u00e9gression lin\u00e9aire simple:<\/p>\n<pre class=\"brush: sql; ruler: true; highlight: 4\">SQL&gt; SELECT ROUND (REGR_SLOPE (km, delai_heures), 2) vitesse_moyenne_ecoulement_kmh\n  2    FROM dist_depuis_nevers NATURAL JOIN delai_propag;\n\nVITESSE_MOYENNE_ECOULEMENT_KMH\n6.52\n\n\nSQL&gt;\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>Dans les billets pr\u00e9c\u00e9dents, on a pu d\u00e9terminer la latence de propagation des \u00e9v\u00e9nements de crues\/d\u00e9crues de la Loire entre<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"colormag_page_container_layout":"default_layout","colormag_page_sidebar_layout":"default_layout","footnotes":""},"categories":[6,10,13],"tags":[],"class_list":["post-580","post","type-post","status-publish","format-standard","hentry","category-oracle","category-preparation-des-donnees","category-regression"],"_links":{"self":[{"href":"https:\/\/blog.tiran.stream\/index.php?rest_route=\/wp\/v2\/posts\/580","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.tiran.stream\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.tiran.stream\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.tiran.stream\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.tiran.stream\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=580"}],"version-history":[{"count":0,"href":"https:\/\/blog.tiran.stream\/index.php?rest_route=\/wp\/v2\/posts\/580\/revisions"}],"wp:attachment":[{"href":"https:\/\/blog.tiran.stream\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=580"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.tiran.stream\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=580"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.tiran.stream\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=580"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}