From 68801d56ace446b73e8a382ef122afcf98333003 Mon Sep 17 00:00:00 2001
From: Michael Orlitzky <michael@orlitzky.com>
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