]>
gitweb.michael.orlitzky.com - dead/census-tools.git/blob - pg2kml
bef23fa4393b8a40e2e596bd0a554ec8b88f64b5
4 Export block geometries from the database (PostGIS) to Keyhole Markup
5 Language (KML). The exported polygons will be assigned a color based
6 upon their blocks' average population densities. Output is written to
15 # Basically, add '../src' to our path.
16 # Needed for the imports that follow.
17 site
.addsitedir(os
.path
.dirname(os
.path
.abspath(sys
.argv
[0])) + '/../src')
20 import Configuration
.Defaults
28 Parse the command line options. All of these are optional; defaults
29 are provided for the database information and the output is written
33 parser
= CLI
.default_option_parser()
35 # Use this module's docstring as the description.
36 parser
.description
= __doc__
.strip()
37 (options
, args
) = parser
.parse_args()
40 def GenerateRedStyles(alpha
='7f'):
42 Generate 256 styles (0-255), each corresponding to a shade of red.
44 The RGB values in KML are represented as aabbggrr, where each of
45 'aa','bb','gg','rr' is a hexadecimal value between 00-ff such that,
55 for rgb_value
in range(0, 256):
56 hex_value
= "%s0000%02x" % (alpha
, rgb_value
)
57 s
= KML
.Style(initial_id
=(hex_value
))
58 poly_style
= KML
.PolyStyle()
59 color
= KML
.Color(hex_value
)
60 poly_style
.children
.append(color
)
61 s
.children
.append(poly_style
)
67 conn
= pgdb
.connect(host
=options
.host
,
68 database
=options
.database
,
69 user
=options
.username
)
71 # We'll use this cursor for all of our queries.
72 cursor
= conn
.cursor()
74 # Here we calculate a bunch of magic parameters. If we try to use a
75 # linear gradient based on the population density, it turns out that
76 # everyone gets assigned the color for "zero." So, we need to massage
77 # the numbers a little bit in order to get some color in our polygons.
79 avg_avg_pop_query
= """
80 SELECT AVG(population_density)
82 WHERE population_density > 0.005;
85 cursor
.execute(avg_avg_pop_query
)
86 rows
= cursor
.fetchall()
87 avg_avg_population
= float(rows
[0][0])
89 stddev_avg_pop_query
= """
90 SELECT stddev_samp(population_density)
92 WHERE population_density > 0.005;
95 cursor
.execute(stddev_avg_pop_query
)
96 rows
= cursor
.fetchall()
97 stddev_avg_population
= float(rows
[0][0])
99 # These parameters were carefully guessed.
100 floor_avg_population
= avg_avg_population
- (0.5 * stddev_avg_population
)
101 ceil_avg_population
= avg_avg_population
+ (2*stddev_avg_population
)
104 # We always want to include these styles (defining all 256 colors) in
107 red_styles
= GenerateRedStyles()
109 for style
in red_styles
:
110 doc
.styles
.append(style
)
114 SELECT tiger_blocks.blkidfp00, population_density, AsKML(the_geom) as geom
115 FROM (sf1_blocks INNER JOIN tiger_blocks
116 ON sf1_blocks.blkidfp00 = tiger_blocks.blkidfp00);
119 cursor
.execute(query
)
120 rows
= cursor
.fetchall()
123 placemark
= KML
.Placemark()
124 name
= KML
.Name(row
[0])
125 placemark
.children
.append(name
)
127 avg_pop
= float(row
[1])
129 # If the average population is outside of the limits that I have
130 # decreed acceptable, set its value back to the max/min
132 if (avg_pop
< floor_avg_population
):
133 avg_pop
= floor_avg_population
135 if (avg_pop
> ceil_avg_population
):
136 avg_pop
= ceil_avg_population
138 # We can calculate the color as a percentage of "completely red,"
139 # which is #ff000ff in hex, or (255, 0, 0, 0) in RGB. We start
140 # with RGB, and then convert it to hex.
142 # We rely on the KML file containing 256 different styles, each
143 # named after the RGB (Hex) representation of its PolyStyle
146 red_base10
= (avg_pop
/ (3*stddev_avg_population
)) * 255.0
147 red_hex
= "#7f0000%02x" % red_base10
148 style
= KML
.StyleUrl(red_hex
)
149 placemark
.children
.append(style
)
151 multigeometry
= KML
.RawText(row
[2])
152 placemark
.children
.append(multigeometry
)
154 doc
.children
.append(placemark
)