From Pro Oracle Spatial: Apress, November 2004.

Part 1 | Part 2

To run the examples in this chapter, you need to load three datasets in the spatial schema as follows. Please refer to the Introduction for instructions on creating the spatial schema and other setup details.

     imp spatial/spatial file=gc.dmp ignore=y full=y
     imp spatial/spatial file=map_large.dmp tables=us_interstates 
     imp spatial/spatial file=map_detailed.dmp tables=us_restaurants 

In Chapter 1, we observed that spatial information can add value to a range of different applications. We examined the benefits of storing spatial information together with other data in the database.

The Spatial technology suite in Oracle enables storage of spatial data in the database and facilitates different types of analyses on spatial data. This chapter provides an overview of the Spatial technology suite and covers the following topics:

• An overview of the Oracle Spatial architecture and technology.
• An examination of the functionality of different components of this Spatial technology suite in Oracle. This includes a brief introduction to the data type that stores spatial data (SDO_GEOMETRY), the query predicates for performing spatial query and analysis, and additional functionality to perform visualization.
• A description of how this functionality is packaged into different products that are shipped with different editions of Oracle software. We will discuss the relative merits of each product in turn.
• What to expect in a typical install of Oracle Spatial. This knowledge should get you off to a smooth start in spatially enabling your application.

Technology and Architecture Overview

Oracle Spatial technology is spread across two tiers: the Database Server and the Application Server. Figure 2-1 depicts the various components that comprise Oracle’s spatial technology stack and indicates the distribution of the components across the Database Server and Application Server tiers. Basic components that are provided as part of Oracle Database Server 10g include storage model, query and analysis tools, and location-enabling/loading utilities. The MapViewer component is provided in Oracle Application Server 10g.

Figure 2-1. Oracle Spatial technology components

The basic components from Figure 2-1 can be described as follows:

• Data model: Oracle Spatial uses a SQL data type, SDO_GEOMETRY, to store spatial data inside an Oracle database. Users may define tables containing columns of type SDO_GEOMETRY to store the locations of customers, stores, restaurants, and so on, or the locations and spatial extents of geographic entities such as roads, interstates, parks, and land parcels. This data type is described in detail in Chapter 4.
• Location enabling: Users can add SDO_GEOMETRY columns to application tables. This process is described in detail in Chapter 3. Users can populate the tables with SDO_GEOMETRY data using standard Oracle utilities such as SQL*Loader, Import, and Export. This process is described in Chapter 5. Alternatively, users can convert implicit spatial information, such as street addresses, into SDO_GEOMETRY columns using the geocoder component of Oracle Spatial, as described in Chapter 6.
• Spatial query and analysis: Users can query and manipulate the SDO_GEOMETRY data using the query and analysis component, comprising the Index and Geometry Engines. Full details of this process are given in Chapters 8 and 9.
• Advanced Spatial Engine: This component comprises several components that cater to sophisticated spatial applications, such as GIS and bioinformatics. This includes, for example, the GeoRaster component that allows storage of spatial objects using images (groups of pixels) rather than points, lines, and vertices. These components are covered
  in Appendixes A through D.
• Visualization: The Application Server components of Oracle’s spatial technology include the means to visualize spatial data via the MapViewer tool. MapViewer renders the spatial data that is stored in SDO_GEOMETRY columns of Oracle tables as displayable maps. This feature is described in detail in Chapter 11.

Note also in Figure 2-1 that third-party tools may access spatial data either through the Application Server or directly from the database using SQL, OCI, JDBC, or other appropriate interfaces. Programming with spatial data via these APIs is described in Chapter 7.

Note: The core subset of this functionality (known as the Locator component) is included for free in all editions of the database (essentially, the SDO_GEOMETRY data type and the Index Engine). The rest of the components, along with the data type and the Index Engine, are packaged in a priced option of the Enterprise Edition of the database (known as the Spatial option).We discuss this in more detail later in this chapter.

In the following sections, we’ll preview these components and introduce you to some (very basic) SQL to create a table that stores spatial data, to populate that data, and to perform simple proximity analysis. All of these topics are covered in full detail in subsequent chapters, as described previously, but this should serve as a useful introduction to the technology and help you to get started.

Getting Started with Oracle Spatial

Oracle Spatial technology is automatically installed with the Standard or Enterprise Edition of an Oracle database server. So, as long as you have one of these editions of version 10.1.0.2 or higher, you should be able to work through the simple examples in the coming sections. If you encounter any problems, you can refer to the “What to Expect in an Oracle Spatial Install” section at the end of this chapter. Note that the Database Server license includes only a few of the functions described in this section. To use the rest of the functionality, you should obtain a separate product license for the Spatial option.

Data Model: Storing Spatial Data

In Chapter 1, we briefly discussed the idea that spatial information is specified using two components: a location with respect to some origin and a geometric shape.

• Location specifies where the data is located with respect to a two-, three-, or fourdimensional coordinate space. For instance, the center of San Francisco is located at coordinates (.122.436, .37.719) in the two-dimensional “latitude, longitude” space.
• Shape specifies the geometric structure of the data. Point, line, and polygon are examples of possible shapes. For instance, the center of San Francisco is located at coordinates (.122.436, .37.719) in the two-dimensional “latitude, longitude” space and is a point shape. Note that point specifies both a location and a default shape. Alternately, shape could specify a line or a polygon connecting multiple points (specified by their locations). For instance, the city boundary of San Francisco could be a polygon connecting multiple points.

  In some applications, the shapes could be more complex and could have multiple polygons, and/or polygons containing holes. For instance, the state boundaries for Texas and California include multiple polygons and some with islands. In general, spatial information, occurring in GIS, CAD/CAM, or simple location-enabled applications, could be arbitrarily complex.

The SDO_GEOMETRY data type captures the location and shape information of data rows in a table. This data type is internally represented as an Oracle object data type. It can model different shapes such as points, lines, polygons, and appropriate combinations of each of these. In short, it can model spatial data occurring in most spatial applications and is conformant with the Open GIS Consortium (OGC) Geometry model.¹

Chapter 4 provides details about what types of spatial data SDO_GEOMETRY can model and what it cannot, and it also covers the structure of SDO_GEOMETRY and the tools to construct, validate, and debug SDO_GEOMETRY objects. For now, it is sufficient to understand that we can create tables with SDO_GEOMETRY columns to store the locations of objects.

Location Enabling

We can create tables with the SDO_GEOMETRY columns to store locations. For instance, we can create the us_restaurants_new² table as shown in Listing 2-1.

SQL> CREATE TABLE  us_restaurants_new
(
  id             NUMBER,
  poi_name       VARCHAR2(32),
location       SDO_GEOMETRY    -- New column to store locations
);

Listing 2-1. Creating the us_restaurants_new Table

Now that you know basically how to create tables to store locations, let’s briefly examine the tools to populate such tables. Since SDO_GEOMETRY is an object type, just like any other object type, you can populate an SDO_GEOMETRY column using the corresponding object constructor. For example, you can insert a location of (–87, –78) for a Pizza Hut restaurant into the us_restaurants table as shown in Listing 2-2.

SQL> INSERT INTO  us_restaurants_new  VALUES
(
  1,
  'PIZZA HUT',
SDO_GEOMETRY
  (
    2001,  -- SDO_GTYPE attribute: "2" in 2001 specifies dimensionality is 2.
    NULL,    -- other fields are set to NULL.
    SDO_POINT_TYPE  -- Specifies the coordinates of the point
    (
      -87,  -- first ordinate, i.e., value in longitude dimension
      -78,  -- second ordinate, i.e., value in latitude dimension
      NULL  -- third ordinate, if any
    ),
    NULL,
    NULL
  )
);

Listing 2-2. Inserting aValue for the SDO_GEOMETRY Column in an Oracle Table

The SDO_GEOMETRY object is instantiated using the object constructor. In this constructor, the first argument, 2001, specifies that it is a two-dimensional point geometry (a line would be represented by 2002, a polygon by 2003, and a collection by 2004).

The fourth argument stores the location of this point in the SDO_POINT attribute using the SDO_POINT_TYPE constructor. Here, we store the geographic coordinates (–87, –78). In this example, the remaining arguments are set to NULL.

Note: In Chapter 4, we examine the structure of the SDO_GEOMETRY type in detail and describe how to choose appropriate values for each field of the SDO_GEOMETRY type.

Note that the preceding example shows a single SQL INSERT statement. Data loading can also be performed in bulk using Oracle utilities such as SQL*Loader, Import/Export, or programmatic interfaces such as OCI, OCCI, and JDBC. These utilities and interfaces come in very handy when populating data from GIS vendors or data suppliers.

In some applications, spatial information is not explicitly available as coordinates. Instead, the address data of objects is usually the only spatial information available. You can convert such address data into an SDO_GEOMETRY object using the geocoder component (provided with the Spatial option). The geocoder takes a postal address, consults an internal countrywide database of addresses and locations, and computes the longitude, latitude coordinates for the specified address. This process is referred to as geocoding in spatial terminology. The address/location database is usually provided by third-party data vendors. For the United States, Canada, and Europe, NAVTEQ and Tele Atlas provide such data.

Listing 2-3 shows how to use the geocoder to obtain the coordinates in the form of an SDO_GEOMETRY object for the address '3746 CONNECTICUT AVE NW' in Washington, D.C.

SQL> SELECT
SDO_GCDR.GEOCODE_AS_GEOMETRY
(
  'SPATIAL',              -- Spatial schema storing the geocoder data
  SDO_KEYWORDARRAY        -- Object combining different address components
  (
    '3746 CONNECTICUT AVE NW',
    'WASHINGTON, DC 20008'
  ),
  'US'                    -- Name of the country
) geom
FROM DUAL ;
GEOM(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
--------------------------------------------------------------------
SDO_GEOMETRY
(
  2001,
  8307,
  SDO_POINT_TYPE(-77.060283, 38.9387083, NULL),
  NULL,
  NULL
)

Listing 2-3. Converting Address Data (Implicit Spatial Information) to the SDO_GEOMETRY (Explicit Spatial Information) Object

This geocoding function, geocode_as_geometry, takes three arguments. The first argument is the schema. In this example, we use the “spatial” schema. The second argument specifies an SDO_KEYWORDARRAY object, composed from different components of an address. In this example, SDO_KEYWORDARRAY is constructed out of the street component '3746 Connecticut Ave NW' and the city/zip code component 'Washington, DC 20008'. The third argument to the geocoding function specifies the 'US' dataset that is being used to geocode the specified street address. The function returns an SDO_GEOMETRY type with the longitude set to –77.060283 and the latitude set to 38.9387083.

The geocoder can also perform fuzzy matching (via tolerance parameters, which we’ll cover in the next chapter). In the same way that search engines can search on related words as well as exact words, Oracle can perform fuzzy matching on the street names and so on. So, for example, suppose the address field in the preceding example was misspelled as 'CONNETICUT AVE'. The geocoder could perform approximate matching to match the misspelled fields with those in the database.

Note that the SDO_GEOMETRY data type is just like any other object type in the database. Users can view the data, and examine and modify the attributes. In contrast, several GIS data vendors and partners have their own proprietary binary formats for representing spatial information. These vendors usually provide tools for loading the data or converting the data into standard Oracle formats. Discussion of these tools, however, is beyond the scope of this book.

Query and Analysis

Now that you’ve seen how to define SDO_GEOMETRY for storage of spatial data in Oracle, and how to populate Spatial tables with data, the next thing to look at is how to query and analyze this SDO_GEOMETRY data.

The query and analysis component provides the core functionality for querying and analyzing spatial geometries. This component has two subcomponents: a Geometry Engine and an Index Engine. It is via these components that we perform our spatial queries and analysis, for example, to identify the five nearest restaurants along Interstate 795 or the five nearest hospitals to a construction site.

The Geometry Engine

The Geometry Engine provides functions to analyze, compare, and manipulate geometries. For instance, you could use the Geometry Engine functionality to identify the nearest five restaurants on I-795 in the greater Washington, D.C. area. This involves computing the
distance between I-795 and all the restaurants in the us_restaurants table, sorting them in order of increasing distance, and returning the top five restaurants. The SQL in Listing 2-4 illustrates this operation.

SQL> SELECT poi_name
FROM
  (
    SELECT poi_name,
      SDO_GEOM.SDO_DISTANCE(P.location, I.geom, 0.5) distance
    FROM us_interstates I, us_restaurants P
    WHERE I.interstate = 'I795'
      ORDER BY distance
  )
WHERE ROWNUM <= 5;

POI_NAME
-----------------------------------
PIZZA BOLI'S
BLAIR MANSION INN DINNER THEATER
KFC
CHINA HUT
PIZZA HUT

5 rows selected.

Listing 2-4. Finding the Five Nearest Restaurants on I-795

Observe that the inner SELECT clause computes the distance between I-795 (which is not a major highway) and each “restaurant” row of the us_restaurants table using the Geometry Engine function SDO_GEOM.SDO_DISTANCE. Also, note that the ORDER BY clause sorts the results in ascending order of distance. The outer SELECT statement selects the first five rows, or the five nearest restaurants.

In the preceding query, the location of the I-795 highway is compared with every restaurant row of the table, irrespective of how far they are from I-795. This could mean considerable time is spent in processing rows for restaurants that are too far from the I-795 highway and hence are irrelevant to the query. To speed up query processing by minimizing the processing overhead, we need to create indexes on the location of the restaurants.

The Index Engine

Oracle Spatial provides the spatial Index Engine for this purpose. Listing 2-5 shows an example of how to create an index on the locations of restaurants.

SQL> DROP INDEX us_restaurants_sidx; 
SQL> CREATE INDEX us_restaurants_sidx ON us_restaurants(location)
INDEXTYPE IS mdsys.spatial_index; 

Listing 2-5. Creating an Index on Locations (SDO_GEOMETRY Column) of Restaurants

Listing 2-5 first drops the index that exists. In the second and third lines, it shows the SQL for creating the spatial index. Note that the clause INDEXTYPE tells the database to create a spatial index on the location (SDO_GEOMETRY) column of the us_restaurants table. This index is a specialized index to cater to the SDO_GEOMETRY data. Using such an index, the Index Engine in Oracle Spatial prunes far-away rows from query processing and thus speeds up the query for most applications. The Index Engine provides equivalent functions, referred to as operators, for identifying rows of the table that satisfy a specified proximity predicate such as closeness to I-795. You can rewrite the preceding query to find the five nearest restaurants to I-795 using such index-based operators. Listing 2-6 shows the resulting query.

SQL> SELECT poi_name
FROM us_interstates I, us_restaurants P
WHERE I.interstate = 'I795'
  AND SDO_NN(P.location, I.geom) ='TRUE'
  AND ROWNUM <= 5;
POI_NAME
-----------------------------------
PIZZA BOLI'S
BLAIR MANSION INN DINNER THEATER
KFC
CHINA HUT
PIZZA HUT

5 rows selected.

Listing 2-6. Finding the Five Nearest Restaurants on I-795 Using the Spatial Index

Note that this query returns the same five rows as Listing 2-4. However, this query has a simpler structure with no subqueries. It uses only a new index-based operator called SDO_NN, with NN being short for Nearest-Neighbor. This index-based operator returns rows of the us_restaurants table whenever the location column is close to the I-795 highway geometry. The SDO_NN operator returns these rows in order of proximity to the I-795 geometry. So, the row with closest location is returned first, the next closest next, and so on. The ROWNUM predicate determines how many close restaurants need to be returned in the query. The query uses a spatial index and examines only those rows that are likely to be close to the location of I-795. Consequently, it is likely to execute faster than the query in Listing 2-4.

As a variation on this, suppose that instead of having to find the five nearest restaurants on I-795, you wish to identify all restaurants within 50 kilometers of I-795. One way to accomplish this would be to construct a buffer around the I-795 highway and determine all businesses inside this buffer geometry. Figure 2-2 shows an example. I-795 is shown in black. The 50 km buffer is shown by the gray oval around it, and the restaurants inside this buffer are shown by x marks.

Figure 2-2. Restaurants in the 50 km buffer around I-795

Listing 2-7 shows the corresponding SQL query and the results.

SQL> SELECT POI_NAME
FROM us_interstates I, us_restaurants P
WHERE
  SDO_ANYINTERACT
  (
    P.location,
    SDO_GEOM.SDO_BUFFER(I.geom, 50, 0.5, 'UNIT=KM')
  ) ='TRUE'
  AND I.interstate='I795' ;
POI_NAME
-----------------------------------
SPICY DELIGHT
PHILLY'S STEAK EXPRESS
EL TAMARINDO
MCDONALD'S
PIZZA HUT
CHINA HUT
KFC
BLAIR MANSION INN DINNER THEATER
PIZZA BOLI'S

9 rows selected.

Listing 2-7. Identifying All Restaurants in a 50 km Radius Around I-795

The function SDO_ANYINTERACT is an index-based operator just like the SDO_NN operator in Listing 2-6. This operator identifies all rows of us_restaurants where the locations intersect with the geometry passed in as the second parameter. The second parameter, in this case, is the result returned by an SDO_BUFFER function. The SDO_BUFFER function generates and returns a 50 km buffer around the I-795 geometry. This SDO_BUFFER function is part of the Geometry Engine, which also provides additional functions to facilitate more complex analysis and manipulation of spatial information.

Note that the number of restaurants returned in Listing 2-7 is nine, as opposed to five in Listings 2-4 and 2-6. This means that we may not know the cardinality of the result set when we use a query buffer. With an SDO_ANYINTERACT operator, we may get more answers than we expect, or fewer answers. The cardinality of the result set depends on distribution of the data (in other words, the restaurants). In general, when you know how far to search (for example, a 50 km radius, as in Listing 2-7), you may use the SDO_BUFFER and SDO_ANYINTERACT functions.³ Alternatively, if you know how many results you wish to return, then you should use the SDO_NN function, as described in Listing 2-6. In Chapters 8 and 9, we will describe in greater detail the different operators and functions in the Index and Geometry Engines.

¹ Open GIS Consortium, Inc., “OpenGIS Simple Features Specification for SQL, Revision 1.1,” http://www.opengis.org/docs/99-049.pdf, May 5, 1999.

² Note that the us_restaurants table already exists. So we name this new table us_restaurants_new.

³ In Chapter 8, we will describe a better alternative using SDO_WITHIN_DISTANCE operator.