The raster data is sand and gravel layer thickness, which is used to do strength calculations for large rectangular concrete structures resting on top of those layers. These structures are not all oriented exactly the same, and none of them is the same orientation as the raster data (which is north-south)
For the actual calculations we just need mean values inside polygons, so nothing too special is needed, but the customer wants to export the raster data neatly aligned to the concrete structures for semi-manual sanity checking of the calculations. ST_ReSkew seemed to me to be the easiest (and fastest) way to get the data in that form, so it was the first approach to try. Frank On Mon, Feb 24, 2025 at 01:43:00PM -0800, Paul Ramsey wrote: > Out of curiousity, what are you using ST_ReSkew for? > > > On Feb 24, 2025, at 11:24 AM, Paul Ramsey <pram...@cleverelephant.ca> wrote: > > > > https://trac.osgeo.org/postgis/ticket/5854 > > > >> On Feb 24, 2025, at 10:22 AM, Paul Ramsey <pram...@cleverelephant.ca> > >> wrote: > >> > >> Replicated. I had to go up to 430 to make mine flip behaviour, but it is > >> similar. > >> > >> P. > >> > >>> On Feb 24, 2025, at 9:46 AM, Frank Gevaerts <frank.gevae...@fks.be> wrote: > >>> > >>> Ok, I managed to simplify this to basically the example in the > >>> documentation: > >>> > >>> This works fine, in a reasonable (typically 100ms to 150ms) time: > >>> SELECT ST_Rotation(ST_Reskew(ST_AddBand(ST_MakeEmptyRaster(100, 359, 0, > >>> 0, 0.001, -0.001, 0, 0, 4269), '8BUI'::text, 1, 0), 0.0015)); > >>> > >>> This does not work fine, I stopped it after more than five minutes: > >>> SELECT ST_Rotation(ST_Reskew(ST_AddBand(ST_MakeEmptyRaster(100, 360, 0, > >>> 0, 0.001, -0.001, 0, 0, 4269), '8BUI'::text, 1, 0), 0.0015)); > >>> > >>> The only difference is the size of the raster, the broken one having just > >>> one more row. The postgres process was using 100% CPU during that time, I > >>> don't think it was doing any IO (so no tempfiles as far as I could see). > >>> > >>> I reproduced it on two systems, and things break for the same numbers, > >>> both on debian with postgresql 16, one with postgis 3.5.2/gdal 3.10.2, > >>> and one with postgis 3.4.2/gdal 3.2.2 > >>> > >>> Frank > >>> > >>> On Mon, Feb 24, 2025 at 07:49:07AM -0800, Paul Ramsey wrote: > >>>> If you provide a dump of a “good” raster, and a “bad” raster, and the > >>>> exact SQL you are running, the odds of someone duplicating your issue > >>>> and then fixing your issue go up 10-fold. I like fixing problems, not > >>>> trying to replicate problems. > >>>> ATB, > >>>> P > >>>> > >>>>> On Feb 24, 2025, at 7:38 AM, Frank Gevaerts <frank.gevae...@fks.be> > >>>>> wrote: > >>>>> > >>>>> Hello, > >>>>> > >>>>> I'm having issues with ST_Reskew(). The source raster (32BSI) contains > >>>>> 150000 points, and ST_Reskew() takes forever. If I rescale the raster > >>>>> to anywhere under ~80000 points, it suddenly only takes around 300ms. > >>>>> This is not linear, ~85000 points is bad, and then suddenly 80000 > >>>>> points is good. > >>>>> > >>>>> I'm not actually sure if it's "just" a performance problem or an > >>>>> infinite loop, I have not had the patience yet to have it run for more > >>>>> than 20 minutes. > >>>>> > >>>>> This is using postgis 3.5.2 on debian installed from > >>>>> apt.postgresql.org, and libgdal36 3.10.2 > >>>>> > >>>>> Is there anything I'm overlooking, or is there a memory usage parameter > >>>>> I can set to help with this? > >>>>> > >>>>> Frank > >>>> > >>> > >>> -- > >>> Frank Gevaerts frank.gevae...@fks.be > >>> fks bvba - Formal and Knowledge Systems http://www.fks.be/ > >>> Grote Baan 79 Tel: ++32-(0)11-21 49 11 > >>> B-3511 KURINGEN-HASSELT Fax: ++32-(0)11-22 04 19 > >> > > > -- Frank Gevaerts frank.gevae...@fks.be fks bvba - Formal and Knowledge Systems http://www.fks.be/ Grote Baan 79 Tel: ++32-(0)11-21 49 11 B-3511 KURINGEN-HASSELT Fax: ++32-(0)11-22 04 19
