The following function, using RETURN QUERY EXECUTE FORMAT, see the docs which is much cleaner and more perfomant than using concatenation with ||, creates a regular grid, with input parameters for grid size, ncols, nrows, and start x,y. On a large table, execute format is likely to be much faster than this sort of construct: '||$4||', '||$3||'
CREATE TYPE return_row as (x int, y int, geom geometry);
CREATE OR REPLACE FUNCTION fishnet(
x_offset float,
y_offset float,
x_gridsize float,
y_gridsize float,
nrows integer,
ncols integer)
RETURNS SETOF return_row AS
$BODY$
BEGIN
RETURN QUERY EXECUTE
FORMAT(
'WITH
grid (x,y) AS (
SELECT
x.xs, y.ys
FROM
(SELECT generate_series(0, ($5-1)) as xs) x,
(SELECT generate_series(0, ($6-1)) as ys) y
),
tile_geom (row, col, geom) AS
(SELECT
x,
y,
ST_Translate(
ST_Envelope(
ST_MakeBox2D(
ST_MakePoint((x * $3),
(y * $4)),
ST_MakePoint(((x + 1) * $3),
((y + 1) * $4)))),
$1, $2)
AS box FROM grid)
SELECT row, col, geom FROM tile_geom')
USING x_offset, y_offset, x_gridsize, y_gridsize, nrows, ncols;
END;
$BODY$
LANGUAGE plpgsql;
Note you need to create the return type. A simple test, using somewhat random input parameters,
SELECT (fishnet(100, 1000, 5, 10, 2, 3)).*;
returns:
x | y | g
---+---+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
0 | 0 | 0103000000010000000500000000000000000059400000000000408F4000000000000059400000000000908F400000000000405A400000000000908F400000000000405A400000000000408F4000000000000059400000000000408F40
0 | 1 | 0103000000010000000500000000000000000059400000000000908F4000000000000059400000000000E08F400000000000405A400000000000E08F400000000000405A400000000000908F4000000000000059400000000000908F40
where you need to use ().* syntax to access the rows, as it is a set returning function.
So, if you wanted to see what that is returning you could do:
WITH
vals (x, y, geom) AS
(SELECT (fishnet(100, 1000, 5, 10, 2, 3)).*)
SELECT
x, y, ST_AsText(geom) AS geom
FROM vals;
which returns the slightly easier to read:
x | y | geom
---+---+---------------------------------------------------------
0 | 0 | POLYGON((100 1000,100 1010,105 1010,105 1000,100 1000))
0 | 1 | POLYGON((100 1010,100 1020,105 1020,105 1010,100 1010))
0 | 2 | POLYGON((100 1020,100 1030,105 1030,105 1020,100 1020))
0 | 3 | POLYGON((100 1030,100 1040,105 1040,105 1030,100 1030))
1 | 0 | POLYGON((105 1000,105 1010,110 1010,110 1000,105 1000))
1 | 1 | POLYGON((105 1010,105 1020,110 1020,110 1010,105 1010))
which illustrates different fishnet grid size, start point and num rows/cols in x and y directions.
EDIT, using input table from OP's question: So, if I have understood the clarification in the comments correctly, you would want to feed start coordinates from a table called ov_boxes, always produce a 6 x 6 fishnet within each box, and return a new table, with a one to one mapping between ov_boxes and the fishnet, meaning that the fishnet will need to be a MultiPolygon for each input box.
To start, get the input parameters from each ov_box and the name, to tie it together with the results of the output query:
CREATE TABLE grid AS WITH
input_boxes (name, x_start, y_start, x_gridsize, y_gridsize, nrows, ncols) AS (
SELECT
name,
ST_XMin(geom),
ST_YMin(geom),
((ST_XMax(geom) - ST_XMin(geom)) / 6),
((ST_YMax(geom) - ST_YMin(geom)) / 6),
6, 6
FROM
ov_boxes
),
fishnet(name, geom) AS (
SELECT
name,
(fishnet(x_start, y_start, x_gridsize, y_gridsize, nrows, ncols)).geom
FROM input_boxes)
SELECT
name,
ST_Union(geom) AS geom
FROM fishnet
GROUP BY name;
Finally, I used ST_Union(geom) grouping by ov_boxes name, which ties together each fishnet into a MultiPolygon corresponding to the name in ov_box. I am still not entirely clear on the width/height of each box in the fishnet, so you will see it is calculated by taking the width/height and dividing by 6, as you stated you wanted 6x6 fishnets for each row in ov_box.
