Inside Data

Graham Keitch checks out the spatial features provided by Oracle Database.

HardCopy Issue: 51 | Found In: Database | Published: 22/02/2011 | Last Revision: 16/05/2011

Most businesses have access to considerable amounts of geographic data. It is therefore not surprising to find a growing requirement for databases to be able to handle the more complex data types associated with geographic information. Leading database vendor Oracle has been at the forefront of meeting this demand for some time. Oracle has been heavily involved in shaping and implementing open standards in the spatial and location services arena. It began when scientists working with the Canadian Hydrographic Service (CHS) equipped Oracle database to handle spatial data with a modification to Oracle 4. A joint project team of CHS and Oracle developers subsequently redesigned the Oracle kernel, resulting in the Spatial Data Option that appeared with Oracle 7. Subsequently, the spatial extension became known simply as Oracle Spatial. Oracle uses a standard R-tree index to handle three dimensional data. The ‘R’ stands for rectangle because R-tree data structures define space with hierarchically nested rectangles, sometimes known as bounding boxes. Oracle Database handles location data as a standard feature in both the free Oracle Database Express Edition (XE) and across the 11g database range which includes Standard and Enterprise editions. This enables the database to support a wide range of applications including automated mapping and facilities management, geographic information systems (GIS), wireless location services and location aware business intelligence. The feature that provides this core functionality is known as Oracle Locator. In addition, Oracle Spatial is an Oracle Database Option that can be purchased for the Database Enterprise Edition for projects that involve more complex geospatial data.

Oracle Locator

Oracle Locator allows users to build location information into their systems. This enables more detailed analyses involving entities such as customer addresses and sales territories that can be used to assist business decision making. Developers can also use the technology to extend existing Oracle tools and applications. They can use Locator to incorporate geographic information in their applications and services, aided by the fact that location data is fully integrated in the Oracle server itself.

The data is manipulated using the same semantics applied to the CHAR, DATE or INTEGER types that will be familiar to users of SQL. Locator provides an object type that supports geometries such as points, lines and polygons, and operators that perform queries to determine the interaction and relationship of geometric features. There are spatial functions for distance, area and length, and a whole Earth geometry model for handling geodetic data. The native spatial vector data type allows users to benefit from a number of Oracle features such as partitioning support for spatial indexes, parallel index builds for spatial R-tree indexes and parallel spatial queries.

Oracle Spatial Option

If you require advanced features to support complex GIS and location based solutions, you will need Oracle Database Enterprise Edition and the separately licensed Spatial Option. The Option conforms to the standards of the Open Geospatial Consortium (OGC) and other bodies to ensure support for Spatial Web Services. It extends Locator by including support for all geospatial data types and models, including vector and raster data as well as topology and network models. It also supports routing and geocoding engines.

Oracle Spatial Option and Oracle Business Intelligence in action.

Typical applications for the Spatial Option might include the use of terrain models for urban planning or advanced logistical operations. A data type is provided that natively manages georeferenced raster data such as satellite images, photographs and grids within the database where Oracle compression technologies can be used to economise on storage. Oracle has recently introduced a new Java API for GeoRaster that supports the functions currently available in the existing PL/SQL interface. The API also includes calls to support the development of tools for extracting, transforming and loading spatial data. This helps simplify the building of Java and Web applications that manipulate raster and gridded datasets stored in the Oracle database. Another important feature is support for the Network Data Model which stores graph structures persistently in the database. This maintains connectivity between networks and provides network analysis to address shortest path, nearest neighbour and within cost scenarios. Applications likely to benefit from this capability include solutions for transport and utilities. Oracle provides many development tools, mid-tier components and applications that interface with the spatial capabilities of their database products. Oracle Fusion Middleware MapViewer is used for map rendering and viewing geospatial data managed by Oracle Spatial or Locator.