I want to calculate the difference between a raster and vector layer,

I used this querie but it's so slow and I don't know if it's right or not.

I'm not using st_difference because it only support vector layers

SELECT ST_Clip(a.raster,b.geom) FROM my_raster a, my_shape b WHERE not ST_Intersects(a.raster, b.geom)

I want to erase raster region wish is intersected with the vector layer


Queries listed bellow work but they take a lot of time

To rasterise vector layer

create table rasterise_vector
select ST_AsRaster(st_transform(geom,4326), (select st_union(raster) from my_raster) , 2BUI') as raster
from shp

To calculate difference between rasters

create table diff_rasters as
SELECT ST_MapAlgebra(st_union(a.raster), st_union(b.raster), 'rast1 - rast2', '32BF', 'FIRST', NULL, 'rast1', NULL)
from my_raster a, rasterise_vector b

Table size :

Raster size Rows (estimation) = 1046 tiles

Vector size Rows (estimation) = 9854 items

enter image description here

could you please help me solve this problem?

  • Hard to know without any metrics on table sizes, polygon complexity, etc. How big, how long. What does explain show? Jul 3, 2015 at 9:45
  • 1
    How did you import your raster data into your database (100x100 tiles)? From th experiences I've made I think the union function could slow down the query noticable. Can you avoid it? What about tiling the new vector_raster?
    – Stefan
    Jul 4, 2015 at 9:03
  • exactly, I imported my ratser data with 100x100 tiles. I will test it by making tiles and give you my results
    – elmo
    Jul 5, 2015 at 0:28

1 Answer 1


Here is my idea:

  1. Use ST_AsRaster to rasterize your vector layer (think about the resolution of the new raster layer)
  2. Then use the ST_MapAlgebra function to create a new raster (new_raster values have raster values where vector_raster values are NULL or something like this.)
  • 1
    This is a very good idea, I tested it but the difference of rasters takes a lot of time, I added queries to my question
    – elmo
    Jul 3, 2015 at 11:59
  • your answer is right, I made union out of queries and it's done
    – elmo
    Jul 13, 2015 at 13:35

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.