1

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.

2

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);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.