I have loaded a number of shapefiles using SPIT (QGIS plugin) to my PostGIS database. These layers did not have a Spatial Index created upon loading. I am wondering if there is a way to create a Spatial Index for each layer in the schema without writing a query for each layer. I am not a good PostGIS script writer, so any help would be greatly appreciated.
Thanks
Best Answer
If you want to batch create indexes on geometry columns, you could try this plpgsql function I've just knocked up:
I've not tried it in anger on my database, but it seems to do the job.
To use it, just run a
SELECT
statement like this:To create indexes on all geometry columns, you can use it like this:
Afterwards, run a
VACUUM ANALYZE
to tidy everything up.