35.54 SDO_UTIL.H3SUM_CREATE_TABLE

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
);

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 numeric columns in the input table to summarize in the result table, along with the rule for summarizing the data. Each field is a column name and a summarizing math operation separated by a comma, and each field is separated by a semi-colon. The legal math operations are MIN, MAX, AVG, SUM, CNT. If no math operation is specified, CNT is assumed. The values are computed using the SQL functions MIN, MAX, AVG, SUM, and COUNT respectively. The value ‘1’ can be used as a column name and works as a column of values which are all 1. The values are computed using the SQL functions MIN, MAX, AVG, SUM, and COUNT respectively.

For example, 'Name,CNT; Vehicles,AVG; col2; col3,MAX' would create columns NAME_CNT (counting the non-null Name values), VEHICLES_AVG, COL2_CNT, COL3_MAX.

max_H3_level

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

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. Only numeric data values can be in the H3 summary output table (including a simple count of the number of points, indicated using “1,CNT” as a data column).

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). Therefore, each row of the table represents more points:

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.