In this guide, we will walk through the steps to add a layer from a SQLite database and join additional attribute data to the geographic information. TileMill supports using SQLite databases as geographic datasources and thus offers an alternative to shapefiles.
If you are not familiar with SQLite, check out their documentation resources. SQLite databases can store geographic features along with non-geographic attribute data. SQLite files can be edited with any SQLite client, including free GIS tools like Quantum GIS or SQLite Database Browser. See note in Step 7 about downloading a SQLite editor to work with your files.
We’ll begin by adding polygon features of the District of Columbia Census tract boundary downloaded from the U.S. Census Bureau TIGER/Line database. The original files were downloaded as shapefiles, but we’ve used QGIS to convert and save as a SQLite database. We’ll begin there and create a new database.
District of Columbiafor Census Tract (2010). Choose
District of Columbiain the final menu. A zipped shapefile will be downloaded.
tl_2010_11001_tract10within the Layers window. Click Save As.
dc-census-tractsfor the Save as name. You can browse to select the directory to save the file.
P1. Total Populationand download as CSV.
Note: SQLite Editors
There are many spreadsheet-like GUI editors available for OSX, Linux, and Windows. You can download an editor to work with your SQLite files, or alternatively, you can edit and manipulate SQLite through the command-line utility named,
sqlite3.exe on Windows).
dc-census-data.sqlite. A create table window will pop up and ask you to create a table, click
Cancelto by-pass this step.
all_140_in_11.P1.csvfile and click Open.
datain the New table name field and check the box for Extract field names from first line. Click Create. Save your changes.
/Mapbox/Data/folder for use in TileMill.
SQLite. You will see the fields change.
dcin the ID field.
dc-census-tracts.sqliteto fill in the Datasource field and then click Done to confirm your selection.
( SELECT * from dc_census_tracts ). This is a query to select the data from table
dc-census-tractswithin your SQLite database. This field acts as a subquery so the information must be entered in a subquery fashion. The name you specify after “AS” is arbitrary and does not affect the
IDfield you gave at the top. We’ll skip the Attach DB field at the moment, and pick it up below when adding attribute data to your geographic features in the section below.
+proj=longlat +ellps=GRS80 +datum=NAD83 +no_defs.
From within the Editor window of the
dc-census-tracts project, toggle on the layer selector and click Edit next to the
dc layer you just added in the previous section.
Move down to the Attach DB field. This is the field for attaching additional databases to base an SQL join statement on to add attribute data to your geographic features.
data@[ path to your ]/MapBox/data/dc-census-data.sqlite to provide the path information to the SQLite file we created from the Census total population data for the District of Columbia.
Within the Table or subquery field, adjust the existing query to be:
( SELECT * from dc_census_tracts JOIN data on dc_census_tracts.geoid10 = data.geoid )
Click Save to save your changes.
Within the layer menu, click the Features button to explore the data table. You will now see the attribute data columns.
Click the X or hit the
esc on your keyboard to exit out of the table view. You can now begin to use conditional styles or add additional data to your interactivity.