oracle| Design | data | database | Database design
Requirements Prerequisite: Data changes 1~2 times per year, software estimated life cycle 50
Practice Environment: Oracle Spatial
Glossary: spatio-temporal objects, snapshots
Spatio-Temporal object: The space and time range and its attribute information that a geographical goal experiences from birth to extinction. This is a concept that is abstracted for the convenience of processing spatio-temporal data, which is equivalent to the whole information object in the whole information model. In order to understand space-time objects, we take two-dimensional feature as an example. Two dimensional objects occupy a certain space in two-dimensional space, which is represented as a two-dimensional geometry, which is its geometrical property. If a time axis is added to the (x,y,t) coordinate system, and any space object is considered to be born and extinct, the two-dimensional feature occupies a certain three-dimensional space in the (x,y,t) coordinate system. We will take the two-dimensional features from the birth to the extinction of the space occupied by this time period, called "Space-Time object." Similarly, spatiotemporal objects can be extended to three-dimensional objects in the time dimension, that is, four-dimensional objects. The attribute information of spatio-temporal objects is a two-dimensional table.
Snapshot: The spatial range and attribute information occupied by a geographical target at a particular time, or the spatial range and corresponding attribute information occupied by space-time object at a particular time. For example, a map of the location of a hotel, size, this is a hotel snapshot. Because the hotel is born from a moment, and will perish in a moment, and in the birth and death, may also move or expand. So the map reflects just the state of the hotel at some point, so call it a snapshot of the hotel at a certain moment or a snapshot of the space-time object at a particular moment. Note that snapshots always correspond to a particular time, or they will lose the meaning of the snapshot. The snapshot's property information is a record in the spatio-Temporal object property Information table.
Design direction:
Spatial data and attribute data are organized by the concept of spatio-temporal objects, which makes it possible to query and analyze the history of feature change.
The process of the application layer shielding historical data is treated as one of the functions of database module.
Technical points:
1. Session Independence of Oracle package variables
2, Dynamic View technology (based on the function of the view)
Graphic Explanation:
Time
Space
Object Birth Time
Observe the direction
Current observation time
Object Extinction Time
Snapshot 3
Snapshot 2
Snapshot 1
Spatio-Temporal objects (object one)
Object Three
Object Two
As you can see from the diagram above, each space-time object is described by multiple snapshot records. Each observation of the observer is based on a specific observation time. For example, an observer in the diagram should see snapshot 2 of Object one and snapshot 2 of object three, but not other snapshot records for object Two and object one or three. Object Two is not visible because object two has died out before the time of observation. The snapshot of object one is not visible 1 because an object with an updated observation data snapshot 2 can better approximate the state of object two at the current observation time.
Datasheet: Data table design based on the above concepts
Object Table
obj_id
Number
Spatio-Temporal Object identification number
<PK>
Start_time
DATE
Object Birth Time
End_time
DATE
Object Extinction Time
Obj_attr
VARCHAR2 (100)
object property Data
Attribute information that will change as time passes.
Snapshot record table
obj_id
Number
Spatio-Temporal Object identification number
<FK>
snap_id
Number
Unique ordinal number of the snapshot data
<PK>
Snap_time
DATE
Snapshot time
Snap_end
DATE
Snapshot End Time
The snapshot time of the most recent next snapshot belonging to the same space-time object. This column is set up for easy query purposes.
Geom.
Mdsys. Sdo_geometry
Snapshot geometry data
Attr
VARCHAR2 (100)
Snapshot property data
Attribute information that will change as time passes.
Stored procedures:
Using session independence for package variables in Oracle, the following basic stored procedures are established in the package: set the current observation time, get the current observation time, and use the current observation time and the incoming snap_time,snap_end parameter to determine whether the snapshot record is visible (Snap_filter).
Views: A view built on the snapshot record table that filters the records in the table based on the storage function Snap_filter. The incoming parameter is: Snap_time,snap_end., the return value is visible for this snapshot.
SELECT obj_id, Snap_time, Geom, Attr
From snapshot record table
WHERE Snap_filter (Snap_time, snap_end) > 0;
obj_id
Number
Spatio-Temporal Object identification number
Snap_time
DATE
Snapshot time
Geom.
Mdsys. Sdo_geometry
Snapshot geometry data
Attr
VARCHAR2 (100)
Snapshot property data
Trigger:
A trigger on the view so that the application-tier user can insert, modify, and delete data in the view.
Important: 1, the maintenance of data in the table to avoid the occurrence of time paradox, resulting in confusion in time logic.
2, the snapshot record table of the Snap_end column maintenance slightly troublesome, should be cautious.
Expansion and change:
1, support a variety of observation mode.
In order to query the flexibility of functionality, you can add several extended functions stored procedures: Set the current observation mode, get the current observation mode, rewrite the Snap_filter function to support a variety of observation patterns.
For example:
The object's historical retrospective mode is not valid at this time, and the behavior of the snap_filter changes, and the view sees all the snapshots of a specified object (or several). This model facilitates the tracking of the entire historical process of a single object.
The time period observation mode needs to be set at this time not a point in time, but a time period. Snap_filter's return value also has a richer meaning, and can be expressed in different numbers (for example: 1,2,4) to indicate that a snapshot record starts at the current time period, the end, and the middle visibility. This pattern can be used for situations where data that needs to be compared to two different points in time.
2, increase the Object Relational table to describe the relationship between space-time objects
There may be a variety of relationships between space-time objects, such as change, integration, division, and so on. Can be described in the following table.
Spatio-Temporal Object Relational table
Obj_1
Number
Spatio-Temporal Object identification number
<PK><FK>
Obj_2
Number
Spatio-Temporal Object identification number
<PK><FK>
Relation_type
Number
Relationship Type
<PK>
How to use:
If you are querying for the latest information, that is, the viewing time is the system current time, you can access the view directly.
If you are querying historical data, you can simply call a stored procedure in a data connection session to set the current observation time, and the effect is that the collection of records visible in all views is a record that is visible at the current observation point, and other records do not appear in the view.
