]>
gitweb.michael.orlitzky.com - dead/census-tools.git/blob - Census.py
a69ff58b9cb68fea0e8c398d5fce289f7954a87e
9 This class wraps all of the operations that we'd like to perform
10 on the census database. Most of the utility scripts will just call
11 one or two methods from within this class.
14 def __init__(self
, _host
, _database
, _username
, _srid
):
15 self
.connection
= pgdb
.connect(host
=_host
,
22 self
.connection
.close()
25 def find_average_population_density(self
, coords
):
27 Find the average population density at a set of GPS coordinates.
29 cursor
= self
.connection
.cursor()
32 SELECT population_density
33 FROM (sf1_blocks INNER JOIN tiger
34 ON sf1_blocks.tiger_blkidfp00=tiger.blkidfp00)
35 WHERE ST_Contains(tiger.the_geom,
36 ST_SetSRID(ST_Point(%.6f, %.6f), %d));
39 sql_params
= (coords
.longitude
, coords
.latitude
, self
.srid
)
40 cursor
.execute(query
, sql_params
)
41 rows
= cursor
.fetchall()
50 def find_contained_population(self
, well_known_text
):
52 Find the population contained within a geometric object,
53 given in OGC Well-Known Text format.
55 cursor
= self
.connection
.cursor()
57 # We're ready to build our query, one step at a time. Firsy, we store
58 # the Text->Geom conversion in a variable; this just makes the query a
59 # little easier to read.
60 geometric_object
= "ST_GeomFromText(%s, %d)"
62 # We want to compute the population "under" the geometric object. We
63 # can compute the percentage of a block that is covered by taking the
64 # area of (the intersection of the object and the block) divided by
65 # the total area of the block.
67 # Once we know the percentage covered, we just multiply that value by
68 # the total population in the block to find the population that is
69 # covered. The sum of these values over all blocks is our final
73 SELECT SUM(sf1_blocks.pop100 *
74 ( ST_Area(ST_Intersection(%s, tiger.the_geom))
75 / ST_Area(tiger.the_geom) )
76 ) AS covered_population
77 """ % geometric_object
78 sql_params
= (well_known_text
, self
.srid
)
81 # Join our two block tables, so that we have both the demographic
84 FROM (sf1_blocks INNER JOIN tiger
85 ON sf1_blocks.tiger_blkidfp00 = tiger.blkidfp00)
89 # We only need to calculate the covered population for the blocks
90 # that actually intersect our object.
92 WHERE (ST_Intersects(%s, tiger.the_geom))
93 """ % geometric_object
94 # geometric_object hasn't been substituted yet, so we need
95 # to add the sql_params twice.
96 sql_params
+= sql_params
99 # And we only take the first result, since they're all going to be the
100 # same (our query returns the sum once for each block).
105 cursor
.execute(query
, sql_params
)
106 rows
= cursor
.fetchall()
116 def get_block_geometry_as_wkt(self
, blkidfp00
):
118 Find the geometry of a (uniquely-identified) block, in
119 Well-Known Text format.
121 cursor
= self
.connection
.cursor()
124 SELECT ST_AsText(tiger.the_geom)
126 WHERE tiger.blkidfp00 = %s;
128 sql_params
= (blkidfp00
,)
130 cursor
.execute(query
, sql_params
)
131 rows
= cursor
.fetchall()