ST_HexagonGrid
Föregående  7.3. Geometry Constructors  Nästa

Namn

ST_HexagonGrid — Returnerar en uppsättning hexagoner och cellindex som helt täcker gränserna för geometriargumentet.

Synopsis

setof record ST_HexagonGrid(float8 size, geometry bounds);

Beskrivning

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.

SRS för utdatahexagonerna är den SRS som tillhandahålls av begränsningsgeometrin.

Om man dubblar eller tripplar hexagonens kantstorlek genereras en ny parent tiling som passar med origin tilingen. Tyvärr är det inte möjligt att generera överordnade hexagonbeläggningar som barnbeläggningarna passar perfekt i.

Tillgänglighet: 3.1.0

Exempel

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;
Rasterutdata
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;
Rasterutdata
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.

För att göra en punktsammanfattning mot ett hexagonalt rutnät genererar du ett hexagonalt rutnät med punkternas utsträckning som gränser och ansluter sedan spatialt till det rutnätet.

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;
Rasterutdata
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:

[Notera]

Nyckelordet LATERAL är underförstått för set-returning-funktioner när man hänvisar till en tidigare tabell i FROM-listan. Så CROSS JOIN LATERAL, CROSS JOIN eller helt enkelt , är likvärdiga constructs för det här exemplet.
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)