+
+ 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. Firsy, 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(sf1_blocks.pop100 *
+ ( ST_Area(ST_Intersection(%s, tiger_blocks.the_geom))
+ / ST_Area(tiger_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 (sf1_blocks INNER JOIN tiger_blocks
+ ON sf1_blocks.blkidfp00 = tiger_blocks.blkidfp00)
+ """
+
+
+ # We only need to calculate the covered population for the blocks
+ # that actually intersect our object.
+ query += """
+ WHERE (ST_Intersects(%s, tiger_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()
+
+ if (len(rows) > 0):
+ return rows[0][0]
+ else:
+ return None
+
+
+
+ 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(tiger_blocks.the_geom)
+ FROM tiger_blocks
+ WHERE tiger_blocks.blkidfp00 = %s;
+ """
+ sql_params = (blkidfp00,)
+
+ cursor.execute(query, sql_params)
+ rows = cursor.fetchall()
+ cursor.close()
+
+ if len(rows) > 0:
+ return rows[0][0]
+ else:
+ return None
+