time required: 5 minutes
Both public and private Splitgraph repositories can be queried over HTTP using
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.
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.
The entire project can be built using a single
index.html file. The
contains all the stages described in this guide as
Each of the following sections describes the changes introduced by the given
No webserver is required to display the dashboard, it works with
URLs. Just open
index.html in your browser and try it out!
Before we can place the accidents on a map, we must query Splitgraph from
function, which does a bit of result post processing (eg: casting dates from
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.
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:
scripttag to include the Mapbox sdk.
divelement to host our map.
A clickable table of contents really improves navigation. To make that happen, this commit adds:
divelement which contains the table of contents.
showAccident()function which "flies" to an accident when it is selected in the ToC.
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
table, but I wanted to gauge how "dangerous" the given intersection is where the
accident happened. The
table provides historic accident information. I naively counted the number of
accidenty 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!
getAccidentCountInArea()function, which queries Splitgraph for the number of accidents recorded in the vicinity of the selected accident.
getAccidentCountInArea()when the popup is opened.
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.
Built a cool map-based dashboard using Splitgraph? We'd love to see it! Share it with us at firstname.lastname@example.org!