Using ST_Value from raster to query second table in PostGIS?

Question

This is a continuation of Returning raster values from multiple raster tables via Lat/Lon in PostGIS?, but feels sufficiently distinct enough to warrant its own post.

I am trying to select values from multiple rasters based on a single point, but need to match the value returned from one of the rasters to a separate table that holds attribute data.

The code as it exists is:

 WITH mypoint AS (SELECT ST_SetSRID(ST_MakePoint(lon, lat), 4326) geom)
               SELECT
                   ST_Value(a.raster, geom) AS value_a,
                   ST_Value(b.raster, geom) AS value_b
               FROM mypoint p
                   LEFT JOIN raster_a a ON (ST_Intersects(p.geom, a.raster))
                   LEFT JOIN raster_b b ON (ST_Intersects(p.geom, b.raster));

Which does the trick of pulling the multiple raster values. I then want to take one of these values (value_a) to use to filter out a value from a secondary table with two columns "value" and "label." This table provides the text land cover class for a given numeric value.

Unfortunately my PostGIS familiarity is not strong enough to determine how I would join the attribute table in this case. I tried adding one more join as below:

LEFT JOIN table_c c ON (ST_Value(ST_Intersects(p.geom, a.raster)) = c.value);

thinking that I would need to still use the mypoint p intersection, but with no success.

My question is similar to Adding attribute table to raster in PostGIS?, but is in reference to how the actual join is made.

Answer 1

You are close. You've already done the intersects which would filter all b's to just be those that intersect p, so you want the value.

Try:

 WITH mypoint AS (SELECT ST_SetSRID(ST_MakePoint(lon, lat), 4326) geom)
           SELECT
               ST_Value(a.raster, geom) AS value_a,
               ST_Value(b.raster, geom) AS value_b, c.label
           FROM mypoint p
               LEFT JOIN raster_a a ON (ST_Intersects(p.geom, a.raster))
               LEFT JOIN raster_b b ON (ST_Intersects(p.geom, b.raster))
               LEFT JOIN table_c c ON (ST_Value(b.raster, p.geom) = c.value);