Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.2k views
in Technique[技术] by (71.8m points)

postgresql - Table with coordinates of a hexagonal grid that covers the world

Looking for an implementation in PostGIS for generating a hexagonal grid that covers the the whole planet in order to aggregate data over each hexagon.

Any pointer in the right direction would be of great help!

End product: - A table containing the center point for each hexagon in a hexagonal grid that covers the whole world. - The hexagons have a fixed area

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

Some time ago I adapted a function to generate hexagons that might be exactly what you're looking for. It takes the parameters cell width, and the coordinates for southwest and northeast corners, and generates a hexagonal grid.

CREATE OR REPLACE FUNCTION create_hexagons(width FLOAT, xmin FLOAT, ymin FLOAT, xmax FLOAT, ymax FLOAT)
RETURNS TABLE (_gid INTEGER, _geom GEOMETRY) AS $$
DECLARE
  b FLOAT := width/2;
  a FLOAT := b/2;
  c FLOAT := 2*a;
  height FLOAT := 2*a+c;
  ncol FLOAT := ceil(abs(xmax-xmin)/width);
  nrow FLOAT := ceil(abs(ymax-ymin)/height);
  polygon_string VARCHAR := 'POLYGON((' || 
    0 || ' ' || 0 || ' , ' || b || ' ' || a || ' , ' || b || ' ' || a+c || ' , ' || 0 || ' ' || a+c+a || ' , ' ||
   -1*b || ' ' || a+c || ' , ' || -1*b || ' ' || a || ' , ' || 0 || ' ' || 0 || '))';
BEGIN
  CREATE TEMPORARY TABLE tmp (gid serial NOT NULL PRIMARY KEY,geom GEOMETRY(POLYGON)) ON COMMIT DROP;
  INSERT INTO tmp (geom)   
  SELECT ST_Translate(geom, x_series*(2*a+c)+xmin, y_series*(2*(c+a))+ymin)
  FROM generate_series(0, ncol::INT, 1) AS x_series,
       generate_series(0, nrow::INT,1 ) AS y_series,
    (SELECT polygon_string::GEOMETRY AS geom
     UNION
     SELECT ST_Translate(polygon_string::GEOMETRY, b, a + c) AS geom) AS two_hex;
    ALTER TABLE tmp ALTER COLUMN geom TYPE GEOMETRY(POLYGON, 4326) USING ST_SetSRID(geom, 4326);   
    RETURN QUERY (SELECT gid, geom FROM tmp);    
END;
$$ LANGUAGE plpgsql;

This function returns a table with the columns _gid and _geom, containing an identifier and the geometry for each hexagon, respectively.

CREATE TABLE t AS
SELECT * FROM create_hexagons(1.0, -180, -90, 180, 45) 

With these parameters, the function generates a grid with 98192 hexagons covering the whole world:

Hexagons covering the whole world

Here a bit closer, so that you can see the grid:

Hexagons covering the whole world - Europe Overview

If you're only interested in covering land, you can create a subset of these hexagons based on a geometry of your choice using ST_Intersects:

CREATE TABLE t_overlap AS 
SELECT t._gid,t._geom FROM t,world 
WHERE ST_Intersects(world.geom,t._geom)

This query will create a subset with a grid containing 35911 hexagons, which intersect with the geometries from the world map:

enter image description here

The world map used in this answer can be downloaded as shapefile here.

End product: - A table containing the center point for each hexagon in a hexagonal grid that covers the whole world. - The hexagons have a fixed area

Generating the centroids for each hexagon isn't a big deal either (see ST_Centroid):

CREATE TABLE t_overlap_centroid AS
SELECT ST_Centroid(_geom) FROM t_overlap;

enter image description here


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...