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.