I'm using PostgreSQL 9.2.4 and PostGIS 2.0.3.

I have some raster layer and I'm trying to get the count of all pixels (including nodata), cliped by polygon.

Raster was imported by raster2sql:

raster2pgsql -I -d -C -M -s ESPG_code -t 80x80 input_source > output.sql
psql -h localhost -U user_name -d database -f output.sql

So my code is:

WITH polygon As ( SELECT ST_GeometryFromText('wtk_polygon_geometry))', epsg) As geom )
SELECT ST_Count(ST_Clip(r.rast,p.geom),1,false) As val FROM raster As r, polygon As p WHERE ST_Intersects(r.rast,p.geom)

and I'm geting overestimated value and it looks like below. enter image description here

but the query should output a count value looks like this - non highlighted pixels:enter image description here

| improve this question | | | | |
  • It's not exactly clear what your requirement to the counted raster cells is. In the text you write that you want the raster cells that intersect the polygon, but in your figure you implement another rule, which looks like you only want raster cells that are covered by more than 50% of the polygon. Please edit your question to clarify this. But couldn't you set the ST_Clip option crop to true to get only raster cells that are intersected by the polygon (question as per the text)? – JonasPedersen Jun 6 '16 at 14:07
  • I mean that i would like to have count only pixels cliped by some polygon layer (also the nodata 'pixels'). So for now I have several extra unnecessary pixels. I didn't specified crop for true because the output raster is crop anyway. – joe_dz Jun 6 '16 at 14:58
  • I believe I'm trying to solve the same problem as @joe_dz To clarify, do you mean that there are some nodata pixels inside the clipped area as well as some nodata pixels outside of the clipped area and that you only want to count pixels with data inside the clip + nodata pixels inside the clip (i.e. total number of pixels inside the clip even if they are nodata)? – apricity Sep 11 '17 at 22:55

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.