I need to join three tables with intersections. I have three tables:

  1. Raster data (Sentinel 2 imagery);
  2. Grid table 1.28 x 1.28 km;
  3. OSM landuse table/

Based on the grid I need to extract band values from raster data and then based on the polygon I need to extract land classes from the 3rd table. For that I wrote this query:

WITH raster_data AS (
      SELECT ST_PixelAsPolygons(rast.rast,1) band1, ST_PixelAsPolygons(rast.rast,2) band2,
             ST_PixelAsPolygons(rast.rast,3) band3, ST_PixelAsPolygons(rast.rast,4) band4,
             ST_PixelAsPolygons(rast.rast,5) band5, ST_PixelAsPolygons(rast.rast,6) band6,
             ST_PixelAsPolygons(rast.rast,7) band7
      FROM raster_grid g , public.t32ulc_entire_tile rast
      WHERE ST_Intersects(rast.rast, g.geom)
     raster_bands as
(select  ((band1).geom) as geom, 
        (band1).val as b1, 
        (band2).val as b2,
        (band3).val as b3,
        (band4).val as b4,
        (band5).val as b5,
        (band6).val as b6,
        (band7).val as b7

from raster_data),

classes as (SELECT
    DISTINCT ON (l.gid)
    round(ST_Area(ST_Intersection(g.geom, ST_MakeValid(l.geom)))::numeric, 2) as area
    raster_bands g
    public.osm_landuse_test l
ON ST_Intersects(g.geom, l.geom)
ORDER BY l.gid, area desc)

select * from classes cl;```

But this query is taking too long. what is wrong with this query? Actually, all tables are intersecting with geom columns



Your Answer

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

Browse other questions tagged or ask your own question.