Script for truncating and repopulating the geometry_columns table in a pre PostGIS 2.0 database.

cleanse_postgis_geometry_columns_table.py

##
## Script for truncating and repopulating the geometry_columns table in a pre PostGIS 2.0 database.
####  This is important because the geometry_columns table often has legacy records where tables have been deleted
####  but the corresponding geom record was not removed.
##
## This could be done as a PostgreSQL function with a trigger as well.  
##
## Author = Justin Lewis
##


import pg


class table_cleanse:

    def clear_geom_table(self, table):
       print "Clearing all records from the geometry_columns table..."
       clearTable = "TRUNCATE {table};".format(table = table)      
       conn.query(clearTable)

    def iter_db_tables(self):
       print "Repopulating the geometry_columns table..."
       selectTables = "SELECT table_name FROM information_schema.tables WHERE table_schema = 'public' ORDER BY table_schema,table_name;"
       tables = conn.query(selectTables)      
       table_list = tables.getresult()
       for table in table_list:
           table = self.strip_list_entries(table)
           self.pop_geom(table)

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

    def pop_geom(self, table):
        popGeom = "SELECT Populate_Geometry_Columns('public.{table}'::regclass);".format(table = table)
        conn.query(popGeom)
        print "Populated geom table for: ",table

  

if __name__ == '__main__':

    #DB connection properties
    conn = pg.connect(dbname = 'YOUR_DB', host= 'YOUR_HOST', port= 5432, user = 'YOUR_USER',passwd= 'YOUR_PASS')


    table = 'geometry_columns'  #### Change for testing!!!
    
    
    table_cleanse = table_cleanse()
    table_cleanse.clear_geom_table(table)
    table_cleanse.iter_db_tables()
    
    print ""
    print "process complete!"