]>
gitweb.michael.orlitzky.com - dead/census-tools.git/blob - Census.py
0f8d23473144d38602e3094797de3f44cd9b05fb
3 import Configuration
.Defaults
10 This class wraps all of the operations that we'd like to perform
11 on the census database. Most of the utility scripts will just call
12 one or two methods from within this class.
16 initial_host
=Configuration
.Defaults
.DATABASE_HOST
,
17 initial_database
=Configuration
.Defaults
.DATABASE_NAME
,
18 initial_username
=Configuration
.Defaults
.DATABASE_USERNAME
,
19 initial_srid
=Configuration
.Defaults
.SRID
):
21 self
.connection
= pgdb
.connect(host
=initial_host
,
22 database
=initial_database
,
23 user
=initial_username
)
24 self
.srid
= initial_srid
28 self
.connection
.close()
31 def find_average_population_density(self
, coords
):
33 Find the average population density at a set of GPS coordinates.
35 cursor
= self
.connection
.cursor()
38 SELECT population_density
39 FROM (sf1_blocks INNER JOIN tiger_blocks
40 ON sf1_blocks.blkidfp00=tiger_blocks.blkidfp00)
41 WHERE ST_Contains(tiger_blocks.the_geom,
42 ST_SetSRID(ST_Point(%.6f, %.6f), %d));
45 sql_params
= (coords
.longitude
, coords
.latitude
, self
.srid
)
46 cursor
.execute(query
, sql_params
)
47 rows
= cursor
.fetchall()
50 # SQL queries that return no results get returned as [[None]].
51 if rows
[0][0] != None:
57 def find_contained_population(self
, well_known_text
):
59 Find the population contained within a geometric object,
60 given in OGC Well-Known Text format.
62 cursor
= self
.connection
.cursor()
64 # We're ready to build our query, one step at a time. Firsy, we store
65 # the Text->Geom conversion in a variable; this just makes the query a
66 # little easier to read.
67 geometric_object
= "ST_GeomFromText(%s, %d)"
69 # We want to compute the population "under" the geometric object. We
70 # can compute the percentage of a block that is covered by taking the
71 # area of (the intersection of the object and the block) divided by
72 # the total area of the block.
74 # Once we know the percentage covered, we just multiply that value by
75 # the total population in the block to find the population that is
76 # covered. The sum of these values over all blocks is our final
80 SELECT SUM(sf1_blocks.pop100 *
81 ( ST_Area(ST_Intersection(%s, tiger_blocks.the_geom))
82 / ST_Area(tiger_blocks.the_geom) )
83 ) AS covered_population
84 """ % geometric_object
85 sql_params
= (well_known_text
, self
.srid
)
88 # Join our two block tables, so that we have both the demographic
91 FROM (sf1_blocks INNER JOIN tiger_blocks
92 ON sf1_blocks.blkidfp00 = tiger_blocks.blkidfp00)
96 # We only need to calculate the covered population for the blocks
97 # that actually intersect our object.
99 WHERE (ST_Intersects(%s, tiger_blocks.the_geom))
100 """ % geometric_object
101 # geometric_object hasn't been substituted yet, so we need
102 # to add the sql_params twice.
103 sql_params
+= sql_params
106 # And we only take the first result, since they're all going to be the
107 # same (our query returns the sum once for each block).
112 cursor
.execute(query
, sql_params
)
113 rows
= cursor
.fetchall()
116 # SQL queries that return no results get returned as [[None]].
117 if rows
[0][0] != None:
124 def get_block_geometry_as_wkt(self
, blkidfp00
):
126 Find the geometry of a (uniquely-identified) block, in
127 Well-Known Text format.
129 cursor
= self
.connection
.cursor()
132 SELECT ST_AsText(tiger_blocks.the_geom)
134 WHERE tiger_blocks.blkidfp00 = %s;
136 sql_params
= (blkidfp00
,)
138 cursor
.execute(query
, sql_params
)
139 rows
= cursor
.fetchall()
142 # Just pass on the None if that's what we got from the