h3

pig repo add pgdg -u          # Add PGDG repo and update cache

CREATE EXTENSION h3;

CREATE EXTENSION h3;

SELECT h3_latlng_to_cell(POINT('37.3615593,-122.0553238'), 5);
  h3_latlng_to_cell
-------------------
 85e35e73fffffff

-- Index a location at a specified resolution (0-15)
SELECT h3_latlng_to_cell(POINT('37.3615593,-122.0553238'), 5);

-- Find the centroid of an index
SELECT h3_cell_to_latlng('85283473fffffff'::h3index);

-- Find the boundary polygon of an index
SELECT h3_cell_to_boundary('85283473fffffff'::h3index);

-- Get resolution of an index (0-15)
SELECT h3_get_resolution('85283473fffffff'::h3index);

-- Get the base cell number
SELECT h3_get_base_cell_number('85283473fffffff'::h3index);

-- Validate an H3 index
SELECT h3_is_valid_cell('85283473fffffff'::h3index);

-- Check if index is a pentagon
SELECT h3_is_pentagon('85283473fffffff'::h3index);

-- Check if resolution has Class III orientation
SELECT h3_is_res_class_iii('85283473fffffff'::h3index);

-- Find all icosahedron faces intersected by an index
SELECT h3_get_icosahedron_faces('85283473fffffff'::h3index);

-- Produce all indices within k distance of origin
SELECT h3_grid_disk('85283473fffffff'::h3index, 2);

-- Indices with distances from origin
SELECT * FROM h3_grid_disk_distances('85283473fffffff'::h3index, 2);

-- Hollow hexagonal ring at distance k
SELECT h3_grid_ring_unsafe('85283473fffffff'::h3index, 1);

-- Line of indexes between two cells (inclusive)
SELECT h3_grid_path_cells('85283473fffffff'::h3index, '8528342bfffffff'::h3index);

-- Grid distance between two indices
SELECT h3_grid_distance('85283473fffffff'::h3index, '8528342bfffffff'::h3index);

-- Local IJ coordinates
SELECT h3_cell_to_local_ij('85283473fffffff'::h3index, '8528342bfffffff'::h3index);
SELECT h3_local_ij_to_cell('85283473fffffff'::h3index, POINT(0,0));

-- Get parent cell at a coarser resolution
SELECT h3_cell_to_parent('85283473fffffff'::h3index, 3);

-- Get all children at a finer resolution
SELECT h3_cell_to_children('85283473fffffff'::h3index, 7);

-- Get center child at a finer resolution
SELECT h3_cell_to_center_child('85283473fffffff'::h3index, 7);

-- Compact an array of cells
SELECT h3_compact_cells(ARRAY['85283473fffffff'::h3index, '85283477fffffff'::h3index]);

-- Uncompact to a target resolution
SELECT h3_uncompact_cells(ARRAY['85283473fffffff'::h3index], 7);

-- Get position of child within parent's children list
SELECT h3_cell_to_child_pos('872834700ffffff'::h3index, 5);

-- Get child at a specific position
SELECT h3_child_pos_to_cell(0, '85283473fffffff'::h3index, 7);

-- Fill a polygon with hexagons at a given resolution
SELECT h3_polygon_to_cells(
    '((37.7,-122.5),(37.8,-122.5),(37.8,-122.4),(37.7,-122.4))'::polygon,
    NULL::polygon[],
    5
);

-- Get the outline polygon(s) of a set of hexagons
SELECT * FROM h3_cells_to_multi_polygon(
    ARRAY['85283473fffffff'::h3index, '85283477fffffff'::h3index]
);

-- Check if two cells are neighbors
SELECT h3_are_neighbor_cells('85283473fffffff'::h3index, '85283477fffffff'::h3index);

-- Get directed edge between neighbors
SELECT h3_cells_to_directed_edge('85283473fffffff'::h3index, '85283477fffffff'::h3index);

-- Validate an edge
SELECT h3_is_valid_directed_edge(edge) FROM ...;

-- Get origin and destination from edge
SELECT h3_get_directed_edge_origin(edge);
SELECT h3_get_directed_edge_destination(edge);

-- Get both as a record
SELECT * FROM h3_directed_edge_to_cells(edge);

-- All edges originating from a cell
SELECT h3_origin_to_directed_edges('85283473fffffff'::h3index);

-- Edge boundary coordinates
SELECT h3_directed_edge_to_boundary(edge);

-- Get a single vertex of a cell
SELECT h3_cell_to_vertex('85283473fffffff'::h3index, 0);

-- Get all vertexes of a cell
SELECT h3_cell_to_vertexes('85283473fffffff'::h3index);

-- Get geocoordinates of a vertex
SELECT h3_vertex_to_latlng(vertex);

-- Validate a vertex
SELECT h3_is_valid_vertex(vertex);

-- Great circle distance between two points (km, m, or rads)
SELECT h3_great_circle_distance(POINT(37.7,-122.5), POINT(40.7,-74.0), 'km');

-- Average hexagon area at a resolution
SELECT h3_get_hexagon_area_avg(5, 'km^2');

-- Exact area of a specific cell
SELECT h3_cell_area('85283473fffffff'::h3index, 'km^2');

-- Average edge length at a resolution
SELECT h3_get_hexagon_edge_length_avg(5, 'km');

-- Exact edge length
SELECT h3_edge_length(edge, 'km');

-- Number of unique cells at a resolution
SELECT h3_get_num_cells(5);

-- All 122 resolution-0 cells
SELECT h3_get_res_0_cells();

-- All pentagons at a resolution
SELECT h3_get_pentagons(5);

-- Grid distance operator
SELECT '85283473fffffff'::h3index <-> '8528342bfffffff'::h3index;

-- B-tree equality / inequality
SELECT a = b, a <> b FROM ...;

-- R-tree spatial operators
SELECT a && b  -- intersects
SELECT a @> b  -- contains
SELECT a <@ b  -- contained by

CREATE INDEX spgist_idx ON h3_data USING spgist(hex h3index_ops_experimental);

-- H3 index to/from bigint
SELECT '85283473fffffff'::h3index::bigint;
SELECT 599686042433355775::bigint::h3index;

-- H3 index to point
SELECT '85283473fffffff'::h3index::point;

CREATE EXTENSION h3_postgis CASCADE;

-- Index a PostGIS geometry at a resolution
SELECT h3_latlng_to_cell(geom, 9) FROM points;

-- Convert H3 cell to PostGIS geometry/geography
SELECT h3_cell_to_geometry('85283473fffffff'::h3index);
SELECT h3_cell_to_geography('85283473fffffff'::h3index);

-- Cell boundary as PostGIS geometry (splits at antimeridian)
SELECT h3_cell_to_boundary_geometry('85283473fffffff'::h3index);

-- Fill PostGIS polygon with H3 cells
SELECT h3_polygon_to_cells(geom, 7) FROM polygons;

-- Convert H3 cells back to PostGIS multipolygon
SELECT h3_cells_to_multi_polygon_geometry(ARRAY['85283473fffffff'::h3index]);

-- PostGIS indexing operators
SELECT geom @ 9 FROM points;  -- geometry @ resolution

SELECT
    (summary).h3 AS h3,
    (h3_raster_summary_stats_agg((summary).stats)).*
FROM (
    SELECT h3_raster_summary(rast, 8) AS summary
    FROM rasters
) t
GROUP BY 1;

SELECT
    h3,
    jsonb_object_agg(
        concat('class_', val::text),
        h3_raster_class_summary_item_to_jsonb(item)
        ORDER BY val
    ) AS summary
FROM (
    SELECT h3, val, h3_raster_class_summary_item_agg(summary) AS item
    FROM rasters, h3_raster_class_summary(rast, 8)
    GROUP BY 1, 2
) t
GROUP BY 1;