Database Management Objectives of Lecture 10 Systems Spatial Data Management Spatial Data Management • Discuss limitations of the relational data model and briefly introduce the Extended- Winter 2004 Relational Model. CMPUT 391: Spatial Data Management • This lecture will give you a basic understanding of spatial data management Dr. Osmar R. Zaïane – What is special about spatial data – What are spatial queries – How do typical spatial index structures work University of Alberta 1 2 Dr. Osmar R. Zaïane 2002-2003 CMPUT 391 – Database Management Systems University of Alberta Dr. Osmar R. Zaïane 2002-2003 CMPUT 391 – Database Management Systems University of Alberta The Need for a DBMS Spatial Data Management • On one hand we have a tremendous increase in the amount of data applications have to handle, on the other hand we want a reduced application • Shortcomings of Relational Databases development time. • Modeling Spatial Data – Object-Oriented programming • Spatial Queries – DBMS features: query capability with optimization, concurrency control, recovery, indexing, etc. • Space-Filling Curves + B-Trees • Can we merge these two to get an object database • R-trees management system since data is getting more complex? Dr. Osmar R. Zaïane 2002-2003 CMPUT 391 – Database Management Systems University of Alberta 3 Dr. Osmar R. Zaïane 2002-2003 CMPUT 391 – Database Management Systems University of Alberta 4
What are the Needs? Manipulating New Kinds of Data • Images • A television channel needs to store video sequences, • Video radio interviews, multimedia documents, geographical • Multimedia in general information, etc., and retrieve them efficiently. • Spatial data (GIS) • A movie producing company needs to store movies, • Biological data frame sequences, data about actors and theaters, etc. • CAD data (textbook example) • Virtual Worlds • A biological lab needs to store complex data about • Games molecules, chromosomes, etc, and retrieve parts of data • List of lists as well as complete data. • User defined data types • Think about NHL data and commercial needs. 5 6 Dr. Osmar R. Zaïane 2002-2003 CMPUT 391 – Database Management Systems University of Alberta Dr. Osmar R. Zaïane 2002-2003 CMPUT 391 – Database Management Systems University of Alberta Shortcomings with RDBMS Existing Object Databases • Supports only a small fixed collection of relatively • Object database is a persistent storage manager for simple data types ( integers, floating point numbers, date, strings ) objects: • No set-valued attributes (sets, lists,…) – Persistent storage for object-oriented programming • No inheritance in the Is-a relationship languages (C ++ , SmallTalk,etc.) • No complex objects, apart from BLOB (binary – Object-Database Systems: large object) and CLOB (character large object) • Object-Oriented Database Systems: alternative to relational • Impedance mismatch between data access systems language (declarative SQL) and host language • Object-Relational Database Systems: Extension to relational (procedural C or Java): programmer must systems explicitly tell how things to be done. • Market: RDBMS ( $8 billion), OODMS ($30 million) world-wide � Is there a different solution? • OODB Commercial Products: ObjectStore, GemStone, Orion, etc. Dr. Osmar R. Zaïane 2002-2003 CMPUT 391 – Database Management Systems University of Alberta 7 Dr. Osmar R. Zaïane 2002-2003 CMPUT 391 – Database Management Systems University of Alberta 8
Object-Relational Features of Oracle DBMS Classification Matrix Methods CREATE TYPE Rectangle_typ AS OBJECT ( Relational Object-Relational Query len NUMBER, DBMS DBMS wid NUMBER, MEMBER FUNCTION area RETURN NUMBER, Object-Oriented ); DBMS No Query CREATE TYPE BODY Rectangle_typ AS File System MEMBER FUNCTION area RETURN NUMBER IS BEGIN RETURN len * wid; END area; Simple Data Complex Data END; 9 10 Dr. Osmar R. Zaïane 2002-2003 CMPUT 391 – Database Management Systems University of Alberta Dr. Osmar R. Zaïane 2002-2003 CMPUT 391 – Database Management Systems University of Alberta Spatial Data Management Object-Relational Features of Oracle Collection types / nested tables CREATE TYPE PointType AS OBJECT ( • Shortcomings of Relational Databases x NUMBER, • Modeling Spatial Data y NUMBER); CREATE TYPE PolygonType AS TABLE OF PointType; • Spatial Queries CREATE TABLE Polygons ( • Space-Filling Curves + B-Trees name VARCHAR2(20), points PolygonType) • R-trees NESTED TABLE points STORE AS PointsTable; The relations representing individual polygons are not stored directly as values of the points attribute; they are stored in a single table, PointsTable Dr. Osmar R. Zaïane 2002-2003 CMPUT 391 – Database Management Systems University of Alberta 11 Dr. Osmar R. Zaïane 2002-2003 CMPUT 391 – Database Management Systems University of Alberta 12
Relational Representation of Spatial Data Relational Representation of Spatial Data • Example : Representation of geometric objects (here: parcels/fields of land) • For (spatial) queries involving parcels it is necessary to reconstruct in normalized relations the spatial information from the different tables Borders Parcels Points – E.g.: if we want to determine if a given point P is inside parcel F 2 , we FNr BNr BNr PNr 1 PNr 2 have to find all corner-points of parcel F 2 first PNr X-Coord Y-Coord F 5 F 2 F 1 B 1 B 1 P 1 P 2 Y P1 P 1 X P1 F 1 B 2 B 2 P 2 P 3 P 2 X P2 Y P2 SELECT Points.PNr, X-Coord, Y-Coord F 1 B 3 B 3 P 3 P 4 Y P3 P 3 X P3 F 1 B 4 B 4 P 4 P 1 FROM Parcels, Border, Points P 4 X P4 Y P4 F 4 B 2 F 1 B 5 P 2 P 5 Y P5 P 5 X P5 F 4 B 5 F 4 WHERE FNr = ‘F 2 ’ AND B 6 P 5 P 6 P 6 X P6 Y P6 F 4 B 6 B 7 P 6 P 7 Y P7 P 7 X P7 F 7 F 4 B 7 Parcel.BNr = Borders.BNr AND B 8 P 7 P 8 P 8 X P8 Y P8 F 4 B 8 B 9 P 8 P 3 Y P9 P 9 X P9 F 4 B 9 ( Borders.PNr 1 = Points.PNr OR B 10 P 6 P 9 P 10 X P10 Y P10 F 7 B 7 B 11 P 9 P 10 Borders.PNr 2 = Points.PNr) F 7 B 10 F 3 F 6 B 12 P 10 P 7 F 7 B 11 F 7 B 12 • Even this simple query requires expensive joins of three tables … … • Querying the geometry (e.g., P in F 2 ?) is not directly supported. Redundancy free representation requires distribution of the information over 3 tables: Parcels , Borders , Points 13 14 Dr. Osmar R. Zaïane 2002-2003 CMPUT 391 – Database Management Systems University of Alberta Dr. Osmar R. Zaïane 2002-2003 CMPUT 391 – Database Management Systems University of Alberta Extension of the Relational Model Extension of the Relational Model to Support Spatial Data to Support Spatial Data – Example • Integration of spatial data types and operations into the core of Relation: ForestZones (Zone: Polygon , ForestOfficial: String , Area: Cardinal ) a DBMS ( � object-oriented and object-relational databases) ForestZones – Data types such as Point , Line , Polygon ForestOfficial Area (m 2 ) Zone R 4 R 2 – Operations such as ObjectIntersect , RangeQuery , etc. R 1 Stevens 3900 R 6 R 2 Behrens 4250 R 3 Lee 6700 R 3 R 4 Goebel 5400 • Advantages R 1 R 5 Jones 1900 R 5 R 6 Kent 4600 – Natural extension of the relational model and query languages – Facilitates design and querying of spatial databases • The province decides that a reforestation is necessary in an area described – Spatial data types and operations can be supported by spatial index by a polygon S. Find all forest officials affected by this decision. structures and efficient algorithms, implemented in the core of a DBMS SELECT ForestOfficial FROM ForestZones • All major database vendors today implement support for spatial data and WHERE ObjectIntersects (S, Zone) operations in their database systems via object-relational extensions Dr. Osmar R. Zaïane 2002-2003 CMPUT 391 – Database Management Systems University of Alberta 15 Dr. Osmar R. Zaïane 2002-2003 CMPUT 391 – Database Management Systems University of Alberta 16
Recommend
More recommend
