2

I have a regular polygon grid (grid) and irregular polygons (block). You can download the data here: https://we.tl/t-BczpR2K2lw

Raster and Polygon

I want to intersect the orange polygons (block) by the regular polygon grid (grid). grid is of type POLYGON, while block is of type MULTIPOLYGON. In the result of the split I expect to have as many split polygons, as there are "rastercells". There are 416 rows in grid and 8 rows in block.

So far I have tried

CREATE TABLE foo AS
    SELECT ST_Intersection(block.geom, grid.geom)
    FROM block, grid;

This results in 3328 rows (which is 416 * 8). When I try to visualize this in QGIS, I do not get the icon for a vector layer, but for an image.

Why do I get 3328 rows as a result? This should be less then 416...

5
  • Nice. I belive each block is being split by each grid cell. So one block with 10 cells will be split 10 times. I dont know how to prevent it though
    – Bera
    Commented Apr 6, 2022 at 10:07
  • Must it be done using PostGIS? The tool Intersection in QGIS will give your desired result (assuming the grid is polygon).
    – Matt
    Commented Apr 6, 2022 at 10:39
  • imgur.com/a/vp0VGc9
    – Matt
    Commented Apr 6, 2022 at 10:42
  • The QGIS tool Intersection, as I mentioned previously.
    – Matt
    Commented Apr 6, 2022 at 10:53
  • In postgis, it still leaves me with 3328 rows though... This is something, I cannot get my head around why this happens!
    – four-eyes
    Commented Apr 6, 2022 at 11:12

1 Answer 1

4

Your are doing a cross join between the two layers, computing an intersection between each cell and each polygon, even if they are disjoint. Most "intersection" will be null/empty.

Try to restrict the computation only where the geometries intersect each others:

CREATE TABLE foo AS
    SELECT ST_Intersection(block.geom, grid.geom)
    FROM block 
    JOIN grid
      ON ST_Intersects(block.geom, grid.geom);
3
  • That easy... JGH, this is something that happens every now and then to me. I do a cross join (in these spatial operations) without being aware that I am doing a cross join. Is there a rule of thumb to realize, that I am doing a cross join?
    – four-eyes
    Commented Apr 6, 2022 at 12:12
  • 1
    @Stophface When you write two table name in the FROM, without a WHERE that effectively joins the tables, it is a cross join. The easiest is to systematically write a JOIN + ON when several tables are involved
    – JGH
    Commented Apr 6, 2022 at 12:15
  • Thanks mate, that helps!
    – four-eyes
    Commented Apr 6, 2022 at 12:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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