Archive | Web RSS feed for this section

Bypass Cognos Forms

29 Jul

The City of Albuquerque has a Cognos report that allows you to see employee earnings. You can view the report on their transparency website or you can download a copy on the open data site. I do not want to go through a Cognos front page every time I need the data and I do not want to check when the last time they exported the xls or xml version to the open data site. I want to grab a fresh copy – I really want a service. Since they do not have one, we are stuck using Cognos every time to get the live data. Luckily, we can script it.

Cognos has several JavaScript functions cognosLaunch(), cognosLaunchWithTarget() and cognosLaunchInWindow(). These functions take different parameters and then call cognosLaunchArgArray(). Where do you get the JavaScipt library from? The City of Albuquerque – or anyone who has Cognos installed. The file is located at:

You can link to this file in your HTML

Now, you just need to know how to format the inputs properly. You can find all the information you need by running the report on the transparency site first. When the report finishes, view the source. You will see all the variables highlighted in the image below:

Cognos Report Source reveals all the parameters.

Cognos Report Source reveals all the parameters.

Now, format the function, passing the correct data. For cognosLaunch(), you will have the function below:

“/content/folder[@name=’Transparency’]/report[@name=’Transparency Report – Graded employees’]”,”ui.action”,”run”,”run.prompt”,

Put this in an HTML file in the <script> section and you will launch a window and download the CSV automatically. I have put a file on GitHub. There is another example which includes HTML and a JS file. The CABQ.js file formats the function for you. In this file, you could pass optional search parameters. I will leave that part up to you – I like grabbing all the data.

You can pas different outputFormats as well – CSV, HTML, PDF, singleXLS, XHTML, XLWA, and XML. Lastly, the City does not allow ajax calls from cross domain, so you may need to have the CORS extension installed in chrome. You can get it from

How would I use this in production? I think I would run a simple Python(cherrypy) or Go server that hosts the HTML and JS. Then I would write my application to call the site. I know where the download will go, so I could parse it when done. Then I could either return it to the server or do something with it on my machine.

Graph Database and Albuquerque Bus Stops: Neo4j with py2neo

15 Apr

I have been slightly obsessed with the question: “How do you define network service areas client-side on a map.” I know it needs a networked data set and something to do with the Djikstra algorithm (Yes, we could just use an ESRI REST service but there is not one available yet – I will ask the City). After looking at JavaScript implementations of NetworkX, I stumbled upon graph databases, most notably Neo4J.  A networked data set is a graph. Guess what, it has Djikstra built-in, so I must be on the right path. I installed it and added a fake social graph using py2neo. That allowed me to make sure I could do a few things:

  • Add a node
  • Add a relationship
  • add attributes

Now it was time to start with some real data.

My first test was to load Albuquerque Bus Stops for a single route. Here is what I have in my database.

Bus Stops for Route 766. No Relations added yet.

Bus Stops for Route 766. No Relations added yet.

The image above was generated by calling the City of Albuquerque REST Endpoint for bus stops, parsing the response, and putting it in to Neo4J. The image is a view from the DB Manager. The code to do this is below.

from py2neo import Graph
from py2neo import Node, Relationship
from py2neo import authenticate
import urllib2
import json




for x in reply[“features”]:

Notice there are no Relationships! This is crucial if we will ever walk the network. I have manually added on, seen in the image below.

San Mateo links to Louisianna.

San Mateo links to Louisianna.

The code for this is:



I need to think about how to automate the relationship creation based on stop order and direction (there are stops on both sides of the street). Then, I will need to figure out how to make a node have relationships to other routes. For example, many stops are connected to the 777 route and I do not want a separate node for each. I want one with a property showing routes.

Well, a start to say the least. It has been fun learning about graph databases and if GIS doesn’t interest you, you could map your social network and walk it.

Historic Bus Location Data

27 Mar

Last night I was trying to think of interesting uses for a Rasberry Pi. One thing I came up with was a data logger. But what to log? Then I thought about a previous post on the Albuquerque Realtime Bus Data. Hmmm. What if I wanted to show the bus locations using a time slider. What if I want to see if they ever deviated from their routes, or if they deviated from their schedules? I can’t really do any analysis without the historic data, and the City does not give that out currently. So I think I find a use – logging Albuquerque Bus Data.

I don’t have a Rasberry Pi, yet, so I wrote a python script on my desktop to test the logger.

I am not going to post the code because I don’t know the impact on the Albuquerque server. I will give a brief explanation. The City has KML files for each bus route. Each route has multiple buses. I grabbed a single route – 766, and parsed the results. I initially sent the results to a csv – as you can see in the data below this post. Writing to CSV is not too helpful when the data gets large (I am not going to say BIG). Once I knew it worked, I sent the data to a MongoDB that was spatially indexed. In the database, I can now:

Get the total records


Get all the Records

                        for x in collection.find():
                                      print x

Get a specific bus number

                       for x in collection.find({‘number’:’6903′}):
                                           print x

Or find near a lat,lng

                       for x in collection.find({“loc”:{“$near”:[35.10341,-106.56711]}}).limit(3):

With a database, multiple people can query it and perform operations on it. Lastly, if the data gets larger, Mongo can be split (sharding) across multiple machines to hold it all.

My MongoDB records look like:

{u’loc’: [35.08156, -106.6287], u’nextstop’: u’Central @ Cornell scheduled at 3:45 PM’, u’number’: u’6903′, u’time’: u’3:46:52 PM’,
u’_id’: ObjectId(‘5515cfd814cd2829e4c1b718′), u’speed’: u’20.5 MPH’}

Here is the results of my original run. I ran the script for 7 minutes and got the following results for Route 766.


Hard to see, but displays bus locations along route 766 over a 7 minute period.


6409,0.0 MPH,1:34:02 PM,Central @ San Mateo (Rapid) scheduled at 1:31 PM,-106.58642,35.07778
6904,1.9 MPH,1:34:03 PM,Central @ Edith scheduled at 1:31 PM,-106.64776,35.08401
6411,0.0 MPH,1:33:56 PM,CUTC Bay B scheduled at 1:39 PM,-106.72266,35.07726
6407,21.7 MPH,1:34:00 PM,Central @ 1st (across from A.T.C.) scheduled at 1:34 PM,-106.64317,35.08359
6410,35.4 MPH,1:33:53 PM,Central @ San Mateo (Rapid) scheduled at 1:36 PM,-106.58247,35.07749
6403,0.0 MPH,1:34:02 PM,Indian School @ Louisiana scheduled at 1:40 PM,-106.57089,35.10305
6903,29.8 MPH,1:33:56 PM,Central @ Atrisco scheduled at 1:36 PM,-106.69049,35.08443
6409,0.0 MPH,1:35:14 PM,Louisiana @ Central (Rapid) scheduled at 1:36 PM,-106.5852,35.07764
6904,1.9 MPH,1:35:14 PM,Central @ Edith scheduled at 1:31 PM,-106.6478,35.08413
6411,0.0 MPH,1:35:07 PM,Next stop is CUTC Bay B scheduled at 1:44 PM,-106.72525,35.07886
6407,0.0 MPH,1:35:11 PM,Copper @ 2nd scheduled at 1:34 PM,-106.64784,35.08417
6410,19.3 MPH,1:35:17 PM,Central @ Carlisle (Rapid) scheduled at 1:40 PM,-106.58734,35.07802
6403,0.0 MPH,1:35:14 PM,Indian School @ Louisiana scheduled at 1:40 PM,-106.57087,35.10306
6903,14.9 MPH,1:35:08 PM,Central @ Tingley (Rapid) scheduled at 1:38 PM,-106.68485,35.08576
6409,23.6 MPH,1:36:38 PM,Louisiana @ Central (Rapid) scheduled at 1:36 PM,-106.58084,35.07723
6904,18.0 MPH,1:35:53 PM,Central @ Edith scheduled at 1:31 PM,-106.647,35.08373
6411,0.0 MPH,1:36:31 PM,Next stop is CUTC Bay B scheduled at 1:44 PM,-106.72525,35.07885
6407,0.6 MPH,1:36:35 PM,Copper @ 5th scheduled at 1:35 PM,-106.64944,35.08541
6410,0.0 MPH,1:36:40 PM,Central @ Carlisle (Rapid) scheduled at 1:40 PM,-106.59512,35.07883
6403,0.0 MPH,1:36:42 PM,Indian School @ Louisiana scheduled at 1:40 PM,-106.57086,35.10306
6903,31.7 MPH,1:36:33 PM,Central @ Rio Grande (Rapid) scheduled at 1:40 PM,-106.67824,35.09252
6409,37.9 MPH,1:37:49 PM,Louisiana @ Central (Rapid) scheduled at 1:36 PM,-106.57203,35.07627
6904,0.6 MPH,1:37:55 PM,Central @ Cedar (Rapid) scheduled at 1:33 PM,-106.63771,35.08276
6411,0.0 MPH,1:37:55 PM,Central @ Coors scheduled at 1:47 PM,-106.72526,35.07885
6407,1.9 MPH,1:37:47 PM,Copper @ 5th scheduled at 1:35 PM,-106.6496,35.08487
6410,0.0 MPH,1:37:52 PM,Central @ Yale (UNM) scheduled at 1:44 PM,-106.60369,35.07979
6403,0.0 MPH,1:37:57 PM,Indian School @ Louisiana scheduled at 1:40 PM,-106.57087,35.10306
6903,0.0 MPH,1:37:56 PM,Central @ Rio Grande (Rapid) scheduled at 1:40 PM,-106.67159,35.09515
6409,0.0 MPH,1:39:14 PM,Louisiana @ Central (Rapid) scheduled at 1:36 PM,-106.56872,35.07595
6904,18.0 MPH,1:38:19 PM,Central @ Cedar (Rapid) scheduled at 1:33 PM,-106.63713,35.08265
6411,0.0 MPH,1:39:19 PM,Central @ Coors scheduled at 1:47 PM,-106.72527,35.07884
6407,3.1 MPH,1:39:10 PM,Central @ Rio Grande (Rapid) scheduled at 1:40 PM,-106.65295,35.086
6410,31.1 MPH,1:39:16 PM,Central @ Yale (UNM) scheduled at 1:44 PM,-106.61167,35.08084
6403,5.6 MPH,1:39:23 PM,Indian School @ Louisiana scheduled at 1:40 PM,-106.5707,35.10368
6903,23.0 MPH,1:39:20 PM,Gold @ 5th (Rapid) scheduled at 1:44 PM,-106.67011,35.09438
6409,26.1 MPH,1:40:37 PM,Louisiana @ Lomas scheduled at 1:38 PM,-106.56848,35.07723
6904,24.9 MPH,1:40:10 PM,Central @ Cedar (Rapid) scheduled at 1:33 PM,-106.63585,35.08255
6411,0.0 MPH,1:40:31 PM,Central @ Coors scheduled at 1:47 PM,-106.72526,35.07884
6407,23.0 MPH,1:40:36 PM,Central @ Rio Grande (Rapid) scheduled at 1:40 PM,-106.65675,35.0863
6410,34.2 MPH,1:40:40 PM,Central @ Yale (UNM) scheduled at 1:44 PM,-106.61567,35.0811
6403,0.0 MPH,1:40:42 PM,Indian School @ Louisiana scheduled at 1:40 PM,-106.56875,35.10221
6903,23.6 MPH,1:40:32 PM,Gold @ 5th (Rapid) scheduled at 1:44 PM,-106.66327,35.08892
6409,0.6 MPH,1:41:49 PM,Indian School @ Uptown Loop Road scheduled at 1:42 PM,-106.56849,35.08691
6904,24.9 MPH,1:41:55 PM,Central @ Cedar (Rapid) scheduled at 1:33 PM,-106.63585,35.08255
6411,0.0 MPH,1:41:55 PM,Central @ Coors scheduled at 1:47 PM,-106.72526,35.07884
6407,0.0 MPH,1:41:58 PM,Central @ Rio Grande (Rapid) scheduled at 1:40 PM,-106.65822,35.08648
6410,28.6 MPH,1:41:52 PM,Central @ Yale (UNM) scheduled at 1:44 PM,-106.6216,35.08113
6403,0.0 MPH,1:42:01 PM,Louisiana @ Lomas scheduled at 1:45 PM,-106.56779,35.10184
6903,23.0 MPH,1:41:55 PM,Gold @ 5th (Rapid) scheduled at 1:44 PM,-106.65819,35.08629
6409,41.0 MPH,1:43:13 PM,Indian School @ Uptown Loop Road scheduled at 1:42 PM,-106.56863,35.09282
6904,24.9 MPH,1:42:15 PM,Central @ Cedar (Rapid) scheduled at 1:33 PM,-106.6217,35.08093
6411,0.0 MPH,1:43:19 PM,Central @ Coors scheduled at 1:47 PM,-106.72528,35.07883
6407,9.9 MPH,1:43:10 PM,Central @ Rio Grande (Rapid) scheduled at 1:40 PM,-106.661,35.08784
6410,34.2 MPH,1:43:16 PM,Central @ Mulberry (Rapid) scheduled at 1:47 PM,-106.62524,35.08125
6403,16.8 MPH,1:43:22 PM,Louisiana @ Lomas scheduled at 1:45 PM,-106.56635,35.10156
6903,19.3 MPH,1:43:19 PM,Gold @ 5th (Rapid) scheduled at 1:44 PM,-106.6549,35.084

Aerial Imagery of Albuquerque and Surrounding Counties

10 Mar

When you need an aerial image, do you go to Google and take a screenshot? If you live in Albuquerque or the surrounding area, you no longer need to do that. The City of Albuquerque publishes aerial imagery of Albuquerque and the surrounding areas as open data. In this post, I will show you how to retrieve an image.

The Service

The Imagery Service provides a list of all the available years.

The historic data is a great addition.

The historic data is a great addition.

For this tutorial, click on Imagery/Aerials2014. At the bottom of the page, choose Export Image. You will see the full extent of the imagery and a form below.


To get an image, you need to enter the lower left and upper right corner coordinates of a bounding box in NAD83(HARN) / New Mexico Central (ftUS). I have no clue what this would be. You can change it to WGS84 (4326) as we have done in previous examples, but still, how would I know the coordinates without clicking on a map and checking the points? The form is pretty much useless to me. Our application will provide a better GUI – map based – to the form.

The Application

Our application will be a Leaflet.js map that allows the user to zoom to their desired location. The map window will be used to select what is exported from the imagery service.

The Logic

The application is a basic Leaflet.js map. We will add an onClick function that grabs the image.




The getImage() function will make an AJAX call to the service. It will pass the coordinates of the map window as parameters to get back an image in a popup. You can then right click and save image as…

function getImage(){

var bounds=b._southWest.lng+”,””,”+b._northEast.lng+”,”;

var url = “;;
var params=”f=json&bbox=”+bounds+“&bboxSR=4326&size=1028,1028&imageSR=4326”;

http=new XMLHttpRequest();“POST”, url, true);
http.setRequestHeader(“Content-type”, “application/x-www-form-urlencoded”);
http.onreadystatechange = function() {//Call a function when the state changes.
if(http.readyState == 4 && http.status == 200) {

result= JSON.parse(http.responseText);;




The above code starts by assigning the map bounds to a variable. We then create a bounds string in the format the service wants – lower left corner to top right corner. We pass the bounds string in to the parameters string and make the request.

The result contains an href property that is the URL to the image. We open a popup to the URL so the user can save the image.

Map on the left. Click. Image popup on the right.

Map on the left. Click. Image popup on the right.

Now the user can zoom to the desired extent and click the map to get back an image. Much easier than finding the coordinates and entering them in to the form.


Reverse Geocoding a Line

3 Mar

In Geocoding with ABQ Open Data, I showed you how to geocode an address and how to reverse geocode a point when the user clicks on the map. Geocoding is an operation performed on points or addresses, so what do we do with lines? In this post I will show you how I am geocoding a line.

The Need

I have an application that allows a user to select a location for where they would like to perform some construction work and need a permit. The application started by allowing the user to specify a point or address. As I met with the users of the application, it turned out that having line features was crucial. Work may be performed along a stretch of road and the approvers of the permit wanted to be able to find at least the starting address when looking at the data in a tabular format – mostly for things like how many permits are on Central Ave.

The Solution

Alert box showing the start and end addresses of a line

Alert box showing the start and end addresses of a line

The solution uses the same reverse geocoding process as my previous post, but parses a line feature in to points. I am using the leaflet.draw plugin to allow the user to draw the line on a map. The draw plugin has a draw:created event that searches to see the type of object drawn. My code runs in the if (type == ‘polyline’) block.  The code below converts the layer drawn to GeoJSON and then the GeoJSON coordinates to a string split on the comma. Using the string at index 0 and 1, I have the starting point of the line. Using the index of length-1 and length-2 I can grab the last point. I use length because the user can draw a line segment with an unlimited number of points so I will not know where the line ends and do not want to assume a two point line. when I have the points, I call reverseGeocode().

var tojson=layer.toGeoJSON();
var x=String(tojson.geometry.coordinates).split(“,”);

//start point
var lt=parseFloat(x[1]);
var ln=parseFloat(x[0]);

var ltend=parseFloat(x[x.length-1]);
var lnend=parseFloat(x[x.length-2]);

The reverseGeocode function just makes an AJAX call to the ESRI REST API service for my geocoder passing the point and parsing the response.

function reverseGeocodeLineFrom(a,b){

var urlgeocode=”http://servername/ArcGIS/rest/services/wgs84locator/GeocodeServer/reverseGeocode&#8221;;

var geocodeparams = “location=”+a+”,”+b+”&distance=1000&f=json”;
var http;
http=new XMLHttpRequest();“POST”, urlgeocode, true);
http.setRequestHeader(“Content-type”, “application/x-www-form-urlencoded”);
http.onreadystatechange = function() {//Call a function when the state changes.
if(http.readyState == 4 && http.status == 200) {


Now I have a street address for the start and end of my line.

Building Games with Leaflet, Turf and Albuquerque Open Data

24 Feb

I remember playing Risk as a kid. It was my first time playing a game that involved a map. When I got older, I loved playing games of world domination and strategy. As I build web maps, I often think, why couldn’t I make a game using Leaflet.js? In this post, I will describe how a game could be built with some sample code.

The Simple Game

There is no point to this game. It is an attempt to

  1. create pieces
  2. load data
  3. move the pieces with rules and
  4. accomplish a goal.

Winning the game is shown in the image below. You can test the game on JsBin.

I won the game by moving my piece in to a park.

I won the game by moving my piece in to a park.

The Game Components

The game board is made of a Leaflet.js map and Albuquerque Parks Data. The park data is loaded as a turf.polygon and pushed in to an array.

var url = “*&where=1=1&#8221;;

http=new XMLHttpRequest();“GET”, url, true);
http.setRequestHeader(“Content-type”, “application/x-www-form-urlencoded”);
http.onreadystatechange = function() {//Call a function when the state changes.
if(http.readyState == 4 && http.status == 200) {
var result= JSON.parse(http.responseText);
var pts=result.features[x].geometry.rings;


Now that I have added the base layer, I want to create game pieces. It would be nice to also use Open Data for the players, but for now I will allow the user to click the map to drop a marker.


var point=L.marker(e.latlng,{draggable:true}).addTo(map);


When the user clicks, a marker is added to the map and is set draggable. The game will allow the user to move the marker by dragging and attempt to land on a park. I want rules. The first rule I will create will only allow the user to drag the marker 30 pixels. This creates a problem. 30 pixels is a different distance based on the view level of the map. To fix this, I prevent the map from zooming so I know the 30 pixel fixed distance.

var map =‘map’, {center: [35.10418, -106.62987], zoom:15, touchZoom:false,scrollWheelZoom:false,doubleClickZoom:false,boxZoom:false

Now the user can pan but not zoom. If the user drags the marker too far, I need to set the marker to its original position. When the user starts dragging, we will get the lat,lng of it.


When the user finishes dragging the marker, we will check the distance. If it is greater than 30, we will alert them to the error and reset the marker position.

alert(“You cannot move that far on a single turn”);

Otherwise, we will allow the marker to move and test to see if the marker is inside of a park polygon. If it is, YAY, you win! else, keep going.


for(k=0;k<polygons.length;k++){if(turf.inside(point.toGeoJSON(),polygons[k])){alert(“you made it to a park”);}}



This is the basic foundations of a game. To make it better, you would need to have turns and allow a computer controlled player to intervene. Maybe a zombie chase? Or you could network the game using websockets and allow users to use their real world position to play against each other(PDF) in a capture the flag or race to points type game.

Using Turf.js to find Carbon Monoxide Levels in Albquerque

12 Feb

I have posted several times on Turf.js and as I explore the possibilities, I found something that peaked my interested and had to find a way to integrate it with Albuquerque Open Data. In this post, I will show you how to click a map and get back the Carbon Monoxide levels for Albuquerque at the location.

The Goal

The goal of this application is to find the carbon monoxide levels at a given point in Albuquerque. To accomplish this, we need to first map the Air Pollution Sources data from the City of Albuquerque and then find a way to interpolate the values between each location. The finished application will look like the image below.

TIN created from CO levels

TIN created from CO levels

How to Accomplish the Goal

Turf has a function planepoint that takes a triangle with values at each corner and can interpolate the value at any point on the plane. This is cool. But where do I get a triangle with some values at each corner? Well, the best way to get a bunch of triangles with values it to create a TIN from a series of points. Now I just needed some data. Albuquerque publishes a data set called Air Pollution Sources that has 1000 records. This is a great start. Let me issue a warning before continuing:

The level of CO at any point may or may not be accurate. The application is a proof of concept showing that for a series of points, values can be interpolated between them. This assumes that the values actually change over a distance and do not exist solely at said location.

Now that I know you and the City are not going to sue me, let’s continue.

Using Triangulated Irregular Networks and Planepoint

The logic of the application is to go from points, to a TIN, to a triangle, then add a point and get back a value. Getting the points on the map is easy. Make an AJAX call to the REST endpoint and add a marker.

var params=”where=FAC_NAME not in (‘COLORS ON PARADE’)&f=json&outSR=4326&outFields=*”; var url = “;; console.log(params); http=new XMLHttpRequest();“POST”, url, true); http.setRequestHeader(“Content-type”, “application/x-www-form-urlencoded”); http.onreadystatechange = function() {//Call a function when the state changes. if(http.readyState == 4 && http.status == 200) { var result= JSON.parse(http.responseText); for(x=0;x<result.features.length;x++){ L.marker([result.features[x].geometry.y,result.features[x].geometry.x]).bindPopup(“<h3>”+result.features[x].attributes.FAC_NAME+”</h3>”).addTo(map); } }} http.send(params);

Notice that my parameter has a where clause removing any record named “COLORS ON PARADE.” After inspecting the data, I noticed that the coordinates for these location were bad – some where 0 and crashed the application. Otherwise, the code above is the same block I have used in several other posts on mapping with Leaflet.js and the ESRI REST API. Having the points on the map accomplishes step 1. Now we can create a TIN. To make the TIN, I need the points to be in a Feature Collection. I will setup an empty feature collection.

var features = { “type”: “FeatureCollection”, “features”: [] };

Then, in the code where we added the markers previously, we will add a turf.point feature to the feature collection instead of adding a marker.

for(x=0;x<result.features.length;x++){ features.features.push(turf.point([result.features[x].geometry.x,result.features[x].geometry.y],{“z”:result.features[x].attributes.CO_TPY})); }

A turf.point allows attributes – it’s geoJSON – so we will pass the Z value needed to create the TIN. The Z value will be the CO_TPY reading of the data set. Now create the TIN and add it to the map.

var tin = turf.tin(features, ‘z’)

var t = L.geoJson(tin).addTo(map);

The user needs to be able to click on the TIN and get a value from an individual plane (triangle). Leaflet allows us to setup an “onclick” event so this is where our code will go.

t.on(“click”,function(e){ …..CODE HERE… }

To get to a specific plane(triangle) in the TIN, we need to take the user click and perform a point in polygon operation. So let’s get the users click location and convert it to GeoJSON so we can use it in Turf.js. a=L.marker(e.latlng); b=a.toGeoJSON(); Now we can use turf.inside to find the plane(triangle) the user clicked in. We will loop through all the planes(triangles) and perform turf.inside until we find the one, then we will call it theT.

for (var i = 0; i<tin.features.length;i++){ if(turf.inside(b, tin.features[i])) {theT = tin.features[i];} }

We now have a point and a plane. We can grab the value of the point using turf.planepoint and attach it to a popup.

var zValue = turf.planepoint(b,theT); a.bindPopup(“<h3>”+zValue+”</h3>”).addTo(map).openPopup();

We are done. We

  1. Loaded a series of points
  2. Created a TIN
  3. Used point in polygon to grab a plane
  4. Used planepoint to get the value

And we did it all on the client side! Below is an image of the map with all the locations added. points