postgis - Create hex grids over a layer

I have a shapefile that has the outlines of the United States and I want to create hex grids that over that layer.

I tried using this function that will create hexes over a layer. I created an empty table and then added a geom column and then ran the custom function as shown.

CREATE TABLE IF NOT EXISTS hex_grid (hid serial NOT NULL PRIMARY KEY)

SELECT AddGeometryColumn('hex_grid', 'geom', 0, 'POLYGON', 2);

-- creating the hex grid
    SELECT genhexagons(0.05,ST_xmin(ST_Extent(SELECT geom FROM usa)),
    ST_ymin(ST_Extent(SELECT geom FROM usa)),
     ST_xmax(ST_Extent(SELECT geom FROM usa)),
     ST_ymax(ST_Extent(SELECT geom FROM usa)));

However I ran into this error that I'm not sure how to deal, I'm sure it deals with the creation of the hexes when it exceeds the US outline boundary. See below:

Answer

Looking at the post you linked to, the genhexagons function takes 5 float parameters, cell width, and the (x,y) coords for SW and NE corners of extent. You're passing in a float and a geometry.

CREATE OR REPLACE FUNCTION genhexagons(width float, xmin float,ymin  float,xmax float,ymax float)

You might be able to use ST_xmin(ST_Extent(your_geom)) (and ST_ymin, ST_ymax etc.) if you don't want to hard-code the coordinate values of the extent in your function call.

I've tried the code in that post verbatim, and it works as advertised - it creates a hex grid over Kenya.

EDIT

Might be simpler if you specify the bounds manually. For the continguous US, I'm guessing the corners of the extent are approximately (-124.8,25.10) and (-66.9,49.6)

so calling

genhexagons(1.0, -124.8, 25.10, -66.9, 49.6)

gives 1 degree grid over the contiguous US...

However it appears this code will generate too many rows of tiles... I find it covers most of Canada too :/

I found I was able to clip the results to the bounding box by dropping any tiles which didn't overlap the bounding box, and writing these to a new table called clipped.

create table clipped as (
    select * from hex_grid where 
        ST_Intersects(
            st_setSRID(the_geom,4326),
            st_setSRID(st_envelope('POLYGON((-124.8 25.10, -124.8 49.6, -66.9 49.6, -66.9 25.10, -124.8 25.10))'::geometry),4326)
    )
);

Bringing this into QGIS...