Notes on getting CartoDB VM working in OSX VirtualBox

I've been wanting to play some more with CartoDB but I've been traveling and my Internet connection has been provided courtesy of whatever cell tower is close by. A VMWare Ubuntu virtual machine is available at aux 2 centimes. Great if your operating system is Windows or linux because VMWare provides a free player to run the VM. On OSX, VMware Fusion can run the VM but it's not free.

The free alternative is Virtual Box, but it requires converting the VMWare vmdk file to an the open ovf format. Before converting the vmdk file, you will need to use your favorite OSX rar archive tool (MacPar, UnRarX) to reassemble the CartoDB vm which is split into three rar files. 

Download the ovftool from VMWare and install. Before converting the vmdk to an ovf file you will need to edit the vmx file so that the path to the vmdk is not hardcoded.

Convert the vmdk to an ova file.

Import the ova into VirtualBox and accept the default settings, they can be changed later.

The next step is to configure the VirtualBox network so that CartoDB is accessible to your host machine. To do this setup the Host-only Adapter. The Host-only Adapter creates a private network that between the host machine and guest machines. 

The vboxnet0 Host-only Adapter is configured from the VirtualBox Preferences menu.

Click on the 'Edit' or screwdriver icon to display the configuration menu. Note that the adapter's address is 192.168.56.1, which is the IP address that the guest machine accesses the host.

Now that we know the adapter's address, we can configure the network interface of the guest to have a static IP that is addressable from the host machine. Edit the /etc/network/interfaces file as root to set the static address. It should look like this:


Reboot the guest to start the network with the assigned static IP.

 
The CartoDB virtual machine is configured with two accounts, a devuser account and an admin account. To access these accounts, two URLs have been configured: http://devuser.localhost.lan (user/password: devuser/devuser) and http://admin.localhost.lan (user/password: admin/admin). 

To access these accounts configure the /private/etc/hosts file on the host side (OSX) by adding these entries:


After updating the hosts file, we can access our local instance of CartoDB through a browser. Note: add 'http://' to the URL so that the browser knows you are looking for the local CartoDB site. The devuser map looks like this:

Happy mapping!

Etymology

A friend in grad school used to go around the office and exhort us to 'go eat' lunch together. Eventually this just became "gweet."


Similarly, an engineer I worked with would build applications using java jars built by another part of our company. I would ask him how his project was going after the jar update and he would invariably say "It's broke." Over time, this elided into 'sproke.


*True story: One Christmas, I bought everyone a computer bag with NPE embroidered on it because everything was 'sproke.

Visualizing data from a PDF

In my previous post, I scraped the data from a PDF and wrote it to a CSV file. I uploaded the data to CartoDB to make a quick map using the "Share This Map" function on the CartoDB site.



I received a number of comments on the map, mostly about making it more legible by separating out the years and providing more information about each incident. In the spirit of Brian Timoney's post on how web maps are actually used, I decided to keep the map as simple as possible.


After reading the API documentation and looking at the examples, I used the Data Interaction example as the starting point for my map. One of the comments I received was to separate the incidents by year. The interesting part of the example is that it shows how to dynamically change what is presented on a map by changing the values in a SQL query. That's very powerful because you have the data handling and geoprocessing capabilities of PostgreSQL and PostGIS available in your map client. Here's the map with a control to show incidents by year. (The blog form squeezes the map, the see the full map here.)



The script for the map is below.


One of the nice things about the map is that it doesn't use external mapping client libraries. It's simple and shows the user where the incidents occurred. However, I would like to show more information about each incident through a popup. CartoDB plays well with leaflet, modestmaps.js, and wax, and it's easy to extend the basic map with these libraries. In the following map, I used leaflet and the CartoDB popup script to make a popup containing the description of each incident when a marker is clicked. (As with the other map, the full size map can be viewed here.)



The script for the clickable map is below:


The script essentially the same as the previous script but it uses leaflet.js to add a base map from MapBox and the leaflet-carto.js to add the Major Shootings layer from CartoDB. Switching between years is handled differently from the previous example. The resetLayer function uses the setOptions function to update the query for each year. Note that I cheated a little and put the operator as part of the value, e.g. '= 2005". It was the most direct solution to handling the 'all' case in the SQL statement, which required that query for all the records as part of a SQL WHERE clause. I admit that putting the number of incidents on the buttons is cheesy; and if I had more time, I would put a label that would change on top of the map that read "x incidents in xxxx" when a different year is selected.. 


I like CartoDB because it's flexible and agnostic with regards to client mapping libraries. Building a simple mapHowever, what I find exciting is that CartoDB puts an API in front of PostgreSQL and PostGIS. This opens up all sorts of possibilities and removes the need to preprocess or reformat data to create a visualization.


The project file is available here.