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