Tuesday, April 11, 2006

Consuming REST services with Oracle

I love REST services (representation state transfer). They provide a powerful yet simple way of consuming data via the web (Some examples of REST services: Yahoo, some ArcWeb Services, Mapdex API, Flickr, even WMS services). Here is an example of one I made up.

I want to be able to query the National Elevation Dataset (via http://seamless.usgs.gov ) to find out what the elevation is at a particular latitude, longitude coordinate. I want to submit a geographic point and extract the elevation value at the point. USGS is publishing this as an ArcIMS image service (USGS_EDC_Elev_NED). I can use the get_raster_info request to extract the elevation at a particular point.

Here is my test REST service:


You can view the source code here.

It queries the service and reformats the ArcIMS response to the following XML output:

<ELEVATION_LOCATION latitude="38" longitude="-115">

In order to access this document in Oracle we need to take advantage of two features. One is the utl_http package (see AskTom or the Oracle docs for more info) to request an html page via a url.

This query will actually return the text/xml response from the server as a 4000 character string to the sqlplus window (use request_pieces for larger files).

We then have to turn this character representation into an xml document so we can query it.

To do this we can use the xmltype datatype (an Oracle system defined datatype) that allows us to parse an xml document and query it via xpath.

Since my REST service returns an xml document all we have to do is store the xml document as an xmltype datatype and use xpath to query the xml document to retrieve the relevant values.

select a.xmldataout.extract
as elevation_feet,
as elevation_meters
from (select
) xmldataout from dual) a
where a.xmldataout.extract('//KANSAS_ELEVATION/ERROR/text()').getStringVal() is null

Which returns...
-------------- ----------------
2886.25208 879.686707

Do you see the power? I can do this for single point or as an update to an entire table.

So now I can map the elevation along my run route...

Comments: Post a Comment

<< Home

This page is powered by Blogger. Isn't yours?