1
--#############################################################################
2
-- This SQL script generates the space time dataset tables to store time
3
-- stamps and revision for SQL queries and temporal GIS support.
5
-- Author: Soeren Gebbert soerengebbert <at> googlemail <dot> com
6
--#############################################################################
8
-- STDS is a placeholder for specific space-time dataset type: strds, str3ds, stvds
10
CREATE TABLE STDS_base (
11
id VARCHAR NOT NULL, -- Id of the space-time dataset, name@mapset this is the primary key
12
name VARCHAR NOT NULL, -- name of the space-time dataset
13
mapset VARCHAR NOT NULL, -- mapset of the space-time dataset
14
creator VARCHAR NOT NULL, -- Name of the creator
15
temporal_type VARCHAR NOT NULL, -- The temporal type of the dataset "absolute" or "relative"
16
semantic_type VARCHAR NOT NULL, -- The semantic data description used for aggregation/decomposition algorithm selection: min, max, mean or sum
17
creation_time TIMESTAMP NOT NULL, -- The time of creation of the space-time dataset
18
modification_time TIMESTAMP NOT NULL,-- The time of the last modification of the space time dataset
22
CREATE TABLE STDS_relative_time (
23
id VARCHAR NOT NULL, -- Id of the space-time dataset, this is the primary key
24
start_time INTEGER, -- The relative valid start time
25
end_time INTEGER, -- The relative valid end time
26
granularity INTEGER, -- The granularity
27
unit VARCHAR, -- The relative time unit, available are "years, months, days, minutes, seconds"
28
map_time VARCHAR, -- The temporal type of the registered maps, may be interval, point or mixed
32
CREATE TABLE STDS_absolute_time (
33
id VARCHAR NOT NULL, -- Id of the space-time dataset, this is the primary key
34
start_time TIMESTAMP, -- Start of the valid time, can be NULL if no map is registered
35
end_time TIMESTAMP, -- End of the valid time, can be NULL if no map is registered
36
granularity VARCHAR, -- The granularity "NNN seconds, NNN minutes, NNN hours, NNN days, NNN months, NNN years"
37
map_time VARCHAR, -- The temporal type of the registered maps, may be interval, point or mixed
41
CREATE TABLE STDS_spatial_extent (
42
id VARCHAR NOT NULL, -- Id of the space-time dataset, this is the primary key
43
north DOUBLE PRECISION, -- The spatial north extent, derived from the registered maps
44
south DOUBLE PRECISION, -- The spatial south extent, derived from the registered maps
45
east DOUBLE PRECISION, -- The spatial east extent, derived from the registered maps
46
west DOUBLE PRECISION, -- The spatial west extent, derived from the registered maps
47
top DOUBLE PRECISION, -- The spatial top extent, derived from the registered maps
48
bottom DOUBLE PRECISION, -- The spatial bottom extent, derived from the registered maps
49
proj VARCHAR, -- The projection of the space time dataset (XY of LL)