The conceptual model described in section ‘Data model for dynamic information’ is translated to a logical database schema for Oracle, followed by another translation to SQL (Structured Query Language) scripts for creating the actual tables in Oracle Spatial. We performed automatic translations within Enterprise Architect. The result of each translation was modified in order to get the correct model for the corresponding level.
Oracle Spatial database schema
Figure 5 shows the logical database schema. The label PK in front of a column name indicates that it is (part of) a primary key, FK indicates that it is a foreign key, pfK indicates that it is (part of) a primary key that is also a foreign key, and * indicates that the column cannot be empty. For each class in the conceptual level, there is a corresponding table in the logical level. All the one-to-one and one-to-many associations are resolved by primary key—foreign key relations; a new table is created for the many-to-many associations.
The automatic translation does not resolve correctly association classes, dependencies and their identifiers, identifiers consisting of more than one attribute, and specific data types. For example, the association class InvolvedIn between DMSUser and Process is translated to a table resolving the many-to-may association and another separate table containing the attributes, which has no relations to the concerned tables. We replaced the two tables with a new table, UserInProcess, that is a merge of the two separate tables. Classes Sectormal, Gasmal and Casualty that are dependent on RealIncident are converted to tables that have no relation with the RealIncident. The identifier of RealIncident ought to be the identifier for each of these tables. Instead, the translation adds a superfluous attribute to each table as its identifier. The identifier of Process is the attribute pair (incidentID, process_type), while the automatic translation has added the superfluous attribute processID as the primary key of the table. The translation assumes a primary key attribute named after the table name, and adds the attribute in case there is no such. For example, EventObject contains an attribute objectID as identifier, whereas the translation added an attribute eventObjectID as primary key for the table. Several data types in the data model require special treatment, e.g. the spatial types, enumeration types, and boolean. The spatial types were changed to MDSYS.SDO_GEOMETRY, which is the spatial type of Oracle Spatial for all point, line, polygon and solid types. The enumeration types were changed to NUMBER(); the numeric values are used as codes that refer to values in the enumeration list. The boolean types were changed to CHAR(1), and a check constraint is added to restrict values to true and false.
The logical schema produced by the automatic translation was corrected for all the above-mentioned problems. Afterward, a model transformation was applied within Enterprise Architect to the corrected logical schema. This second transformation translates the logical model to SQL scripts for creating the actual tables in Oracle Spatial. The automatic generation cannot handle the enumeration lists. Also, the new data types need special treatment. We implemented the enumeration lists as (look-up) tables in Oracle. For example, the attribute process_type takes values from a look-up table Process_Type containing information for all the processes. The table Process_Type is created from:
It is then filled with information for all the processes. Values of process_type attribute are constrained to codes available in Process_Type table, by its declaration inside the Process table:
The new data types were added manually to the SQL scripts. For example, the patient card uses the new data type Exposure, which is created by:
Very important for the model are the temporal and spatiotemporal data types: DYNAMIC_NUM for dynamics counts, e.g. number of missing people; MOVING_POINT for dynamic points, e.g. position of a team in the field; and MOVING_REGION, e.g. the gas plume.
The general approach for temporal data in DBMS is to consider all the records as facts, which can be associated with time when the facts are valid (time stamps). Time stamps are added at the record (object) level or at the attribute level. This is the common approach used also by the most GIS or CAD/AEC systems. Several temporal models have been proposed; some of the models store change at the instant it occurs, others store the period during which a fact or a database state exists (Zaniolo et al. 1997). Classical research on spatiotemporal databases has focused on discrete changes: sporadic events (volcano eruptions and earthquakes), stepwise constant changes (headquarter of field operational team); later research concentrates on continuous changes and uses the term moving objects (Güting et al. 2000; Güting and Schneider 2005). The latter is followed in this implementation. The value of MOVING_POINT attribute is stored as a sequence of pairs point location and time, and a MOVING_REGION as a sequence of pairs polygon geometry and time. Different interpolation techniques can be used for calculating point position and polygon shape for any moment of time (see Meratnia (2005) and Meratnia and de By (2003) for moving point interpolation).
We use nested tables in Oracle for the dynamic types. For example, to create MOVING_POINT type, first an object type is created containing a time instance and point location, which is then used to build the sequence as a table of such objects:
These new data types are used in tables containing attributes of a dynamic nature. For example, Team table has an attribute position that is a moving point. The SQL command that creates the Team table:
provides as well for an internal storage table TeamPosition, where the data of the nested table column are placed. The following section discusses about the storage, analysis and visualisation of spatiotemporal data.
Spatiotemporal data visualisation and analysis
A spatiotemporal attribute can be filled by adding values (point location or polygon shape) for each time instant one by one, or a block of values at once. Suppose we have GPS logs for the position of a team in the field and have put the data in a temporary table GPStrack, which contains:
To insert the data from this flat table into the Team table in our model, e.g. for team with ID = 16, the following statement is executed:
Each individual record of a spatiotemporal attribute can be accessed by performing an un-nesting of the table. For example, the following statement returns the trajectory of team 16 that was entered above.
For the purpose of visualisation with web services (Scholten et al. 2008), spatiotemporal data are converted to spatial data. We create views to perform an un-nesting of tables that turns the spatiotemporal types to spatial types, which can be accessed by any standard Web service or commercial software. Metadata are added for the spatial attributes created from un-nesting in order to visualise the data. Figure 6a shows the (dynamic) positions of six teams in the field by visualising data from the view TeamTracking that is created from the Team table by:
which structure is
The spatiotemporal data are to be used in the analysis that is performed to help the decision-making process during an emergency. For example, an AGS adviser (for dangerous substances) needs to know the positions of measurement teams in the last two hours. This requires analysis of dynamic data: selecting the positions of measurement teams (\(\texttt{TEAM\_TYPE} = 2\)) since 2 h from the actual time. Figure 6a shows the trajectories of six teams in the field, and Fig. 6b the positions of the measurement teams (12 and 14) in the last two hours.
Analysis usually requires combination of existing data with the dynamic data, which in turn requires combination of spatial functionality (on static data) with spatiotemporal functionality. For example, to organise the evacuation procedure during an incident the command and control centre needs information about the threatened area (dynamic data), residential buildings and the number of registered people in these buildings (static data).
Spatiotemporal operators are to be build on the spatiotemporal types introduced in ‘Spatiotemporal data visualisation and analysis’ to provide the functionality for data analysis. These functions should provide for resolving typical queries during the emergency response, e.g. find police vehicles that are in a radius of 5 km from the incident; which police car is the closest to the incident; calculate the route for an ambulance, which does not overlap with the gas plume; evaluate the evacuation area for the next 8 h from the gas plume shape and its prediction.