Retrieve the distance from a point to the nearest raster pixel with a given value in Postgresql

Question

Context

I have the table (hs_buildings) in my Postgresql DB containing the Point location of my buildings (hs_buildings.coordinates). Then I have a raster containing the land use in stored in a Postgresql raster table (named hs_raster_values).

The resolution of the raster is 100x100 and all geometries are in epsg 2056.

Question

How can I retrieve the shortest distance from my buildings location to a pixel with a given value (let say 170005)

Transforming the raster pixel into points

The following query work but is not efficient at all and would take too long if run over the entire building table.

WITH hs_points AS
(
    WITH gv AS 
    (
        SELECT
            (ST_PixelAsCentroids(hs_raster_values.geometry, 1, true)).*
        FROM hs_raster_values
        WHERE hs_raster_values.raster_id = 1
    )

    SELECT
        (gv).x,
        (gv).y,
        (gv).val,
        gv.geom 
    FROM gv 
    WHERE val = 170005
)
SELECT 
    ST_Distance(hs_points.geom, hs_buildings.coordinates)
FROM hs_points, hs_buildings
WHERE hs_buildings.id = 1
ORDER BY hs_buildings.coordinates <-> hs_points.geom

LIMIT 1

With built in Postgis raster function

By looking at the documentation, I have seen that there is a Postgis function made to achieve this results : ST_MinDist4ma. However, the documentation is very poor and I find myself stuck to understand the appropriate argument I have to pass in the funciton.

I tried this query:

WITH building_pixel AS 
(
    SELECT 
        (ST_WorldToRasterCoordX(hs_raster_values.geometry, hs_buildings.coordinates),
        ST_WorldToRasterCoordY(hs_raster_values.geometry, hs_buildings.coordinates)) AS xy
    FROM hs_raster_values
    INNER JOIN hs_buildings ON ST_Intersects(hs_buildings.coordinates, hs_raster_values.geometry)
    WHERE hs_raster_values.raster_id = 1
    AND hs_buildings.egid = 1
    AND ST_Intersects(hs_buildings.coordinates, hs_raster_values.geometry)
)

SELECT 
    ST_MinDist4ma(170005, building_pixel.xy, hs_raster_values.geometry) AS min_distance
FROM hs_raster_values, building_pixel
WHERE raster_id = 1;

And get the following error: ERROR: function st_mindist4ma(integer, record, raster) does not exist LINE 14: ST_MinDist4ma(170005, building_pixel.xy, hs_raster_values.g...

Does any one know I can use this function?

Answer 1

Thanks for your help. I was indeed using ST_MinDist4ma incorrectly. I use it like this now:

SELECT
        ST_Value(ST_SetSRID(ST_MapAlgebra(
            hs_raster_values.geometry, 
            ARRAY[170005]::integer[],
            'ST_MinDist4ma(double precision[], int[], text[])'::regprocedure
        ), 2056), hs_buildings.coordinates) AS min_distance
    FROM hs_buildings
    INNER JOIN hs_raster_values ON ST_Intersects(hs_raster_values.geometry, hs_buildings.coordinates)
    WHERE hs_raster_values.raster_id = 1 AND hs_buildings.id =1

Which give me a raster as output and then I try to find the associated value at the location of the building. However, I get the following error:

NOTICE:  All input rasters do not have bands at indicated indexes. Returning empty raster

ERROR:  Could not find raster band of index 1 when getting pixel value. Returning NULL 

SQL state: XX000

Answer 2

It seems like there might be an issue with the arguments you're passing to ST_MinDist4ma. The function might not accept the types you're providing. Have you checked the function signature and ensured the data types match?