1

I have a rasterA (whole Scandinavia, with values from 0-255) and a rasterB (in blue, a small part of south of Norway, entirely inside rasterA). I want to query the pixels of rasterA which are inside rasterB.

Will this query return only those pixels which overlap, or will it return values for the whole of rasterA, since rasterA and rasterB overlap?

Query: SELECT A.rid, pvc FROM rasterA AS A, rasterB AS B, ST_ValueCount(A.rast,1) AS pvc WHERE st_intersects(A.rast, B.rast);

rasterA(white) and raster B (blue): enter image description here

Also, I'm importing the raster into PostGIS like so:

raster2pgsql \ -s 3857 -I -C -Y \ -t ${TILESIZE} \ -l 4,32,128 \ ${RASTERFILE} $TABLENAME | psql -q --set ON_ERROR_STOP=1

Can this have anything do to with the faulty results I'm getting?

2
  • Thanks @JohnBarça, you're a real hero for answering all my PG questions! :) I'm getting unexpected results of values, they are somewhat off (0-10%) from expectations. More details here if you're interested, github.com/systemapic/systemapic.js/issues/216, if you have any clue as to why, please don't hesitate to tip me!
    – knutole
    Jun 3, 2016 at 10:44
  • 1
    I'm sorry, I completely misread the above. Yes, ST_Intersection is what you want. Jun 5, 2016 at 18:44

1 Answer 1

2

It will return for the whole raster. You need to use something like ST_Intersection to just get the portion that overlaps. http://postgis.net/docs/manual-2.2/RT_ST_Intersection.html

So something like:

SELECT A.rid, pvc 
  FROM rasterA AS A INNER JOIN rasterB AS B ON 
      ST_Intersects(A.rast, B.rast)   
       , LATERAL 
         ST_ValueCount(ST_Intersection(A.rast,B.rast, 'BAND1') ,1) AS pvc;

Since you only care about band1 values the other approach to do this if you don't have the same alignment is to use ST_Clip with ST_polygon. So would look like

SELECT A.rid, pvc 
  FROM rasterA AS A INNER JOIN rasterB AS B ON 
      ST_Intersects(A.rast, B.rast)   
       , LATERAL 
         ST_ValueCount(ST_Clip(A.rast,ST_Polygon(B.rast) ) ,1) AS pvc;
4
  • Thanks @LR1234567! Looks good. However, I'm getting The two rasters provided do not have the same alignment on this query now... Any idea why?
    – knutole
    Jun 7, 2016 at 12:33
  • The rasters have different scales on the X axis is the reason. Can I incorporate an auto-alignment in the query directly?
    – knutole
    Jun 7, 2016 at 12:36
  • 1
    Hmm might be easier to do with ST_Clip then. I'll add an alternative answer.
    – Regina Obe
    Jun 7, 2016 at 15:28
  • Thanks a bunch @LR1234567! Spot on! :) Learned a lot from this, thanks a lot!
    – knutole
    Jun 7, 2016 at 17:17

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.