Create Spatial Index in PostGIS on an entire Schema

Question

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

Answer 1

If you want to batch create indexes on geometry columns, you could try this plpgsql function I've just knocked up:

CREATE OR REPLACE FUNCTION BatchIndex(sn text, tn text, cn text) RETURNS void AS $$
DECLARE i_exists integer;
DECLARE idxname text;
BEGIN
  idxname := 'idx_' || tn || '_' || cn;
  select into i_exists count(*) from pg_class where relname = idxname;

  IF i_exists = 0 THEN
    EXECUTE 'CREATE INDEX ' ||idxname || ' ON '
      || sn || '.' || tn
      || ' USING GIST(' || cn || ')';
  END IF;
END;
$$ LANGUAGE plpgsql;

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:

select BatchIndex('public', f_table_name, f_geometry_column) from geometry_columns where f_table_schema = 'public';

To create indexes on all geometry columns, you can use it like this:

select BatchIndex(f_table_schema, f_table_name, f_geometry_column) from geometry_columns;

Afterwards, run a VACUUM ANALYZE to tidy everything up.

Answer 2

say your table is 'building', you can index using GIST

CREATE INDEX building_gindx ON building USING GIST (geom);

Is that what you are looking for?

Answer 3

The top answer won't work if you have views with geometry. Changing the 'IF' statement to check that you're not trying to build a index on a view solves that issue. If you do wish to use views with geometry, replace this line:

IF i_exists = 0

with this:

IF i_exists = 0 AND tn IN (SELECT table_name, table_type FROM information_schema.tables WHERE table_type = 'BASE TABLE')