sql - ST_CreateFishnet inside given rectangles: Getting two geometry's instead of one

My goal is to get a fishnet inside the rectangles of 'ov_boxes' (image below) with the given extends from the attribute-table ('width' and 'height' in relation of 'scale').

I used ST_CreateFishnet and modified it: But the function produces two geometry_columns: One have got the right spatial placement but isn't a fishnet (left image). The other is a fishnet but with the wrong (no) spatial placement (right image).

The query I used:

CREATE OR REPLACE FUNCTION ST_CreateFishnet(

        nrow integer, ncol integer,
        xsize float8, ysize float8,
        x0 float8 DEFAULT 0, y0 float8 DEFAULT 0,
        OUT "row" integer, OUT "col" integer,
        OUT geom geometry)
    RETURNS SETOF record AS
$$
SELECT i + 1 AS row, j + 1 AS col, ST_Translate(cell, j * $3 + $5, i * $4 + $6) AS geom
FROM generate_series(0, $1 - 1) AS i,
     generate_series(0, $2 - 1) AS j,

(
SELECT ('POLYGON((0 0, 0 '||$4||', '||$3||' '||$4||', '||$3||' 0,0 0))')::geometry AS cell
) AS foo;
$$ LANGUAGE sql IMMUTABLE STRICT;

drop table grid;
create table grid as
SELECT name, scale, new_geom, (ST_CreateFishnet(6,6,((width * (scale * 0.1)))/6,((height * (scale * 0.1)))/6)).*
FROM ov_boxes;
ALTER TABLE grid ADD gid serial PRIMARY KEY;

It seems to me, I produced two geometry's where I should have one: The grid inside the rectangles. I'm not very familiar with SQL, so maybe there is a miss-thinking how I create the "ouputs". But also if there is a totally different way to get to my fishnets - every idea is very welcome!

Answer

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.