0

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)
    *, 
    l.fclass,
    round(ST_Area(ST_Intersection(g.geom, ST_MakeValid(l.geom)))::numeric, 2) as area
FROM
    raster_bands g
LEFT JOIN
    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

1

0

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.