**Mapping Geometric Data with Oracle Spatial**

Pages: 1, **2**

### Querying

Oracle Spatial uses a two-tier process for querying spatial data. The first part of the query selects candidate rows based only on their spatial index. The second step of the query looks at each candidate and determines if it meets the query exactly. This two-tier approach allows for quick querying by leveraging the spatial indexes.

The first part of the query uses an Oracle Spatial function called
`SDO_FILTER`

, which is defined below.

```
SDO_FILTER(geometry1 MDSYS.SDO_GEOMETRY,
geometry2 MDSYS.SDO_GEOMETRY, params VARCHAR2)
```

The first geometry argument, `geometry1`

, is a column name of
spatially indexed geometries. The second geometry argument,
`geometry2`

, may or may not come from a table and does not need to
be indexed spatially. The final argument defines how the filtering works. If
the final argument is `querytype=WINDOW`

, filtering is done in
memory and works very well when `geometry2`

doesn't come from a
table. If the final argument is `querytype=JOIN`

,
`geometry2`

must come from a table and performance depends on the
spatial index type of the two tables. `SDO_FILTER`

returns a string
of `TRUE`

if a successful filtering occurs or `FALSE`

otherwise. An example query using `SDO_FILTER`

follows.

```
SELECT name boat_name
FROM mylake t
WHERE feature_id = 12
AND SDO_FILTER(t.shape, mdsys.sdo_geometry(2003,NULL,NULL,
mdsys.sdo_elem_info_array(1,1003,1),
mdsys.sdo_ordinate_array(2,2, 5,2, 5,5, 2,5, 2,2)),
'querytype=WINDOW') = 'TRUE';
```

This query selects all boats that have a geometry with an indexed grid
square within the polygon defined. This doesn't necessarily mean the returned
boats are within the rectangle or if they are just touching the defined
rectangle. To obtain an exact query, a second function called
`SDO_RELATE`

must be executed. `SDO_RELATE`

looks at two
geometries and determines if they interact in a specified way. It is important
to note that `SDO_RELATE`

only works on two-dimensional data. It is
defined below.

```
SDO_RELATE(geometry1 MDSYS.SDO_GEOMETRY, geometry2 MDSYS.SDO_GEOMETRY,
params VARCHAR2)
```

`SDO_RELATE`

arguments are the same as those for
`SDO_FILTER`

with the exception of the last argument. The
`params`

argument has a masktype value in addition to the querytype
value. The masktype value can take the values listed below.

`DISJOINT`

— the boundaries and interiors do not intersect`TOUCH`

— the boundaries intersect but the interiors do not intersect`OVERLAPBDYDISJOINT`

— the interior of one object intersects the boundary and interior of the other object, but the two boundaries do not intersect. This relationship occurs, for example, when a line originates outside a polygon and ends inside that polygon.`OVERLAPBDYINTERSECT`

— the boundaries and interiors of the two objects intersect`EQUAL`

— the two objects have the same boundary and interior`CONTAINS`

— the interior and boundary of one object is completely contained in the interior of the other object`COVERS`

— the interior of one object is completely contained in the interior of the other object and their boundaries intersect`INSIDE`

— the opposite of`CONTAINS`

.`A INSIDE B`

implies`B CONTAINS A`

.`COVEREDBY`

— the opposite of`COVERS`

.`A COVEREDBY B`

implies`B COVERS A`

.`ON`

— the interior and boundary of one object is on the boundary of the other object (and the second object covers the first object). This relationship occurs, for example, when a line is on the boundary of a polygon.`ANYINTERACT`

— the objects are non-disjoint.

*— Oracle Spatial User Guide and Reference 9.2*

To select all boats that are inside a defined rectangle the following query would work:

```
SELECT name boat_name
FROM mylake t
WHERE feature_id = 12
AND SDO_FILTER(t.shape, mdsys.sdo_geometry(2003,NULL,NULL,
mdsys.sdo_elem_info_array(1,1003,1),
mdsys.sdo_ordinate_array(2,2, 5,2, 5,5, 2,5, 2,2)),
'querytype=WINDOW') = 'TRUE'
AND SDO_RELATE(t.shape, mdsys.sdo_geometry(2003,NULL,NULL,
mdsys.sdo_elem_info_array(1,1003,1),
mdsys.sdo_ordinate_array(2,2, 5,2, 5,5, 2,5, 2,2)),
'masktype=INSIDE querytype=WINDOW') = 'TRUE'
```

It is also possible to combine masktypes to select sites that are inside or touching the defined polygon with the query below.

```
SELECT feature_id id
FROM mylake t
WHERE feature_id = 12
AND SDO_FILTER(t.shape, mdsys.sdo_geometry(2003,NULL,NULL,
mdsys.sdo_elem_info_array(1,1003,1),
mdsys.sdo_ordinate_array(2,2, 5,2, 5,5, 2,5, 2,2)),
'querytype=WINDOW') = 'TRUE'
AND SDO_RELATE(t.shape, mdsys.sdo_geometry(2003,NULL,NULL,
mdsys.sdo_elem_info_array(1,1003,1),
mdsys.sdo_ordinate_array(2,2, 5,2, 5,5, 2,5, 2,2)),
'masktype=INSIDE+TOUCH querytype=WINDOW') = 'TRUE'
```

Oracle Spatial provides other powerful querying functions beside
`SDO_FILTER`

and `SDO_RELATE`

. They include:

Query | Description |

`SDO_NN` | Nearest neighbor |

`SDO_SDO_WITHIN_DISTANCE` | All geometries with a certain distance |

Functions | Description |

`SDO_GEOM.SDO_MBR` | The minimum bounding rectangle for a geometry |

`SDO_GEOM.SDO_DISTANCE` | The distance between two geometries |

`SDO_GEOM.SDO_INTERSECTION` | Provides the intersection point of two geometries |

There are many more query operators and functions in Oracle Spatial. A complete list and explanation may be found in reference section below.

### Conclusion

Oracle Spatial makes it possible to combine the relational power of a database with spatial data. The ability to use indexes, various queries, and functions means complex spatial calculations may be pushed back onto large database servers. As mobile applications and technologies increase, so will the demands to store and analyze spatial data in a transactional setting.

### Reference

- Oracle Spatial Home
- Oracle Spatial Users Guide and Reference 9.2 - Requires free Oracle Technology Network login
- Open GIS Consortium
- Geographic Information Systems
- PostgeSQL GIS

*Matt Bauer
is the founder of MetroRacer, a Minneapolis based firm developing software for professional and amateur athletes.
*

Return to the O'Reilly Network.