[postgis-users] Raster SQL to separate high value areas

[Pierre Racine]

Or ST_ReClass() and ST_PixelAsCentroids()?

> -----Original Message-----
> From: postgis-users [mailto:postgis-users-bounces at lists.osgeo.org] On
> Behalf Of Regina Obe
> Sent: Wednesday, March 09, 2016 7:04 AM
> To: 'PostGIS Users Discussion'
> Subject: Re: [postgis-users] Raster SQL to separate high value areas
> 
> You probably want to use ST_Reclass  and then ST_DumpAsPolygons.
> 
> 
> 
> http://postgis.net/docs/manual-2.2/RT_ST_Reclass.html - this will reduce
> your pixels into a binary of above 70% , not above 70%
> 
> 
> 
> 
> 
> http://postgis.net/docs/manual-2.2/RT_ST_DumpAsPolygons.html would
> then give you two sets of polygons with the two new pixel values.
> 
> 
> 
> 
> 
> 
> 
> From: postgis-users [mailto:postgis-users-bounces at lists.osgeo.org] On
> Behalf Of liglio at pobox.com
> Sent: Tuesday, March 08, 2016 3:10 PM
> To: postgis-users at lists.osgeo.org
> Subject: [postgis-users] Raster SQL to separate high value areas
> 
> 
> 
> Hi,
> 
> 
> 
> I am trying to separate areas of high value of lightning probability (above
> 70%) in a Raster and calculate their centroids. I successfully loaded a GeoTiff
> raster image (one band, gray scale, 982x1108 pixels) in PostgreSQL 9.5
> (Postgis 2.2.1). I see the SQL results in OenJump 1.9.
> 
> 
> 
> I used the next SQL, but the function ST_PixelAsPoints result in a geometry
> points which are of the pixel's upper-left corner. I am not sure that is the
> right way to do, because of that displacement. Can I do without this
> transformation raster-geometry ?
> 
> 
> 
> Thanks.
> 
> 
> 
> SELECT row_number() over () AS id,
> 
>   ST_NumGeometries(gc),
> 
>   gc AS geom_collection,
> 
>   ST_Centroid(gc) AS centroid
> 
> FROM (
> 
>   SELECT unnest(ST_ClusterWithin(geom, 0.1)) AS gc
> 
>   FROM (SELECT (ST_PixelAsPoints(rast, 1)).*
> 
>     FROM tb_lightningprob) foo
> 
>     WHERE val > 0.7
> 
> ) f;
> 
> 
> 
>