src/Census.py

   1 import pgdb
   2
   3 import GPS
   4
   5
   6 class Database:
   7     """
   8     This class wraps all of the operations that we'd like to perform
   9     on the census database. Most of the utility scripts will just call
  10     one or two methods from within this class.
  11     """
  12
  13     def __init__(self, _host, _database, _username, _srid):
  14         self.connection = pgdb.connect(host=_host,
  15                                        database=_database,
  16                                        user=_username)
  17         self.srid = _srid
  18
  19
  20     def __del__(self):
  21         self.connection.close()
  22
  23
  24     def find_average_population_density(self, coords):
  25         """
  26         Find the average population density at a set of GPS coordinates.
  27         """
  28         cursor = self.connection.cursor()
  29
  30         query = """
  31         SELECT population_density
  32         FROM (sf1_blocks INNER JOIN tiger
  33               ON sf1_blocks.tiger_blkidfp00=tiger.blkidfp00)
  34         WHERE ST_Contains(tiger.the_geom,
  35                           ST_SetSRID(ST_Point(%.6f, %.6f), %d));
  36         """
  37
  38         sql_params = (coords.longitude, coords.latitude, self.srid)
  39         cursor.execute(query, sql_params)
  40         rows = cursor.fetchall()
  41         cursor.close()
  42
  43         if len(rows) > 0:
  44             return rows[0][0]
  45         else:
  46             return None
  47
  48
  49     def find_contained_population(self, well_known_text):
  50         """
  51         Find the population contained within a geometric object,
  52         given in OGC Well-Known Text format.
  53         """
  54         cursor = self.connection.cursor()
  55
  56         # We're ready to build our query, one step at a time. Firsy, we store
  57         # the Text->Geom conversion in a variable; this just makes the query a
  58         # little easier to read.
  59         geometric_object = "ST_GeomFromText(%s, %d)"
  60
  61         # We want to compute the population "under" the geometric object. We
  62         # can compute the percentage of a block that is covered by taking the
  63         # area of (the intersection of the object and the block) divided by
  64         # the total area of the block.
  65         #
  66         # Once we know the percentage covered, we just multiply that value by
  67         # the total population in the block to find the population that is
  68         # covered. The sum of these values over all blocks is our final
  69         # result.
  70         #
  71         query = """
  72         SELECT SUM(sf1_blocks.pop100 *
  73                    ( ST_Area(ST_Intersection(%s, tiger.the_geom))
  74                    / ST_Area(tiger.the_geom) )
  75                ) AS covered_population
  76                """ % geometric_object
  77         sql_params = (well_known_text, self.srid)
  78
  79
  80         # Join our two block tables, so that we have both the demographic
  81         # and geometric data.
  82         query += """
  83         FROM (sf1_blocks INNER JOIN tiger
  84               ON sf1_blocks.tiger_blkidfp00 = tiger.blkidfp00)
  85         """
  86
  87
  88         # We only need to calculate the covered population for the blocks
  89         # that actually intersect our object.
  90         query += """
  91         WHERE (ST_Intersects(%s, tiger.the_geom))
  92         """ % geometric_object
  93         # geometric_object hasn't been substituted yet, so we need
  94         # to add the sql_params twice.
  95         sql_params += sql_params
  96
  97
  98         # And we only take the first result, since they're all going to be the
  99         # same (our query returns the sum once for each block).
 100         query += """
 101         LIMIT 1
 102         """
 103
 104         cursor.execute(query, sql_params)
 105         rows = cursor.fetchall()
 106         cursor.close()
 107
 108         if (len(rows) > 0):
 109             return rows[0][0]
 110         else:
 111             return None
 112