{"id":605,"date":"2016-05-19T07:15:42","date_gmt":"2016-05-19T07:15:42","guid":{"rendered":"http:\/\/blog.tiran.info\/?p=605"},"modified":"2017-12-05T14:22:13","modified_gmt":"2017-12-05T13:22:13","slug":"oracle-locator-google-maps","status":"publish","type":"post","link":"https:\/\/blog.tiran.stream\/?p=605","title":{"rendered":"Oracle Locator &#038; Google Maps"},"content":{"rendered":"<p style=\"text-align: justify;\">Le partage de donn\u00e9es g\u00e9ographiques entre outils repose sur l&rsquo;utilisation de <a href=\"https:\/\/fr.wikipedia.org\/wiki\/Open_Geospatial_Consortium\" target=\"_blank\" rel=\"noopener\">formats d&rsquo;\u00e9change standardis\u00e9es<\/a>. <a href=\"https:\/\/fr.wikipedia.org\/wiki\/Keyhole_Markup_Language\" target=\"_blank\" rel=\"noopener\">KML<\/a> est un de ces formats et il est support\u00e9 notamment par Google Maps.<\/p>\n<p style=\"text-align: justify;\">Oracle Locator permet de produire des donn\u00e9es dans ce format \u00e0 l&rsquo;aide de la fonction <a href=\"https:\/\/docs.oracle.com\/cd\/E11882_01\/appdev.112\/e11830\/sdo_util.htm#SPATL1423\" target=\"_blank\" rel=\"noopener\">SDO_UTIL.TO_KMLGEOMETRY<\/a>.\u00a0Les fichiers au format KML peuvent ensuite \u00eatre import\u00e9es dans Google Maps (via <a href=\"https:\/\/www.google.com\/maps\/d\/splash?hl=en_US&amp;app=mp\" target=\"_blank\" rel=\"noopener\">My Maps<\/a>) afin d&rsquo;en obtenir une repr\u00e9sentation cartographique.<\/p>\n<p style=\"text-align: justify;\">Dans le <a href=\"http:\/\/blog.tiran.info\/prise-en-charge-de-donnees-de-geolocalisation-avec-oracle\">billet pr\u00e9c\u00e9dent<\/a>, on a r\u00e9cup\u00e9r\u00e9 les position GPS de tout les ponts enjambant la Loire entre Nevers et Tours. Cela a permis de calculer approximativement la longueur du fleuve (en sommant les distances inter-ponts). Ici, on va utiliser les m\u00eames donn\u00e9es mais dans le but de repr\u00e9senter\u00a0la position\u00a043 ponts sur\u00a0Google Maps.<\/p>\n<p style=\"text-align: justify;\">La fonction TO_KMLGEOMETRY\u00a0prend un objet <a href=\"https:\/\/docs.oracle.com\/database\/121\/SPATL\/GUID-683FF8C5-A773-4018-932D-2AF6EC8BC119.htm#SPATL489\" target=\"_blank\" rel=\"noopener\">SDO_GEOMETRY<\/a> en entr\u00e9e et renvoie un CLOB contenant un fragment XML.<\/p>\n<p style=\"text-align: justify;\">Ici, l&rsquo;objet SDO_GEOMETRY sera construit \u00e0 partir des attributs:<\/p>\n<ul style=\"text-align: justify;\">\n<li style=\"text-align: justify;\">SDO_GTYPE (attribut scalaire) valoris\u00e9 \u00e0 la valeur 2005 (g\u00e9om\u00e9trie 2D \u00ab\u00a02\u00a0\u00bb, de type multipoint \u00ab\u00a05\u00a0\u00bb).<\/li>\n<li style=\"text-align: justify;\">SDO_SRID valoris\u00e9 (attribut scalaire) \u00e0 8307 qui correspond au syst\u00e8me de positionnement g\u00e9od\u00e9sique WGS84 utilis\u00e9 par les GPS.<\/li>\n<li style=\"text-align: justify;\">SDO_ELEM_INFO_ARRAY (attribut tabulaire) valoris\u00e9 avec les donn\u00e9es 1, 1, et n (n \u00e9tant le nombre de points de la structure multipoint &#8211; 43 dans notre cas)<\/li>\n<li style=\"text-align: justify;\">SDO_ORDINATE_ARRAY (attribut tabulaire) valoris\u00e9 avec une succession des informations longitude puis latitude de chaque point.\u00a0<span style=\"line-height: 1.5;\">Ce dernier attribut est peupl\u00e9 en faisant un <\/span><a style=\"line-height: 1.5;\" href=\"https:\/\/docs.oracle.com\/database\/121\/LNPLS\/tuning.htm#LNPLS891\" target=\"_blank\" rel=\"noopener\">BULK COLLECT<\/a><span style=\"line-height: 1.5;\"> des informations de la table LOC_PONTS_LOIRE.\u00a0<\/span><\/li>\n<\/ul>\n<p style=\"text-align: justify;\">Une fois r\u00e9cup\u00e9r\u00e9 le CLOB en sortie de l&rsquo;appel de SDO_UTIL.TO_KMLGEOMETRY, on convertit ce dernier au format <a href=\"https:\/\/docs.oracle.com\/database\/121\/ARPLS\/t_xml.htm#ARPLS369\" target=\"_blank\" rel=\"noopener\">XMLType<\/a>.\u00a0Cela permet d&rsquo;y adjoindre \u00e0 l&rsquo;aide des <a href=\"https:\/\/docs.oracle.com\/database\/121\/ADXDB\/xdb13gen.htm#ADXDB4994\" target=\"_blank\" rel=\"noopener\">fonctions XML<\/a>\u00a0quelques attributs (prologue, balises kml, document et Placemark) afin de produire un fichier KML correctement structur\u00e9.<\/p>\n<p style=\"text-align: justify;\">Le r\u00e9sultat est \u00e0 nouveau convertit en CLOB avant d&rsquo;\u00eatre affich\u00e9.<\/p>\n<p style=\"text-align: justify;\">L&rsquo;ensemble de l&rsquo;op\u00e9ration est r\u00e9alis\u00e9e \u00e0 l&rsquo;aide du bloc PL\/SQL suivant:<\/p>\n<pre class=\"brush: sql; ruler: true;\">SQL&gt; set serveroutput on\r\nSQL&gt; set lines 32767\r\nSQL&gt; spool C:\\RTI\\ponts_Loire.kml\r\nSQL&gt;\r\nSQL&gt; DECLARE\r\n  2     l_kmlclob   CLOB;\r\n  3     l_geom      SDO_GEOMETRY;\r\n  4     l_coords    sdo_ordinate_array;\r\n  5     l_nb        NUMBER;\r\n  6     l_kmlxml    XMLTYPE;\r\n  7     l1          CLOB;\r\n  8  BEGIN\r\n  9       SELECT f\r\n 10         BULK COLLECT INTO l_coords\r\n 11         FROM (SELECT seq, longitude f, 1 rn FROM LOC_PONTS_LOIRE\r\n 12               UNION ALL\r\n 13               SELECT seq, latitude, 2 rn FROM LOC_PONTS_LOIRE)\r\n 14     ORDER BY seq, rn;\r\n 15\r\n 16     l_nb := l_coords.COUNT \/ 2;\r\n 17\r\n 18     SELECT sdo_geometry (2005,\r\n 19                          8307,\r\n 20                          NULL,\r\n 21                          sdo_elem_info_array (1, 1, l_nb),\r\n 22                          l_coords)\r\n 23       INTO l_geom\r\n 24       FROM DUAL;\r\n 25\r\n 26     l_kmlclob := SDO_UTIL.TO_KMLGEOMETRY (l_geom);\r\n 27     l_kmlxml := XMLTYPE.createXML (l_kmlclob);\r\n 28\r\n 29     SELECT XMLROOT (\r\n 30               XMLELEMENT (\r\n 31                  &quot;kml&quot;,\r\n 32                  xmlattributes (&#039;http:\/\/earth.google.com\/kml\/2.2&#039; AS &quot;xmlns&quot;),\r\n 33                  XMLELEMENT (\r\n 34                     &quot;Document&quot;,\r\n 35                        XMLCONCAT (\r\n 36                           XMLELEMENT (\r\n 37                              &quot;name&quot;,\r\n 38                              &#039;Position des ponts sur la Loire entre Nevers et Tours&#039;),\r\n 39                           XMLELEMENT (&quot;Placemark&quot;, l_kmlxml)))),\r\n 40               VERSION &#039;1.0&quot;  encoding=&quot;ISO-8859-1&#039;).getClobVal ()\r\n 41       INTO l1\r\n 42       FROM DUAL;\r\n 43\r\n 44     DBMS_OUTPUT.put_line (l1);\r\n 45  END;\r\n 46  \/\r\n&lt;?xml version=&quot;1.0&quot;  encoding=&quot;ISO-8859-1&quot;?&gt;\r\n&lt;kml xmlns=&quot;http:\/\/earth.google.com\/kml\/2.2&quot;&gt;\r\n  &lt;Document&gt;\r\n    &lt;name&gt;Position des ponts sur la Loire entre Nevers et Tours&lt;\/name&gt;\r\n    &lt;Placemark&gt;\r\n      &lt;MultiGeometry&gt;\r\n        &lt;Point&gt;\r\n          &lt;extrude&gt;0&lt;\/extrude&gt;\r\n          &lt;tessellate&gt;0&lt;\/tessellate&gt;\r\n          &lt;altitudeMode&gt;relativeToGround&lt;\/altitudeMode&gt;\r\n          &lt;coordinates&gt;3.158804,46.983663 &lt;\/coordinates&gt;\r\n        &lt;\/Point&gt;\r\n\r\n&lt; ... &gt;\r\n&lt; ... &gt;\r\n&lt; ... &gt;\r\n\r\n        &lt;Point&gt;\r\n          &lt;extrude&gt;0&lt;\/extrude&gt;\r\n          &lt;tessellate&gt;0&lt;\/tessellate&gt;\r\n          &lt;altitudeMode&gt;relativeToGround&lt;\/altitudeMode&gt;\r\n          &lt;coordinates&gt;0.685248,47.399097 &lt;\/coordinates&gt;\r\n        &lt;\/Point&gt;\r\n      &lt;\/MultiGeometry&gt;\r\n    &lt;\/Placemark&gt;\r\n  &lt;\/Document&gt;\r\n&lt;\/kml&gt;\r\n\r\n\r\nPL\/SQL procedure successfully completed.\r\n\r\nSQL&gt;\r\nSQL&gt; spool off\r\nSQL&gt;\r\n<\/pre>\n<p style=\"text-align: justify;\">Le fichier KML complet est accessible ici (l&rsquo;extension doit \u00eatre chang\u00e9e en \u00ab\u00a0.kml\u00a0\u00bb avant import dans My Maps):\u00a0<a href=\"https:\/\/blog.tiran.stream\/wp-content\/uploads\/2016\/04\/ponts_Loire.txt\" rel=\"\">ponts_Loire<\/a><\/p>\n<p style=\"text-align: justify;\">Ce dernier peut alors \u00eatre import\u00e9 dans\u00a0<a href=\"https:\/\/www.google.com\/maps\/d\/splash?hl=en_US&amp;app=mp\" target=\"_blank\" rel=\"noopener\">Google My Maps<\/a> \u00e0 partir du lien \u00ab\u00a0Import\u00a0\u00bb:<\/p>\n<p style=\"text-align: justify;\"><a href=\"https:\/\/blog.tiran.stream\/wp-content\/uploads\/2016\/04\/My-Maps-2.png\" rel=\"attachment wp-att-612\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-medium wp-image-612\" src=\"https:\/\/blog.tiran.stream\/wp-content\/uploads\/2016\/04\/My-Maps-2.png\" alt=\"My Maps 2\" width=\"300\" height=\"295\" \/><\/a><\/p>\n<p style=\"text-align: justify;\">Les points sont alors affich\u00e9s sous la forme de calque sur une carte Google Maps:<\/p>\n<p><iframe loading=\"lazy\" src=\"https:\/\/www.google.com\/maps\/d\/embed?mid=1j9JJZRT6A2UIMOjmNORPs1rPzuc\" width=\"960\" height=\"720\" align=\"center\"><\/iframe><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Le partage de donn\u00e9es g\u00e9ographiques entre outils repose sur l&rsquo;utilisation de formats d&rsquo;\u00e9change standardis\u00e9es. KML est un de ces formats<\/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,17],"tags":[],"class_list":["post-605","post","type-post","status-publish","format-standard","hentry","category-oracle","category-preparation-des-donnees","category-visualisation"],"_links":{"self":[{"href":"https:\/\/blog.tiran.stream\/index.php?rest_route=\/wp\/v2\/posts\/605","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=605"}],"version-history":[{"count":1,"href":"https:\/\/blog.tiran.stream\/index.php?rest_route=\/wp\/v2\/posts\/605\/revisions"}],"predecessor-version":[{"id":1163,"href":"https:\/\/blog.tiran.stream\/index.php?rest_route=\/wp\/v2\/posts\/605\/revisions\/1163"}],"wp:attachment":[{"href":"https:\/\/blog.tiran.stream\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=605"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.tiran.stream\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=605"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.tiran.stream\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=605"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}