ST_HexagonGrid
Précédent  7.3. Constructeurs de géométries  Suivant

Nom

ST_HexagonGrid — Renvoie un ensemble d'hexagones et d'indices de cellules qui couvrent complètement les limites de l'argument géométrie.

Synopsis

setof record ST_HexagonGrid(float8 size, geometry bounds);

Description

Starts with the concept of a hexagon tiling of the plane. (Not a hexagon tiling of the globe; for H3 use the h3-pg extension.) For a given planar SRS, and a given edge size, starting at the origin of the SRS, there is one unique hexagonal tiling of the plane, Tiling(SRS, Size). This function answers the question: what hexagons in a given Tiling(SRS, Size) overlap with a given bounds.

Le SRS pour les hexagones de sortie est le SRS fourni par la géométrie des limites.

Doubler ou tripler la taille des bords de l'hexagone génère un nouveau pavage parent qui s'adapte au pavage d'origine. Malheureusement, il n'est pas possible de générer des tuiles d'hexagones parents dans lesquelles les tuiles enfants s'insèrent parfaitement.

Disponibilité : 3.1.0

Exemples

Unit hexagons covering rectangular bounds.

Code
WITH bounds AS (
    SELECT ST_MakeEnvelope(2, 1, 4, 3, 3857) AS geom
), grid AS (
    SELECT hex.*
    FROM bounds
    CROSS JOIN LATERAL ST_HexagonGrid(1, geom) AS hex
)
SELECT (SELECT geom FROM bounds) AS input_bounds,
    ST_Collect(geom ORDER BY i, j) AS grid
FROM grid;
Export de raster
POLYGON((2 1,2 3,4 3,4 1,2 1)) | MULTIPOLYGON(((0.5 0.866,1 0,2 0,2.5 0.866,2 1.732,1 1.732,0.5 0.866)),((0.5 2.598,1 1.732,2 1.732,2.5 2.598,2 3.464,1 3.464,0.5 2.598)),((2 1.732,2.5 0.866,3.5 0.866,4 1.732,3.5 2.598,2.5 2.598,2 1.732)),((2 3.464,2.5 2.598,3.5 2.598,4 3.464,3.5 4.33,2.5 4.33,2 3.464)),((3.5 0.866,4 0,5 0,5.5 0.866,5 1.732,4 1.732,3.5 0.866)),((3.5 2.598,4 1.732,5 1.732,5.5 2.598,5 3.464,4 3.464,3.5 2.598)))
Figure
Geometry figure for visual-st-hexagongrid-01

Child tiles that intersect a doubled-size parent hexagon share the tiling origin, but do not nest perfectly inside the parent.

Code
WITH parent AS (
    SELECT ST_Hexagon(2, 0, 0) AS geom
), children AS (
    SELECT child.*
    FROM parent
    CROSS JOIN LATERAL ST_HexagonGrid(1, geom) AS child
    WHERE ST_Area(ST_Intersection(child.geom, parent.geom)) > 0
)
SELECT ST_Collect(geom ORDER BY i, j) AS children,
    (SELECT geom FROM parent) AS parent
FROM children;
Export de raster
MULTIPOLYGON(((-2.5 -0.866,-2 -1.732,-1 -1.732,-0.5 -0.866,-1 0,-2 0,-2.5 -0.866)),((-2.5 0.866,-2 0,-1 0,-0.5 0.866,-1 1.732,-2 1.732,-2.5 0.866)),((-1 -1.732,-0.5 -2.598,0.5 -2.598,1 -1.732,0.5 -0.866,-0.5 -0.866,-1 -1.732)),((-1 0,-0.5 -0.866,0.5 -0.866,1 0,0.5 0.866,-0.5 0.866,-1 0)),((-1 1.732,-0.5 0.866,0.5 0.866,1 1.732,0.5 2.598,-0.5 2.598,-1 1.732)),((0.5 -0.866,1 -1.732,2 -1.732,2.5 -0.866,2 0,1 0,0.5 -0.866)),((0.5 0.866,1 0,2 0,2.5 0.866,2 1.732,1 1.732,0.5 0.866))) | POLYGON((-2 0,-1 -1.732,1 -1.732,2 0,1 1.732,-1 1.732,-2 0))
Figure
Geometry figure for visual-st-hexagongrid-02

Counting points in hexagons.

Pour faire un résumé des points par rapport à un pavage hexagonal, générez une grille hexagonale en utilisant l'étendue des points comme limites, puis joignez spatialement cette grille.

Code
SELECT COUNT(*), hexes.geom
FROM ST_HexagonGrid(
  10000,
  ST_SetSRID(ST_EstimatedExtent('pointtable', 'geom'), 3857)
) AS hexes
INNER JOIN pointtable AS pts
  ON ST_Intersects(pts.geom, hexes.geom)
GROUP BY hexes.geom;

Generating hex coverage of polygons.

If a grid is generated for each polygon boundary and filtered to intersecting cells, adjacent regions receive overlapping hexagons along their shared border. The build-time figure below uses two simple regions in place of an external administrative-boundary table, so the example is self-contained.

Code
WITH admin1(gid, geom) AS (
    VALUES
        (1, ST_MakeEnvelope(0, 0, 3, 3, 3857)),
        (2, ST_MakeEnvelope(3, 0, 6, 3, 3857))
), coverage AS (
    SELECT hex.i, hex.j, hex.geom
    FROM admin1
    CROSS JOIN LATERAL ST_HexagonGrid(1.5, admin1.geom) AS hex
    WHERE ST_Intersects(admin1.geom, hex.geom)
    GROUP BY hex.i, hex.j, hex.geom
), layers AS (
    SELECT 'region' AS kind, gid AS position, geom
    FROM admin1
    UNION ALL
    SELECT
        'hexagon',
        1000 + row_number() OVER (ORDER BY i, j),
        geom
    FROM coverage
)
SELECT
    CASE kind WHEN 'region' THEN geom END AS input_region,
    CASE kind WHEN 'hexagon' THEN geom END AS hexagon
FROM layers
ORDER BY position;
Export de raster
POLYGON((0 0,0 3,3 3,3 0,0 0)) | null
POLYGON((3 0,3 3,6 3,6 0,3 0)) | null
null | POLYGON((-1.5 0,-0.75 -1.3,0.75 -1.3,1.5 0,0.75 1.3,-0.75 1.3,-1.5 0))
null | POLYGON((-1.5 2.6,-0.75 1.3,0.75 1.3,1.5 2.6,0.75 3.9,-0.75 3.9,-1.5 2.6))
null | POLYGON((0.75 1.3,1.5 0,3 0,3.75 1.3,3 2.6,1.5 2.6,0.75 1.3))
null | POLYGON((0.75 3.9,1.5 2.6,3 2.6,3.75 3.9,3 5.2,1.5 5.2,0.75 3.9))
null | POLYGON((3 0,3.75 -1.3,5.25 -1.3,6 0,5.25 1.3,3.75 1.3,3 0))
null | POLYGON((3 2.6,3.75 1.3,5.25 1.3,6 2.6,5.25 3.9,3.75 3.9,3 2.6))
null | POLYGON((5.25 1.3,6 0,7.5 0,8.25 1.3,7.5 2.6,6 2.6,5.25 1.3))
null | POLYGON((5.25 3.9,6 2.6,7.5 2.6,8.25 3.9,7.5 5.2,6 5.2,5.25 3.9))
Figure
Geometry figure for visual-st-hexagongrid-03

The same pattern applies to a real administrative-boundary table:

[Note]

Le mot-clé LATERAL est implicite pour les fonctions de retour d'ensemble lorsqu'elles font référence à une table antérieure dans la liste FROM. Ainsi, CROSS JOIN LATERAL, CROSS JOIN, ou tout simplement , sont des constructions équivalentes pour cet exemple.
Code
SELECT admin1.gid, hex.geom
FROM
    admin1
    CROSS JOIN
    ST_HexagonGrid(100000, admin1.geom) AS hex
WHERE
    adm0_a3 = 'USA'
    AND
    ST_Intersects(admin1.geom, hex.geom)