src/Census.py

   1 import pgdb
   2
   3 import Configuration.Defaults
   4 import GPS
   5 import SummaryFile1
   6
   7
   8 class Database:
   9     """
  10     This class wraps all of the operations that we'd like to perform
  11     on the census database. Most of the utility scripts will just call
  12     one or two methods from within this class.
  13     """
  14
  15     def __init__(self,
  16                  initial_host=Configuration.Defaults.DATABASE_HOST,
  17                  initial_database=Configuration.Defaults.DATABASE_NAME,
  18                  initial_username=Configuration.Defaults.DATABASE_USERNAME,
  19                  initial_srid=Configuration.Defaults.SRID):
  20
  21         self.connection = pgdb.connect(host=initial_host,
  22                                        database=initial_database,
  23                                        user=initial_username)
  24         self.srid = initial_srid
  25
  26
  27     def __del__(self):
  28         try:
  29             self.connection.close()
  30         except:
  31             pass
  32
  33
  34     def find_average_population_density(self, coords):
  35         """
  36         Find the average population density at a set of GPS coordinates.
  37         """
  38         cursor = self.connection.cursor()
  39
  40         query = """
  41         SELECT population_density
  42         FROM blocks
  43         WHERE ST_Contains(blocks.the_geom,
  44                           ST_SetSRID(ST_Point(%.6f, %.6f), %d));
  45         """
  46
  47         sql_params = (coords.longitude, coords.latitude, self.srid)
  48         cursor.execute(query, sql_params)
  49         rows = cursor.fetchall()
  50         cursor.close()
  51
  52         # SQL queries that return no results get returned as [[None]].
  53         if rows[0][0] != None:
  54             return rows[0][0]
  55         else:
  56             return 0
  57
  58
  59     def find_contained_population(self, well_known_text):
  60         """
  61         Find the population contained within a geometric object,
  62         given in OGC Well-Known Text format.
  63         """
  64         cursor = self.connection.cursor()
  65
  66         # We're ready to build our query, one step at a time. First, we store
  67         # the Text->Geom conversion in a variable; this just makes the query a
  68         # little easier to read.
  69         geometric_object = "ST_GeomFromText(%s, %d)"
  70
  71         # We want to compute the population "under" the geometric object. We
  72         # can compute the percentage of a block that is covered by taking the
  73         # area of (the intersection of the object and the block) divided by
  74         # the total area of the block.
  75         #
  76         # Once we know the percentage covered, we just multiply that value by
  77         # the total population in the block to find the population that is
  78         # covered. The sum of these values over all blocks is our final
  79         # result.
  80         #
  81         query = """
  82         SELECT SUM(pop100 *
  83                    ( ST_Area(ST_Intersection(%s, blocks.the_geom))
  84                    / ST_Area(blocks.the_geom) )
  85                ) AS covered_population
  86                """ % geometric_object
  87         sql_params = (well_known_text, self.srid)
  88
  89
  90         # Join our two block tables, so that we have both the demographic
  91         # and geometric data.
  92         query += """
  93         FROM blocks
  94         """
  95
  96
  97         # We only need to calculate the covered population for the blocks
  98         # that actually intersect our object.
  99         query += """
 100         WHERE (ST_Intersects(%s, blocks.the_geom))
 101         """ % geometric_object
 102         # geometric_object hasn't been substituted yet, so we need
 103         # to add the sql_params twice.
 104         sql_params += sql_params
 105
 106
 107         # And we only take the first result, since they're all going to be the
 108         # same (our query returns the sum once for each block).
 109         query += """
 110         LIMIT 1
 111         """
 112
 113         cursor.execute(query, sql_params)
 114         rows = cursor.fetchall()
 115         cursor.close()
 116
 117         # SQL queries that return no results get returned as [[None]].
 118         if rows[0][0] != None:
 119             return rows[0][0]
 120         else:
 121             return 0
 122
 123
 124
 125     def get_block_geometry_as_wkt(self, blkidfp00):
 126         """
 127         Find the geometry of a (uniquely-identified) block, in
 128         Well-Known Text format.
 129         """
 130         cursor = self.connection.cursor()
 131
 132         query = """
 133         SELECT ST_AsText(the_geom)
 134         FROM blocks
 135         WHERE blkidfp00 = %s;
 136         """
 137         sql_params = (blkidfp00,)
 138
 139         cursor.execute(query, sql_params)
 140         rows = cursor.fetchall()
 141         cursor.close()
 142
 143         # Just pass on the None if that's what we got from the
 144         # database.
 145         return rows[0][0]
 146