Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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

share|improve this question
add comment

3 Answers

up vote 5 down vote accepted

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.

share|improve this answer
    
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
    
Thank you, any help would be great. –  Ryan Garnett Jun 7 '12 at 14:42
add comment

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?

share|improve this answer
    
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
add comment

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')
share|improve this answer
add comment

Your Answer

 
discard

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

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