From 68801d56ace446b73e8a382ef122afcf98333003 Mon Sep 17 00:00:00 2001 From: Michael Orlitzky Date: Tue, 15 Sep 2009 23:42:31 -0400 Subject: [PATCH] Added the pg2kml script which exports PostGIS data to KML format. --- bin/pg2kml | 175 +++++++++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 175 insertions(+) create mode 100755 bin/pg2kml diff --git a/bin/pg2kml b/bin/pg2kml new file mode 100755 index 0000000..084abcc --- /dev/null +++ b/bin/pg2kml @@ -0,0 +1,175 @@ +#!/usr/bin/env python + +""" +Export block geometries from the database (PostGIS) to Keyhole Markup +Language (KML). The exported polygons will be assigned a color based +upon their blocks' average population densities. Output is written to +stdout. +""" + +from optparse import OptionParser +import os +import pgdb +import site +import sys + +# Basically, add '../src' to our path. +# Needed for the imports that follow. +site.addsitedir(os.path.dirname(os.path.abspath(sys.argv[0])) + '/../src') + +import Configuration.Defaults +import ExitCodes +import GPS +import SummaryFile1 +import KML + + +""" +Parse the command line options. All of these are optional; defaults +are provided for the database information and the output is written +to stdout. +""" +# -h (help) Conflicts with -h HOSTNAME +parser = OptionParser(add_help_option = False) + +# Use this module's docstring as the description. +parser.description = __doc__ + +parser.add_option('-h', + '--host', + help='The hostname/address where the database is located.', + default=Configuration.Defaults.DATABASE_HOST) + +parser.add_option('-d', + '--database', + help='The database in which the population data are stored.', + default=Configuration.Defaults.DATABASE_NAME) + +parser.add_option('-U', + '--username', + help='The username who has access to the database.', + default=Configuration.Defaults.DATABASE_USERNAME) + +(options, args) = parser.parse_args() + + + +def GenerateRedStyles(alpha='7f'): + """ + Generate 256 styles (0-255), each corresponding to a shade of red. + + The RGB values in KML are represented as aabbggrr, where each of + 'aa','bb','gg','rr' is a hexadecimal value between 00-ff such that, + + aa -> Alpha Component + bb -> Blue Component + gg -> Green Component + rr -> Red Component + """ + + styles = [] + + for rgb_value in range(0, 256): + hex_value = "%s0000%02x" % (alpha, rgb_value) + s = KML.Style(initial_id=(hex_value)) + poly_style = KML.PolyStyle() + color = KML.Color(hex_value) + poly_style.children.append(color) + s.children.append(poly_style) + styles.append(s) + + return styles + + +conn = pgdb.connect(host=options.host, + database=options.database, + user=options.username) + +# We'll use this cursor for all of our queries. +cursor = conn.cursor() + +# Here we calculate a bunch of magic parameters. If we try to use a +# linear gradient based on the population density, it turns out that +# everyone gets assigned the color for "zero." So, we need to massage +# the numbers a little bit in order to get some color in our polygons. + +avg_avg_pop_query = """ +SELECT AVG(population_density) +FROM sf1_blocks +WHERE population_density > 0.005; +""" + +cursor.execute(avg_avg_pop_query) +rows = cursor.fetchall() +avg_avg_population = float(rows[0][0]) + +stddev_avg_pop_query = """ +SELECT stddev_samp(population_density) +FROM sf1_blocks +WHERE population_density > 0.005; +""" + +cursor.execute(stddev_avg_pop_query) +rows = cursor.fetchall() +stddev_avg_population = float(rows[0][0]) + +# These parameters were carefully guessed. +floor_avg_population = avg_avg_population - (0.5 * stddev_avg_population) +ceil_avg_population = avg_avg_population + (2*stddev_avg_population) + + +# We always want to include these styles (defining all 256 colors) in +# our document. +doc = KML.Document() +red_styles = GenerateRedStyles() + +for style in red_styles: + doc.styles.append(style) + + +query = """ +SELECT tiger.blkidfp00, population_density, AsKML(the_geom) as geom + FROM (sf1_blocks INNER JOIN tiger + ON sf1_blocks.tiger_blkidfp00=tiger.blkidfp00); +""" + +cursor.execute(query) +rows = cursor.fetchall() + +for row in rows: + placemark = KML.Placemark() + name = KML.Name(row[0]) + placemark.children.append(name) + + avg_pop = float(row[1]) + + # If the average population is outside of the limits that I have + # decreed acceptable, set its value back to the max/min + # appropriately. + if (avg_pop < floor_avg_population): + avg_pop = floor_avg_population + + if (avg_pop > ceil_avg_population): + avg_pop = ceil_avg_population + + # We can calculate the color as a percentage of "completely red," + # which is #ff000ff in hex, or (255, 0, 0, 0) in RGB. We start + # with RGB, and then convert it to hex. + # + # We rely on the KML file containing 256 different styles, each + # named after the RGB (Hex) representation of its PolyStyle + # color. + # + red_base10 = (avg_pop / (3*stddev_avg_population)) * 255.0 + red_hex = "#7f0000%02x" % red_base10 + style = KML.StyleUrl(red_hex) + placemark.children.append(style) + + multigeometry = KML.RawText(row[2]) + placemark.children.append(multigeometry) + + doc.children.append(placemark) + +print doc.to_kml() + +conn.close() -- 2.44.2