I have a table where each row contains a raster covering a different geographic area. I need to query the value of the raster at a given point, but only need to query the raster that covers my lon/lat, not each one in the table. I've created a spatial index on st_convexhull(rast), but according to EXPLAIN, queries on the table aren't using the index.

I suspect I'm just not formulating the query properly, but can't quite figure out what I'm doing wrong. Here's what I've got:

CREATE INDEX topo_st_convexhull_idx ON topo USING GIST (ST_ConvexHull(rast));

EXPLAIN SELECT ST_Value(topo.rast, 1, ST_SetSRID(ST_MakePoint(0, 51), 4326))
    FROM topo
    WHERE ST_Contains(ST_ConvexHull(rast),ST_SetSRID(ST_MakePoint(0, 51), 4326));

Your Answer

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

Browse other questions tagged or ask your own question.