import pgdb
+import Configuration.Defaults
import GPS
+import SummaryFile1
class Database:
one or two methods from within this class.
"""
- def __init__(self, _host, _database, _username, _srid):
- self.connection = pgdb.connect(host=_host,
- database=_database,
- user=_username)
- self.srid = _srid
+ def __init__(self,
+ initial_host=Configuration.Defaults.DATABASE_HOST,
+ initial_database=Configuration.Defaults.DATABASE_NAME,
+ initial_username=Configuration.Defaults.DATABASE_USERNAME,
+ initial_srid=Configuration.Defaults.SRID):
+
+ self.connection = pgdb.connect(host=initial_host,
+ database=initial_database,
+ user=initial_username)
+ self.srid = initial_srid
def __del__(self):
self.connection.close()
-
+
def find_average_population_density(self, coords):
"""
Find the average population density at a set of GPS coordinates.
query = """
SELECT population_density
- FROM (sf1_blocks INNER JOIN tiger
- ON sf1_blocks.tiger_blkidfp00=tiger.blkidfp00)
- WHERE ST_Contains(tiger.the_geom,
+ FROM blocks
+ WHERE ST_Contains(blocks.the_geom,
ST_SetSRID(ST_Point(%.6f, %.6f), %d));
"""
cursor.execute(query, sql_params)
rows = cursor.fetchall()
cursor.close()
-
- if len(rows) > 0:
+
+ # SQL queries that return no results get returned as [[None]].
+ if rows[0][0] != None:
return rows[0][0]
else:
- return None
+ return 0
def find_contained_population(self, well_known_text):
# result.
#
query = """
- SELECT SUM(sf1_blocks.pop100 *
- ( ST_Area(ST_Intersection(%s, tiger.the_geom))
- / ST_Area(tiger.the_geom) )
+ SELECT SUM(pop100 *
+ ( ST_Area(ST_Intersection(%s, blocks.the_geom))
+ / ST_Area(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
- ON sf1_blocks.tiger_blkidfp00 = tiger.blkidfp00)
+ FROM blocks
"""
# We only need to calculate the covered population for the blocks
# that actually intersect our object.
query += """
- WHERE (ST_Intersects(%s, tiger.the_geom))
+ WHERE (ST_Intersects(%s, blocks.the_geom))
""" % geometric_object
# geometric_object hasn't been substituted yet, so we need
# to add the sql_params twice.
rows = cursor.fetchall()
cursor.close()
- if (len(rows) > 0):
+ # SQL queries that return no results get returned as [[None]].
+ if rows[0][0] != None:
return rows[0][0]
else:
- return None
+ return 0
+
+
+
+ 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(the_geom)
+ FROM blocks
+ WHERE blkidfp00 = %s;
+ """
+ sql_params = (blkidfp00,)
+
+ cursor.execute(query, sql_params)
+ rows = cursor.fetchall()
+ cursor.close()
+
+ # Just pass on the None if that's what we got from the
+ # database.
+ return rows[0][0]