Oracle and Yandex Maps integration

I faced with a requirement to add yandex integration. Yandex was chosen because Google Maps suck (no Maps for my region). And this is my solution.

Don't forget to add http://geocode-maps.yandex.ru to ACL if you are running Oracle 11g.

Example:

SELECT * from table(tb.YMAPS_API.GET_GEOCODE('Kazakhstan Almaty Klochkova', 'ru-RU'))

CREATE OR REPLACE TYPE "GEOLOCATION_TAG_T" AS OBJECT
( address_line            varchar2(4000 char),
    country_code            varchar2(2 char),
    country_name            varchar2(200 char),
    locality_name           varchar2(500 char),
    dependent_locality_type varchar2(500 char),
    dependent_locality_name varchar2(500 char),
    street_prefix             varchar2(200 char) ,
    street_name             varchar2(1000 char) ,
    house_number            varchar2(20 char),
    apartment_number        varchar2(20 char),
    position_x              number,
    position_y              number
)

/



  CREATE OR REPLACE TYPE "GEOLOCATION_TAGS_T"
AS TABLE OF GEOLOCATION_TAG_T;

/

  CREATE OR REPLACE PACKAGE "YMAPS_API"
as

  function get_geocode(
      p_search_base in varchar2,
      p_lang        in varchar2 := 'ru-RU')
    return geolocation_tags_t pipelined;
end ymaps_api;

/

create or replace
PACKAGE BODY      "YMAPS_API"
as
function get_geocode(
    p_search_base in varchar2,
    p_lang        in varchar2 := 'ru-RU')
  return geolocation_tags_t pipelined
is
  l_http_request utl_http.req;
  l_http_response utl_http.resp;
  l_clob clob;
  l_text varchar2(32767);
  l_xml xmltype;
  l_object geolocation_tag_t;
  l_dependent_locality_type varchar2(500 char);
  l_street_prefix           varchar2(200 char) ;
begin
  dbms_lob.createtemporary(l_clob, false);
  -- Make a HTTP request and get the response.
  l_http_request := utl_http.begin_request(utl_url.escape(url => 'http://geocode-maps.yandex.ru/1.x/?lang='||p_lang||
  '&results=100&format=xml&geocode='|| p_search_base , url_charset =>'UTF8' ), 'GET',utl_http.http_version_1_1);
  l_http_response := utl_http.get_response(l_http_request);
  -- Copy the response into the CLOB.
  begin
    loop
      utl_http.read_text(l_http_response, l_text, 32766);
      dbms_lob.writeappend (l_clob, length(l_text), l_text);
      dbms_output.put_line(l_text);
    end loop;
  exception
  when utl_http.end_of_body then
    utl_http.end_response(l_http_response);
  end;
  l_xml := xmltype.createxml(l_clob);
  for i in
  (
    select
      extract(addressdetails, '/Country/AddressLine/text()', 'xmlns="urn:oasis:names:tc:ciq:xsdschema:xAL:2.0"').getstringval() addressline
    , extract(addressdetails, '/Country/CountryNameCode/text()', 'xmlns="urn:oasis:names:tc:ciq:xsdschema:xAL:2.0"').getstringval()
      countrynamecode
    , extract(addressdetails, '/Country/CountryName/text()', 'xmlns="urn:oasis:names:tc:ciq:xsdschema:xAL:2.0"').getstringval() countryname
    , extract(addressdetails, '/*/*/LocalityName/text()', 'xmlns="urn:oasis:names:tc:ciq:xsdschema:xAL:2.0"').getstringval() city
    , extract(addressdetails, '/*/*/*/DependentLocalityName/text()', 'xmlns="urn:oasis:names:tc:ciq:xsdschema:xAL:2.0"').getstringval()
      district
    , extract(addressdetails, '/*/Locality/Thoroughfare/ThoroughfareName/text()', 'xmlns="urn:oasis:names:tc:ciq:xsdschema:xAL:2.0"')
      .getstringval() street
    , extract(addressdetails, '/*/Locality/Thoroughfare/Premise/PremiseNumber/text()', 'xmlns="urn:oasis:names:tc:ciq:xsdschema:xAL:2.0"')
      .getstringval() house
    , extract(addressdetails, '/*/*/DependentLocality/Thoroughfare/ThoroughfareName/text()',
      'xmlns="urn:oasis:names:tc:ciq:xsdschema:xAL:2.0"').getstringval() street1
    , extract(addressdetails, '/*/*/DependentLocality/Thoroughfare/Premise/PremiseNumber/text()' ,
      'xmlns="urn:oasis:names:tc:ciq:xsdschema:xAL:2.0"').getstringval() house1
    , (substr(pos, 1,instr(pos, ' ')-1)) x
    , (substr(pos, instr(pos, ' ')  +1)) y
    , text
    from
      (
        select
          column_value
        , extract(column_value, '/*/*/*/GeocoderMetaData/*/*',
          'xmlns="http://maps.yandex.ru/geocoder/1.x" xmlns="urn:oasis:names:tc:ciq:xsdschema:xAL:2.0"' ) addressdetails
        , extract(column_value, '/*/*/*/GeocoderMetaData/text/text()', 'xmlns="http://maps.yandex.ru/geocoder/1.x"').getstringval() text
        , extract(column_value, '/*/*/*/pos/text()', 'xmlns="http://www.opengis.net/gml""') pos
        from
          table(xmlsequence(extract(l_xml,'/ymaps/GeoObjectCollection/*',
          'xmlns:gml="http://www.opengis.net/gml" xmlns="http://maps.yandex.ru/ymaps/1.x" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://www.opengis.net/gml"'
          )) )
      )
    where
      addressdetails is not null
  )
  loop
    l_object := geolocation_tag_t( i.countryname||', '|| i.addressline, i.countrynamecode , i.countryname, i.city , null, i.district,null,
    nvl(i.street,i.street1) , nvl(i.house,i.house1) , null , i.x , i.y );
    pipe row (l_object);
  end loop;
  dbms_lob.freetemporary(l_clob);
  return;
end;
begin
  null;
end ymaps_api;