Skip Headers
Oracle® Spatial Developer's Guide
11g Release 1 (11.1)

Part Number B28400-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

C Complex Spatial Queries: Examples

This appendix provides examples, with explanations, of queries that are more complex than the examples in the reference chapters in Part III, "Reference Information". This appendix focuses on operators that are frequently used in Spatial applications, such as SDO_WITHIN_DISTANCE and SDO_NN.

This appendix is based on input from Oracle personnel who provide support and training to Spatial users. The Oracle Spatial training course covers many of these examples, and provides additional examples and explanations.

Before you use any of the examples in this appendix, be sure you understand the usage and reference information for the relevant operator or function in Part I, "Conceptual and Usage Information" and Part III, "Reference Information".

This appendix contains the following major sections:

C.1 Tables Used in the Examples

The examples in this appendix refer to tables named GEOD_CITIES, GEOD_COUNTIES, and GEOD_INTERSTATES, which are defined as follows:

CREATE TABLE GEOD_CITIES(
  LOCATION     SDO_GEOMETRY,
  CITY         VARCHAR2(42),
  STATE_ABRV   VARCHAR2(2),
  POP90        NUMBER,
  RANK90       NUMBER);

CREATE TABLE GEOD_COUNTIES(
  COUNTY_NAME   VARCHAR2(40),
  STATE_ABRV    VARCHAR2(2),
  GEOM          SDO_GEOMETRY);

CREATE TABLE GEOD_INTERSTATES(
  HIGHWAY   VARCHAR2(35),
  GEOM      SDO_GEOMETRY);

C.2 SDO_WITHIN_DISTANCE Examples

The SDO_WITHIN_DISTANCE operator identifies the set of spatial objects that are within some specified distance of a given object. You can indicate that the distance is approximate or exact. If you specify querytype=FILTER, the distance is approximate because only a primary filter operation is performed; otherwise, the distance is exact because both primary and secondary filtering operations are performed.

Example C-1 finds all cities within 15 miles of the interstate highway I170.

Example C-1 Finding All Cities Within a Distance of a Highway

SELECT /*+ ORDERED */ c.city
FROM geod_interstates i, geod_cities c
WHERE i.highway = 'I170'
   AND sdo_within_distance (
        c.location, i.geom,
       'distance=15 unit=mile') = 'TRUE';

Example C-1 finds all cities within 15 miles ('distance=15 unit=mile') of the specified highway (i.highway = 'I170'), and by default the result is exact (because the querytype parameter was not used to limit the query to a primary filter operation). In the WHERE clause of this example:

Example C-2 finds all interstate highways within 15 miles of the city of Tampa.

Example C-2 Finding All Highways Within a Distance of a City

SELECT /*+ ORDERED */ i.highway
FROM geod_cities c, geod_interstates i
WHERE c.city = 'Tampa'
   AND sdo_within_distance (
        i.geom, c.location,
        'distance=15 unit=mile') = 'TRUE';

Example C-2 finds all highways within 15 miles ('distance=15 unit=mile') of the specified city (c.city = 'Tampa'), and by default the result is exact (because the querytype parameter was not used to limit the query to a primary filter operation). In the WHERE clause of this example:

C.3 SDO_NN Examples

The SDO_NN operator determines the nearest neighbor geometries to a geometry. No assumptions should be made about the order of the returned results. If you specify no optional parameters, one nearest neighbor geometry is returned.

If you specify the optional sdo_num_res keyword, you can request how many nearest neighbors you want, but no other conditions in the WHERE clause are evaluated, and any sdo_batch_size specification is ignored. For example, assume that you want the five closest banks from an intersection, but only where the bank name is CHASE. If the five closest banks are not named CHASE, SDO_NN with any sdo_num_res value and with sdo_batch_size=5 will return no rows because the sdo_num_res keyword only takes proximity into account, and not any sdo_batch_size specification or conditions in the WHERE clause.

If you specify the optional sdo_batch_size keyword instead of the sdo_num_res keyword, SDO_NN keeps returning neighbor geometries in distance order to the WHERE clause. If the WHERE clause specifies bank_name = 'CHASE' AND rownum < 6, you can return the five closest banks with bank_name = 'CHASE'.

SDO_NN_DISTANCE is an ancillary operator to the SDO_NN operator. It returns the distance of an object returned by the SDO_NN operator and is valid only within a call to the SDO_NN operator.

Example C-3 finds the five cities nearest to the interstate highway I170 and the distance in miles from the highway for each city, ordered by distance in miles.

Example C-3 Finding the Cities Nearest to a Highway

SELECT /*+ ORDERED */
       c.city,
       sdo_nn_distance (1) distance_in_miles
FROM geod_interstates i, 
     geod_cities c 
WHERE i.highway = 'I170'
  AND sdo_nn(c.location, i.geom,
             'sdo_num_res=5 unit=mile', 1) = 'TRUE'
ORDER BY distance_in_miles;

In Example C-3, because the /*+ ORDERED*/ optimizer hint is used, it is important to have an index on the GEOD_INTERSTATES.HIGHWAY column. In this example, the hint forces the query to locate highway I170 before it tries to find nearest neighbor geometries. In the WHERE clause of this example:

In Example C-3, ORDER BY distance_in_miles orders the results from the WHERE clause by distance in miles.

The statement in Example C-3 produces the following output (slightly reformatted for readability):

CITY                    DISTANCE_IN_MILES
----------------------  ------------------------------
St Louis                  5.36297295
Springfield              78.7997464
Peoria                  141.478022
Evansville              158.22422
Springfield             188.508631

Example C-4 extends Example C-3 by limiting the results to cities with a 1990 population over a certain number. It finds the five cities nearest to the interstate highway I170 that have a population greater than 300,000, the 1990 population for each city, and the distance in miles from the highway for each city, ordered by distance in miles.

Example C-4 Finding the Cities Above a Specified Population Nearest to a Highway

SELECT /*+ ORDERED  NO_INDEX(c pop90_idx) */
       c.city, pop90,
       sdo_nn_distance (1) distance_in_miles
FROM geod_interstates i,
     geod_cities c
WHERE i.highway = 'I170'
  AND sdo_nn(c.location, i.geom,
            'sdo_batch_size=10 unit=mile', 1) = 'TRUE'
  AND c.pop90 > 300000
  AND rownum < 6
ORDER BY distance_in_miles;

In Example C-4, because the ORDERED optimizer hint is used, it is important to have an index on the GEOD_INTERSTATES.HIGHWAY column. In this example, the hint forces the query to locate highway I170 before it tries to find nearest neighbor geometries.

To ensure correct results, disable all nonspatial indexes on columns that come from the same table as the SDO_NN search column (geometry1). In this example, the NO_INDEX(c pop90_idx) optimizer hint disables the nonspatial index on the POP90 column.

In the WHERE clause of this example:

In Example C-4, ORDER BY distance_in_miles orders the results from the WHERE clause by distance in miles.

The statement in Example C-4 produces the following output (slightly reformatted for readability):

CITY               POP90    DISTANCE_IN_MILES
-----------------  -------  ---------------------
St Louis            396685     5.36297295
Kansas City         435146   227.404883
Indianapolis        741952   234.708666
Memphis             610337   244.202072
Chicago            2783726   253.547961

C.4 SDO_AGGR_UNION Example

When you use the SDO_AGGR_UNION aggregate function, very large geometries can result. When geometries have many coordinates, spatial operations (such as union) can be time-consuming. It may be better to divide a single spatial aggregate union operation function into multiple nested aggregate functions in the same SQL statement.

Example C-5 aggregates all the counties in Texas, producing the boundary for the state of Texas.

Example C-5 Performing Aggregate Union of All Counties in Texas

SELECT sdo_aggr_union(mdsys.sdoaggrtype(aggr_geom,0.5)) aggr_geom
FROM (SELECT sdo_aggr_union(mdsys.sdoaggrtype(aggr_geom,0.5)) aggr_geom
  FROM (SELECT sdo_aggr_union(mdsys.sdoaggrtype(aggr_geom,0.5)) aggr_geom
      FROM (SELECT sdo_aggr_union(mdsys.sdoaggrtype(aggr_geom,0.5)) aggr_geom
            FROM (SELECT sdo_aggr_union(mdsys.sdoaggrtype(geom,0.5)) aggr_geom
                  FROM geod_counties WHERE state_abrv='TX'
                  GROUP BY mod(rownum,16)
                 )
            GROUP BY mod (rownum, 8)
           )
      GROUP BY mod (rownum, 4)
     )
  GROUP BY mod (rownum, 2)
 );