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