SDO_UTIL.H3SUM_AS_TABLE
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 NUMBERRelated Topics