h3

pig repo add pgdg -u          # 添加 PGDG 仓库并更新缓存

CREATE EXTENSION h3;

CREATE EXTENSION h3;

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

-- 在指定分辨率（0-15）索引一个位置
SELECT h3_latlng_to_cell(POINT('37.3615593,-122.0553238'), 5);

-- 查找索引的质心
SELECT h3_cell_to_latlng('85283473fffffff'::h3index);

-- 查找索引的边界多边形
SELECT h3_cell_to_boundary('85283473fffffff'::h3index);

-- 获取索引的分辨率（0-15）
SELECT h3_get_resolution('85283473fffffff'::h3index);

-- 获取基础单元格编号
SELECT h3_get_base_cell_number('85283473fffffff'::h3index);

-- 验证 H3 索引
SELECT h3_is_valid_cell('85283473fffffff'::h3index);

-- 检查索引是否为五边形
SELECT h3_is_pentagon('85283473fffffff'::h3index);

-- 检查分辨率是否为 Class III 方向
SELECT h3_is_res_class_iii('85283473fffffff'::h3index);

-- 查找索引相交的所有二十面体面
SELECT h3_get_icosahedron_faces('85283473fffffff'::h3index);

-- 获取距起点 k 步以内的所有索引
SELECT h3_grid_disk('85283473fffffff'::h3index, 2);

-- 带距离的索引
SELECT * FROM h3_grid_disk_distances('85283473fffffff'::h3index, 2);

-- 距离为 k 的空心六边形环
SELECT h3_grid_ring_unsafe('85283473fffffff'::h3index, 1);

-- 两个单元格之间的索引路径（含端点）
SELECT h3_grid_path_cells('85283473fffffff'::h3index, '8528342bfffffff'::h3index);

-- 两个索引之间的网格距离
SELECT h3_grid_distance('85283473fffffff'::h3index, '8528342bfffffff'::h3index);

-- 局部 IJ 坐标
SELECT h3_cell_to_local_ij('85283473fffffff'::h3index, '8528342bfffffff'::h3index);
SELECT h3_local_ij_to_cell('85283473fffffff'::h3index, POINT(0,0));

-- 获取更粗分辨率的父单元格
SELECT h3_cell_to_parent('85283473fffffff'::h3index, 3);

-- 获取更细分辨率的所有子单元格
SELECT h3_cell_to_children('85283473fffffff'::h3index, 7);

-- 获取更细分辨率的中心子单元格
SELECT h3_cell_to_center_child('85283473fffffff'::h3index, 7);

-- 压缩单元格数组
SELECT h3_compact_cells(ARRAY['85283473fffffff'::h3index, '85283477fffffff'::h3index]);

-- 解压到目标分辨率
SELECT h3_uncompact_cells(ARRAY['85283473fffffff'::h3index], 7);

-- 获取子单元格在父单元格子列表中的位置
SELECT h3_cell_to_child_pos('872834700ffffff'::h3index, 5);

-- 获取指定位置的子单元格
SELECT h3_child_pos_to_cell(0, '85283473fffffff'::h3index, 7);

-- 用指定分辨率的六边形填充多边形
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
);

-- 获取一组六边形的轮廓多边形
SELECT * FROM h3_cells_to_multi_polygon(
    ARRAY['85283473fffffff'::h3index, '85283477fffffff'::h3index]
);

-- 检查两个单元格是否相邻
SELECT h3_are_neighbor_cells('85283473fffffff'::h3index, '85283477fffffff'::h3index);

-- 获取相邻单元格之间的有向边
SELECT h3_cells_to_directed_edge('85283473fffffff'::h3index, '85283477fffffff'::h3index);

-- 验证边
SELECT h3_is_valid_directed_edge(edge) FROM ...;

-- 从边获取起点和终点
SELECT h3_get_directed_edge_origin(edge);
SELECT h3_get_directed_edge_destination(edge);

-- 以记录形式获取两端
SELECT * FROM h3_directed_edge_to_cells(edge);

-- 从一个单元格出发的所有边
SELECT h3_origin_to_directed_edges('85283473fffffff'::h3index);

-- 边界坐标
SELECT h3_directed_edge_to_boundary(edge);

-- 获取单元格的单个顶点
SELECT h3_cell_to_vertex('85283473fffffff'::h3index, 0);

-- 获取单元格的所有顶点
SELECT h3_cell_to_vertexes('85283473fffffff'::h3index);

-- 获取顶点的地理坐标
SELECT h3_vertex_to_latlng(vertex);

-- 验证顶点
SELECT h3_is_valid_vertex(vertex);

-- 两点之间的大圆距离（km、m 或 rads）
SELECT h3_great_circle_distance(POINT(37.7,-122.5), POINT(40.7,-74.0), 'km');

-- 指定分辨率的六边形平均面积
SELECT h3_get_hexagon_area_avg(5, 'km^2');

-- 特定单元格的精确面积
SELECT h3_cell_area('85283473fffffff'::h3index, 'km^2');

-- 指定分辨率的平均边长
SELECT h3_get_hexagon_edge_length_avg(5, 'km');

-- 精确边长
SELECT h3_edge_length(edge, 'km');

-- 指定分辨率的唯一单元格数
SELECT h3_get_num_cells(5);

-- 全部 122 个分辨率 0 的单元格
SELECT h3_get_res_0_cells();

-- 指定分辨率的所有五边形
SELECT h3_get_pentagons(5);

-- 网格距离运算符
SELECT '85283473fffffff'::h3index <-> '8528342bfffffff'::h3index;

-- B-tree 等于/不等于
SELECT a = b, a <> b FROM ...;

-- R-tree 空间运算符
SELECT a && b  -- 相交
SELECT a @> b  -- 包含
SELECT a <@ b  -- 被包含

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

-- H3 索引与 bigint 互转
SELECT '85283473fffffff'::h3index::bigint;
SELECT 599686042433355775::bigint::h3index;

-- H3 索引转 point
SELECT '85283473fffffff'::h3index::point;

CREATE EXTENSION h3_postgis CASCADE;

-- 对 PostGIS 几何体建立指定分辨率的索引
SELECT h3_latlng_to_cell(geom, 9) FROM points;

-- 将 H3 单元格转换为 PostGIS geometry/geography
SELECT h3_cell_to_geometry('85283473fffffff'::h3index);
SELECT h3_cell_to_geography('85283473fffffff'::h3index);

-- 单元格边界转 PostGIS 几何体（在反子午线处分割）
SELECT h3_cell_to_boundary_geometry('85283473fffffff'::h3index);

-- 用 H3 单元格填充 PostGIS 多边形
SELECT h3_polygon_to_cells(geom, 7) FROM polygons;

-- 将 H3 单元格转回 PostGIS 多多边形
SELECT h3_cells_to_multi_polygon_geometry(ARRAY['85283473fffffff'::h3index]);

-- PostGIS 索引运算符
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;