SDO_UTIL.H3SUM_CREATE

Format

SDO_UTIL.H3SUM_CREATE_TABLE(
  table_out     IN VARCHAR2,  
  table_in      IN VARCHAR2,
  geomcol_spec  IN VARCHAR2,
  col_spec      IN VARCHAR2,
  max_H3_level  IN NUMBER DEFAULT 15,
  dop           IN NUMBER DEFAULT 16
);

Description

Creates an H3 summary table for geodetic point data.

Parameters

table_out

The name of the table to create.

table_in

The name of the input table, which contains point geometry information in a geodetic (longitude, latitude) coordinate system.

geomcol_spec

A description of the columns containing the point geometry. This is either the name of a column that contains the point geometries (for example, 'LOCATION'), or the names of two NUMBER columns that form a (longitude, latitude) pair, separated by a comma (for example, 'X, Y').

col_spec

A list of columns in the input table to summarize in the result table, along with the rule for summarizing the data. Each field is specified with a column name and an aggregating operation (separated by a comma), and each field is separated by a semi-colon. The supported operations are MIN, MAX, AVG, SUM, CNT, and ANY. The values are computed using the SQL functions MIN, MAX, AVG, SUM, COUNT and ANY_VALUE respectively. If no operation is specified, CNT is assumed. The value ‘1’ can be used as a column name and works as a column of values which are all 1.

For example, 'Name,CNT; Vehicles,AVG; col2; col3,MAX; col4,ANY' would create columns NAME_CNT (counting the non-null Name values), VEHICLES_AVG, COL2_CNT, COL3_MAX, and COL4_ANY. COUNT and ANY are the only aggregations that can be used on a non-numeric column.

max_H3_level

Specifies the maximum level (smallest cells) to create in the summary table.

dop

Controls the degree of parallelization.

If dop is NULL, then no parallelization is requested. If the parameter is not specified, then the default is 16.

The H3SUM table is created in two steps: first the data table itself is created; then an index is created on the table. The dop parameter is used both for the table creation and for the index creation.

Usage Notes

Creates a new table which summarizes the point data from a specified input table into one data row per H3 hex, per H3 resolution level. Only geometries which are single points are included; non-point and multipoint data is ignored. H3 values are computed from the specified geometry columns. H3 key columns in the source table (if any) are ignored.

All points which are in the same hex have their data values combined. The col_spec specifies which numeric columns you want in the resulting output (H3 summary) table, and how you want the values combined.

The table is indexed for fast access by the SDO_UTIL.H3SUM_GET_CURSOR and SDO_UTIL.H3SUM_VECTORTILE functions.

Examples

Consider, the following WORLD table as an example:

SQL> describe WORLD
 Name                             Null?    Type
 -------------------------------- -------- -------------------
 X                                         NUMBER
 Y                                         NUMBER
 ID                                        NUMBER
 PT                                        PUBLIC.SDO_GEOMETRY
 INFO                                      VARCHAR2(20)

The following code uses the SDO_UTIL.H3SUM_CREATE_TABLE procedure to create an H3 summary table which simply counts the number of points that are combined into each hex:

begin
sdo_util.h3sum_create_table('WORLD_H3', 'WORLD', 'pt', '1,CNT');
end;
/

On running the following SQL query on the H3 summary table, it is observed that although the number of points are the same at each level, the hexes are larger and fewer at the coarser levels (lower level numbers). All the data that was in level 15 has been combined into fewer (larger) hexes at the coarser levels.

SQL> select levelnum, count(1), sum(id_cnt) from WORLD_H3 group by levelnum order by levelnum;

  LEVELNUM   COUNT(1) SUM(ID_CNT)
---------- ---------- -----------
         0        122       64621
         1        842       64621
         2       5882       64621
         3      38781       64621
         4      57074       64621
         5      61857       64621
         6      63650       64621
         7      64329       64621
         8      64442       64621
         9      64442       64621
        10      64442       64621

  LEVELNUM   COUNT(1) SUM(ID_CNT)
---------- ---------- -----------
        11      64442       64621
        12      64442       64621
        13      64442       64621
        14      64442       64621
        15      64442       64621

16 rows selected.

35.54 SDO_UTIL.H3SUM_CREATE_TABLE