SQL extension for spatio-temporal data

1. SQL extension for spatio-temporal data Authors: Jose R Rios Viqueira Nikos A. Lorentzos Presentation: Thomas Heidt

2. Key Terms • Spatial Data – the data or information that identifies the geographic location of features and boundaries of the earth, such as natural or constructed features or oceans. • Geographic Information Systems (GIS) - Tool that allow users to create interactive queries, analyze spatial information, edit data, maps, and present the results of all these operations.

3. Overview • Overcome current system limitation • SQL extension is formalized for the management of spatio-temporal data • The extension is dedicated to applications such as topography, cartography and cadastral systems. • Based on the formulation of data types and of SQL constructs • Designed to consider discrete changes both in space and in time.

4. Data Types • Defined in terms of time and spatial quanta • A minimum set of data types is supported: Two generic types for time (instant, period), three types for space (point, pure line, and pure surface), and two more, line (either a pure line or a point) and surface (either a pure surface or a line). • Hybrid Surfaces, composed of pure surfaces connected by pure lines, are valid spatial objects.

5. Quanta & Data Types for Time • Instant – Time Quanta – Ordered Sequence of Time • Period – Time Frame of Interest - Subset of Instant

6. Spatial Quanta & Data Types • Provides a rigid formalization of the spatial data types • All data types have been defined as sets. This makes it possible to define set operations on any two distinct types making all the spatial objects spatially compatible.

7. Spacial Quanta & Data Types • Points: P0, P1, …, P168 • Pure Lines: (i), (ii), (iv) and (v) • Lines: Any of the previous pure lines and points • Pure Surfaces: (iii), (vi) and (vii) • Surfaces: Any of the above surfaces, any of the above line objects and object (viii)

8. Relational Algebra • Relational operations are based on the defined data structures • Fold & Unfold operations

9. Relational Algebra • Example of a spatio-temporal relation recording the evolution of a spatial object, Morpheas, with respect to time

10. Relational Algebra • Fold & Unfold on a space & time attributes • UH2=Unfold[Shape, Time](H1) • H = Fold[Shape, Time](UH2)

11. Relational Algebra • Normalise [TG](R) ≡ Fold[TG](Unfold [TG](R)) • H = Normalise [Shape, Time](H1)

12. SQL Extension • SQL constructs are defined in terms of a kernel of few relational algebra operations and Fold & Unfold

13. Query Specification • SELECT [<set quantifier>] <select list> • FROM <table ref list> • [WHERE <search condition>] • [GROUP BY <grouping column ref list>] • [HAVING <search condition>] • [<reformat clause>] • [<normalise clause>] • [ORDER BY <sort spec list>]

14. Non-Join Query Expression • The syntax of the SQL:2003 non-join query expression, has been extended to support two sets of binary operations, namely quantum and pairwise operations

15. Spatio-Temporal Relations

16. Quantum Operations SELECT Shape, Time FROM LAND_USE WHERE Use = ‘Industrial’ SELECT Shape, Time FROM P_OWNER WHERE Owner = ‘Susan’ NORMALISE ON Shape,Time Then the expression: SELECT * FROM I UNION EXPANDING (Shape, Time) SELECT * FROM S

17. Quantum Operations

18. Pairwise Operations SELECT Use, Shape, Time FROM LAND_USE WHERE Use = ’Industrial’ SELECT Owner, Shape, Time FROM P_OWNER WHERE Owner = ’Susan’ or Owner =’Peter’ NORMALISE ON Shape,Time Then the expression SELECT * FROM I WUNION OF (Shape, Time) SELECT * FROM S

19. Pairwise Operations

20. Unary Query Expression

21. Advantages Disadvantages • Spatial compatibility of all objects • Single operation results • Reduction in processing and resources • The syntax and semantics of the extension is fully consistent with the SQL:2003 standard • Implementation and Support

22. Wrap Up • A SQL extension for the management of spatio-temporal data has been developed, whose definition has been based on a formal extension of the relational model. • An efficient implementation should consider storage structures, optimization techniques, and an appropriate mapping to vector based approaches.

23. Questions