Mapping query results with Mapbox

What you will build in this guide

time required: 5 minutes

Using Mapbox and Splitgraph, we can build a dashboard displaying traffic accidents in Fort Worth, Texas. All you need is basic knowledge of Javascript and SQL to put together your own map-based dashboard in a few minutes!

How it works

Both public and private Splitgraph repositories can be queried over HTTP using fetch(). Over 40,000 public datasets are available for querying, including Fort Worth, TX traffic accident data, which we will use for this project.

Mapbox provides a convenient API for placing markers at the location of each accident on the map. As a final step, we can direct viewers to the Splitgraph query editor with a query revealing past accidents near the area of interest.

Initial SQL query

To get the list of recent traffic accidents in Fort Worth, we can run the following query:

As displayed on the repository's page, accident data is updated multiple times each day, so our dashboard will be fairly up-to-date.

Building the dashboard

The entire project can be built using a single index.html file. The GitHub repository contains all the stages described in this guide as separate tags. Each of the following sections describes the changes introduced by the given tag.

No Web server is required to display the dashboard, it works with file:/// URLs. Just open index.html in your browser and try it out!

01-querying-splitgraph: Getting started

Before we can place the accidents on a map, we must query Splitgraph from Javascript. Our first step is to define the query function, which does a bit of result post processing (e.g.: casting dates from string to Date), but the most important bit is the HTTP request to https://data.splitgraph.com/sql/query/ddn. The array of accident objects is printed to the console.

02-render-map-with-markers: Displaying the map

As a prerequisite to using Mapbox, we must obtain an API key. Signing up is free, so do so if you haven't yet!

This stage introduces the following:

• A script tag to include the Mapbox SDK.
• CSS for Mapbox SDK and the accident markers
• A div element to host our map.
• Map initialization, including the "buildings" layer.
• Database query moved inside map.on("load", ...).
• Placing the accident markers on the map.

03-accident-list: Listing all accidents

A clickable table of contents really improves navigation. To make that happen, this commit adds:

• An absolute positioneddiv element which contains the table of contents.
• Code to populate our table of contents.
• The showAccident() function which "flies" to an accident when it is selected in the ToC.

04-popup-message: Per-marker popup messages

Now comes the final piece: a contextual popup providing more information on a given accident when it's marker is clicked. There's plenty of information in the current-traffic-accidents table, but I wanted to gauge how "dangerous" the given intersection is where the accident happened. The vehicle-accident-data table provides historic accident information. I naively counted the number of accidents which seemed close by based on their longitude and latitude.

Of course, our users may want to know something different about the accident. By providing a link to the Splitgraph query editor, anything published about the accident is just a single click away!

• CSS styling for the popup container.
• Introduce the getAccidentCountInArea() function, which queries Splitgraph for the number of accidents recorded in the vicinity of the selected accident.
• Set the static part of the popup message.
• Define the function which provides the dynamic content within the popup. Splitgraph is queried using getAccidentCountInArea() when the popup is opened.
• Register the popup to be opened when its associated marker is clicked.

Publishing the dashboard

Since the dashboard works when opened as a local file, email attachments or Slack messages are an option for sharing the dashboard. Sharing a URL is often more convenient though. Any static HTML server works.

I opted to use GitHub pages, but Firebase hosting and S3 are just two of the countless alternatives which work just as well.

Get back to us!

Built a cool map-based dashboard using Splitgraph? We'd love to see it! Share it with us at hello@splitgraph.com!