SDO_UTIL.LINEAR

Format

SDO_UTIL.LINEAR_KEY(
     geometry  IN SDO_GEOMETRY, 
     min_x     IN NUMBER DEFAULT 0.0, 
     min_y     IN NUMBER DEFAULT 0.0, 
     max_x     IN NUMBER DEFAULT 0.0, 
     max_y     IN VARCHAR2 DEFAULT 'FALSE', 
     lvl       IN NUMBER DEFAULT 8 
     ) RETURN SDO_GEOMETRY;

Description

Returns a linear (Hilbert) key for a geometry at the specified level. (If the geometry is not a point, it uses some points on the geometry to generate the key.)

Parameters

geometry

Input geometry for which to generate a linear (Hilbert) key.

min_x

Minimum value along the x-axis to consider in generating the key. (See the Usage Notes for more information.)

min_y

Minimum value along the y-axis to consider in generating the key. (See the Usage Notes for more information.)

max_x

Maximum value along the x-axis to consider in generating the key. (See the Usage Notes for more information.)

max_y

Maximum value along the y-axis to consider in generating the key. (See the Usage Notes for more information.)

lvl

A value greater than or equal to 1, where 1 is the size of 1/4 of the coordinate system bounds. The default value is 8. (See the Usage Notes for more information.)

[??? more, especially trade-offs of smaller versus larger values ???]

Usage Notes

The main use case for this function is to be able to cluster data using this linear key so that the goemetries that are close to each other are also close to each other on the disk. Another use case is to be able to generate clusters for data using this linear key.

Linear key clustering is an efficient technique for boosting performance for large point data sets. This function is parallel enabled, and can cluster millions of rows in seconds. You can imagine the world as covered by a piece of graph paper, with each cell of the paper having a unique ID. Oracle Spatial does not actually create such cells, but when you pass a point into the SDO_UTIL.LINEAR_KEY function, it returns a "cell" ID. Many points can map to the same cell ID (thus the "clustering"). You can use the lvl parameter to affect the cell size: larger cell sizes (lvl values) will result in more points mapping to the same cell, resulting in fewer cluster groups.

For min_x, min_y, max_x, max_y, with longitude/latitude data (geodetic SRS) the values are -180, -180, 180, 180 for square cells, although you can specify -180, -90, 180, 90 if you want rectangular cells. With projected coordinate systems, the bounds values vary; for example, with World Mercator the recommended values are -20037508, -20037508, 20037508, 20037508.

The clustering results can be persisted in a table or generated "on the fly". See the Examples for an example of each approach.

Before using this function, you should validate the input geometry using the SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT function.

Examples

The following example persists the results in a table. This approach is especially recommended when clustering large data sets. The example uses a lvl value of 17, and specifies substantial degree of parallel execution through the optimizer hint (/*+ append parallel(16) */).

ALTER SESSION ENABLE PARALLEL DML;
DROP TABLE results;
CREATE TABLE results (cnt NUMBER, cell_center SDO_GEOMETRY);
INSERT /*+ append parallel(16) */ INTO results NOLOGGING 
SELECT  count(*) cnt
              ,sdo_geom.sdo_centroid(sdo_util.hhcell_boundary (cell_id,-180,-180,180,180), .05) cell_center
FROM (SELECT sdo_util.linear_key (geom, -180,-180,180,180,17) as cell_id
             FROM lon_lat_geoms)
GROUP BY cell_id;

The following performs "on the fly" clustering of point data. This example uses a lvl value of 13. A low degree of parallel execution may be sufficient for this approach, although you can specify higher values.

SELECT /*+ parallel(2) */  count(*) cnt
         ,sdo_geom.sdo_centroid(set_srid (sdo_util.hhcell_boundary (cell_id,-180,-180,180,180), 4326), .05) cell_center
FROM (SELECT sdo_util.linear_key (geom, -180,-180,180,180,13) as cell_id
         FROM lon_lat_geoms
         WHERE sdo_filter(geom,sdo_geometry(2003,4326,null,sdo_elem_info_array(1,1003,3),
                                       sdo_ordinate_array(-72,41,-71,42))) = 'TRUE')
GROUP BY cell_id;