]>
gitweb.michael.orlitzky.com - dead/census-tools.git/blob - src/Census.py
ae4d28d0de1a72bd631021cb6da270193850d991
8 This class wraps all of the operations that we'd like to perform
9 on the census database. Most of the utility scripts will just call
10 one or two methods from within this class.
13 def __init__(self
, _host
, _database
, _username
, _srid
):
14 self
.connection
= pgdb
.connect(host
=_host
,
21 self
.connection
.close()
24 def find_average_population_density(self
, coords
):
26 Find the average population density at a set of GPS coordinates.
28 cursor
= self
.connection
.cursor()
31 SELECT population_density
32 FROM (sf1_blocks INNER JOIN tiger
33 ON sf1_blocks.tiger_blkidfp00=tiger.blkidfp00)
34 WHERE ST_Contains(tiger.the_geom,
35 ST_SetSRID(ST_Point(%.6f, %.6f), %d));
38 sql_params
= (coords
.longitude
, coords
.latitude
, self
.srid
)
39 cursor
.execute(query
, sql_params
)
40 rows
= cursor
.fetchall()
49 def find_contained_population(self
, well_known_text
):
51 Find the population contained within a geometric object,
52 given in OGC Well-Known Text format.
54 cursor
= self
.connection
.cursor()
56 # We're ready to build our query, one step at a time. Firsy, we store
57 # the Text->Geom conversion in a variable; this just makes the query a
58 # little easier to read.
59 geometric_object
= "ST_GeomFromText(%s, %d)"
61 # We want to compute the population "under" the geometric object. We
62 # can compute the percentage of a block that is covered by taking the
63 # area of (the intersection of the object and the block) divided by
64 # the total area of the block.
66 # Once we know the percentage covered, we just multiply that value by
67 # the total population in the block to find the population that is
68 # covered. The sum of these values over all blocks is our final
72 SELECT SUM(sf1_blocks.pop100 *
73 ( ST_Area(ST_Intersection(%s, tiger.the_geom))
74 / ST_Area(tiger.the_geom) )
75 ) AS covered_population
76 """ % geometric_object
77 sql_params
= (well_known_text
, self
.srid
)
80 # Join our two block tables, so that we have both the demographic
83 FROM (sf1_blocks INNER JOIN tiger
84 ON sf1_blocks.tiger_blkidfp00 = tiger.blkidfp00)
88 # We only need to calculate the covered population for the blocks
89 # that actually intersect our object.
91 WHERE (ST_Intersects(%s, tiger.the_geom))
92 """ % geometric_object
93 # geometric_object hasn't been substituted yet, so we need
94 # to add the sql_params twice.
95 sql_params
+= sql_params
98 # And we only take the first result, since they're all going to be the
99 # same (our query returns the sum once for each block).
104 cursor
.execute(query
, sql_params
)
105 rows
= cursor
.fetchall()