]>
gitweb.michael.orlitzky.com - dead/census-tools.git/blob - bin/pg2kml
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
10 from optparse
import OptionParser
16 # Basically, add '../src' to our path.
17 # Needed for the imports that follow.
18 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
32 # -h (help) Conflicts with -h HOSTNAME
33 parser
= OptionParser(add_help_option
= False)
35 # Use this module's docstring as the description.
36 parser
.description
= __doc__
.strip()
38 parser
.add_option('-h',
40 help='The hostname/address where the database is located.',
41 default
=Configuration
.Defaults
.DATABASE_HOST
)
43 parser
.add_option('-d',
45 help='The database in which the population data are stored.',
46 default
=Configuration
.Defaults
.DATABASE_NAME
)
48 parser
.add_option('-U',
50 help='The username who has access to the database.',
51 default
=Configuration
.Defaults
.DATABASE_USERNAME
)
53 (options
, args
) = parser
.parse_args()
57 def GenerateRedStyles(alpha
='7f'):
59 Generate 256 styles (0-255), each corresponding to a shade of red.
61 The RGB values in KML are represented as aabbggrr, where each of
62 'aa','bb','gg','rr' is a hexadecimal value between 00-ff such that,
72 for rgb_value
in range(0, 256):
73 hex_value
= "%s0000%02x" % (alpha
, rgb_value
)
74 s
= KML
.Style(initial_id
=(hex_value
))
75 poly_style
= KML
.PolyStyle()
76 color
= KML
.Color(hex_value
)
77 poly_style
.children
.append(color
)
78 s
.children
.append(poly_style
)
84 conn
= pgdb
.connect(host
=options
.host
,
85 database
=options
.database
,
86 user
=options
.username
)
88 # We'll use this cursor for all of our queries.
89 cursor
= conn
.cursor()
91 # Here we calculate a bunch of magic parameters. If we try to use a
92 # linear gradient based on the population density, it turns out that
93 # everyone gets assigned the color for "zero." So, we need to massage
94 # the numbers a little bit in order to get some color in our polygons.
96 avg_avg_pop_query
= """
97 SELECT AVG(population_density)
99 WHERE population_density > 0.005;
102 cursor
.execute(avg_avg_pop_query
)
103 rows
= cursor
.fetchall()
104 avg_avg_population
= float(rows
[0][0])
106 stddev_avg_pop_query
= """
107 SELECT stddev_samp(population_density)
109 WHERE population_density > 0.005;
112 cursor
.execute(stddev_avg_pop_query
)
113 rows
= cursor
.fetchall()
114 stddev_avg_population
= float(rows
[0][0])
116 # These parameters were carefully guessed.
117 floor_avg_population
= avg_avg_population
- (0.5 * stddev_avg_population
)
118 ceil_avg_population
= avg_avg_population
+ (2*stddev_avg_population
)
121 # We always want to include these styles (defining all 256 colors) in
124 red_styles
= GenerateRedStyles()
126 for style
in red_styles
:
127 doc
.styles
.append(style
)
131 SELECT tiger.blkidfp00, population_density, AsKML(the_geom) as geom
132 FROM (sf1_blocks INNER JOIN tiger
133 ON sf1_blocks.tiger_blkidfp00=tiger.blkidfp00);
136 cursor
.execute(query
)
137 rows
= cursor
.fetchall()
140 placemark
= KML
.Placemark()
141 name
= KML
.Name(row
[0])
142 placemark
.children
.append(name
)
144 avg_pop
= float(row
[1])
146 # If the average population is outside of the limits that I have
147 # decreed acceptable, set its value back to the max/min
149 if (avg_pop
< floor_avg_population
):
150 avg_pop
= floor_avg_population
152 if (avg_pop
> ceil_avg_population
):
153 avg_pop
= ceil_avg_population
155 # We can calculate the color as a percentage of "completely red,"
156 # which is #ff000ff in hex, or (255, 0, 0, 0) in RGB. We start
157 # with RGB, and then convert it to hex.
159 # We rely on the KML file containing 256 different styles, each
160 # named after the RGB (Hex) representation of its PolyStyle
163 red_base10
= (avg_pop
/ (3*stddev_avg_population
)) * 255.0
164 red_hex
= "#7f0000%02x" % red_base10
165 style
= KML
.StyleUrl(red_hex
)
166 placemark
.children
.append(style
)
168 multigeometry
= KML
.RawText(row
[2])
169 placemark
.children
.append(multigeometry
)
171 doc
.children
.append(placemark
)