stds_tables_template.sql 3.6 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657
  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.
  4. --
  5. -- Author: Soeren Gebbert soerengebbert <at> googlemail <dot> com
  6. --#############################################################################
  7. -- STDS is a placeholder for specific space-time dataset type: strds, str3ds, stvds
  8. --PRAGMA foreign_keys = ON;
  9. CREATE TABLE STDS_base (
  10. id VARCHAR NOT NULL, -- Id of the space-time dataset, name@mapset this is the primary key
  11. name VARCHAR NOT NULL, -- name of the space-time dataset
  12. mapset VARCHAR NOT NULL, -- mapset of the space-time dataset
  13. creator VARCHAR NOT NULL, -- Name of the creator
  14. temporal_type VARCHAR NOT NULL, -- The temporal type of the dataset "absolute" or "relative"
  15. semantic_type VARCHAR NOT NULL, -- The semantic data description used for aggregation/decomposition algorithm selection: min, max, mean or sum
  16. creation_time TIMESTAMP NOT NULL, -- The time of creation of the space-time dataset
  17. -- Uncommented due to performance issues
  18. -- modification_time TIMESTAMP NOT NULL, -- The time of the last modification of the grass map
  19. -- revision SMALLINT NOT NULL, -- The revision number -- The revision number
  20. PRIMARY KEY (id)
  21. );
  22. CREATE TABLE STDS_relative_time (
  23. id VARCHAR NOT NULL, -- Id of the space-time dataset, this is the primary foreign 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
  29. FOREIGN KEY (id) REFERENCES STDS_base (id) ON DELETE CASCADE ON UPDATE CASCADE
  30. );
  31. CREATE TABLE STDS_absolute_time (
  32. id VARCHAR NOT NULL, -- Id of the space-time dataset, this is the primary foreign key
  33. start_time TIMESTAMP, -- Start of the valid time, can be NULL if no map is registered
  34. end_time TIMESTAMP, -- End of the valid time, can be NULL if no map is registered
  35. granularity VARCHAR, -- The granularity "NNN seconds, NNN minutes, NNN hours, NNN days, NNN months, NNN years"
  36. timezone VARCHAR, -- The timezone of the valid time stored as string. This is currently not in use. Instead the timezone is set in the datetime strings
  37. map_time VARCHAR, -- The temporal type of the registered maps, may be interval, point or mixed
  38. FOREIGN KEY (id) REFERENCES STDS_base (id) ON DELETE CASCADE ON UPDATE CASCADE
  39. );
  40. CREATE TABLE STDS_spatial_extent (
  41. id VARCHAR NOT NULL, -- Id of the space-time dataset, this is the primary foreign key
  42. north DOUBLE PRECISION, -- The spatial north extent, derived from the registered maps
  43. south DOUBLE PRECISION, -- The spatial south extent, derived from the registered maps
  44. east DOUBLE PRECISION, -- The spatial east extent, derived from the registered maps
  45. west DOUBLE PRECISION, -- The spatial west extent, derived from the registered maps
  46. top DOUBLE PRECISION, -- The spatial top extent, derived from the registered maps
  47. bottom DOUBLE PRECISION, -- The spatial bottom extent, derived from the registered maps
  48. proj VARCHAR, -- The projection of the space time dataset (XY of LL)
  49. FOREIGN KEY (id) REFERENCES STDS_base (id) ON DELETE CASCADE ON UPDATE CASCADE
  50. );