Data models help build our mental models as we use information technology to solve complex problems

A complete data model consists of 3 components (Codd 1981):

• Geospatial constructs take the form of data structures
• implemented as a data structure
• Operations that can be performed on those structures to derive information from the data
• implemented using software operations
• Rules for maintaining the integrity of data
• Specifies the constraints on the data structures and/or the software operations

Generic feature classes are defined as:

• point - single point represented by ID
• multipoint - multiple points cluster represented by ID

Network junction features:

• simple junction feature - like a node storing topology, but can have logical behavior e.g., valve connecting pipes
• complex junction feature - can contain internal parts, like a transformer or junction box, e.g., switch in electrical network

line:

• line segments - straight line from point to point
• circular arcs - parameterized by radius (pixel subpoints for shape)
• splines - multiple arcs to fit a series of points

network edge features:

• simple edge feature - lines play a topological role (no interior junctions) can have connectivity rules
• complex edge feature - support one or more junctions along edge

Data types are the foundation of data model schemas

A logical data model both enables and constrains choices simultaneously

Vector data structure - data constructs - a matter of dimension (D)

Raster data structure - gridded data constructs - stored as a grid of values which are rendered on a map as pixels

• pixel primitive commonly used to partition image space
• cell primitive commonly used to partition map space

Each pixel value represents an area on the Earth's surface making the data spatial

A raster file is composed of regular grid of cells, all of which are the same size

Raster files are spatially referenced while as photographs are not

Defines an area by spatial resolution

Raster data can have one or more layers

Single Files

• .tif/.tiff - stands for Tagged Image File Format
• .asc - stands for ASCII Raster Files

Hierarchical data formats can store many different types of data in one single file

• .hdf/.hdf5 - stands for Hierarchical Data Format
• .nc (NetCDF)- stands for Network Common Data Form

Types of data stored in Rasters

1. Satellite imagery e.g. Landsat, MODIS
2. Land use over large areas
3. Elevation data e.g. SRTM
4. Weather data e.g. climate data
5. Bathymetry data

For spatial coordinate storage e.g., single precision or double precision

For time data storage e.g., date and time, just date

For attribute data fields e.g., string, character, alphanumeric, numeric

For performance retrieval e.g., B-tree (binary tree), R-tree (region tree)

CRUD operations

Query operations

Geometry operations

Topology operations

Spatial analysis operations

Spatial SQL

Transactions

Create, Read, Update, Delete (CRUD) operations

• Create - add row and/or data value
• Read - retrieve data value
• Update - change the data value
• Delete/Drop - eliminate the data value

Database queries are formed using operators

• SELECT - query rows of a table, all data values or those meeting criteria
• PROJECT - query columns of a table, removing duplicate data values
• JOIN - concatenates data from rows in one table with rows in another table on same value
• PRODUCT - concatenate every row in one table with every row in another table

• UNION - generate new table by appending rows from one table onto another
• INTERSECT - generate new table consisting of all rows appearing in both tables
• DIFFERENCE - generates new table consisting of all rows in one table not appearing in another
• DIVIDE - takes a two column table and a one column tables and cerates new table consisting of all vales of one column of binary table that match values in the single-column table

• Spatial reference - returns reference system
• Envelope - returns the minimum bounding rectangle of geometry
• Export - converts geometry into different representation
• IsEmpty - tests if geometry is empty set or not
• IsSimple - returns TRUE if geometry is simple
• Boundary - returns of the boundary of the geometry

• Equal - tests if geometries are spatially equal
• Disjoint - tests if geometries are disjoint
• Intersect - tests if geometries intersect
• Touch - tests if geometries touch each other

• Cross - tests if geometries cross each other
• Within - tests if geometries is within another geometry
• Contains - tests if a given geometry overlaps another given geometry

• Distance - Returns shortest distance between any two points
• Buffer - Returns a geometry that represents all points at a pre-defined distance
• ConvexHull - Returns convex hull of a given geometry
• Intersection - Returns the geometry of two geometries
• Union - Returns the combination of two geometries
• Difference - Returns the separate geometry of the two geometries
• SymDifference - returns the symmetric difference of two geometries; 0 if the same; 1 if different

• SELECT-FROM-WHERE construct
• Using spatial data type in contruct
• Location queries
• SELECT, PROJECT, JOIN etc.

Transaction - input what is to be done, process the data, and provide the output - as a package

• Atomicity - all or nothing of transaction is executed
• Consistency preservation - data remains consistent
• Isolation - results of simultaneous transactions are independent
• Durability - after transaction, the result can be traced from beginning

• Concurrent control - locks data items involved in transaction
• Logging transactions - keep track of all changes made to data, enables redo
• Transaction commitment - prevents change to database unless transaction is ready to complete
• Rollback - allows database to undo an incomplete transaction

Conceptual Design of a Database Model

1. Identify information products and/or need to know questions
2. Identify the key thematic layers and feature classes
3. Detail all feature class(es)
4. Group feature classes into datasets (logical collections)

Logical Design of a Database Model

5. Define attribute database structure and behavior for descriptive attributes, e.g. using rules
6. Define spatial properties of datasets, e.g. using rules
7. Propose a database design

Physical Design of a Database Model

8. Implement, prototype, review and refine schema design
9. Design workflows for building and maintaining each layer
10. Document design using appropriate methods

Develop Database Model for the following cases

1. Hydrology
2. Land records
3. Transportation

Queries about this Lesson, please send them to: jmwaura@jkuat.ac.ke

*References*

• Database Systems: Design, Implementation, and Project Management, Springer. Albert K W Yeung & G. Brent Hall
• Database Systems: Design, Implementation, and Management, 12th ed. Carlos Coronel & Steven Morris
• Database Modeling and Design; Logical Design, 5th ed. Taby Teorey et.al
• Fundamentals of database systems, 6th ed. Ramez Elmasri & Shamkant B. Navathe