src/Census.py

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