[postgis-users] RE select raster by pixel value

Steve.Toutant at inspq.qc.ca Steve.Toutant at inspq.qc.ca
Fri Oct 16 09:33:59 PDT 2015

Clearly I misunderstood what the -t 20x20 switch meant in my raster2pgsql 
I understand now that I have 400 pixels by tile (rid).

I need to intersects all pixel with value = 9 with a multipolygon postgis 
layer. I'll see if my sql below will do it

Steve.Toutant at inspq.qc.ca@lists.osgeo.org 
Envoyé par : "postgis-users" <postgis-users-bounces at lists.osgeo.org>
2015-10-16 10:41
Veuillez répondre à
PostGIS Users Discussion <postgis-users at lists.osgeo.org>

PostGIS Users Discussion <postgis-users at lists.osgeo.org>

[postgis-users] select raster by pixel value

First try on using raster in postgis 2.1. I used raster2pgsl to load a 

Then I want to select the rids where pixel value = 9 
I ended up with this query 
SELECT a.rid,(pvc).value 
from test as a 
join (SELECT rid,ST_ValueCount(rast) As pvc FROM test) as b ON b.rid = 
a.rid and (pvc).value = 9 
order by a.rid; 

This select returns 28517 records 

But using this SQL to get the distinct count i get 1055421 records for 
pixel value 9 
SELECT (pvc).value, SUM((pvc).count) As total 
 FROM (SELECT ST_ValueCount(rast) As pvc 
   FROM test) As f 
    GROUP BY (pvc).value 
order by value; 

Obviously there is something I do wrong... 
What is it I don't understand? 


postgis-users mailing list
postgis-users at lists.osgeo.org

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20151016/cf666acb/attachment.html>

More information about the postgis-users mailing list