Model a temporal, event driven data source in Oracle

Summary

Instructions provided describe how to model a data source in Oracle that has a temporal aspect and is accessed using a window query (a query that defines a beginning and ending moment in the where clause).

For example, a wildlife organization collects spatial and attribute data for migratory birds each week. The event being monitored is the weekly movement of the flock and is captured and recorded every Tuesday. The organization maintains a feature class that represents the flocks location, attributes describing the flock, and a timestamp (a date attribute named event_week) when the event is recorded (manually in the database each Sunday evening).

Next, the organization's objective is to publish the flock's current location and the last four weeks of historical data as individual layers in an ArcGIS Server application. This allows the bird-watching community to easily view the flocks migratory path over time as individual layers.

The following procedure outlines how an organization can define their workflow for maintaining the data and how to publish a Map service containing the data source as five distinct layers representing the current location and the previous four weeks of information (where each layer is referencing the same feature class).

Procedure

1. Delete the oldest week's data. This can be accomplished using ArcEditor, ArcObjects, or SQL directly.
   
   For example, using SQL in SQL*Plus, the owner of the data can delete the oldest records in the feature class using a date attribute as a filter in the DELETE statement.
   
   

   Code:
   SQL> DELETE FROM flock WHERE event_week < ROUND(SYSDATE - 28,'DAY');

   
   The Oracle ROUND function returns a date value rounded to the unit specified in the format argument. In this case, the date value is the current SYSDATE minus 28 days rounded to the starting day of the week by specifying the 'DAY' argument. Therefore, executing this statement deletes all rows where the event_week is older than four weeks.
   
   

   Note:
   For additional information on the Oracle ROUND function, see the Oracle documentation.

   
   
   

   Note:
   The intention of the article is not to provide specific examples on deleting data, but a workflow that meets the requirements of working with temporal data. See the ArcGIS documentation for specific details on deleting data with ArcEditor or ArcObjects.

   
   
2. Load the current week's data. Again, this process can be accomplished using various tools such as ArcCatalog's data loaders, ArcToolbox, ArcEditor, ArcObjects or SQL directly (if the spatial attribute is a native spatial type in the DBMS).
   
   

   Note:
   The intention of the article is not to provide specific examples on data loading, but a workflow that meets the requirements of working with temporal data. See the ArcGIS documentation for specific details on inserting and loading data.

   
   
3. Create a map document to publish with ArcGIS Server. The map document contains the feature class flock, added to the map five times as a layer with a definition query used to specify what data is displayed.
   
   Since the feature class flock contains all the historical data, definition queries must be added to each layer in ArcMap to be used as a filter for representing just the interested data.
   
   The following five clauses represent the definition queries for each of the five layers in ArcMap.
   
   To represent the current week's data, the definition query states where all event_week values are greater than the current SYSDATE (current time) rounded to the first day of the week (Sunday). Therefore, no matter what day of the week the user is accessing the Map service, only the latest rows from the data source are returned.
   
   

   Code:
   /* Current week */
   event_week >= ROUND(SYSDATE,'DAY')

   
   
   To display the previous week's historical data, the definition query is changed to a range by specifying the filter to start at the beginning of the previous week up to the start of the current week. Again, by using the Oracle ROUND function and subtracting 7 days, the date for the filter is the previous Sunday.
   
   

   Code:
   /* Previous week */
   event_week >= ROUND(SYSDATE - 7,'DAY') AND event_week < ROUND(SYSDATE,'DAY')

   
   
   To represent the flock's location two weeks ago, subtract 14 days in the first ROUND function and 7 days in the second ROUND function.
   
   

   Code:
   /* 2 weeks ago */
   event_week >= ROUND(SYSDATE - 14,'DAY') AND event_week < ROUND(SYSDATE - 7,'DAY')

   
   
   To represent the flock's location three weeks ago, simply subtract 21 days in the first ROUND function and 14 days in the second ROUND function.
   
   

   Code:
   /* 3 weeks ago */
   event_week >= ROUND(SYSDATE - 21,'DAY') AND event_week < ROUND(SYSDATE - 14,'DAY')

   
   
   Finally, to represent the historical data from the fourth week, subtract 28 days in the ROUND function. There is no need for a second ROUND function because the objective is to retrieve all data older than fours weeks.
   
   

   Code:
   /* 4 weeks ago */
   event_week < ROUND(SYSDATE - 28,'DAY')

   
   Now that the map document has been created, save the document, and publish the map document as an ArcGIS Map service.