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:
And here's the (shortened) output of that query:
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:
The above gives us this output:
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:
The output gives us the step by step driving instructions:
(Google gives us the step instructions in html.)
And finally we can fool around a bit with those data to get nice directions:
Which gives us this output:
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.
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' ||
'®ion=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'
|| '®ion=us'
|| '&language=en'
|| '&sensor=false'
).getxml()
columns
response xmltype path '/'
) directions, xmltable(
'if (fn:empty(/DirectionsResponse/route))
then
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'
|| '®ion=us'
|| '&language=en'
|| '&sensor=false'
).getxml()
columns
response xmltype path '/'
) directions, xmltable(
'if (fn:empty(/DirectionsResponse/route))
then
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'
|| '®ion=us'
|| '&language=en'
|| '&sensor=false'
).getxml()
columns
response xmltype path '/'
) directions, xmltable(
'if (fn:empty(/DirectionsResponse/route))
then
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.
Comments
Post a Comment