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.



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;
  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 || ')';
$$ 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.

  • Thank you so much, this looks great. I have run this, but there seems to be an issue, mind you, this could be from my lack of scripting ability. But when I run the SELECT statements I get the following error: ERROR: function batchindex(unknown, character varying, character varying) does not exist LINE 1: select BatchIndex('public', f_table_name, f_geometry_column)... I am not sure if I am to add anything to get the batch, or if this is just a place holder for something different. The CREATE query ran through without an issue, but no indexes were generated. – Ryan Garnett Jun 7 '12 at 13:25
  • Hmm... I'm not sure what's happening there then. The fact that it thinks the first parameter you're passing is of type unknown rather than character varying is setting off alarm bells, but I can't see where it has a problem. I'll give it some thought, meanwhile any PostgreSQL gurus out there fancy giving it a go? :) – MerseyViking Jun 7 '12 at 13:52

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')

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?

  • Thanks Naresh, no I am looking for a way to create indexes for all tables in a schema, in a batch system. – Ryan Garnett Jun 7 '12 at 13:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.