Welcome

Welcome to the New York City Traffic Collision Mapping Tutorial using Vertica Database and Carto DB.

Please follow this tutorial so that you can fully understand how this project works.

Cover

Preparation

In this tutorial we’re using several existing tools. Please prepare yourself with the following software.

  1. Eclipse for Java EE Developers
  2. Tomcat 7.0
  3. VMWare Workstation / Fusion / Player, with HP Vertica virtual machine configured
  4. Java 7 and above

In this tutorial, I assume you have experience of programming Java, JavaScript, HTML, CSS and a bit knowledge of SQL. Actually we’re using VSQL in Vertica and PostgreSQL in Carto DB, but we’re going to use very small subsets of them and they are in the very basic part of the SQL language.

Also, please prepare yourself with a cartodb.com account. We’re going to use cartodb to display the records points on a map. If you prefer to use my API Key that’s cool, but if you want to truely understand how this project works, please get yourself in the cartodb website and take a quick look.

Deployment

Please visit this github repository for the project source code. You can select Download ZIP to download the zip file.

The deployment guide is on the github repository README.md file. Please follow that step by step.

Or, you can take a look at this playlist for the video deployment guide.

Tutorial

We’ll go through the whole project from the UI to the implementation of the data layer, from the top down.

UI

As we can see from the source code in WebContent/index.html, the UI of this project can be divided into several parts.

In the head tag, there are some links linking to library CSS files and also screen.css, which is our styling sheet.

In the body tag, there first comes the div.page-loading-mask, which is used to display a mask on top of the web page, to prevent the user from performing operations on the web page while the data is loading.

Then, there appears a nav bar, containing a title of the web page, and on the right side there are two buttons, which are used to toggle display of different part of the webpage.

Then the div.slider, which is the date slider, and the filter-form, which is used to filter the data and display it on the map.

After the filter-form, we have a confirmation dialog. This confirmation dialog is displayed when the size of collision records fetched from our server is too big (larger than 10,000), and loading these data into the map would cost a very long time. So we need a confirmation dialog to ask the user whether or not he / she really needs to load these records on the map.

Then in the end of the body, we have a canvas inside a div.chart-canvas-wrapper. The canvas is used to display the bar chart using chart.js.

This is all the UI components we introduced in the project. All of the users operations are performed on them, and everything in this project is related with them.

Next, we’ll look into WebContent/js/script.js for more information on the UI, and the use case of the project.

The Map

First let’s see how the map is loaded in the webpage.

Tooltips

We use cartodb as the map provider. The reason that we don’t use Google Map is that, Google Map limits the quota usage on each API we have. This is a tutorial project that contains a very large amount of records that needs to be shown on the map, and we can’t use a map whose quota is very limited.

On the other hand, cartodb provides a very good set of API, including JavaScript API, which allows the caller manipulating the map on the web page programatically, and the SQL API, which allows the caller manipulating geographic data using SQL, including data insertion and data deletion. These operations are very valuable in this tutorial project, hence we select this GIS provider in this project. Also, cartodb is open sourced. You can find here on github.

Please go through the deployment guide, video or text, especially the part containing cartodb configuration, before reading the following paragraphs

We use both JavaScript API and SQL API provided by cartodb.

Let’s dig into script.js. In the document ready function $(function(){}), we first create the visualization of the map, using the cartodb.createVis function. This function accepts two parameters, the first is the map div id, and the second one is the visulization JSON file url that we copied from cartodb website.

	var cartodbMapUrl = 'http://albusshin.cartodb.com/api/v2/viz/9e4b7ba6-cb15-11e4-b57c-0e853d047bba/viz.json';
	var vis = cartodb.createVis('map', cartodbMapUrl);

That’s it! The map is displayed on the web page just like that. So simple, isn’t it?

The Filter Form

This tutorial is all about getting the collision records data from Vertica, and display them on a map. The filter form provides a filter button which does just that, and several additional conditions.

Filter Form

These conditions are the constrains which will be sent to the database, when getting records from there.

How do we query against the database and display the records we get from there to the map, is the ultimate question. Here, we have a piece of code from script.js file.

Let’s take a look in the first few lines of code in the btnFilter.click handler.

    var dateValues = dateSlider.dateRangeSlider("values"),
		minDate = dateValues.min,
		maxDate = dateValues.max;
	
	//Build loadUrl to get the records from our Servlet
	//With the options the user specified in the filter form and the date slider
	var loadUrl =  "ShowRecordsOnMap?start=" + minDate.yyyymmdd() + "&end=" + maxDate.yyyymmdd();
		loadUrl += "&withDeaths=" + $("#withdeaths").is(":checked");
		loadUrl += "&withInjuries=" + $("#withinjuries").is(":checked");
		loadUrl += "&withPedestriansInvolved=" + $("#withpedestriansinvolved").is(":checked");
		loadUrl += "&withCyclistsInvolved=" + $("#withcyclistsinvolved").is(":checked");
		loadUrl += "&withMotoristsInvolved=" + $("#withmotoristsinvolved").is(":checked");
		
	var vehicleTypes = $("select#vehicleTypes").val();
	if (vehicleTypes !== null) loadUrl += "&vehicleTypes=" + vehicleTypes;
	var contributingFactors = $("select#contributingFactors").val();
	if (contributingFactors !== null) loadUrl += "&contributingFactors=" + contributingFactors;
	loadUrl += "&vehiclesInvolved=" + $("select#vehiclesInvolved").val();

We use the code above to construct the URL that’ll be called using $.ajax. The URL is a java Servlet, which is named ShowRecordsOnMap, and we’ll introduce this Servlet below near the end in this tutorial. Right now you just need to know that this ShowRecordsOnMap Servlet gets the records from the database, and insert them to cartodb, thus resulting a change on the map. Then, the Servlet writes a JSON Object as a string on the PrintWriter, to provide the metadata of this operation, such as the records amount.

I’m not going to post the rest of the btnFilter.click handler here. Please read the source file on your machine.

We then declare several different handlers such as ajaxCompleteHandler, ajaxSuccessHandler and two more. What we need to notice is the ajaxSuccessHandler. This handler performs the operations when we get a success load of data from the Servlet. It first invalidates the map layers, to force a refresh of the map. Then, it shows a notification indicating how many records are shown. Then it draws a bar chart using the canvas we mentioned above.

The bar chart uses the data returned as JSON in the ShowRecordsOnMap Servlet, displaying amount of deaths and injuries.

Confirmation Dialog

In the filter form’s btnFilter.click handler function, there is one thing we missed in the last section. That is the confirmation dialog.

As we said above, the confirmation dialog is designed to prevent loading too many records into the map without asking the user. In the btnFilter.click function’s first ajax call, we have the check of the returned JSON in its success handler.

    $.ajax({
        url: loadUrl
    }).success(function (msg) {
        var retJson = $.parseJSON(msg);
        if (retJson.exceeded) {
            var confirmationDialog = $("div.confirmation-dialog");
            confirmationDialog.html("Are you sure you want to load " + retJson.amount + " records?<br><br>" +
                "This might take a <em>VERY LONG</em> time.<br><br>" +
                "Maybe you want to add more filters or shorten the selected time interval.");
            confirmationDialog.dialog({ //See http://jqueryui.com/dialog/#modal-confirmation for the jQuery UI dialog options
                resizable: false,
                height: confirmationDialogHeight,
                modal: true,
                buttons: {
                    "Yes, Load Them Any Way": function () {
                        $(this).dialog("close");
                        begin = getTime();
                        loadUrl += "&confirmed=true"; // Add the `confirmed` parameter to the request,
                        ajaxStartHandler(); //start making ajax call, mask the page to prevent any changes,
                        $.ajax({ //and make the confirmed request again.
                            url: loadUrl
                        }).success(function (msg2) {
                            var retJson2 = $.parseJSON(msg2);
                            ajaxSuccessHandler(retJson2);
                        })
                            .fail(ajaxFailHandler)
                            .complete(ajaxCompleteHandler);
                    },
                    Cancel: function () {
                        $(this).dialog("close");
                    }
                }
            });
        } else {
            ajaxSuccessHandler(retJson); //If there aren't too many records, we reload the map after the success of this filtering request.
        }
    }).fail(ajaxFailHandler)
    .complete(ajaxCompleteHandler);

This is the whole block of code of this ajax call.

In the success handler of the first ajax call, we first detect if the returned JSON’s exceeded attribute is true. If so, we have too many records to load onto the map. Then we show a confirmation dialog to the user. If the user want to load these records anyway, we then make another ajax call against the ShowRecordsOnMap Servlet, only this time with the additional url parameter confirmed=true, to tell the Servlet that the user really does want to load these records.

Bar Chart

The bar chart is very easy to understand. Please see the documentation of chartjs here, and read the piece of code in script.js where we build the bar chart. It’s in ajaxSuccessHandler in btnFilter.click event handler.

Bar Chart

Data Layer

After understanding the UI, we need to know how the data layer works. We have a DBHelper java class, which provides several helpers to get data from the Vertica database.

Model class

To get the collision records, we must first have a CollisionRecord model. We have this class in haven.mappingtutorial.model package.

This class provides getters and setters for each field, so it’s an open model.

DB

There are several considerations while we get down to the data layer.

The first is efficiency, thus we cannot open too many database connections at the same time. Luckily, in this tutorial project, we don’t need to open many connections at once.

The second is security. We don’t want any SQL Injection vulnerabilities in our project. So we need to use PreparedStatements wherever possible, and double check the place where there might be a vulnerability.

Opening a connection

Opening a connection with Vertica DB is simple. As long as we have the Vertica jdbc driver, we’re good to go. (The Vertica jdbc driver is provided, WebContent/WEB-INF/lib/vertica_4.1.13_jdk_5.jar)

First we test if the vertica driver is loaded, using Class.forName. Then we try to open a connection using the following code.

   
	dbConfig = new Config().getDBConfig(); //Use the Config class to get the configuration of database connection 
	Connection conn = DriverManager.getConnection("jdbc:vertica://"
			+ dbConfig.verticaHostIP + ":" + dbConfig.portNumber + "/" + dbConfig.databaseName,
			dbConfig.username, dbConfig.password);

Please see the Config.java file for the DBConfig definition. It simply contains several configurable strings.

By the way, Config.java is the configuration file. It queries config.json inside the package haven.mappingtutorial, and then parse the content in it as a JSON object, then get the configuration strings from the JSON object.

Getting a List of CollisionRecords

Here is the signation of the function.

    public List<CollisionRecord> getCollisionRecordsIn(Date start, Date end, AdditionalConditions additionalConditions)

To get a List of CollisionRecords from the database, we first need to open a connection using the code above.

We use the try-with-resource statement introduced in Java 7 to open a connection, so that it would be automatically closed when no longer used.

    try ( Connection conn = connectToDB() )

Then, we need to construct an SQL statement, to query against the database. Here is where we use the PreparedStatement.

The String sql is constructed using the parameters in the signature of the function above. The start is the start Date, and end is the end Date. additionalConditions is the additional constraints specified in the UI widget div.filter-form, such as With Injuries and stuff.

The function checks each additional condition in the additionalConditions object, and constructs the sql string according to it.

Then, it fills the blanks we leave out in the sql using the question mark ?.

After that, the function queries the database using

	ResultSet rs = stmt.executeQuery();

And converts them to a List of CollisionRecords, using a convertion function:

	List<CollisionRecord> collisionRecords = getListWithResultSet(rs);

Then the function returns the List.

The Servlet

Finally, we’re here with the introduction to our Servlet ShowRecordsOnMap.

The doGet method plays the major role here in our Servlet.

It first gets several parameters using request.getParameter function.

Then it tries to parse the start and end parameters as Dates.

Then, according to the additional parameters passed in the URL, the Servlet constructs an AdditionalConditions object, to be passed to the DBHelper. The AdditionalConditions object contains all of the additional constraints the DBHelper needs to know.

After getting the List of CollisionRecords, the Servlet first checks whether the size of the List is too big. If so, it simply returns the JSON string indicating that there are too many records.

Otherwise, if the size is not so big, or if the confirmed=true flag is set in the parameter passed in from the URL, What we need to do next is to insert these records into the cartodb database, hence refreshing the data table.

First, the servlet constructs a TRUNCATE TABLE nytc; statement, to clear all the data existing in cartodb, because we don’t want to show old data in the new map.

Then, we create an INSERT statement, inserting all the CollisionRecords data to cartodb, along with their geolocation data.

Please note, here, we use the ST_GeomFromText function of postgis to convert strings to geolocations, because cartodb supports that. The grammer is fixed. Please visit here if you have the interest to know more knowledge of this function.

For each record we get from the Vertica database, we insert a record into cartodb so that we can get the location displayed on the map. Along with the geolocation data, we also insert the injuries and deaths data into the cartodb, so that tooltip on the map is able to displaying some data, when we click on the dot in the map.

Then, if the insertion is successful, the Servlet would use the PrintWriter to write on the response, with the metadata of the insertion.

End

That’s it! This is a very detailed tutorial, covering everything from the HTML UI, to the js file manipulating the UI, to the Data layer and to the Servlet at last.

It’s important to know, that whenever we want to build something, we must distinguish the layers from each other to prevent from making a big mess. In this tutorial, We’ve seperated the UI layer from the Data layer. There can be further layer abstractions, and that’s for you to try out!

Please leave a comment if you like it, or if you have any doubts about this tutorial.

Cheers!