Wednesday, October 17, 2012

Find your way with HttpUriType and Google Maps

Recently I read Duke Ganote writing about using UTL_HTTP to get stock quote from Yahoo. (Duke must have a thing for authorities, particularly Marshalls of Legoredo ;-) Anyway, I posted a comment how to do a similar thing with HttpUriType.

And that reminded me that long time ago I reminded myself that I should blog about how we use HttpUriType to query driving distance and time from Google Maps. (I have even tried to submit abstract to KScope and UKOUG on getting data with HttpUriType, UTL_HTTP or UTL_FTP, but no go so far...)


Let's imagine Larry needs directions to get from Oracle HQ to the Moscone Center to deliver his keynote.

Here's a simple little query using HttpUriType:

select
   httpuritype(
      'maps.googleapis.com/maps/api/directions/xml' ||
      '?origin=' || utl_url.escape('500 Oracle Parkway, Redwood Shores, CA 94065') ||
      '&destination=' || utl_url.escape('747 Howard Street, San Francisco, CA 94103') ||
      '&mode=driving' ||
      '&alternatives=true' ||
      '&units=metric' ||
      '&region=us' ||
      '&language=en' ||
      '&sensor=false'
   ).getxml() directions
from dual
/

And here's the (shortened) output of that query:


 OK
 
  US-101 N
  
   
    DRIVING
    
     37.5304900
     -122.2609900
    
    
     37.5285000
     -122.2666200
    
    
     qdadFdbfiVKFo@NQ@y@AG?UAa@?M@MBKDCBIDSPKLEHGTCLCH?LAPAp@@|A?l@ANA\CVEXEPCNM\[|@_@vAKf@Ef@Cj@?P?N?T@RBZF^J\LVT^JLPJPHLFNDLBN@P?\?TATBhCLNBJDJFRNJNJRJVHRPRNLLHLFNBP@T?RENARI^[PYJUN?DCFEFCFCRERI
    
    
     101
     2 mins
    
    Head <b>north</b> on <b>Oracle Pkwy</b>
    
     1085
     1.1 km
    
   
   
   
    1895
    32 mins
   
   
    37696
    37.7 km
   
   
    37.5304900
    -122.2609900
   
   
    37.7841700
    -122.4015500
   
   500 Oracle Pkwy, Redwood City, CA 94065, USA
   747 Howard St, San Francisco, CA 94103, USA
  
  Map data ©2012 Google
  
   qdadFdbfiV{@VkA?mA?g@R_@^M^GVA^AlEEt@Kj@mAbEQnAC|@DtAR|@b@v@\X^P\H`@@r@A~CPZH^Vl@nA`@`@ZP`@Dh@Eb@K^[PYJUN?LIv@W^tB@d@bAnCR~@RlBBdBFjDEbBIp@}@~Ba@n@cBjC[r@aG~GaF|EsOlNiAjAcCzB{CpCsJrIaIhHsG~FuFxE_@l@m@h@wClCaGlFqBjBcBvAo@b@cD|CsOdN}VpUaBzAml@ni@yPdRwHrIyHrIsEnFcFdFcJrKqAzA_LxLwJ|KgGxGo]f`@{RtTyA|A{@fAgBdCwAdDkAfFu@tFWvD?rEEpL_AtvAChFG~AOrDKlBw@nGk@`Di@jCq@rBc@tA{CfHk@nA{AdCILmMbU{~@|`Ber@jmAsEfI{C|DcDlDEDsCxBsChBsB`A[N_Cv@}Cx@gd@lKqEnAsNjDuB`@uBd@kD`@eOx@W@sETgCToCVaC\{Cn@kCr@cBj@aNxF_FtA}Cn@aBP_Px@gK`@qIXcNn@uH^gFRaDHw@?a@?sAKaBOyCe@uA[{Ai@o@[aLwEqAy@_Am@mA_AqBwBiB_CeEuHqF{JyAcCw@uA}CgEgCqCcLwKeLuKoBcBmAcAoFwDmC_ByE}B}CsAaE}AsCu@iB]iCWg@Cm@CoD@s@ByCZeI`AsVhDyd@nGaVbDsL`B{RnCkNpBuL`B{IlAk[nEiB^wBp@y@\yBlAcFvDyBbBoBnAiBx@yBr@yGnAc@@{@NeEx@yCl@mEdAsLnDaFtAu@PGHE@sFpA_IxByCz@e@Pc@NwAv@y@`@oAn@qMtFaElBmB|@sFtCsCxAu@^{Aj@MDwCv@kAPwBPcDDaCQwAS{AYkBk@cCkAeBkAaGwEIIy@g@eCcAgBa@_FeAeJoBkDu@uHcBiBa@cC[qAIuCEcQz@uAPeAZ[NoBrAkAvAgD|Ew@x@gAv@eA`@_AV}@FoA?iAMcAWsAk@y@i@iCsAkA_@sAWgBMuCIiBB[BUIYAc@@cGV_GTkF\uAJi@Z_Bp@}BpAmAl@KB{A^mBJu@EW?gAIaBi@gAe@kAs@w@m@cA{@cAgA_@]k@y@m@eA]o@u@gBgE_K}@gBG[aF}IUm@O_AC}@B[s@}@mCsD_DiEmBlCo@~@kIxKyFvHlDzE
  
  
   
    37.5277300
    -122.4080100
   
   
    37.7850400
    -122.2609900
   
  
 
 
  I-280 N and US-101 N
  
   
    DRIVING
    
     37.5304900
     -122.2609900
    
    
     37.5285000
     -122.2666200
    
    
     qdadFdbfiVKFo@NQ@y@AG?UAa@?M@MBKDCBIDSPKLEHGTCLCH?LAPAp@@|A?l@ANA\CVEXEPCNM\[|@_@vAKf@Ef@Cj@?P?N?T@RBZF^J\LVT^JLPJPHLFNDLBN@P?\?TATBhCLNBJDJFRNJNJRJVHRPRNLLHLFNBP@T?RENARI^[PYJUN?DCFEFCFCRERI
    
    
     101
     2 mins
    
    Head <b>north</b> on <b>Oracle Pkwy</b>
    
     1085
     1.1 km
    
   
   
   
    2159
    36 mins
   
   
    45225
    45.2 km
   
   
    37.5304900
    -122.2609900
   
   
    37.7841700
    -122.4015500
   
   500 Oracle Pkwy, Redwood City, CA 94065, USA
   747 Howard St, San Francisco, CA 94103, USA
   
    
     37.6637748
     -122.4654197
    
    6
    0.5245810
   
  
  Map data ©2012 Google
  
   qdadFdbfiV{@VkA?mA?g@R_@^M^Iv@AlEQ`BmAbEQnAC|@DtAR|@b@v@|@j@~@Jr@A~CPz@`@l@nA|@r@`@Dh@Eb@Kp@u@JUN?LIv@W`@zCvAnERlBBdBFjDOtC_BnD_C~DaG~GaF|E}QxP_HlGsJrIaIhHiOxMmAvAwClCaGlFqBjBcBvAsE`EsOdN}VpUaBzAml@ni@yPdReYv[cFdFcJrKqAzA_LxLwJ|Kwe@`i@{RtTuCdDgBdCwAdDkAfFu@tFWvD?rEEpL_AtvAKhI[`Hw@nGuAlHq@rB_E|Jk@nA{AdCwMpU{~@|`Ber@jmAsEfI{C|DcDlDEDsCxBsChBsB`A[N_Cv@ei@fMqEnAiRlEsAP_@CSAkG\eKb@qADqDVuAf@g@Z_Av@g@n@w@zAe@hAy@xDUtBEdCJhDh@fIBdCGdKGnDKlIF`Fj@rFnAzFxF~PjG|QpElO^xAL`@h@zFhAlYGxAe@pBgAtBc@xAsAfBaHjJuDdDs@h@aE~BqQ`HsDrB{BbBmGjFcJ|HqCjDeBlC_HtOaBnCcBpB_AbA_Ar@oJdFkQxIuA|@uAhAmBfB}@fAek@xw@kC|CaBrAaB|@eBv@cBf@yBTkB?aRT{CC_DN{BJcEn@s@Ny@RqBl@}D`B_EpBcAx@wKlIgAz@oJzGeBt@mAVsD`@mD?kWm@wMScCSoE_A}G{AmCWaU@kHTwB`@wFxAqB`@eBRcFDaFH_DB}BOeL?gBIcCa@cAWcC}@iC{AgA{@uE}EiDqD{AkCs@_B{@oC}@kFQuDAsG@_LEwJK{Ea@eEu@uDIYuAsDg@aA_B}BoHiJwHiJmCaC}BsAgBm@qDm@_He@iKg@kBQwDg@e@KQE{EuAgCgAeBcAiD_CgAaAcDgDsCiE}BqFkCkI}AsFe@aBWu@wBcKg@oCk@gDkC}O_@}CMqCA}BPmEtByQPiDH{DCoEe@cIq@uIKsDDgLAiCQ_Dm@gFWkAsEqNmEcM{@kBq@}@{A}A_Ai@i@[sBk@m@IYEkB@mALgBTsCZoBBiAAQJk@IoDu@kAc@aCoAoIsGm@a@yCqAqOeDoJuBaLcCiDYsBGePt@uBPcAR}@\_Al@_BzAsCdEwAbBs@l@cAj@kA^q@LoADwAG{@QiCgAiCyAqC_AcCYcCIoDD_@K_@?eEPgEPaLl@{@PcBt@qAr@qCzAs@RsAR_ADkAIy@?y@O_Bo@qC}AgC_C{@{@sAsBoAmCuDcJaBqDoF{JS_AEm@@_AmGsIsAgBwAnBUXKR{HdKwGzIU^j@v@`CbD
  
  
   
    37.5277300
    -122.4715800
   
   
    37.7850400
    -122.2609900
   
  
 


HttpUriType makes an HTTP call to some URL. The HttpUriType object has methods GetBlob(), GetClob() and GetXml() depending on what type of data the URL is returning.

In this case the URL gives us XML data, which we then can query using the XMLTABLE function and XQuery syntax:

select route.routename
     , leg.startaddr
     , leg.endaddr
     , to_char(
          to_date(
             to_char(leg.seconds,'TM9')
           , 'SSSSS'
          )
        , 'HH24:MI:SS'
       ) legtime
     , leg.meters/1000 legkm
from xmltable(
   '/DirectionsResponse'
   passing
      httpuritype(
       'maps.googleapis.com/maps/api/directions/xml'
    || '?origin='
       || utl_url.escape('500 Oracle Parkway, Redwood Shores, CA 94065')
    || '&destination='
       || utl_url.escape('747 Howard Street, San Francisco, CA 94103')
    || '&mode=driving'
    || '&alternatives=true'
    || '&units=metric'
    || '&region=us'
    || '&language=en'
    || '&sensor=false'
      ).getxml()
   columns
      response xmltype path '/'
) directions, xmltable(
   'if (fn:empty(/DirectionsResponse/route))
    then 
    else /DirectionsResponse/route'
   passing
      directions.response
   columns
      routenum for ordinality
    , routename varchar2(100) path 'summary'
    , routexml xmltype path '/'
) route, xmltable(
   'for $l in /route/leg return $l'
   passing
      route.routexml
   columns
      legnum for ordinality
    , seconds number path 'duration/value'
    , meters number path 'distance/value'
    , startaddr varchar2(100) path 'start_address'
    , endaddr varchar2(100) path 'end_address'
    , legxml xmltype path '/'
) leg
order by route.routenum
       , leg.legnum
/

The above gives us this output:

ROUTENAME            STARTADDR                      ENDADDR                        LEGTIME    LEGKM
-------------------- ------------------------------ ------------------------------ -------- -------
US-101 N             500 Oracle Pkwy, Redwood City, 747 Howard St, San Francisco,  00:31:35  37,696
                      CA 94065, USA                 CA 94103, USA

I-280 N and US-101 N 500 Oracle Pkwy, Redwood City, 747 Howard St, San Francisco,  00:35:59  45,225
                      CA 94065, USA                 CA 94103, USA

We asked Google for alternatives=true, so it gave us two alternative routes. The xml data gives us routes, which consists of legs, which consists of steps. As we have no waypoints, each route for us consists of only one leg, but each leg has several steps, which we can see in this way:

select route.routename
     , step.instructions
     , to_char(
          to_date(
             to_char(step.seconds,'TM9')
           , 'SSSSS'
          )
        , 'HH24:MI:SS'
       ) steptime
     , step.meters/1000 stepkm
from xmltable(
   '/DirectionsResponse'
   passing
      httpuritype(
       'maps.googleapis.com/maps/api/directions/xml'
    || '?origin='
       || utl_url.escape('500 Oracle Parkway, Redwood Shores, CA 94065')
    || '&destination='
       || utl_url.escape('747 Howard Street, San Francisco, CA 94103')
    || '&mode=driving'
    || '&alternatives=true'
    || '&units=metric'
    || '&region=us'
    || '&language=en'
    || '&sensor=false'
      ).getxml()
   columns
      response xmltype path '/'
) directions, xmltable(
   'if (fn:empty(/DirectionsResponse/route))
    then 
    else /DirectionsResponse/route'
   passing
      directions.response
   columns
      routenum for ordinality
    , routename varchar2(100) path 'summary'
    , routexml xmltype path '/'
) route, xmltable(
   'for $l in /route/leg return $l'
   passing
      route.routexml
   columns
      legnum for ordinality
    , seconds number path 'duration/value'
    , meters number path 'distance/value'
    , startaddr varchar2(100) path 'start_address'
    , endaddr varchar2(100) path 'end_address'
    , legxml xmltype path '/'
) leg, xmltable(
   'for $s in /leg/step return $s'
   passing
      leg.legxml
   columns
      stepnum for ordinality
    , seconds number path 'duration/value'
    , meters number path 'distance/value'
    , instructions varchar2(400) path 'html_instructions'
) step
order by route.routenum
       , leg.legnum
       , step.stepnum
/

The output gives us the step by step driving instructions:

ROUTENAME            INSTRUCTIONS                                                 STEPTIME  STEPKM
-------------------- ------------------------------------------------------------ -------- -------
US-101 N             Head <b>north</b> on <b>Oracle Pkwy</b>                      00:01:41   1,085
US-101 N             Turn <b>right</b> onto <b>Marine Pkwy</b>                    00:00:33    ,056
US-101 N             Merge onto <b>US-101 N</b> via the ramp to <b>San Francisco< 00:23:48  33,605
                     /b>

US-101 N             Take exit <b>433B</b> for <b>Interstate 80</b> toward <b>Bay 00:00:27    ,541
                      Bridge</b>

US-101 N             Merge onto <b>I-80 E</b> (signs for <b>Bay Bridge/Oakland</b 00:00:55   1,111
                     >)

US-101 N             Take exit <b>2</b> for <b>Fourth Street</b>                  00:00:27    ,305
US-101 N             Turn <b>left</b> onto <b>Bryant St</b>                       00:00:41    ,277
US-101 N             Take the 1st <b>left</b> onto <b>3rd St</b>                  00:02:07    ,579
US-101 N             Turn <b>left</b> onto <b>Howard St</b><div style="font-size: 00:00:56    ,137
                     0.9em">Destination will be on the left</div>

I-280 N and US-101 N Head <b>north</b> on <b>Oracle Pkwy</b>                      00:01:41   1,085
I-280 N and US-101 N Turn <b>right</b> onto <b>Marine Pkwy</b>                    00:00:33    ,056
I-280 N and US-101 N Merge onto <b>US-101 N</b> via the ramp to <b>San Francisco< 00:12:10  17,194
                     /b>

I-280 N and US-101 N Take exit <b>423B</b> for <b>Interstate 380 W</b> toward <b> 00:01:20   1,574
                     San Bruno/Interstate 280</b>

I-280 N and US-101 N Merge onto <b>I-380 W</b>                                    00:00:51   1,454
I-280 N and US-101 N Take exit <b>5A</b> for <b>Interstate 280 N</b> toward <b>Sa 00:00:31    ,580
                     n Francisco</b>

I-280 N and US-101 N Merge onto <b>I-280 N</b>                                    00:05:47   9,036
I-280 N and US-101 N Slight <b>right</b> to stay on <b>I-280 N</b>                00:04:16   7,335
I-280 N and US-101 N Take exit <b>54B</b> on the <b>left</b> to merge onto <b>US- 00:03:17   3,961
                     101 N</b> toward <b>San Francisco Civic Center/Bay Bridge</b
                     >

I-280 N and US-101 N Take exit <b>433B</b> for <b>Interstate 80</b> toward <b>Bay 00:00:27    ,541
                      Bridge</b>

I-280 N and US-101 N Merge onto <b>I-80 E</b> (signs for <b>Bay Bridge/Oakland</b 00:00:55   1,111
                     >)

I-280 N and US-101 N Take exit <b>2</b> for <b>Fourth Street</b>                  00:00:27    ,305
I-280 N and US-101 N Turn <b>left</b> onto <b>Bryant St</b>                       00:00:41    ,277
I-280 N and US-101 N Take the 1st <b>left</b> onto <b>3rd St</b>                  00:02:07    ,579
I-280 N and US-101 N Turn <b>left</b> onto <b>Howard St</b><div style="font-size: 00:00:56    ,137
                     0.9em">Destination will be on the right</div>


24 rows selected.

(Google gives us the step instructions in html.)
And finally we can fool around a bit with those data to get nice directions:

with routing as (
   select route.routename
        , leg.startaddr
        , leg.endaddr
        , leg.seconds legseconds
        , leg.meters/1000 legkilometers
        , step.instructions
        , step.seconds stepseconds
        , step.meters/1000 stepkilometers
        , route.routenum
        , leg.legnum
        , step.stepnum
        , row_number() over (
             partition by route.routenum
                        , leg.legnum
             order by step.stepnum desc
          ) stepnumdesc
   from xmltable(
      '/DirectionsResponse'
      passing
         httpuritype(
          'maps.googleapis.com/maps/api/directions/xml'
       || '?origin='
          || utl_url.escape('500 Oracle Parkway, Redwood Shores, CA 94065')
       || '&destination='
          || utl_url.escape('747 Howard Street, San Francisco, CA 94103')
       || '&mode=driving'
       || '&alternatives=true'
       || '&units=metric'
       || '&region=us'
       || '&language=en'
       || '&sensor=false'
         ).getxml()
      columns
         response xmltype path '/'
   ) directions, xmltable(
      'if (fn:empty(/DirectionsResponse/route))
       then 
       else /DirectionsResponse/route'
      passing
         directions.response
      columns
         routenum for ordinality
       , routename varchar2(100) path 'summary'
       , routexml xmltype path '/'
   ) route, xmltable(
      'for $l in /route/leg return $l'
      passing
         route.routexml
      columns
         legnum for ordinality
       , seconds number path 'duration/value'
       , meters number path 'distance/value'
       , startaddr varchar2(100) path 'start_address'
       , endaddr varchar2(100) path 'end_address'
       , legxml xmltype path '/'
   ) leg, xmltable(
      'for $s in /leg/step return $s'
      passing
         leg.legxml
      columns
         stepnum for ordinality
       , seconds number path 'duration/value'
       , meters number path 'distance/value'
       , instructions varchar2(400) path 'html_instructions'
   ) step
), generator as (
   select level num
     from dual
   connect by level <= 2
)
select routing.routename
     , 'Leg '||to_char(routing.legnum,'TM9') legname
     , to_char(
          to_date(
             to_char(
                case
                   when generator.num = 1 then routing.stepseconds
                   when routing.stepnum = 1 then null
                   else routing.legseconds
                end
              , 'TM9'
             )
           , 'SSSSS'
          )
        , 'HH24:MI:SS'
       ) time
     , case
          when generator.num = 1 then routing.stepkilometers
          when routing.stepnum = 1 then null
          else routing.legkilometers
       end km
     , case
          when generator.num = 1 then
             routing.instructions
          when routing.stepnum = 1 then
             '> Start at: '||routing.startaddr
          else
             '> End at: '||routing.endaddr
       end description
  from routing
  join generator
       on routing.stepnum = 1
       or routing.stepnumdesc = 1
       or generator.num = 1
order by routenum
       , legnum
       , stepnum
       , case stepnum
            when 1 then -generator.num
            else generator.num
         end
/

Which gives us this output:

ROUTENAME            LEGNA TIME          KM DESCRIPTION
-------------------- ----- -------- ------- ------------------------------------------------------------
US-101 N             Leg 1                  > Start at: 500 Oracle Pkwy, Redwood City, CA 94065, USA
US-101 N             Leg 1 00:01:41   1,085 Head <b>north</b> on <b>Oracle Pkwy</b>
US-101 N             Leg 1 00:00:33    ,056 Turn <b>right</b> onto <b>Marine Pkwy</b>
US-101 N             Leg 1 00:23:48  33,605 Merge onto <b>US-101 N</b> via the ramp to <b>San Francisco<
                                            /b>

US-101 N             Leg 1 00:00:27    ,541 Take exit <b>433B</b> for <b>Interstate 80</b> toward <b>Bay
                                             Bridge</b>

US-101 N             Leg 1 00:00:55   1,111 Merge onto <b>I-80 E</b> (signs for <b>Bay Bridge/Oakland</b
                                            >)

US-101 N             Leg 1 00:00:27    ,305 Take exit <b>2</b> for <b>Fourth Street</b>
US-101 N             Leg 1 00:00:41    ,277 Turn <b>left</b> onto <b>Bryant St</b>
US-101 N             Leg 1 00:02:07    ,579 Take the 1st <b>left</b> onto <b>3rd St</b>
US-101 N             Leg 1 00:00:56    ,137 Turn <b>left</b> onto <b>Howard St</b><div style="font-size:
                                            0.9em">Destination will be on the left</div>

US-101 N             Leg 1 00:31:35  37,696 > End at: 747 Howard St, San Francisco, CA 94103, USA
I-280 N and US-101 N Leg 1                  > Start at: 500 Oracle Pkwy, Redwood City, CA 94065, USA
I-280 N and US-101 N Leg 1 00:01:41   1,085 Head <b>north</b> on <b>Oracle Pkwy</b>
I-280 N and US-101 N Leg 1 00:00:33    ,056 Turn <b>right</b> onto <b>Marine Pkwy</b>
I-280 N and US-101 N Leg 1 00:12:10  17,194 Merge onto <b>US-101 N</b> via the ramp to <b>San Francisco<
                                            /b>

I-280 N and US-101 N Leg 1 00:01:20   1,574 Take exit <b>423B</b> for <b>Interstate 380 W</b> toward <b>
                                            San Bruno/Interstate 280</b>

I-280 N and US-101 N Leg 1 00:00:51   1,454 Merge onto <b>I-380 W</b>
I-280 N and US-101 N Leg 1 00:00:31    ,580 Take exit <b>5A</b> for <b>Interstate 280 N</b> toward <b>Sa
                                            n Francisco</b>

I-280 N and US-101 N Leg 1 00:05:47   9,036 Merge onto <b>I-280 N</b>
I-280 N and US-101 N Leg 1 00:04:16   7,335 Slight <b>right</b> to stay on <b>I-280 N</b>
I-280 N and US-101 N Leg 1 00:03:17   3,961 Take exit <b>54B</b> on the <b>left</b> to merge onto <b>US-
                                            101 N</b> toward <b>San Francisco Civic Center/Bay Bridge</b
                                            >

I-280 N and US-101 N Leg 1 00:00:27    ,541 Take exit <b>433B</b> for <b>Interstate 80</b> toward <b>Bay
                                             Bridge</b>

I-280 N and US-101 N Leg 1 00:00:55   1,111 Merge onto <b>I-80 E</b> (signs for <b>Bay Bridge/Oakland</b
                                            >)

I-280 N and US-101 N Leg 1 00:00:27    ,305 Take exit <b>2</b> for <b>Fourth Street</b>
I-280 N and US-101 N Leg 1 00:00:41    ,277 Turn <b>left</b> onto <b>Bryant St</b>
I-280 N and US-101 N Leg 1 00:02:07    ,579 Take the 1st <b>left</b> onto <b>3rd St</b>
I-280 N and US-101 N Leg 1 00:00:56    ,137 Turn <b>left</b> onto <b>Howard St</b><div style="font-size:
                                            0.9em">Destination will be on the right</div>

I-280 N and US-101 N Leg 1 00:35:59  45,225 > End at: 747 Howard St, San Francisco, CA 94103, USA

28 rows selected.

And that concludes the demo on getting Google Maps driving directions in a single SQL statement.

In real life in our system this is used to retrieve how long time to drive between our shops. When we plan delivery of fireworks to the shops in late December, we use that information to send estimated time of arrival to the shop keepers. When the truck arrives at a shop along the way, the shop keeper sends an SMS back to the system that delivery 1234 has arrived. The system then checks if it is delayed and in that case sends an SMS to the next shop keeper with a new ETA.

No comments:

Post a Comment