Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top
SELECT st_band(rast, 1), st_width(rast), st_height(rast),
st_bandpixeltype(rast, 1), st_bandnodatavalue(rast, 1),
st_scalex(rast), st_scaley(rast), st_upperleftx(rast),
st_upperlefty(rast) FROM public.elevation WHERE st_intersects(rast,
67.50010416666670210, -93.00010416666670210 67.50010416666670210,
-93.00010416666670210 67.00010416666670210, -94.00010416666670210
67.00010416666670210, -94.00010416666670210 67.50010416666670210))',

There's a GIST index created over rast column:

CREATE INDEX elevation_rast_gist
  ON elevation
  USING gist
  (st_convexhull(rast) );

This is the 'explain analyze' output

Seq Scan on elevation  (cost=0.00..6.30 rows=1 width=32) (actual
time=2039.622..2614.787 rows=2 loops=1)
   Filter: (((rast)::geometry &&
AND _st_intersects('0103000020AD1000000100000005000000511BE8B4018057C0511BE8B401E05040511BE8B4014057C0511BE8B401E05040511BE8B4014057C0511BE8B401C05040511BE8B4018057C0511BE8B401C05040511BE8B4018057C0511BE8B401E05040'::geometry,
rast, NULL::integer))
 Total runtime: 2615.145 ms

The coordinates used in intersection are not important. The point is I want to fetch all raster rows that intersect a given window. So:

  1. Is there any other additional index I should create?
  2. Is there any other way to write the query to make it faster?

Many thanks in advance, and best regards,

share|improve this question
Didn't you write the PostGIS Raster type?!? – Ragi Yaser Burhum Aug 29 '12 at 0:23
Not exactly. I wrote the GDAL PostGIS Raster driver, and helped with PostGIS Raster extension (did some functions, mostly related with GDAL). I know the raster-polygon intersection implies the polygonization of the raster previous to intersection. That could be a heavy process. I don't know if there's some PostgreSQL specific thing I can do to make it faster without changing PostGIS Raster internals. – jorgeas80 Aug 29 '12 at 10:29

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.