[postgis-users] How to speed up this query using Spatial Index in PostGIS?

[Nicolas Ribot]

HI,

Yes, you can create a spatial index on your globaldtm table.
To check if such an index already exists, you can type:
\d globaldtm
in a psql console. It will tell you if the rast column is indexed.

If it is not, you can build an index on the raster column, as
described here in the 5.1.2 chapter:
http://postgis.net/docs/manual-2.0/using_raster.xml.html#RT_Creating_Rasters

You should then vacuum analyse your table.

Nicolas

On 11 July 2013 17:01, Guo YU <yuguo365 at gmail.com> wrote:
> I have put the Tiled Global DTM into PostGIS. It has around 1 million rows.
> So, when I search for the specific raster data for my interested area
> (polygon), it takes almost 1 minutes to get the result. The SQL I were using
> is as follows:
>
> SELECT rast
> FROM globaldtm
> WHERE ST_Intersects(rast,ST_envelope( (select geometry from polygons where
> id =25)));
>
> I knew there is a way to speed up this query by using spatial index. But I
> don't know how to get it work. Can I build my own index like creating index
> according to the UTM zone.
>
> Does anyone have some good example about that? Thanks.
>
>
>
> --
> YU Guo
> MSc. Hydroinformatics and Water Management
> Email: yuguo365 at gmail.com
> Phone: +33 668 195803
>
>
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>