Creates a PostGIS view of hexagonal tiles.

create_hex_tile_view.sql

/*=============================================================================
Create a PostGIS view of hexagon tiles using the given user_variables.
***CAUTION! Uses floating point math, so polygons are not topological.***
2015; Tom Arnold; Walker Associates, Inc.
Free and unencumbered without warranty... like http://unlicense.org/UNLICENSE

The fun way to do this is to create a table with the user_variables and
select into the view from there.  Then you can change your grid dynamically.
=============================================================================*/

-- ***Replace "these_hex_tiles" with appropriate schema.tablename***
CREATE OR REPLACE VIEW these_hex_tiles AS (
-- ******
  WITH user_variables AS (
    SELECT
    -- ***Change the following numbers as needed***
      102630 "srid", -- All units will be in this SRID
      500 "hex_width", -- Measured across the points
      1958662 "center_x",
      1162836 "center_y",
      10000 "field_height",
      10000 "field_width"
    -- ******
  ),
  unit_hexagon AS (
    SELECT
      ST_Scale(
        ST_PolygonFromText(
          'POLYGON((
            -0.5 0,
            -0.25 0.433012701892219,
            0.25 0.433012701892219,
            0.5 6.12303176911189e-17,
            0.25 -0.433012701892219,
            -0.25 -0.43301270189222,
            -0.5 0
          ))',
          (SELECT srid FROM user_variables)
        ),
        (SELECT hex_width FROM user_variables),
        (SELECT hex_width FROM user_variables)
      ) "the_geom"
  ),
  unit_grid_half AS (
    SELECT
      (ptx * 1.5 * hex_width) + translate_x "ptx",
      (pty * (sin(pi() / 3)) * hex_width) + translate_y "pty"
    FROM
      (
        SELECT
          generate_series(
            0, (ceiling(field_width / (hex_width * 1.5)))::integer, 1
          ) "ptx"
        FROM
          user_variables
      ) "tx",
      (
        SELECT
          generate_series(
            0, (ceiling(field_height / (sin(pi() / 3) * hex_width)))::integer, 1
          ) "pty"
        FROM
          user_variables
      ) "ty",
      (
        SELECT
          hex_width,
          center_x - (field_width / 2) "translate_x",
          center_y - (field_height / 2) "translate_y"
        FROM user_variables
      ) "tv"
  ), -- unit_grid_half
  unit_grid AS (
    SELECT ptx, pty FROM unit_grid_half
    UNION
    SELECT
      ptx + (hex_width * 0.75) "ptx",
      pty + (hex_width * sin(pi() / 3) / 2) "pty"
    FROM
      unit_grid_half,
      user_variables
  )
  SELECT
    ROW_NUMBER() OVER () "ROWID",
    ptx "centroid_x",
    pty "centroid_y",
    ST_Translate(the_geom, ptx, pty) "the_geom"
  FROM
    unit_grid,
    unit_hexagon
)