From 985c5ad8a0d217225fe28eef8c8e3d63bae8ba85 Mon Sep 17 00:00:00 2001 From: Michael Orlitzky Date: Sun, 27 Sep 2009 17:17:25 -0400 Subject: [PATCH] Factored out the contained population code in to the Census.Database class. Modified wkt2pop to use the new Census.Database method. --- bin/wkt2pop | 67 ++++++--------------------------------------------- src/Census.py | 66 ++++++++++++++++++++++++++++++++++++++++++++++++++ 2 files changed, 74 insertions(+), 59 deletions(-) diff --git a/bin/wkt2pop b/bin/wkt2pop index 24e4a1f..6b6290e 100755 --- a/bin/wkt2pop +++ b/bin/wkt2pop @@ -16,13 +16,13 @@ string. import sys import os import site -import pgdb from optparse import OptionParser # Basically, add '../src' to our path. # Needed for the imports that follow. site.addsitedir(os.path.dirname(os.path.abspath(sys.argv[0])) + '/../src') +import Census import Configuration.Defaults import ExitCodes @@ -65,67 +65,16 @@ if len(args) < 1: raise SystemExit(ExitCodes.NOT_ENOUGH_ARGS) -conn = pgdb.connect(host=options.host, - database=options.database, - user=options.username) - -cursor = conn.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)" % (args[0], options.srid) - -# 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.the_geom)) - / ST_Area(tiger.the_geom) ) - ) AS covered_population -""" % geometric_object - - -# Join our two block tables, so that we have both the demographic -# and geometric data. -query += \ -""" -FROM (sf1_blocks INNER JOIN tiger - ON sf1_blocks.tiger_blkidfp00 = tiger.blkidfp00) -""" - +cdb = Census.Database(options.host, + options.database, + options.username, + options.srid) -# We only need to calculate the covered population for the blocks -# that actually intersect our object. -query += \ -""" -WHERE (ST_Intersects(%s, tiger.the_geom)) -""" % geometric_object +population = cdb.find_contained_population(args[0]) - -# 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) -rows = cursor.fetchall() - -if len(rows) > 0: - population = rows[0][0] +if (population != None): print population else: print 'Error: No rows returned.' raise SystemExit(ExitCodes.NO_RESULTS) - -conn.close() + diff --git a/src/Census.py b/src/Census.py index eff25d4..ae4d28d 100644 --- a/src/Census.py +++ b/src/Census.py @@ -38,9 +38,75 @@ class Database: 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.the_geom)) + / ST_Area(tiger.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 + ON sf1_blocks.tiger_blkidfp00 = tiger.blkidfp00) + """ + + + # We only need to calculate the covered population for the blocks + # that actually intersect our object. + query += """ + WHERE (ST_Intersects(%s, tiger.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 + -- 2.44.2