Tell me more ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.
up vote 1 down vote favorite 
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,
st_polygonfromtext('POLYGON((-94.00010416666670210
67.50010416666670210, -93.00010416666670210 67.50010416666670210,
-93.00010416666670210 67.00010416666670210, -94.00010416666670210
67.00010416666670210, -94.00010416666670210 67.50010416666670210))',
4269))

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 &&
'0103000020AD1000000100000005000000511BE8B4018057C0511BE8B401E05040511BE8B4014057C0511BE8B401E05040511BE8B4014057C0511BE8B401C05040511BE8B4018057C0511BE8B401C05040511BE8B4018057C0511BE8B401E05040'::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

Know someone who can answer? Share a link to this question via email, Google+, Twitter, or Facebook.

Your Answer

 
discard

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

Browse other questions tagged or ask your own question.