[postgis-users] RES: Clipping a raster and a polygon using ST_Intersection

Regina Obe lr at pcorp.us
Sun Dec 4 09:09:53 PST 2022


b1 being choppier than b2, is because the rasterized polygon takes on the
pixel size of your low res polygon.

I’d try resampling your tb_densitysurface  to lower pixel size before you do
the operation with ST_Resample.




The width and height are measured in pixels.  The idea is you want your
tb_densitysurface to have more pixels than it does now so that the size of
each pixel is smaller.


If you do something like 


CREATE TABLE tb_densitysurface_x5 AS 

SELECT rid, ST_Resample(rast, ST_Width(rast)*5, ST_Height(rast)*5 ) AS rast

FROM tb_densitysurface;


That should give you the same spatial ref sys, but the pixels will be
smaller in size.

You can then use this to rerun the ST_Intersection in raster space.  

I think it will be a little slower though, because the more pixels you have
the slower things get.

Just keep on increasing that number 5 until you get something that meets
your choppiness criteria.

As you get less choppy, operations will get slower, so you need to find a
sweet spot there.


Regarding: There is a way to export this image and associate a value for
each square (polygon) to be colored accordingly ?

Was that to resolve the above issue, or you just want to know.


Well there is ST_PixelAsPolygons which will convert each pixel to a square
polygon. https://postgis.net/docs/RT_ST_PixelAsPolygons.html



There is also ST_DumpAsPolygons
https://postgis.net/docs/RT_ST_DumpAsPolygons.html  which tries to aggregate
nearby pixels with the same value into a polygon.


Both return geomval rows similar to the ST_Intersection of raster, geom.  As
I recall I think those were completely rewritten in C so should still be
faster than ST_Intersection(geometry,raster).


From: postgis-users [mailto:postgis-users-bounces at lists.osgeo.org] On Behalf
Of liglio.pessoal at nexxa.com.br
Sent: Wednesday, November 30, 2022 9:37 AM
To: 'PostGIS Users Discussion' <postgis-users at lists.osgeo.org>
Subject: [postgis-users] RES: Clipping a raster and a polygon using




Thanks for your replay. 


Your solution is fast like using ST_Clip, but I still have the problem that
my pixels are 25km square (low definition) and the image look like this:
(image b1.jpg)


And I want to produce something like this: (image b2.jpg)


I know that the second image cannot be reproduced as just raster with big
pixels, the pixels cannot be cutted.


There is a way to export this image and associate a value for each square
(polygon) to be colored accordingly ?






De: postgis-users <postgis-users-bounces at lists.osgeo.org
<mailto:postgis-users-bounces at lists.osgeo.org> > Em nome de Regina Obe
Enviada em: quinta-feira, 24 de novembro de 2022 12:19
Para: 'PostGIS Users Discussion' <postgis-users at lists.osgeo.org
<mailto:postgis-users at lists.osgeo.org> >
Assunto: Re: [postgis-users] Clipping a raster and a polygon using


I’m not quite sure what you mean by edges aren’t smooth.  Rasters are never
really smooth. Vectors are smooth.  Rasters may look smooth at the edges,
but that’s more because pixels are square and the pixels in each tile line
up with the pixels.


If you want it to look a little smoother, maybe expand your envelop ever so
slightly so it lines up with a pixel in a raster.  So each edge pixel is
guaranteed to be fully inside the raster and no edge partly outside the
raster or do the operation in raster space which I will explain shortly.


ST_Intersection yes is known to be very slow and as you noticed does not
give you a raster back.  

Is ST_Clip + ST_Intersects performance sufficient for your needs? 


ST_Clip really is ST_Intersection in raster space for raster/geometry.  So
you could do the intersection in pure raster space by converting your
geometry to a raster (making sure to align your new raster with your
reference raster).  That should make the edges smooth.


Like so – not tested so might have some syntax errors and also I haven’t
used this in a very long time so may be wrong about what it returns.


    SELECT ST_Intersection(r.rast, r2.rast) AS new_rast

    FROM tb_densitysurface  AS r, country, 

-- create a raster to represent the country that has the same alignment as
our density raster (pixels use the same grid)

                ST_AsRaster(  country.area_country, r.rast, '1BB', 0) AS

  WHERE country.gid = 19 AND ST_Intersects(r.rast, r2.rast) 

) As foo



As far as tiling goes, yah that would improve the smoothness of the inner
tiles but not most outer ones.

You’d also need to ST_Union them all up to get back to a single tile which
will add perhaps more overhead than just keeping as  single tile.


If you want to tile your raster, you can use this function -


Hope that helps,







From: postgis-users [mailto:postgis-users-bounces at lists.osgeo.org] On Behalf
Of liglio.pessoal at nexxa.com.br <mailto:liglio.pessoal at nexxa.com.br> 
Sent: Tuesday, November 22, 2022 2:00 PM
To: postgis-users at lists.osgeo.org <mailto:postgis-users at lists.osgeo.org> 
Subject: [postgis-users] Clipping a raster and a polygon using




I created a retangular raster representing number of lightnings in a square.
So I have this matrix raster created using ST_MapAlgebraFct and

This raster is not a tiled raster (tb_densitysurface), and when I Clip this
raster using a polygon, the query below, the performance is not good.

Maybe because the raster is not tiled. So how do I tile this raster ? And I
want the result to be a raster (not gval) to be exported as a file, and then
colored using GDAL.

It worked with a different query only using ST_Clip (this returns a rast
column and is very fast), but the edges aren´t smooth as using
ST_Insersection. I am using Postgis 2.5.






    SELECT ST_Intersection(




    ) As gval

    FROM country, tb_densitysurface WHERE ST_Intersects(rast, area_country)
and country.gid = 19

) As foo







-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20221204/28dc3b40/attachment.htm>

More information about the postgis-users mailing list