X-Git-Url: http://gitweb.michael.orlitzky.com/?a=blobdiff_plain;f=src%2FCensus.py;h=da9283d2efb20c4c7d1f8896b120fad8f98b3815;hb=3cbb8eb8dbfa4891e4251b3520cf3029b59ebb34;hp=eff25d4fbacd45e3053015462921aad55d46c68a;hpb=591f5177a7f58f6d5acc3314574eaf9f19dad963;p=dead%2Fcensus-tools.git

diff --git a/src/Census.py b/src/Census.py
index eff25d4..da9283d 100644
--- a/src/Census.py
+++ b/src/Census.py
@@ -1,6 +1,7 @@
 import pgdb
 
 import GPS
+import SummaryFile1
 
 
 class Database:
@@ -20,7 +21,7 @@ class Database:
     def __del__(self):
         self.connection.close()
 
-        
+
     def find_average_population_density(self, coords):
         """
         Find the average population density at a set of GPS coordinates.
@@ -29,18 +30,109 @@ class Database:
                 
         query = """
         SELECT population_density
-        FROM (sf1_blocks INNER JOIN tiger
-              ON sf1_blocks.tiger_blkidfp00=tiger.blkidfp00)
-        WHERE ST_Contains(tiger.the_geom,
+        FROM (sf1_blocks INNER JOIN tiger_blocks
+              ON sf1_blocks.blkidfp00=tiger_blocks.blkidfp00)
+        WHERE ST_Contains(tiger_blocks.the_geom,
                           ST_SetSRID(ST_Point(%.6f, %.6f), %d));
         """
         
         sql_params = (coords.longitude, coords.latitude, self.srid)        
         cursor.execute(query, sql_params)        
         rows = cursor.fetchall()
+        cursor.close()
         
         if len(rows) > 0:
             return rows[0][0]
         else:
             return None
 
+
+    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
+