From Pro Oracle Spatial: Apress, November 2004.

Part 1 | Part 2

Visualizing Spatial Data

How do you visualize the results of spatial queries? Oracle technology includes the MapViewer component to facilitate generation of maps from spatial data. Each map is associated with a set of themes. Each theme denotes spatial data from a specific table and is associated with a rendering style. For instance, you can specify that the interstates theme (data from the INTERSTATES table) should be rendered as thick blue lines. Oracle Spatial provides appropriate dictionary views, USER_SDO_MAPS, USER_SDO_THEMES, and USER_SDO_STYLES, to define new maps, to associate them with themes, and to specify rendering styles for the themes inside the database, respectively.

In addition, MapViewer renders the map for a specified map name. Basically, a servlet consults the database views and retrieves the themes and associated styling rules for a specified map name. Using this information, the MapViewer servlet generates an image of the constructed map. Figure 2-3 shows an image of such a map constructed using MapViewer (constructed entirely using spatial technology and the data provided in this book). This map shows I-795 along with the larger interstates.

Figure 2-3. Sample map with multiple themes generated using MapViewer

The map consists of multiple themes: cities, county boundaries, rivers, interstates, and parks. The cities, D.C. and Baltimore, are rendered as points in black color. The counties, Howard, Fairfax, Charles, Frederick, etc., are shown as white polygons. The river in the right side of the map is shown in a dark grey color. The interstates, such as I-795, are rendered as line strings in black, and the parks are rendered as polygons in light gray.

Onto this map, we can also superimpose the locations of the five closest restaurants to I-795. In addition to rendering predefined themes/base maps, the MapViewer request can specify a predefined base-map (such as the map in Figure 2-3) and a dynamic theme, such as a SQL/JDBC query retrieving the locations of the five nearest restaurants. MapViewer will then generate a new map that contains the locations of the five restaurants superimposed on the predefined base map.

Note that the map in Figure 2-3 displays vector data stored as SDO_GEOMETRY columns in different (theme) tables. In addition to vector data, MapViewer can display spatial data stored in the raster (or image) format. Such data is stored in Oracle tables using the SDO_GEORASTER data type. Chapter 11 provides full details of how to construct maps and display the results of queries on such maps using MapViewer technology.

Advanced Spatial Engine

The Advanced Spatial Engine has several subcomponents that cater to the complex analysis and manipulation of spatial data that is required in traditional GIS applications.

Note: Our focus in this book is the applicability of Oracle Spatial to Oracle business applications, so we do not cover most of these advanced options, with the exception of the network data model, in great detail. However, we provide a good overview of these topics in the appendixes, with references for further details.

Internally, each of these additional components uses the underlying geometry data type and index and geometry engine functionality.

• The Network Data Model provides a data model for storing networks inside the Oracle database. Network elements (links and nodes) can be associated with costs and limits, for example, to model speed limits for road segments. Other functionality includes computation of the shortest path between two locations given a network of road segments, finding the N nearest nodes, and so on. The network data model is useful in routing applications. Typical routing applications include web services such as MapQuest and Yahoo! Maps, or navigation applications for roaming users using GPS technology. We cover more details about this component in Chapter 10.
• The Linear Referencing System (LRS) facilitates the translation of mile-markers on a highway (or any other linear feature) to geographic coordinate space and vice versa. This component allows users to address different segments of a linear geometry, such as a highway, without actually referring to the coordinates of the segment. This functionality is useful in transportation and utility applications, such as gas pipeline management.
• The Spatial Analysis and Mining Engine provides basic functionality for combining demographic and spatial analysis. This functionality is useful in identifying prospective sites for starting new stores based on customer density and income. These tools can also be used to materialize the influence of the neighborhood, which in turn can be used in improving the efficacy and predictive power of the Oracle Data Mining Engine.
• GeoRaster facilitates the storage and retrieval of georeferenced images using their spatial footprints and the associated metadata. GeoRaster defines a new data type for storing raster images of geographically referenced objects. This functionality is useful in the management of satellite imagery.
• The Topology Data Model supports detailed analysis and manipulation of spatial geometry data using finer topological elements such as nodes and edges. In some land-management applications, geometries share boundaries, as in the case of a property boundary and the road on which the property is situated. Oracle Spatial defines a new data type to represent topological elements (such as the shared “road segment”) that can be shared between different spatial objects. Updates to shared elements implicitly define updates to the sharing geometry objects. In general, this component allows for the editing and manipulation of nodes and edges without disturbing the topological semantics of the application.

Oracle Spatial Technology Products

In the previous sections, we briefly described the functionality that Oracle Spatial provides to support the following operations on spatial data:

• Storage data model using the SDO_GEOMETRY data type
• Query and analysis using the Index Engine and Geometry Engine
• Location enabling using the geocoder by conversion of address data into SDO_GEOMETRY data
• Visualization using MapViewer
• Advanced Spatial Engine functionality such as network analysis

Let’s next look at how this functionality is productized or licensed in Oracle Database 10g, version 10.1.0.2, and Oracle Application Server 10g, version 9.0.4. Note, though, that this packaging may change with later versions of Oracle.

MapViewer, the visualization tool of Spatial, is included as part of the Oracle Application Server. You can also deploy MapViewer by just installing the Oracle Containers for Java(OC4J) without installing the entire Application Server. We will look at these details in Chapter 11. The remainder of the Spatial functionality is included, sometimes optionally, with the Database Server. Let’s look at these details next.

In the Lite edition of Oracle Database Server, none of the spatial functionality is included. As mentioned in an earlier Note, in the Personal, Standard,⁴ and Enterprise editions, a subset of the spatial functionality is included for free with the database. This subset is referred to as the Locator. In the Personal and the Enterprise Editions, the full functionality of spatial technology is available as a priced option, called Spatial. Let’s look at each of these versions of Oracle Spatial and what you can do with them.

Locator

Locator provides a core subset of spatial functionality to cater to specific applications. Specifically, it includes the following functionality:

• The data model for storing spatial data using the SDO_GEOMETRY data type: This includes storing all types of geometries (points, lines, polygons, and so on).
• Query and analysis using the Index Engine: This includes creating spatial indexes and querying using associated spatial operators like SDO_NN.
• The SDO_GEOM.SDO_DISTANCE and the SDO_GEOM.VALIDATE_GEOMETRY_XXX functions: These functions are also part of Locator.

Figure 2-4 shows the functionality provided in Locator. The Locator components are highlighted in black. The non-Locator components of Spatial technology are shown in solid gray.

Figure 2-4. The functionality of Locator, the free part of Spatial technology, is shown in black ovals.

Applications that use Locator may need to use third-party geocoding services to convert
addresses in application tables. After storing the spatial locations as SDO_GEOMETRY columns, Locator enables a variety of spatial queries, such as identification of customers within a specified sales territory or the nearest ATM to a specific location. Locator is typically used in the following applications:

• Simple GIS applications, which may just work with geographic data such as state, city, or property boundaries and index-based query using associated spatial operators. Typically, though, most GIS applications may need the Geometry Engine functionality (which is not supported in Locator).
• Simple business applications, where the spatial data is obtained from third-party vendors. As you will see in Chapter 8, the index-based operators supported in Locator can perform a great deal of analysis in business applications.
• CAD/CAM and similar applications, where the spatial data does not refer to locations on the surface of the Earth. For instance, in CAD/CAM applications, the data represents the structure/shapes of different parts of an automobile. In this case, the data is inherently in the two- or three-dimensional coordinate space.that is, there is no need to convert nonspatial columns (such as addresses) to obtain spatial information. The operations that are needed for such applications are the index-based proximity analysis operators. The advanced spatial engine functions such as routing are of no use in these applications.

To summarize, Locator offers a core subset of spatial technology. If you want to exploit the full feature-set of spatial technology, you will need to purchase the Spatial option in the Enterprise Edition of Oracle Database.

Spatial Option

The Spatial option is a priced option of the Enterprise Edition of Oracle Database Server. This option includes all the components of the spatial technology referred to in Figure 2-4 and is a superset of Locator. Figure 2-5 shows the functionality of the Spatial option in gray. Note that the Spatial option does not include the MapViewer component (shown in black) of spatial technology. The Spatial option consists of

• Storage data model using SDO_GEOMETRY data type: This includes storing of all types of geometries (points, lines, polygons, and so on).
• Query and analysis using the Index Engine: This includes creating spatial indexes and querying using associated spatial operators like SDO_NN.
• Query and analysis using the Geometry Engine: This supports different analysis functions for individual geometries, pairs of geometries, or a set of geometries.
• Location enabling using the geocoder: This facilitates conversion of address data into SDO_GEOMETRY data.
• Advanced Spatial Engine functionality: This includes routing and network analysis.

Figure 2-5. The functionality of the Spatial option is shown in gray.

A wide variety of applications can use the full set of functionality provided in the Spatial option.

By now, you should have a good idea of how Oracle Spatial functionality is packaged. This understanding is helpful in determining whether or not your application needs to license the full set of spatial functionality using the Spatial option. For the remainder of this book, we will not differentiate or explicitly refer to Locator and Spatial option products. Instead, we will refer to the entire set of functionality as “Oracle Spatial technology” or simply as “Oracle Spatial.”

What to Expect in an Oracle Spatial Install

In this section, we discuss what to expect during or after you install Oracle Spatial technology inside the Oracle Database Server. We describe how to install the MapViewer component, which is part of Oracle Application Server 10g, separately in Chapter 11.

Installing Oracle Spatial in the Database

As noted previously, Oracle Spatial is automatically installed with the Standard or Enterprise Edition of an Oracle Database Server. All Spatial data types, views, packages, and functions are installed as part of a schema called MDSYS.

To verify that Spatial has been installed properly, you first have to check that the MDSYS account exists. If it does not, then Spatial is not installed. Otherwise, you can execute the SQL in Listing 2-8 after connecting through your SYS (SYSDBA) account.

SQL> SELECT COMP_NAME, STATUS 
FROM DBA_REGISTRY 
WHERE COMP_NAME = 'Spatial'; 
COMP_NAME                          STATUS 
-----------------------------      ----------- 
Spatial                            VALID 

Listing 2-8. Verifying That a Spatial Install Is Successful

After a successful installation, the status will be set to VALID or LOADED.

Upgrades

To understand upgrades properly, let’s look at how Spatial technology evolved between different versions of Oracle. Figure 2-6 shows the progression from Oracle 7.2 to Oracle 10g.

Figure 2-6. Evolution of Spatial technology in Oracle

Spatial technology was first introduced in Oracle 7.2 under the name Oracle MultiDimension (MD). Later, the product name was changed to Oracle Spatial Data Option (or SDO) and to Spatial Data Cartridge in Oracle 8. Since objects were not supported in these releases, the coordinates of a geometry were stored as multiple rows in an associated table. Managing spatial (geometry) data in these prior versions was inefficient and cumbersome.

Starting with Oracle8i, the SDO_GEOMETRY data type was introduced to store spatial data. Even in Oracle 10g, the same SDO_GEOMETRY model is used to store spatial data in Oracle. In Oracle9i (and Oracle 10g), the geometry data also included support for coordinate systems information specified using the SRID attribute in the SDO_GEOMETRY data type. In Oracle 10g, additional functionality (that exists in the Advanced Spatial Engine) such as the Network Data Model is introduced in the Spatial option of Oracle.

Since the prior versions are named MultiDimension (MD) and Spatial Data Option (SDO), you will see the prefixes MD and SDO for the files and schemas that install Spatial technology. The name of the spatial install schema is MDSYS in all versions of Oracle.

In spite of the evolution of Spatial technology with each release, upgrading to the latest version, Oracle 10g, is not difficult. Spatial technology is automatically upgraded with the upgrade of Oracle Database Server. The upgrade may not need your intervention at all.⁵ However, if you are upgrading from pre-8i releases, you need to additionally migrate your geometry data from the pre-8i format to the SDO_GEOMETRY data model. Oracle Spatial provides the SDO_MIGRATE package to migrate the data from pre-8i models to the current SDO_GEOMETRY data model. We discuss this migration package’s functionality in Chapter 5.

Understanding a Spatial Install

In this section, we cover where to find appropriate spatial files and how to perform some preliminary investigation when an installation or upgrade fails.

To view all the spatial files, you can go to the $ORACLE_HOME/md/admin directory. In this directory, you will find all files relevant to Oracle Spatial. You will observe that a majority of the files have a prefix of either SDO or PRVT. In other words, the files are of the form sdoxxxx.sql or prvtxxxx.plb. The SDO files, in most cases, contain package definitions for different components of Spatial technology. The PRVT files, on the other hand, are binary files and define the package bodies and so on.⁶ You should not tamper with these SDO and PRVT files at any time.

During the creation of the database,⁷ the MDSYS account is created with appropriate privileges (see scripts mdinst.sql and mdprivs.sql for more details) and the catmd.sql file is loaded into the MDSYS schema. This file loads all the SDO and PRVT files in an appropriate order that resolves all dependencies between all the Spatial packages. In the case of Locator, catmdloc.sql (instead of catmd.sql) is loaded. Likewise, appropriate files in this directory such as sdodbmig.sql or sdopatch.sql are loaded/executed at the time of upgrades, downgrades, and patches.

During some installations or upgrades, you may find that several package dependencies are unresolved and hence invalid. You may check for such invalid packages or other objects in your Spatial installation by running the SQL in Listing 2-9.

SQL> SELECT OBJECT_NAME, OBJECT_TYPE, STATUS 
FROM ALL_OBJECTS 
WHERE OWNER='MDSYS'  AND STATUS <> 'VALID' 
ORDER BY OBJECT_NAME; 

Listing 2-9. Checking for Invalid Objects in a Spatial Installation

If Listing 2-9 returns any rows, you should contact Oracle Support for troubleshooting help.

Summary

This chapter provided a brief overview of the various components of Oracle Spatial technology. First, we examined the functionality provided in Oracle Spatial. This functionality included a SQL-level data type for storing spatial data, new operators and functions to perform spatial query and analysis, a MapViewer tool for visualizing spatial data, and advanced components to perform more sophisticated analysis such as routing or network analysis. We then described how this functionality is packaged in the Database and Application Servers. Finally, we described what to expect in a typical Spatial installation and where to find appropriate Spatial files.

Starting with the next chapter, we will look at Oracle Spatial functionality in more detail. Specifically in Chapter 3, we describe how to location-enable your application.

⁴ “Standard” implies both Standard Edition One and Standard Edition.

⁵ Note that some spatial components such as GeoRaster have dependencies on other Oracle components such as interMedia and XML. You need to ensure that these components are also upgraded properly or installed if they do not exist in a custom install.

⁶ Most functions in these package bodies are linked to C/Java libraries that are included with the Oracle kernel.

⁷ The database can be created either at install time or using a variety of Oracle tools such as DBCA.