4

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').

enter image description here

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).

enter image description here

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!

7
  • You are just trying to create a regular grid, correct? I ask, because your polygons start with 0 0 and end with 0 0. Sep 7 '17 at 12:15
  • It looks like your function is from this answer. It is only returning one geometry- the second one - which creates an irregular grid because you vary the cell size arguments based on columns in your ov_boxes table. The first geometry column presumably comes from your ov_boxes table. What is your end goal?
    – RoperMaps
    Sep 7 '17 at 12:53
  • Yes, I got the code from that answer. And since I'm not very familiar with SQL, I just used it without really knowing, whats going on. My goal is to get a fishnet inside the rectangles of 'ov_boxes' (left image) with the given extends from the attribute-table (width and height). What I got are (correct) fishnets, but they are not in the rectangles... maybe I have to change my question?
    – MAP
    Sep 7 '17 at 18:01
  • Is there something wrong with my answer? If gives a perfect grid is the most performant way possible. Perhaps I misunderstood the question? Sep 7 '17 at 22:14
  • It is days like today when I think about giving up answering questions. You spend an hour giving a really complex answer to a question and get completely ignored. Meanwhile someone says oh you use this plugin in arcgis and they get ten upvotes. Sure it isn't all about rep, butt would be nice to be recognised for making an effort Sep 7 '17 at 22:18
4
+50

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.

8
  • This seems to be a fast and elegant way to get to a fishnet. But how do I get a fishnet inside every single rectangles of "ov_boxes"? (I improved my question)
    – MAP
    Sep 8 '17 at 8:06
  • Ah, ok, so you want a fishnet where the input start x,y, and box widths, essentially comes from ov_boxes? OK, that is easy enough, but you will have to give me a few hours, very busy today. If you could post a couple of examples of the geometries, as WKT or some such, that would be helpful for testing/illustration purposes. Sep 8 '17 at 8:13
  • Query: SELECT ST_AsText(new_geom) FROM "ov_boxes" WHERE name ILIKE 'willisau'; Result: st_astext POLYGON((422165.494364731 5217570.12032816,422165.494364731 5220270.12032816,424965.494364731 5220270.12032816,424965.494364731 5217570.12032816,422165.494364731 5217570.12032816))
    – MAP
    Sep 8 '17 at 9:57
  • Where do you get the fishnet box size from. eg, for the first one you have width/height of 560/540. How many boxes would you want for that one? Sep 8 '17 at 11:22
  • The size is set manually and is static (in centimeter for final printed map-size). I need always a raster by 6x6 boxes.
    – MAP
    Sep 11 '17 at 6:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.