Using SQL to query USGS Data to monitor recent earthquakes

I live in Ohio which is fairly stable as far as earthquakes go; but I have coworkers and friends that live in other, more quake-prone areas of the country.  So, sometimes I like to look up what’s going on in those areas.  Naturally, I decided to see how I could use Oracle features to assist in my monitoring.

First, I needed a reliable source of information.  The U.S. Geological Survey web site is my data source and they have a variety of free data feeds available.  If you’re going to do a lot of data analysis I recommend pulling the data into a local table and saving it rather than hammering their server again and again for each query.

So, while these example show how to access and parse the data, your production use should be to local storage.  You’ll get better performance on repeated access rather than pulling across the internet anyway.

The USGS has a several different formats available.  I’ll be using the Quakeml formatted feed.  Quakeml is simply xml with a predefined schema.  Since I’m not a seismologist I’m only interested in the basic information of when?/where?/how big? and not necessarily all of the minute details like sampling method, uncertainty, etc.

In the examples below I’ll use a rectangular box to define the area I want to examine and I’ll request all of the data for the current and previous 2 days.  For more complete information on constructing other query urls, see the reference here: http://earthquake.usgs.gov/fdsnws/event/1

The service returns a single xml document consisting of multiple event tags.  Each event represents one earthquake.  We’ll extract the text within the following nodes to get our result set.

/q:quakeml/eventParameters/event/description/text
/q:quakeml/eventParameters/event/origin/time/value
/q:quakeml/eventParameters/event/origin/longitude/value
/q:quakeml/eventParameters/event/origin/latitude/value
/q:quakeml/eventParameters/event/origin/depth/value
/q:quakeml/eventParameters/event/magnitude/mag/value

Note text is an xml tag,  not the xquery function text().

The url construction requires some cumbersome syntax but isn’t really all that complicated.  It basically boils down to a base url followed by the parameters for the start and end times (in GMT),  a boundary box of latitude/longitude coordinates, a minimum magnitude and an ordering criteria.

In this case I’m looking for a bounding box that includes the state of Oklahoma, thus yielding a url construction of:

'http://earthquake.usgs.gov/fdsnws/event/1/query.quakeml?starttime='
|| TO_CHAR(FROM_TZ(CAST(TRUNC(SYSDATE - 2) AS TIMESTAMP), 'US/Eastern') AT TIME ZONE 'GMT', 'yyyy-mm-dd')
|| '&endtime='
|| TO_CHAR(SYSTIMESTAMP AT TIME ZONE 'GMT', 'yyyy-mm-dd"%20"hh24:mi:ss')
|| '&maxlatitude=37&minlatitude=33&maxlongitude=-94&minlongitude=-103'
|| '&minmagnitude=0&orderby=time'

Using the HTTPURITYPE function we can read the USGS site for that url and extract the xml.  Using the XMLTABLE we can then extract each of the event nodes into individual rows and parse out the values we’re interested in.

SELECT FROM_TZ(TO_TIMESTAMP(time, 'yyyy-mm-dd"T"hh24:mi:ssxff"Z"'), 'GMT') 
            AT TIME ZONE 'US/Central' quake_time,
       latitude,
       longitude,
       magnitude,
       depth_in_meters,
       location_name,
       XMLSERIALIZE(DOCUMENT eventxml AS CLOB INDENT SIZE = 3)
  FROM XMLTABLE(
           xmlnamespaces(DEFAULT 'http://quakeml.org/xmlns/bed/1.2',
                         'http://anss.org/xmlns/catalog/0.1' AS "catalog",
                         'http://quakeml.org/xmlns/quakeml/1.2' AS "q"),
           '/q:quakeml/eventParameters/event'
           PASSING httpuritype(
                          'http://earthquake.usgs.gov/fdsnws/event/1/query.quakeml?starttime='
                       || TO_CHAR(FROM_TZ(CAST(TRUNC(SYSDATE - 2) AS TIMESTAMP), 'US/Eastern')
                                AT TIME ZONE 'GMT','yyyy-mm-dd"')
                       || '&endtime='
                       || TO_CHAR(SYSTIMESTAMP AT TIME ZONE 'GMT', 'yyyy-mm-dd"%20"hh24:mi:ss')
                       || '&maxlatitude=37&minlatitude=33&maxlongitude=-94&minlongitude=-103'
                       || '&minmagnitude=0&orderby=time').getxml()
           COLUMNS 
              eventxml XMLTYPE PATH '.',
              time VARCHAR2(100) PATH './origin/time/value',
              longitude NUMBER PATH './origin/longitude/value',
              latitude NUMBER PATH './origin/latitude/value',
              magnitude NUMBER PATH './magnitude/mag/value',
              depth_in_meters NUMBER PATH './origin/depth/value',
              location_name VARCHAR2(100) PATH './description/text')

Since Oklahoma is in US/Central timezone I’ll extract the data in time local to the events.

Running the query above yields a result set like the following…