SDO_UTIL.H3SUM_AS

Format

SDO_UTIL.H3SUM_AS_TABLE(
  h3table_in      VARCHAR2,
  levelnum        NUMBER,
  mbr             SDO_GEOMETRY
)RETURN H3SUM_TAB_T PIPELINED;

Description

Returns rows from an H3 summary table as a table of type H3SUM_TAB_T .

Parameters

h3table_in: The name of the H3 summary table to query.
levelnum: The H3 level to query within the H3 summary table.
mbr: The search query.

Usage Notes

The returned table has a fixed structure, with all the numeric columns returned as a single array. The information for these columns is implicit in the names of the columns in the H3 summary table, returned in the same order.

TYPE h3sum_row_t IS RECORD(
  hex           sdo_geometry,     -- The geometry of the hex
  levelnum      number(2),        -- The H3 level number
  h3            raw(8),           -- The h3 code of this cell
  cols          sdo_number_array  -- The payload data
);

TYPE h3sum_tab_t IS TABLE OF h3sum_row_t;

Examples

The following H3 summary table is created with three data fields: ID_CNT, ID_MIN, and ID_MAX. Every summary table automatically includes the LEVELNUM and KEY columns.

SQL> CREATE TABLE PARIS AS (SELECT * 
    FROM SDO_UTIL.H3SUM_AS_TABLE('WORLD_H3', 2, SDO_GEOMETRY(2003, 4326, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 3),
         SDO_ORDINATE_ARRAY(48.8383005, 2.34064005, 48.8636193, 2.36593751))));

Table created.

SQL> DESCRIBE PARIS;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 HEX                                                MDSYS.SDO_GEOMETRY
 LEVELNUM                                           NUMBER(2)
 H3                                                 RAW(8)
 COLS                                               MDSYS.SDO_NUMBER_ARRAY

SQL> SELECT * FROM PARIS WHERE ROWNUM < 2;

HEX(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
--------------------------------------------------------------------------------
  LEVELNUM H3
---------- ----------------
COLS
--------------------------------------------------------------------------------
SDO_GEOMETRY(2003, 4326, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARRAY(48.4824473, .425072204, 48.9470689, 1.89827044, 47.8984915, 3.15671027, 46.3634494, 2.96578105, 45.8697755, 1.48783745, 46.9399386, .205091826, 48.4824473, .425072204))
         2 0827A17FFFFFFFFF
SDO_NUMBER_ARRAY(6, 40725, 40906)

The values (6, 40725, 40906) in the preceding query output correspond to the three user-data columns, ID_CNT, ID_MIN, and ID_MAX in the same order as they occur in the H3 summary table.

SQL> DESCRIBE WORLD_H3;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 LEVELNUM                                           NUMBER(2)
 KEY                                                RAW(8)
 ID_CNT                                             NUMBER
 ID_MIN                                             NUMBER
 ID_MAX                                             NUMBER