Generating a KML with data from postgis and libkml

Polls on Google Earth / Westmount--Ville-Marie

So right now I am working on an experimental project to produce a mashup with the electoral data down to the polling station level.

Right now, I imported the data from the Shapefile provided free of charge on Geogratis to a PostgreSQL w/ Postgis database.

I am then using the libkml library (with SWIG bindings) to merge the data from postgis. Postgis conveniently has a SQL function called "ST_AsKML()" that outputs the geometry as the corresponding KML code. Here is the code that I used:

#!/usr/bin/env python

import sys
import pg
import kmlbase
import kmldom
import kmlengine

def usage():
    return 'usage: [fed_num]'

def main():
    if len(sys.argv) > 1:
	if len(sys.argv[1]) != 5:
	   print usage()
	   return False
	factory = kmldom.KmlFactory_GetFactory()
	docu = factory.CreateDocument()
	kml = factory.CreateKml()
	conn = pg.connect('postgis', '', 5432, None, None, 'DB_NAME', 'DB_PASS')
	rows = conn.query('SELECT pd_id, pd_num, pd_nbr_sfx, pd_type, adv_poll, ed_id, fed_num, ST_AsKML(the_geom) as boundary FROM pd308_a WHERE fed_num = %05d ORDER BY pd_num ' % int(sys.argv[1]))
	res = rows.dictresult()
	for poll in res:
	    pl = factory.CreatePlacemark()
	    kmlfile,errors = kmlengine.KmlFile.CreateFromParse(poll['boundary'])
	    mg = kmldom.AsMultiGeometry(kmlfile.get_root())
	print kmldom.SerializePretty(kml)
	print usage()

if __name__ == '__main__':

Run this on the command line with python (my version is 2.6). This code generates a KML file with all the different polls as separate placemarks for any given federal riding. For now, there is no styling or balloons.

Edit (2009-08-17): Here is the result if I randomize the polygon's colors. Federal Electoral Polls on Google Earth / Westmount--Ville-Marie

Download this generated KML (for Westmount--Ville-Marie - 24075)



Leave a comment

Type the characters you see in the picture above.

About this Entry

This page contains a single entry by Cedric published on August 16, 2009 9:27 PM.

Canada electoral results down to the polling station level was the previous entry in this blog.

Google Earth - results per poll by party color is the next entry in this blog.

Find recent content on the main index or look in the archives to find all content.