Visualizing SSLC results over seven years.

I spent most of the last one month building a dashboard for the SSLC results at the Karnataka Learning Partnership. We released it in beta yesterday and here’s the blog post I wrote detailing how we went about it.

Patterns in examination results are something which we are always interested at the Karnataka Learning Partnership. After the design jam in June 2012, where we tried to understand the SSLC data – it’s content and structure, and visualized performance of Government and Private schools in contrast to each other, we decided to take a step deep and find patterns from the past seven years. Results of this effort is what you find here, in beta.

The Karnataka Secondary Education Examination Board shared the data over the last seven years in a combination of several Microsoft Access Database. It came with very little meta data and Megha did all the hard work of making sense of this and pulled it into a PostgreSQL database. Inconsistencies are everywhere, and the quality always depends on how you handle each exception in isolation. Among other things, we decided to look at three aspects of the data to begin with – performance of Government and Private schools, performance in Mathematics, Kannada and English, and performance of each gender. All three across seven years (from 2004-2005 to 2010-2011) for each district in Karnataka.

One of the important data wrangling that we did this time was to aggregate this data at the district level. The raw data came at the educational district level and unfortunately, we did not have geographic boundary shapes for this classification. What we have instead is the geographic boundary at the political level. We massaged the shapefiles, geocoded the data and converted it to GeoJSON in QGIS. We wrote a bunch of Python scripts to perform the aggregation and generate JSON (JavaScript Object Notation) required for the visualization. Every bit of code that we wrote for this project is on Github.

A dashboard of this sort is something which we have never attempted, and honestly it took a while for us to get around it. I had tried D3.js sometime last year and found it to be amazing. D3 is Data Driven Documents, a brilliant JavaScript library to make infographics on the web driven completely by the data. What makes D3.js awesome for me is that everything is an SVG (Scalable Vector Graphic), and there are barely any limits to the representation and interaction that you can bring into the browser with it. I’ve had good experiences with Twitter’s Bootstrap to quickly design and be consistent on the page layout and aesthetics. There are some issues while you work with D3.js and Bootstrap together, especially the way bootstrap manages events. The best way is to trust D3.js and use Bootstrap features of scaffolding and layout.

We found few interesting facts from this exercise. As you may guess, private schools perform better than government schools consistently. Western districts like Udupi, Uttar Kannada and Belgaum performs better than rest of the state. North Karnataka, especially Bidar performs terribly across the last seven years. Something which we are very curious to know why. Bangalore Rural performs better than Bangalore Urban. Government schools does much better and comparable to private schools in Bangalore Rural than Bangalore Urban. Private schools grab the cap in all the three subjects across the last seven years. Girls performs way better than boys in private schools consistently across seven years in every district. Boys does a better job in Bangalore Urban while girls dominate in Bangalore Rural.

This research will continue while we churn few more aspects from the data as the dashboard gets out of beta.


Beyond just a ‘Map’

This morning, I came across another dose of my Apple maps debate. We’ve been continuously going back and forth about how bad the data on iOS6 maps are and that it’s a shame that Apple is doing this to their customers. To be fair, making a map, a base layer, is not a tiny task. Nor it is something that a team of 50 can pull together in 3 months. It takes rigorous learning and efficient processes to build a map which ‘delivers’ what people want. It’s not about building a map that looks good on the phone. It’s about building the geospatial data repository where you find what you are looking for.

The whole new ‘Social Map’ phenomenon is making me think that we are attempting the Borgesian map. In 1946, Jorge Luis Borges published a short story on how the map of an empire got too accurate and exact enough that anything smaller than the representation equivalent to the size of the empire will be considered incorrect – ‘a scale of a mile to the mile’.

May be we are not attempting it to be of that size, which quite clearly is impossible, but where we are heading is a bit scary – the map where you will find anything you want – you will see where your friends are – you will see what you would want to do next. Trust me, I’ve seen my friends in trouble after they started using Google Latitude.

The amount of detail that people want on the map makes me cringe at times. I leave it here for everyone else to think.

And yes, the Curiosity Rover checked in from Mars!

MapQuest tiles through Leaflet.js

Cross posted from

Leaflet.js by default supports Cloudmade’s awesome map tiles. The Open MapQuest project also provides beautiful tiles, which are now used at To use these tiles all you have to do is to change the map preamble as follows.

The Cloudmade maps layer looks like this.

Change the preamble to.

Geohackers in the news!

Finally, we have some attention from the media! Featured on the third page of the New Indian Express.
Geohackers shows the way in Open Mapping
About two years ago, in an open map party held in Hyderabad, some geeks sowed the idea of forming an open technology mapping consultancy team. Now, the idea has grown into a full-fledged, non-profit, open technology mapping consultancy team called Geohackers.

The team, which consists of seven active members and several contributors, gives consultancy services on open mapping for different organisations and projects, free of cost. The team is also contributing to the Open Street Map, a project to make free and open maps.

“With the belief in the ideology that information has to be free, we undertake consultancy works and training for students on cartography,” said Sajjad Anwar, a coordinator of the open map team and a student of the MES College of Engineering, Kozhikode. The contributors include several technology freelancers and techies, working in different parts of the world, he said.

Explaining that the Geohackers might be the first consultancy service on open mapping in the country, he said, “We thought about starting an open mapping consultancy team to impart the ideology of open mapping to the people, as the need of such a consultancy is high in the country.”

The team is involved in several open mapping projects such as Assistance for Maps for Making a Social Change project and Heritage project.

“The first project was sponsored by two NGOs- Centre for Internet and Society and Tactical Tech and we have conducted several workshops and training classes for the social workers on how to use open maps. The training and workshops were held in New Delhi and Ahmedabad,” he explained.

“Heritage is a unique project to help tourists. People can download maps from the website for free,” he said.

At present, the mapping of heritage sites in Ahmedabad, Kozhikode, Kochi and Thiruvananthapuram has been completed.

“The project is aimed to cover the whole India. More heritage sites will be added to the website shortly,” he said.

For mapping purpose, we use GPS devices and free satellite images. The open mapping foundation has contributed two such devices for the purpose, he said.

Arky’s blog about the news is here

Setting up Mapnik for rendering tiles in your language.

Mapnik is the default renderer on the OpenStreetMap main site. We can setup our own OpenStreetMap server for rendering the tiles either online or offline. This note would help you to setup an offline one (online if you do on your server) with special eye on font setup for localized tiles.

To render the tiles using Mapnik, you need to create a PostgreSQL database out of the OSM data. Then install Mapnik depending on the Operating System you run. These primary steps are very well covered in the following pointers.

  1. Setting up PostgreSQL database out of the OSM data. Read more.
  2. Build your own OpenStreetMap server – Richard Weait. Read more.
    • For Ubuntu Lucid Lynx read here.
  3. Using Mapnik for rendering the tiles – Richard Weait. Read more.

Once you are done with the above steps, we might think of tweaking the Mapnik rules to render the tiles in a local language. For this all we need to do is point Mapnik to the right font. You can quickly follow the steps briefed by Richard, here.

You can run

>>> from mapnik import *
>>> for face in FontEngine.face_names(): print face
… [Enter]

DejaVu Sans Bold
DejaVu Sans Bold Oblique
DejaVu Sans Book
DejaVu Sans Condensed
DejaVu Sans Condensed Bold
DejaVu Sans Condensed Bold Oblique
DejaVu Sans Condensed Oblique
DejaVu Sans ExtraLight
DejaVu Sans Mono Bold
DejaVu Sans Mono Oblique


>>> Ctrl-d

to see what fonts are currently being recognized by Mapnik. The second task is to install the local language unicode font to Mapnik’s default font directory. If you have already installed Mapnik, you can run strace -ff, and search for font to see which directory is used by Mapnik. In my case it was the default directory at /usr/share/fonts/

Next, you need to copy the required fonts to the above directory. You might run the python code snippet again to see whether Mapnik recognizes the fonts. For example, in my case, it looks like this.

DejaVu Sans Bold

DejaVu Sans Bold Oblique

DejaVu Sans Book

DejaVu Sans Condensed

DejaVu Sans Condensed Bold

DejaVu Sans Condensed Bold Oblique

DejaVu Sans Condensed Oblique

DejaVu Sans ExtraLight

Rachana Regular —> new one
Note the name of the font that appears in the above list. This name would be used in the Mapnik rule file.
Now, we’ll edit the Mapnik custom rule file, as described below.
You can find this in ~/mapnik/<template name>.xml. Open the file in an editor of your choice. The file begins with something like this.

<FontSet name=”bold-fonts”>
<Font face_name=”DejaVu Sans Bold”></Font>
<FontSet name=”book-fonts”>
<Font face_name=”DejaVu Sans Book”></Font>
<FontSet name=”oblique-fonts”>
<Font face_name=”DejaVu Sans Oblique”></Font>

Change the face_name to the font name we just copied to the fonts directory.  In my case, it would look like this.
<FontSet name=”bold-fonts”>
<Font face_name=”Rachana Regular”></Font>
<FontSet name=”book-fonts”>
<Font face_name=”Rachana Regular”></Font>
<FontSet name=”oblique-fonts”>
<Font face_name=”Rachana Regular”></Font>
Mapnik rule file defines fonts for each of the possible tags which appear in the map data. You need change that in order to apply the special fonts to specific tags.

<TextSymbolizer name=”name” fontset_name=”book-fonts” size=”11″ fill=”rgb(0,0,0)” halo_radius=”1″></TextSymbolizer>

You can edit the lines which are like as shown above. Change the fontset_name, to the one you want. And run the renderer! You are done!
I have two images to egg you on!
Before installing font After installing fonts
DejaVu Sans Bold
DejaVu Sans Bold Oblique
DejaVu Sans Book
DejaVu Sans Condensed
DejaVu Sans Condensed Bold
DejaVu Sans Condensed Bold Oblique
DejaVu Sans Condensed Oblique

DejaVu Sans ExtraLigh

Thanks to the #osm channel and Richard Weait for helping out to figure the changes.

OpenStreetMap and GIS: Bridging the gap.

Many a times I have heard my GIS friends say that OpenStreetMaps are ‘just’ street maps and lack quality and ability to be used in efficient GIS applications. But this is not always true. I would like to make a comment that, GIS applications they mention still uses any kind of map data which are essentially gathered and processed through GPS or Satellite Images. OpenStreetMap is the culmination all of those sources.  People around the planet are contributing to OSM through satellite images, raw GPS data and geo-referenced cadastral maps. OpenStreetMap is thus a wonderful resource that can be used into GIS applications and more over it is free!

Now, let us see how this could be done at the first level. Probably most of the leading GIS softwares uses PostgreSQL database with PostGIS extension to process geographic information. This note would help you to download the OSM data of the region of your interest and create a PostgreSQL database with duly applied postgis extensions. The database would fit into any GIS requirement as you expect.

A word about OSM layering of data. In OSM there are no layers in the traditional GIS sense. All features are in one big coherent
database. But still, when you are creating a database out of it, you are actually layering the entire data. More information on layering is available here.

Lets us begin with collecting the OSM data. There are many ways for you to download the OSM data sets. I prefer those from CloudMade. There are other sources like GeoFabrik. And of course the direct data sets from The data might be in the default OSM xml style sheet which is called the .osm file.

Now that we have the data, we’ll use some tools to set the database up. Please ensure that you have PostgreSQL, PostGIS including the contrib packages. Osm2pgsql is the important tool which we would be using to straight away convert the OSM data set into a PostgreSQL database.  You can install osm2pgsql from the source or packages listed here.

Next, we need to setup a database to load the OSM data. This could be done in the following steps.

  • $ sudo -u postgres createuser <username>
  • $ sudo -u postgres createdb -E UTF8 -O <username> <dbname>
  • $ sudo -u postgres createlang plpgsql <dbname>

Now add the postgis extensions. Note the location of the lwpostgis.sql file may vary.

  • $ sudo -u postgres psql -d <dbname> -f /usr/share/postgresql-8.3-postgis/lwpostgis.sql

Next give access to the user to update the postgis metatables.

  • $ sudo -u postgres psql -d <dbname> -c “ALTER TABLE geometry_columns OWNER TO <username>”
  • $ sudo -u postgres psql -d <dbname> -c “ALTER TABLE spatial_ref_sys  OWNER TO <username>”

Apply  900913 diff which is not normally included with postgis, you can get it from the source of osm2pgsql. To add it you should run:

  • $ sudo -u postgres psql -d <dbname> -f 900913.sql

Run, osm2pgsql, this should load the OSM data into the database we just created.

  • $ osm2pgsql <osm_file> -d <database_name> -U <user_name>

Now you can import the database into any GIS software.

For the completion of this document, I would illustrate usage of the database with qGIS. Start qGIS with a new project.

  • Click on the “Add a PostGIS layer” button on the toolbar.
  • In the “Add PostGIS Tables” pop up, click “new”.
Click to enlarge
  • Fill in the fields accordingly and click “OK”.
  • Connect to the database and select the required tables to import.
Click to enlarge
  • Click Add.
Click to enlarge

Now we have the OSM data in the GIS software, just like any other geographic data source.

Hope this quick guide helps! Please let me know if you have any difficulties.