Speeding up geospatial queries with search optimization

The search optimization service can improve the performance of queries with predicates that use geospatial functions with GEOGRAPHY objects.

Note

You must enable this feature for specific columns using the ON clause in the ALTER TABLE … ADD SEARCH OPTIMIZATION command. For example:

ALTER TABLE mytable ADD SEARCH OPTIMIZATION ON GEO(mygeocol);
Copy

Queries that use geospatial functions are not improved if you omit the ON clause.

The following sections provide more details:

Note

GEOMETRY objects are not yet supported.

Supported predicates with geospatial functions

For queries with predicates that use the following functions:

The search optimization service can improve performance if:

  • One input expression is a GEOGRAPHY column in a table, and

  • The other input expression is a GEOGRAPHY constant (created through a conversion or constructor function).

  • For ST_DWITHIN, the distance argument is a non-negative REAL constant.

Note that this feature has the same limitations that apply to the search optimization service.

Other performance considerations

Because the search optimization service is designed for predicates that are highly selective and because predicates filter by proximity between geospatial objects, clustering geospatial objects by proximity in the table can result in better performance. You can cluster your data either by specifying the sort order when loading the data or by using Automatic Clustering, depending on whether the base table changes frequently:

Loading Pre-Sorted Data

If the data in your base table does not change often, you can specify the sort order when loading the data. You can then enable search optimization on the GEOGRAPHY column. For example:

CREATE TABLE new_table AS SELECT * FROM source_table ORDER BY st_geohash(geom);
ALTER TABLE new_table ADD SEARCH OPTIMIZATION ON GEO(geom);
Copy

After every large change made to your base data, you can manually re-sort the data.

Automatic clustering

If there are frequent updates to your base table, you can use the ALTER TABLE … CLUSTER BY … command to enable Automatic Clustering so the table is automatically reclustered as it changes.

The following example adds a new column geom_geohash of the type VARCHAR and stores the geohash or H3 index of the GEOGRAPHY column geom in that new column. It then enables Automatic Clustering with the new column as the cluster key. This approach will automatically recluster the parts of the table that change.

CREATE TABLE new_table AS SELECT *, ST_GEOHASH(geom) AS geom_geohash FROM source_table;
ALTER TABLE new_table CLUSTER BY (geom_geohash);
ALTER TABLE new_table ADD SEARCH OPTIMIZATION ON GEO(geom);
Copy

Examples that use geospatial functions

The following statements create and configure the table used in the examples in this section. The last statement uses the ON clause in ALTER TABLE … ADD SEARCH OPTIMIZATION command to add search optimization for the g1 GEOGRAPHY column.

CREATE OR REPLACE TABLE geospatial_table (id NUMBER, g1 GEOGRAPHY);
INSERT INTO geospatial_table VALUES
  (1, 'POINT(-122.35 37.55)'),
  (2, 'LINESTRING(-124.20 42.00, -120.01 41.99)'),
  (3, 'POLYGON((0 0, 2 0, 2 2, 0 2, 0 0))');
ALTER TABLE geospatial_table ADD SEARCH OPTIMIZATION ON GEO(g1);
Copy

Examples of supported predicates

The following query is an example of a query supported by the search optimization service. The search optimization service can use search access paths to improve the performance of this query:

SELECT id FROM geospatial_table WHERE
  ST_INTERSECTS(
    g1,
    TO_GEOGRAPHY('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'));
Copy

The following are examples of additional predicates that are supported by the search optimization service:

...
  ST_INTERSECTS(
    TO_GEOGRAPHY('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'),
    g1)
Copy
...
  ST_CONTAINS(
    TO_GEOGRAPHY('POLYGON((-74.17 40.64, -74.1796875 40.58, -74.09 40.58, -74.09 40.64, -74.17 40.64))'),
    g1)
Copy
...
  ST_CONTAINS(
    g1,
    TO_GEOGRAPHY('MULTIPOINT((0 0), (1 1))'))
Copy
...
  ST_WITHIN(
   TO_GEOGRAPHY('{"type" : "MultiPoint","coordinates" : [[-122.30, 37.55], [-122.20, 47.61]]}'),
   g1)
Copy
...
  ST_WITHIN(
    g1,
    TO_GEOGRAPHY('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'))
Copy
...
  ST_COVERS(
    TO_GEOGRAPHY('POLYGON((-1 -1, -1 4, 4 4, 4 -1, -1 -1))'),
    g1)
Copy
...
  ST_COVERS(
    g1,
    TO_GEOGRAPHY('POINT(0 0)'))
Copy
...
  ST_COVEREDBY(
    TO_GEOGRAPHY('POLYGON((1 1, 2 1, 2 2, 1 2, 1 1))'),
    g1)
Copy
...
  ST_COVEREDBY(
    g1,
    TO_GEOGRAPHY('POINT(-122.35 37.55)'))
Copy
...
  ST_DWITHIN(
    TO_GEOGRAPHY('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'),
    g1,
    100000)
Copy
...
  ST_DWITHIN(
    g1,
    TO_GEOGRAPHY('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'),
    100000)
Copy

Examples of constructing GEOGRAPHY constants

The following are examples of predicates that use different conversion and constructor functions for the GEOGRAPHY constant.

...
  ST_INTERSECTS(
    g1,
    ST_GEOGRAPHYFROMWKT('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'))
Copy
...
  ST_INTERSECTS(
    ST_GEOGFROMTEXT('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'),
    g1)
Copy
...
  ST_CONTAINS(
    ST_GEOGRAPHYFROMEWKT('POLYGON((-74.17 40.64, -74.1796875 40.58, -74.09 40.58, -74.09 40.64, -74.17 40.64))'),
    g1)
Copy
...
  ST_WITHIN(
    ST_GEOGRAPHYFROMWKB('01010000006666666666965EC06666666666C64240'),
    g1)
Copy
...
  ST_COVERS(
    g1,
    ST_MAKEPOINT(0.2, 0.8))
Copy
...
  ST_INTERSECTS(
    g1,
    ST_MAKELINE(
      TO_GEOGRAPHY('MULTIPOINT((0 0), (1 1))'),
      TO_GEOGRAPHY('POINT(0.8 0.2)')))
Copy
...
  ST_INTERSECTS(
    ST_POLYGON(
      TO_GEOGRAPHY('SRID=4326;LINESTRING(0.0 0.0, 1.0 0.0, 1.0 2.0, 0.0 2.0, 0.0 0.0)')),
    g1)
Copy
...
  ST_WITHIN(
    g1,
    TRY_TO_GEOGRAPHY('POLYGON((-1 -1, -1 4, 4 4, 4 -1, -1 -1))'))
Copy
...
  ST_COVERS(
    g1,
    ST_GEOGPOINTFROMGEOHASH('s00'))
Copy
Language: English