Building a KML feed with YQL and coldfusion

4 views of Comet Lulin, originally uploaded by eat your greens.

Following on from my javascript photo browser, for viewing astronomy photos in Google sky, I’ve written a feed to display Astronomy Photographer of the Year (APY) photos in Google Earth. The address is That link should open in Google Earth. If it doesn’t, add it manually in Google Earth via ‘Add > Network Link’ (some browsers save the KML feed rather than opening it).

If you’re interested in seeing how the feed is generated, have a look at the source code. I’ll also go through the code here to try and explain how it works. I’ve written it in coldfusion, but it should be straightforward to rewrite in any other server-side language.

First we define a YQL query to retrieve 30 photos from the APY Flickr group (group ID 973956@N23). We select only photos tagged with 'astro:RA=' to make sure we only get photos tagged with positions by the robot. We then encode the query in a URL and get the data, as xml, using cfhttp.

<cfset yql = "select farm, server, id, secret, title, urls.url.content, tags.tag.raw
		where (tags.tag.raw like 'astro:%')
		and photo_id in (
			select id from
			where group_id='973956@N23'
			and machine_tags='astro:RA='
<cfset yqlURL = "">
<!--- Get our results as XML for better performance.. --->
<cfhttp url="#yqlURL#" redirect="no" />

The YQL query returns XML that looks something like the following fragment, one photo element, with title and urls, for each returned tag.

	<photo farm="4" id="3465625670" secret="95d665362f" server="3495">
		    <tag raw="astro:RA=83.8283780568"/>
	<photo farm="4" id="3465625670" secret="95d665362f" server="3495">
		    <tag raw="astro:Dec=-5.41730075227"/>

First we parse the output of the YQL query and create a new structure, photos, which will hold one element for each photo, keyed on photo id. We get an array of photo elements by selecting all the children of query.results and loop through this array. If we are dealing with a new photo id, we store it and create a new photo hash to hold the properties of this photo. At the end of this loop, we aim to have each photo stored as a hash with properties photo.title, photo.url etc. photo.imgroot holds the root URL used to construct links to the thumbnail images on Flickr.

	rawData = XmlParse(cfhttp.FileContent);
	//array of results is now in rawdata.query.results.XmlChildren
	results = rawData.query.results.XmlChildren;
	//fun with Java - arrays are easier to loop through if you use their Iterator object.
	iterator = results.Iterator();
	//photos will hold our parsed photo data
	photos = StructNew();
	//loop through YQL results, parse and store photo data in photos, keyed on photo id.
	while (iterator.HasNext()) {
		result = iterator.Next();
		id =;
		if(not structKeyExists(photos, id)) {
			photos[id] = structNew();
			photos[id]['name'] = ArrayNew(1);
			p = photos[id];
			p.title = result.title.XmlText;
			p.url = result.urls.url.XmlText;
			p.imgroot = "http://farm

Parsing the values of the machine tags requires a little bit of extra work. The values we want are stored in the raw attribute for each tag. We split the value on = to get the tag name and value, then split the tag name on : and discard the namespace prefix. The results are stored in our new photo hash as photo[tagname] = value. astro:name and astro:fieldsize require slightly different treatment from the other machine tags. There may be several astro:name tags in a single photo, so we store as an array. astro:fieldsize, which may be in degrees, arcmins or arcsecs, is converted to x and y values in degrees and stored in photo.fov.x and photo.fov.y.

		tag = result.tags.tag.XMLAttributes.raw;
			//split machine tag name and value
			tmp = ListToArray(tag, '=');
			//discard namespace from tag name by splitting tmp[1] on :
			// store data as o[predicate] = value eg. o[id].RA = 85.123456
			tagname = ListToArray(tmp[1],':');
			tagname = tagname[2];
			if (tagname eq 'name') {
			} else {
				p[tagname] = tmp[2];
			//fieldsize is a string in degrees, arcminutes or arcseconds.
			//We will standardise on a value in degrees.
			if (tmp[1] eq 'astro:fieldsize') {
			//convert fieldsize string into a numeric value in degrees.
				tmp = ListToArray(p.fieldsize,' ');
				p.fov = structNew();
				p.fov.x = tmp[1];
				p.fov.y = tmp[3];
				if (tmp[4] eq 'arcminutes') {
					p.fov.x = p.fov.x/60;
					p.fov.y = p.fov.y/60;
				if (tmp[4] eq 'arcseconds') {
					p.fov.x = p.fov.x/3600;
					p.fov.y = p.fov.y/3600;

Having parsed the YQL results into a more manageable array of photos, the final step is loop through the photos and generate latitude, longitude, range, rotation and field boundaries for each that we can use in a KML photo overlay. Equations for latitude, longitude and range are taken from Sky data in KML. Unfortunately, there isn’t much documentation explaining the boundaries of photo overlays, but I found by trial and error that we can set the north–south distance to our vertical field in degrees. The east–west distance is the horizontal field corrected by a factor of cos(lat). Note that this works regardless of whether north–south is vertical or horizontal in the photo.

//for each photo, convert machine tag values into numbers used by Google Earth overlays.
		for (id in photos) {
			p = photos[id]; = p.Dec;
			p.long = p.RA - 180;
			beta = max(p.fov.x,p.fov.y) * 6.28/360;
		    p.range = 1.5 *6378000 *(1.1917536 * sin(beta/2) - cos(beta/2) + 1);
			p.rotation = 180-p.orientation;
			latField = p.fov.y;
			longField = p.fov.x/cos( * 6.28/360);
			p.north = + latField/2;
			p.south = - latField/2;
			p.east = p.long + longField/2;
			p.west = p.long - longField/2;


That done, we can generate a KML feed by looping through the photos array and printing the photo properties into the appropriate elements of a KML template.

	<?xml version="1.0" encoding="UTF-8"?>
	<kml xmlns="" hint="target=sky">
	  <name>Astronomy Photographer of the Year</name>
	  <cfloop collection="#photos#" item="id">

A quick footnote about performance. My first version of this code got the YQL results as JSON, but I found that decoding the JSON response was taking around 30 or 40s. Parsing XML, on the other hand, takes around 2 or 3s. So I switched to XML even though the code for processing the XML results was somewhat uglier. I think this is down to CFJSON’s decode function making heavy use of regular expressions, which slows it right down when dealing with large or complicated JSON responses.