Chapter 1. Defining a Data Model for Spatial Data Storage

Oracle Spatial and Graph provides a SQL schema and functions that facilitate the storage, update, and query of collections of spatial features in an Oracle database. Oracle Spatial and Graph is the new name for the feature formerly known as Oracle Spatial. In this book, we refer to this feature as Oracle Spatial for the sake of simplicity. We also focus exclusively on spatial feature of Oracle Spatial and Graph in this book. Oracle Spatial mainly consists of the following:

• A schema (MDSYS derived from Multi-Dimensional System) that defines the storage, syntax, and semantics of the supported geometric (both vector and raster) data types

• A spatial indexing mechanism for faster querying and retrieval

• Operators, functions, and procedures for performing spatial analysis and query operations

• A persistent topology data model for working with data about nodes, edges, and faces in a topology

• A network data model for modeling and working with spatial networks

• A GeoRaster data type and associated functions that let you store, index, query, analyze, and deliver raster data

The spatial component of a real-world feature is the geometric representation of its shape in some coordinate space (either in 2D or 3D), and in vector space, this is referred to as its geometry. Oracle Spatial is designed to make spatial data management easier and more natural to users of location-enabled business applications and geographic information system (GIS) applications. Oracle allows the storage of spatial data in a table using the SDO_GEOMETRY data type that is just like any other data type in the database. Once the spatial data is stored in the Oracle database, it can be easily manipulated, retrieved, and related to all other data stored in the database.

A spatial database should be designed just like any other database with a fully specified model. A fully specified model that is application independent should control the spatial data storage. A good data model supports and enhances application access without compromising the quality. In addition to these features, database features can be used to support applications that have limited functionality when it comes to table and column design. For example, some applications mandate a single spatial column per table or only a single homogeneous geometry type per spatial column. These limitations can be accommodated quite easily using database features such as views and triggers. In addition, there are a number of issues that arise when designing a data model that directly affects the data quality, performance, and access.

The goal of this chapter is to give readers an understanding of how to model spatial data as SDO_GEOMETRY columns within tables, how to support spatial constraints for improved data quality, how to use synchronous and asynchronous triggers for implementing topological constraint checking, and to present methods for coping with multiple representations for faster web service access. All these issues, with solutions, are covered in this chapter:

• Defining a sample schema

• Using spatial metadata

  • Using Oracle metadata views

  • Using OGC metadata views

• Using different types of geometric representations

  • Implementing tables with homogeneous and heterogeneous columns

  • Implementing multiple representations for a single object

  • Implementing multiple instances of a single column, for example, pre-thinned data for different scales and reprojection for faster web service access

• Restricting data access via views

  • Using views to expose a single geometry type when multiple geometry types are present in the table

  • Using views to expose tables with single geometry columns when multiple geometry columns are present in the table

• Implementing spatial constraints at the database level

  • Restricting geometry types

  • Spatial topological constraints

  • Implementation of synchronous triggers

  • Implementation of asynchronous triggers