ST_Collect + ST_InterpolateRaster = "Unknown geometry type"

[Player701]

It seems that sometimes ST_InterpolateRaster does not like the geometry produced by ST_Collect. The geometries in question are all multipoints produced from 1 or more points. Re-creating them from ST_AsText output seems to clear the error, but is unoptimal.

I don't know whether it is ST_Collect or ST_InterpolateRaster being at fault here, but I suspect the latter because all other operations I've tried on the output of ST_Collect (ST_XMin, ST_NumGeometries etc) do not throw any errors.

Also, depending on the input values, ST_InterpolateRaster may instead complain that input geometry does not have Z-values even though it clearly does. Again, re-creating the geometry from ST_AsText output makes it work.

I first thought I was doing something wrong when I saw the message about no Z-values, but after I got the "unknown geometry type" error I came to a conclusion that I indeed encountered a bug.

Example query (NB: in the real life scenario points are selected from a table):

SELECT ST_InterpolateRaster(
  (SELECT ST_Collect(pt)
    FROM (
    SELECT 'POINT Z(3729230.119184725 5657111.610875956 273.3486328125)'::geometry AS pt
    UNION SELECT 'POINT Z(3757059.9918830437 5657111.610875956 273.0422668457031)'::geometry AS pt
    ) pts
  ),
  'invdist',
  ST_AddBand(ST_MakeEmptyRaster(1, 1, 0, 0, 1), '32BF')
);

Produces the following error on my machine (Windows 10 x64 22H2):

ERROR:  Unknown geometry type: 1699745866 - Invalid type
SQL state: XX000

But this works:

SELECT ST_InterpolateRaster(
  ST_AsText((SELECT ST_Collect(pt)
    FROM (
    SELECT 'POINT Z(3729230.119184725 5657111.610875956 273.3486328125)'::geometry AS pt
    UNION SELECT 'POINT Z(3757059.9918830437 5657111.610875956 273.0422668457031)'::geometry AS pt
    ) pts
  ))::geometry,
  'invdist',
  ST_AddBand(ST_MakeEmptyRaster(1, 1, 0, 0, 1), '32BF')
);

Another example query:

SELECT ST_InterpolateRaster(
  (SELECT ST_Collect(pt)
    FROM (
    SELECT 'POINT Z(1 2 3)'::geometry AS pt
    UNION SELECT 'POINT Z(3 4 5)'::geometry AS pt
    ) pts
  ),
  'invdist',
  ST_AddBand(ST_MakeEmptyRaster(1, 1, 0, 0, 1), '32BF')
);

Produces the following error:

ERROR:  RASTER_InterpolateRaster: input geometry does not have Z values
SQL state: XX000

But this works:

SELECT ST_InterpolateRaster(
  ST_AsText((SELECT ST_Collect(pt)
    FROM (
    SELECT 'POINT Z(1 2 3)'::geometry AS pt
    UNION SELECT 'POINT Z(3 4 5)'::geometry AS pt
    ) pts
  ))::geometry,
  'invdist',
  ST_AddBand(ST_MakeEmptyRaster(1, 1, 0, 0, 1), '32BF')
);

[pramsey]

I'm not 100% sure this is an ST_InterpolateRaster problem… it certainly does nothing special with its inputs, nothing different from other functions, and this seems very much an "input memory disappears" problem.

What PgSQL version are you on? I see the same thing, but this is the kind of thing that might change over PgSQL versions.

This works, for example.

SELECT ST_InterpolateRaster(
  pts,
  'invdist',
  ST_AddBand(ST_MakeEmptyRaster(1, 1, 0, 0, 1), '32BF')
)
FROM (SELECT ST_Collect(pt) AS pts
    FROM (                                                                              
    SELECT 'POINT Z(3729230.119184725 5657111.610875956 273.3486328125)'::geometry AS pt
    UNION SELECT 'POINT Z(3757059.9918830437 5657111.610875956 273.0422668457031)'::geometry AS pt
    ) pts) cpts;

[Paul Ramsey <pramsey@…>]

In 1aafd8b/git:

Detoast Datum before casting result to pointer, references #5313

[pramsey]

Yes, it was a ST_InterpolateRaster problem. Not fully detoasting the datum meant that when the collection result was sufficiently fresh the datum didn't point right at the result memory, but somewhere else, so casting it before detoasting led to bad memory access.

[Paul Ramsey <pramsey@…>]

In dd85bbc/git:

Detoast Datum before casting result to pointer, references #5313

[Paul Ramsey <pramsey@…>]

In 20194fc/git:

Detoast Datum before casting result to pointer, references #5313