Script for building spatial indexes on tables that do not have them already in a PostGIS database. Attempts to build spatial indexes for all geometry fields in the db.

1_build_spatial_index_all_postgis_tables.py

##
## Script for building spatial indexes on tables that do not have them already.
## Attempts to build spatial indexes for all geometry fields in the db.
##
## Uses the PyGreSQL module
##
## Future Improvements:
#### add some handling of existing indexes to possibly delete them and rebuild them with this naming convention
#### rather than just building a list of tables that have 1 or more indexes this should determine what fields for a specific table have an index (or dont) and handle them.
###### The current script would not catch a second geometry field in a table that has a single index because it would not be added to the index_list.
##
## This could be done as a PostgreSQL function with a trigger as well.  
##
## Author = Justin Lewis
##


import pg


class build_spatial_index:

    def iter_db_tables(self):
        
       print "Building spatial indexes on all tables in the database..."

       #### Builds a list of tables that have associated indexes with the word "geom" or "centroid" in the geometry field name the index is built on. 
       selectIndexes ="""select t.relname as table_name from pg_class t, pg_index ix, pg_attribute a
       where t.oid = ix.indrelid and a.attrelid = t.oid and a.attnum = ANY(ix.indkey)
       and t.relkind = 'r' and (a.attname like '%geom%' or a.attname like '%centroid%') and t.relname != 'geometry_columns' and t.relname != 'spatial_ref_sys';"""
       indexes = conn.query(selectIndexes)
       index_list = indexes.getresult()
       
       #### Builds a list of tables in the database 
       selectTables = "SELECT table_name FROM information_schema.tables WHERE table_schema = 'public' and table_type = 'BASE TABLE' and table_name != 'geometry_columns';"
       tables = conn.query(selectTables)      
       table_list = tables.getresult()
       for table in table_list:
           if table not in index_list:
               table = self.strip_list_entries(table)
               #### selects the geometry fields from the table
               geom = self.select_geom(table)
               if geom != None:
                   print "Creating spatial index for: ", table
                   indexName = table+"_"+geom+"_gist"
                   cmd = "CREATE INDEX {indexName} ON public.{table} USING gist({geom});".format(indexName = indexName, table = table, geom = geom)
                   conn.query(cmd)


    def select_geom(self, pgtable_name):
        
        sel = "select f_geometry_column from geometry_columns where f_table_name = '{pgtable_name}';".format(pgtable_name = pgtable_name)
        sel = conn.query(sel)
        result = sel.getresult()
        for geoms in result:
            geoms = self.strip_list_entries(geoms)
            return geoms


    def strip_list_entries(self, entry):
        entry = str(entry)
        entry = entry.lstrip("('")
        entry = entry.rstrip("',)")
        return entry


if __name__ == '__main__':

    #DB connection properties
    conn = pg.connect(dbname = 'YOUR_DB', host= 'YOUR_HOST', port= 5432, user = 'YOUR_USER',passwd= 'YOUR_PASSWORD')
    
    
    build_spatial_index = build_spatial_index()
    build_spatial_index.iter_db_tables()
    
    print ""
    print "process complete!"

2_build_spatial_index_all_postgis_tables.py

##
## Script for building spatial indexes on tables that do not have them already.
## Attempts to build spatial indexes for all geometry fields in the db.
##
##
## Uses the Psycopg2 module
##
## Future Improvements:
#### add some handling of existing indexes to possibly delete them and rebuild them with this naming convention
#### rather than just building a list of tables that have 1 or more indexes this should determine what fields for a specific table have an index (or dont) and handle them.
###### The current script would not catch a second geometry field in a table that has a single index because it would not be added to the index_list.
##
## This could be done as a PostgreSQL function with a trigger as well.  
##
## Author = Justin Lewis
##
 
import psycopg2
 
 
class build_spatial_index:
 
    def iter_db_tables(self):
        
       print "Building spatial indexes on all tables in the database..."
 
       #### Builds a list of tables that have associated indexes with the word "geom" or "centroid" in the geometry field name the index is built on. 
       selectIndexes = """select t.relname as table_name from pg_class t, pg_index ix, pg_attribute a
       where t.oid = ix.indrelid and a.attrelid = t.oid and a.attnum = ANY(ix.indkey)
       and t.relkind = 'r' and (a.attname like '%geom%' or a.attname like '%centroid%') and t.relname != 'geometry_columns' and t.relname != 'spatial_ref_sys';"""
       indexes = cur.execute(selectIndexes)
       index_list = cur.fetchall()
       print index_list
       
       #### Builds a list of tables in the database 
       selectTables = """SELECT table_name FROM information_schema.tables
                        WHERE table_schema = 'public' and table_type = 'BASE TABLE' and table_name != 'geometry_columns';"""
       tables = cur.execute(selectTables)      
       table_list = cur.fetchall()
       for table in table_list:
           if table not in index_list:
               table = self.strip_list_entries(table)
               #### selects the geometry fields from the table
               geom = self.select_geom(table)
               if geom != None:
                   print "Creating spatial index for: ", table
                   indexName = table+"_"+geom+"_gist"
                   cmd = "CREATE INDEX {indexName} ON public.{table} USING gist({geom});".format(indexName = indexName, table = table, geom = geom)
                   cur.execute(cmd)
                   conn.commit()
 
 
    def select_geom(self, pgtable_name):
      
        sel = "select f_geometry_column from geometry_columns where f_table_name = '{pgtable_name}';".format(pgtable_name = pgtable_name)
        sel = cur.execute(sel)
        result = cur.fetchall()
        for geoms in result:      
            geoms = self.strip_list_entries(geoms)
            return geoms
 
 
    def strip_list_entries(self, entry):
        entry = str(entry)
        entry = entry.lstrip("('")
        entry = entry.rstrip("',)")
        return entry
 
 
if __name__ == '__main__':
 
    #DB connection properties
    conn = psycopg2.connect(database = 'YOUR_DATABASE', host= 'YOUR_HOST', port= 5432, user = 'USERNAME',password= 'PASSWORD') 
    
    cur = conn.cursor()
    build_spatial_index = build_spatial_index()
    build_spatial_index.iter_db_tables()
    
    print ""
    print "process complete!"