import pgdb

import Configuration.Defaults
import GPS
import SummaryFile1


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,
                 initial_host=Configuration.Defaults.DATABASE_HOST,
                 initial_database=Configuration.Defaults.DATABASE_NAME,
                 initial_username=Configuration.Defaults.DATABASE_USERNAME,
                 initial_srid=Configuration.Defaults.SRID):
        
        self.connection = pgdb.connect(host=initial_host,
                                       database=initial_database,
                                       user=initial_username)
        self.srid = initial_srid


    def __del__(self):
        try:
            self.connection.close()
        except:
            pass


    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 blocks
        WHERE ST_Contains(blocks.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()
        cursor.close()

        # SQL queries that return no results get returned as [[None]].
        if rows[0][0] != None:
            return rows[0][0]
        else:
            return 0


    def find_contained_population(self, well_known_text):
        """
        Find the population contained within a geometric object,
        given in OGC Well-Known Text format.
        """
        cursor = self.connection.cursor()

        # We're ready to build our query, one step at a time. First, we store
        # the Text->Geom conversion in a variable; this just makes the query a
        # little easier to read.
        geometric_object = "ST_GeomFromText(%s, %d)"

        # We want to compute the population "under" the geometric object. We
        # can compute the percentage of a block that is covered by taking the
        # area of (the intersection of the object and the block) divided by
        # the total area of the block.
        #
        # Once we know the percentage covered, we just multiply that value by
        # the total population in the block to find the population that is
        # covered. The sum of these values over all blocks is our final
        # result.
        #
        query = """
        SELECT SUM(pop100 *
                   ( ST_Area(ST_Intersection(%s, blocks.the_geom))
                   / ST_Area(blocks.the_geom) )
               ) AS covered_population
               """ % geometric_object
        sql_params = (well_known_text, self.srid)


        # Join our two block tables, so that we have both the demographic
        # and geometric data.
        query += """
        FROM blocks
        """


        # We only need to calculate the covered population for the blocks
        # that actually intersect our object.
        query += """
        WHERE (ST_Intersects(%s, blocks.the_geom))
        """ % geometric_object
        # geometric_object hasn't been substituted yet, so we need
        # to add the sql_params twice.
        sql_params += sql_params


        # And we only take the first result, since they're all going to be the
        # same (our query returns the sum once for each block).
        query += """
        LIMIT 1
        """

        cursor.execute(query, sql_params)
        rows = cursor.fetchall()
        cursor.close()

        # SQL queries that return no results get returned as [[None]].
        if rows[0][0] != None:
            return rows[0][0]
        else:
            return 0



    def get_block_geometry_as_wkt(self, blkidfp00):
        """
        Find the geometry of a (uniquely-identified) block, in
        Well-Known Text format.
        """
        cursor = self.connection.cursor()

        query = """
        SELECT ST_AsText(the_geom)
        FROM blocks
        WHERE blkidfp00 = %s;
        """
        sql_params = (blkidfp00,)

        cursor.execute(query, sql_params)
        rows = cursor.fetchall()
        cursor.close()

        # Just pass on the None if that's what we got from the
        # database.
        return rows[0][0]