bin/pg2kml

   1 #!/usr/bin/env python
   2
   3 """
   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
   7 stdout.
   8 """
   9
  10 from optparse import OptionParser
  11 import os
  12 import pgdb
  13 import site
  14 import sys
  15
  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')
  19
  20 import Configuration.Defaults
  21 import ExitCodes
  22 import GPS
  23 import SummaryFile1
  24 import KML
  25
  26
  27 """
  28 Parse the command line options. All of these are optional; defaults
  29 are provided for the database information and the output is written
  30 to stdout.
  31 """
  32 # -h (help) Conflicts with -h HOSTNAME
  33 parser = OptionParser(add_help_option = False)
  34
  35 # Use this module's docstring as the description.
  36 parser.description = __doc__.strip()
  37
  38 parser.add_option('-h',
  39                   '--host',
  40                   help='The hostname/address where the database is located.',
  41                   default=Configuration.Defaults.DATABASE_HOST)
  42
  43 parser.add_option('-d',
  44                   '--database',
  45                   help='The database in which the population data are stored.',
  46                   default=Configuration.Defaults.DATABASE_NAME)
  47
  48 parser.add_option('-U',
  49                   '--username',
  50                   help='The username who has access to the database.',
  51                   default=Configuration.Defaults.DATABASE_USERNAME)
  52
  53 (options, args) = parser.parse_args()
  54
  55
  56
  57 def GenerateRedStyles(alpha='7f'):
  58     """
  59     Generate 256 styles (0-255), each corresponding to a shade of red.
  60
  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,
  63
  64       aa -> Alpha Component
  65       bb -> Blue Component
  66       gg -> Green Component
  67       rr -> Red Component
  68     """
  69
  70     styles = []
  71
  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)
  79         styles.append(s)
  80
  81     return styles
  82
  83
  84 conn = pgdb.connect(host=options.host,
  85                     database=options.database,
  86                     user=options.username)
  87
  88 # We'll use this cursor for all of our queries.
  89 cursor = conn.cursor()
  90
  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.
  95
  96 avg_avg_pop_query = """
  97 SELECT AVG(population_density)
  98 FROM sf1_blocks
  99 WHERE population_density > 0.005;
 100 """
 101
 102 cursor.execute(avg_avg_pop_query)
 103 rows = cursor.fetchall()
 104 avg_avg_population = float(rows[0][0])
 105
 106 stddev_avg_pop_query = """
 107 SELECT stddev_samp(population_density)
 108 FROM sf1_blocks
 109 WHERE population_density > 0.005;
 110 """
 111
 112 cursor.execute(stddev_avg_pop_query)
 113 rows = cursor.fetchall()
 114 stddev_avg_population = float(rows[0][0])
 115
 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)
 119
 120
 121 # We always want to include these styles (defining all 256 colors) in
 122 # our document.
 123 doc = KML.Document()
 124 red_styles = GenerateRedStyles()
 125
 126 for style in red_styles:
 127     doc.styles.append(style)
 128
 129
 130 query = """
 131 SELECT tiger_blocks.blkidfp00, population_density, AsKML(the_geom) as geom
 132        FROM (sf1_blocks INNER JOIN tiger_blocks
 133              ON sf1_blocks.blkidfp00 = tiger_blocks.blkidfp00);
 134 """
 135
 136 cursor.execute(query)
 137 rows = cursor.fetchall()
 138
 139 for row in rows:
 140     placemark = KML.Placemark()
 141     name = KML.Name(row[0])
 142     placemark.children.append(name)
 143
 144     avg_pop = float(row[1])
 145
 146     # If the average population is outside of the limits that I have
 147     # decreed acceptable, set its value back to the max/min
 148     # appropriately.
 149     if (avg_pop < floor_avg_population):
 150         avg_pop = floor_avg_population
 151
 152     if (avg_pop > ceil_avg_population):
 153         avg_pop = ceil_avg_population
 154
 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.
 158     #
 159     # We rely on the KML file containing 256 different styles, each
 160     # named after the RGB (Hex) representation of its PolyStyle
 161     # color.
 162     #
 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)
 167
 168     multigeometry = KML.RawText(row[2])
 169     placemark.children.append(multigeometry)
 170
 171     doc.children.append(placemark)
 172
 173 doc.print_kml()
 174
 175 conn.close()