import pgdb

import GPS


class Database:
    """
    This class wraps all of the operations that we'd like to perform
    on the census database. Most of the utility scripts will just call
    one or two methods from within this class.
    """

    def __init__(self, _host, _database, _username, _srid):
        self.connection = pgdb.connect(host=_host,
                                       database=_database,
                                       user=_username)
        self.srid = _srid


    def __del__(self):
        self.connection.close()

        
    def find_average_population_density(self, coords):
        """
        Find the average population density at a set of GPS coordinates.
        """
        cursor = self.connection.cursor()
                
        query = """
        SELECT population_density
        FROM (sf1_blocks INNER JOIN tiger
              ON sf1_blocks.tiger_blkidfp00=tiger.blkidfp00)
        WHERE ST_Contains(tiger.the_geom,
                          ST_SetSRID(ST_Point(%.6f, %.6f), %d));
        """
        
        sql_params = (coords.longitude, coords.latitude, self.srid)        
        cursor.execute(query, sql_params)        
        rows = cursor.fetchall()
        
        if len(rows) > 0:
            return rows[0][0]
        else:
            return None