1

I am new to PostGIS and am unsure if I'm thinking of this correctly, but I'd like to return values from multiple raster tables in a PostGIS database in one go.

The purpose is to query legend values for a webmap based on the lat/long values of a cursor click. So, if there are multiple raster tables in the database e.g., raster1 and raster2, I would like to shoot lat and lon to the DB and return the values from both rasters in some sort of dictionary.

How would I go about doing so with an SQL command? Would I need to do some sort of spatial join? Multiple select statements?

  • It is very doable but it depends a bit on how you store your rasters in the database. Did you tile them? Are the rasters aligned? Same projection? – tilt Jul 3 '17 at 9:52
  • I loaded them using a GeoDjango raster model, so I'm guessing that some default tiling may have happened. The raw raster files are in the same projection, so yes on that. What do you mean by aligned? Same scale and extent? If so, then they ~are not~ aligned. – jbukoski Jul 3 '17 at 9:56
2

I have no knowledge on how the GeoDjango raster model works but querying a raster table goes roughly the same way as a geometry table. I would take the following approach, provided that you know the names of your raster tables and each raster has only 1 band:

WITH mypoint AS (
  SELECT ST_SetSrid(ST_MakePoint(<x>,<y>),<mysrid>) geom
)
SELECT 
  ST_Value(a.rast, geom) AS value1,
  ST_Value(b.rast, geom) AS value2,
  ST_Value(c.rast, geom) AS value3
FROM mypoint p
LEFT JOIN table1 a ON (ST_Intersects(p.geom, a.rast))
LEFT JOIN table2 b ON (ST_Intersects(p.geom, b.rast))
LEFT JOIN table3 c ON (ST_Intersects(p.geom, c.rast))

Documentation and examples on ST_Value: https://postgis.net/docs/RT_ST_Value.html

Side note: make sure the raster table is tiled and indexed (it also works without but can make a large difference in speed)

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.