Book Image

Applying and Extending Oracle Spatial

By : Siva Ravada, Simon Greener
Book Image

Applying and Extending Oracle Spatial

By: Siva Ravada, Simon Greener

Overview of this book

Spatial applications should be developed in the same way that users develop other database applications: by starting with an integrated data model in which the SDO_GEOMETRY objects are just another attribute describing entities and by using as many of the database features as possible for managing the data. If a task can be done using a database feature like replication, then it should be done using the standard replication technology instead of inventing a new procedure for replicating spatial data. Sometimes solving a business problem using a PL/SQL function can be more powerful, accessible, and easier to use than trying to use external software. Because Oracle Spatial's offerings are standards compliant, this book shows you how Oracle Spatial technology can be used to build cross-vendor database solutions. Applying and Extending Oracle Spatial shows you the clever things that can be done not just with Oracle Spatial on its own, but in combination with other database technologies. This is a great resource book that will convince you to purchase other Oracle technology books on non-spatial specialist technologies because you will finally see that "spatial is not special: it is a small, fun, and clever part of a much larger whole".
Table of Contents (20 chapters)
Applying and Extending Oracle Spatial
About the Authors
About the Reviewers
Table Comparing Simple Feature Access/SQL and SQL/MM–Spatial


The focus of this book is the application of Oracle Spatial to the sorts of business problems that are better solved inside the database using a fully integrated technology approach. This approach takes as its first point of reference the view that spatial data and processing is no different from using timestamps, numbers, text, or dates to describe business entities (assets), and also that solutions to business problems involving spatial data processing should use relevant, or related, IT technologies first, before introducing specialist GIS software. One of the issues facing all proponents and users of spatial databases is the lack of knowledge within the professional GIS community about the underlying database software being used to manage spatial descriptions of business entities.

This book elucidates a holistic approach to spatial data management by highlighting how spatial data management and processing is enhanced and supported by utilizing all the data storage types that a database offers, but particularly spatial data.

The examples in this book have been drawn from many years of working with Oracle Spatial within a business IT environment. In addition, some examples have been drawn from requests readers of various blogs and forums have made over the years; some though are purely speculative based as they are on the application of theory to problems.

What this book covers

Chapter 1, Defining a Data Model for Spatial Data Storage, provides a SQL schema and functions that facilitate the storage, update, and query of collections of spatial features in an Oracle database. Oracle Spatial mainly consists of the following:

  • A schema (MDSYS) that defines the storage, syntax, and semantics of 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, which is just like any other data type in the database. Once 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 spatial data storage. A good data model supports and enhances application access without compromising quality. In addition, 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 affect 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 how to present methods for coping with multiple representations for faster web service access. All these issues, with solutions, are covered in this chapter.

Chapter 2, Importing and Exporting Spatial Data, explains how once we have a data model defined, the next step is to load data into it, and after data is loaded, it needs to be checked for cleanliness before indexing and using it. There are many methods for loading data of different types and formats. In this chapter, we describe some of the more common formats and how they can be loaded into the Oracle database using free tools, tools already available with Oracle, and tools from third party vendors. In addition, we also discuss other issues relating to import performance and organization of data for efficient access by applications.

The goal of this chapter is to give you a complete overview of all aspects of data loading from tools, through physical loading techniques, data organization, data quality checking, and indexing:

  • Extract, Transform, and Load (ETL) tools: GeoKettle, Oracle Spatial shapefile loader, and MapBuilder

  • Using SQL, Application Express, and Excel

  • Implementing theoretical storage resolution and the minimal resolution used by functions

  • Using ordinate resolution and the effect of rounding

  • Using tolerance and precision

  • Creating spatial autocorrelation via a Morton key

  • Geometry validation and methods to clean imported data

  • Coordinate system transformation techniques

  • Spatial indexing

  • Exporting formats, for example, Shapefile, GML, WKT, and GeoJson

Chapter 3, Using Database Features in Spatial Applications, introduces some of the standard features that the Oracle database makes available to solve some common data processing, auditing, version management, and quality issues related to the maintenance of spatial data. These database features allow developers to keep the data processing operations in the database instead of doing them in the application code:

  • Row-level and Statement-level triggers

  • Avoiding the mutating table problem

  • Using materialized views

  • Logging changes independently of application

  • Flashback queries

  • AWR reports and ADDM

  • Database replay

  • Workspace manager

  • SecureFile compression

Chapter 4, Replicating Geometries, shows the process of copying and maintaining data across different databases in a distributed system. The goal of this chapter is to present a few methods for replicating geometry data. Some of the traditional Oracle replication technologies do not directly support replication of tables with SDO_GEOMETRY data. The examples given here show alternate ways of replicating tables with geometry data. Replication does not always mean replicating the same data in different databases. In some cases, it also means copying the data in one database into a different database in a different form. For example, data in Online Transaction Processing (OLTP) databases can be converted to Online Analytical Processing (OLAP) databases by replicating or combining data from different OLTP tables into a single table in the OLAP system. We show how to do this conversion of data from a transactional OLTP database to a publication or OLAP database. Starting with the 12cR1 release, Logical Standby support for SDO_GEOMETRY data types is introduced, so we will look at how this feature can be used to replicate geometry data. In this chapter, the following topics will be covered:

  • Introducing different types of replication

  • Materialized-view based replication

  • Streams based replication

  • Physical and Logical Standby

  • OLTP and OLAP databases

Chapter 5, Partitioning of Data Using Spatial Keys, explains how spatial applications tend to generate large volumes of data, especially as the scale of observation of the world's surface extends to large parts of the Earth's surface. With the increasing level of data, database models have to adapt to deal with large volumes of spatial data that is not seen in traditional GIS applications. GIS applications expect all the related data in one feature layer, even if the feature layer contains millions of features. Oracle database supports a feature called partitioning that can break large tables at the physical storage level to smaller units while keeping the table as one object at the logical level. In this chapter, we cover the following five topics that are useful for managing large volumes of spatial data:

  • Introduction to partitioning

  • Time-based partitioning

  • Spatial key based partitioning

  • Implementing space curves based partitioning

  • High performance loading of spatial data

Chapter 6, Implementing New Functions, shows us to create new functions that use and extend those offered by Oracle Spatial and locator products. The SDO_GEOMETRY object, its attributes, and its structure must be thoroughly understood. Therefore, this chapter will start by building some functions that will help us understand, access, and process an SDO_GEOMETRY object's attributes. In building these functions, those Oracle SDO_GEOMETRY methods and SDO_GEOM and SDO_UTIL package functions that relate to the processing of the SDO_GEOMETRY attributes and structure will be introduced and used. This chapter will present information that will help the reader understand how to:

  • Expose or create additional properties for the SDO_GEOMETRY object

  • Manipulate SDO_ORDINATE_ARRAY in SQL and PL/SQL

  • Create functions to expose SDO_ELEM_INFO_ARRAY properties

  • Use SDO_ELEM_INFO_ARRAY to process SDO_ORDINATE_ARRAY correctly

  • Organize functions via object types and packaging

  • Sort geometries

Later chapters will build on the knowledge gained in this chapter when creating functions that solve specific real-world problems.

Chapter 7, Editing, Transforming, and Constructing Geometries, explains Desktop GIS, CAD, and Extract Transform and Load (ETL) software that provide a rich set of tools that the experienced operator can use to construct, edit, or process geometric objects. But few realize that creating and applying such functionality within the database is also possible, and this can be more effective, efficient, and less complicated.

While the Oracle database SDO_GEOMETRY data type provides an excellent storage, search, and processing engine for spatial data, what users often overlook is its ability to provide geometry modification and processing capabilities that can be used in database objects such as views, materialized views, and triggers for the implementation of specific business functionality, with that functionality being available to any software product that connects to the database.

Chapter 8, Using and Imitating Linear Referencing Functions, shows how to use the functions created in Chapter 6, Implementing New Functions and Chapter 7, Editing, Transforming, and Constructing Geometries to build new functions that can be used to solve business problems relating to managing linear assets. The main business problems that need this functionality are road, cycle way, or track management, geocoding street addresses, survey, inventory, condition assessment, and water management applications.

Oracle Spatial has a robust linear referencing package (SDO_LRS) that can be applied to all the above problems. The SDO_LRS package can only be licensed for use by purchasing the Spatial package and deploying it within an Oracle Enterprise database. In addition, SDO_LRS cannot be purchased separately from the whole Spatial package. Finally, SDO_LRS cannot be used with locator.

The functions created in this chapter will provide SDO_LRS functionality where licensing of the SDO_LRS package is beyond the user's resources. These functions will support simple linear processing against measured and non-measured geometries (a normal 2D linestring is a non-measured geometry). The following are the uses of linear processing:

  • Snapping a point to a line

  • Splitting a line using a known point that is on or off the line

  • Adding, modifying, and removing measures to and from a linestring

  • Finding linear centroids

  • Creating a point at a known distance along, and possibly offset from, a line

  • Extracting segments of linear geometries

  • Linear analysis of point data

The examples presented in this chapter will include real-world situations to demonstrate the power of developing and using these types of functions. The SQL statements that demonstrate the use of the functions developed in this chapter are available in an SDO_LRS equivalent form in the SQL scripts shipped with this book for this chapter; they will not be included in the actual chapter.

Chapter 9, Raster Analysis with GeoRaster, shows how spatial features can be represented in vector or raster format. So far, we have discussed the vector related features of Oracle Spatial, and we introduce the raster related features called GeoRaster in this chapter. Traditional GISs propose to store the raster data as BLOBs in the database. This approach might be sufficient if the raster data is only used as backdrop images in maps.

But if any raster data processing and analysis is required, storing raster data as GeoRaster objects offers many features and advantages over storing this data just as BLOBs. Loading and storing any raster data inside a database simply for the purpose of storage or visualization provides limited utility. Storing raster data for use within a transactional system has engendered a view that one must see all data as a part of a complete model; the data loaded must be seen in relation to all other data under control of that model.

The goal of this chapter is to demonstrate how to use raster data in conjunction with all the data in the database to answer questions that otherwise could not be answered in the database. The following topics are covered in this chapter to show how this goal can be achieved:

  • Introduction to GeoRaster

  • Loading and storing raster data inside a database

  • Raster data for visualization applications

  • Raster data for analytical applications

  • Mapping between raster and vector space

Chapter 10, Integrating Java Technologies with Oracle Spatial, shows how to embrace and extend the standard functionality available with Oracle Locator and Spatial using PL/SQL. PL/SQL is the programming language that is native to Oracle. Oracle also supports the creation of Java Stored Procedures. This chapter explores the application of Java to spatial processing involving the SDO_GEOMETRY type.

In particular, this chapter will cover the following topics:

  • Why Java and Oracle Spatial?

  • Available Java spatial technologies

  • Matching requirements to source code project

  • Strengths and limitations of using Java

  • How to download, modify, compile, and install external libraries

  • Calling an external method

  • Converting an SDO_GEOMETRY to a Java object

  • Exposing Java Topology Suite (JTS) functionality:

    • One-sided buffers

    • Snapping geometries

    • Building polygons from lines

  • Performance of Java-based SQL processing

Chapter 11, SQL/MM – A Basis for Cross-platform, Inter-operable, and Reusable SQL, explains how Oracle's SDO_GEOMETRY type is very widely used even by the open source community, which promotes the virtues of standards conformance and compliance. However, many in the geospatial industry still criticize Oracle's SDO_GEOMETRY for its lack of perceived standards compliance. Whether this criticism is based on ignorance or maleficence, SDO_GEOMETRY is standards compliant in its storage, geometry description, and with some functions; but, its API is not fully compliant.

SDO_GEOMETRY is not the whole story however. Oracle also provides an ST_GEOMETRY object type which is an implementation that is based on the ISO/IEC FCD 13249-3 Spatial (ISO 13249-3, Information technology - Database languages - SQL Multimedia and Application Packages - Part 3: Spatial.) standard (hereafter known as the SQL/MM standard). This chapter aims to show that such criticism of standards compliance of Oracle is limited and ill informed through exposure of the benefits of ST_GEOMETRY to practitioners.

ST_GEOMETRY is of special importance in situations where a business IT environment has a heterogeneous database environment (for example, Oracle, SQL Server, PostgreSQL). It can be a most useful mechanism for implementing cross-platform spatial data processing and developing highly reusable skills. This latter aspect of skills development is important because reusability and training, which increases and improves an individual's skill-set, is an important ingredient in staff training and development.

This chapter will present two aspects of how the use of standards: OGC SFA 1.x OpenGIS® Implementation Specification for Geographic information - Simple Feature Access - Part 1: Common architecture, Version 1.1 and 1.2. Previously known as Simple Features – SQL (SFS). (hereafter known as OGC SFA or OGC SFA 1.1 or 1.2, if function is only available in one of the standards) and SQL/MM, to aid cross-platform interoperability:

  • A demonstration of how the spatial data types offered by three databases: Oracle, SQL Server 2012 (Express Edition), and PostgreSQL 9.x with the PostGIS 2.x extension. PostgreSQL can be used to create constrained geometry storage. This does not repeat the material presented in Chapter 1, Defining a Data Model for Spatial Data Storage. Rather, it presents the material in light of the OGC SFA 1.x and SQL/MM standards and the benefits for database interoperability.

  • How the standardized methods common to the three databases can be used to develop SQL statements as well as stored procedures and functions that can be ported to other platforms with relative ease.

Appendix A, Table Comparing Simple Feature Access/SQL and SQL/MM–Spatial provides a comparison of SFA-SQL 1.2 ( and SQL/MM-Spatial (ISO 13249-3, Information technology - Database)

Appendix B, Use of TREAT and IS OF TYPE with ST_GEOMETRY examines the need for TREAT in more detail. In the ST_GEOMETRY hierarchy, a POINT object can be created in the following two ways:

MDSYS.ST_GEOMETRY.FROM_WKT('POINT(6012578.005 2116495.361)',2872)

MDSYS.ST_POINT.FROM_WKT('POINT(6012578.005 2116495.361)',2872).

The result in both cases is not an ST_POINT, rather it is an ST_GEOMETRYobject.

What you need for this book

If the reader of this chapter wishes to use SQL and the example code that ships with this book, then the following technologies are required:

  • Oracle database 11g Release 1 or 2

  • SQL Developer Version 3.2.x (not Version 4.x)

  • For visualizing SDO_GEOMETRY data in SQL Developer queries, either use SQL Developer's integrated Spatial extension or the open source extension GeoRaptor (download from SourceForge)

  • SQL Developer's embedded Data Modeling extension

  • JDeveloper 11.x

Who this book is for

This book is aimed at the experienced practitioner who is already literate with Oracle Locator or Spatial and who has at least heard of or used the standard Oracle database technologies this book uses to solve problems.

The reader should be familiar with using SQL Developer or a similar product to execute the SQL examples, though for visualization, SQL Developer and the free GeoRaptor extension are preferred.

The reader should be at least familiar with physical database modeling, and even if they have not used SQL Developer's Data Modeler tool, should be willing to learn how to use it.

For the programming aspects of this book, it is preferable but not mandatory that the reader has some experience in writing relatively simple PL/SQL and a base level of knowledge of writing Java. In respect of Java, while the actual Java Stored Procedures are relatively simple in structure, the use of external source code and JAR files to construct the complete solution is something that requires some experience of working within a larger Java framework. As such, familiarity with JDeveloper or a similar Integrated Development Environment (IDE) is recommended.


In this book, you will find a number of styles of text that distinguish between different kinds of information. Here are some examples of these styles, and an explanation of their meaning.

Code words in text are shown as follows: "Oracle allows the storage of spatial data in a table using the SDO_GEOMETRY data type, which is just like any other data type in the database".

A block of code is set as follows:

grant connect,resource to Book identified by <password>;
grant connect, resource to book;
grant create table to book;
grant create view to book;
grant create sequence to book;
grant create synonym to book;
grant create any directory to book;
grant query rewrite to book;
grant unlimited tablespace to book;

When we wish to draw your attention to a particular part of a code block, the relevant lines or items are set in bold:

Enter value for report_type: html

Type Specified: html

Instances in this Workload Repository schema

Any command-line input or output is written as follows:

-- this is the setup for UNIX/Linux machines
setenv clpath $ORACLE_HOME/jdbc/lib/ojdbc5.jar:$ORACLE_HOME/md/jlib/sdoutl.jar:$ORACLE_HOME/md/jlib/sdoapi.jar

New terms and important words are shown in bold. Words that you see on the screen, in menus or dialog boxes for example, appear in the text like this: "Once the table is created, go to the APEX home page and then navigate to the SQL Workshop and Utilities tab".


Warnings or important notes appear in a box like this.


Tips and tricks appear like this.

Reader feedback

Feedback from our readers is always welcome. Let us know what you think about this book—what you liked or may have disliked. Reader feedback is important for us to develop titles that you really get the most out of.

To send us general feedback, simply send an e-mail to , and mention the book title via the subject of your message.

If there is a topic that you have expertise in and you are interested in either writing or contributing to a book, see our author guide on

Customer support

Now that you are the proud owner of a Packt book, we have a number of things to help you to get the most from your purchase.

Downloading the example code

You can download the example code files for all Packt books you have purchased from your account at If you purchased this book elsewhere, you can visit and register to have the files e-mailed directly to you.

Downloading the color images of this book

We also provide you a PDF file that has color images of the screenshots/diagrams used in this book. The color images will help you better understand the changes in the output. You can download this file from:


Although we have taken every care to ensure the accuracy of our content, mistakes do happen. If you find a mistake in one of our books—maybe a mistake in the text or the code—we would be grateful if you would report this to us. By doing so, you can save other readers from frustration and help us improve subsequent versions of this book. If you find any errata, please report them by visiting, selecting your book, clicking on the errata submission form link, and entering the details of your errata. Once your errata are verified, your submission will be accepted and the errata will be uploaded on our website, or added to any list of existing errata, under the Errata section of that title. Any existing errata can be viewed by selecting your title from


Piracy of copyright material on the Internet is an ongoing problem across all media. At Packt, we take the protection of our copyright and licenses very seriously. If you come across any illegal copies of our works, in any form, on the Internet, please provide us with the location address or website name immediately so that we can pursue a remedy.

Please contact us at with a link to the suspected pirated material.

We appreciate your help in protecting our authors, and our ability to bring you valuable content.


You can contact us at if you are having a problem with any aspect of the book, and we will do our best to address it.