<commit_before>---
layout: book
section: documentation
category: TileMill
tag: Guides
title: "Creating and Adding SQLite Layers"
permalink: /docs/guides/add-sqlite
prereq:
- "[Installed](/tilemill/docs/install) TileMill on your computer."
- "Reviewed [Crash Course](/tilemill/docs/crashcourse/overview/)"
nextup:
- "[Using conditional styles](/tilemill/docs/crashcourse/conditional-styles/) to control the appearance of points based on data."
- "[Adding tooltips](/tilemill/docs/crashcourse/tooltips/) to your map."
- "[Exporting](/tilemill/docs/crashcourse/exporting/) your map."
code1: | `( SELECT * from dc_census_tracts JOIN data on dc_census_tracts.geoid10 = data.geoid )`  
---
{% include prereq.html %}

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](). 

Let's start 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. 

## Creating SQLite database files  

1. To begin, download the [District of Columbia Census Tract TIGER/Line shapefile](http://www.census.gov/cgi-bin/geo/shapefiles2010/main) from the U.S. Census Bureau. Select `Census Tracts` and `District of Columbia` for Census Tract (2010). Choose `District of Columbia` in the final menu. A zipped shapefile will be downloaded.
2. Unzip the shapefile and open it up in [QGIS]().
3. Right-click on the layer named `tl_2010_11001_tract10` within the **Layers** window. Click **Save As**.
![step3a](/tilemill/assets/pages/addsqlite-3a.png)
4. Select **SQLite** for the **Format**, and enter `dc-census-tracts` for the **Save as** name. You can browse to select the directory to save the file. 
![step4a](/tilemill/assets/pages/addsqlite-4a.png)
5. Download Census Tract data for the District of Columbia from the [CENSUS.IRE.ORG](http://census.ire.org/data/bulkdata.html?state=11&sumlev=140). Select  `P1. Total Population` and download as CSV. 
![step5a](/tilemill/assets/pages/addsqlite-5a.png)
6. For a smooth import into a SQLite database, we need to clean up the headers within the CSV. Open the CSV in a text editor. The first line is the header line. Within the header line, remove any periods ("."). Save your changes. 
![step6a](/tilemill/assets/pages/addsqlite-6a.png)
7. Open SQLite Browser and create a new database by choosing **File > New** from the menu. Save your new database as `dc-census-data.sqlite`. A create table window will pop up and ask you to create a table, click `Cancel` to by-pass this step.
8. Choose **File > Import > Table from CSV File** from the menu. Locate the `all_140_in_11.P1.csv` file and click **Open**.
9. Enter `data` in the **New table name** field and check the box for **Extract field names from first line**. Click **Create**. Save your changes.  
![step9a](/tilemill/assets/pages/addsqlite-9a.png)
10. Move your newly creating SQLite files to the `/Mapbox/Data/` folder for use in TileMill. 

## Adding SQLite layers

1. To begin, we'll work with an existing TileMill project. We've called ours `dc-census`.<commit_msg>link change to sqlite doc<commit_after>---
layout: book
section: documentation
category: TileMill
tag: Guides
title: "Creating and Adding SQLite Layers"
permalink: /docs/guides/add-sqlite
prereq:
- "[Installed](/tilemill/docs/install) TileMill on your computer."
- "Reviewed [Crash Course](/tilemill/docs/crashcourse/overview/)"
nextup:
- "[Using conditional styles](/tilemill/docs/crashcourse/conditional-styles/) to control the appearance of points based on data."
- "[Adding tooltips](/tilemill/docs/crashcourse/tooltips/) to your map."
- "[Exporting](/tilemill/docs/crashcourse/exporting/) your map."
code1: | `( SELECT * from dc_census_tracts JOIN data on dc_census_tracts.geoid10 = data.geoid )`  
---
{% include prereq.html %}

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](http://www.sqlite.org/docs.html). 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](http://www.qgis.org/). 

Let's start by adding polygon features of the District of Columbia Census tract boundary downloaded from the U.S. Census Bureau [TIGER/Line database](http://www.census.gov/geo/www/tiger/). The original files were downloaded as shapefiles, but we've used [QGIS](http://www.qgis.org/) to convert and save as a SQLite database. We'll begin there and create a new database. 

## Creating SQLite database files  

1. To begin, download the [District of Columbia Census Tract TIGER/Line shapefile](http://www.census.gov/cgi-bin/geo/shapefiles2010/main) from the U.S. Census Bureau. Select `Census Tracts` and `District of Columbia` for Census Tract (2010). Choose `District of Columbia` in the final menu. A zipped shapefile will be downloaded.
2. Unzip the shapefile and open it up in QGIS.
3. Right-click on the layer named `tl_2010_11001_tract10` within the **Layers** window. Click **Save As**.
![step3a](/tilemill/assets/pages/addsqlite-3a.png)
4. Select **SQLite** for the **Format**, and enter `dc-census-tracts` for the **Save as** name. You can browse to select the directory to save the file. 
![step4a](/tilemill/assets/pages/addsqlite-4a.png)
5. Select 
5. Download Census Tract data for the District of Columbia from the [CENSUS.IRE.ORG](http://census.ire.org/data/bulkdata.html?state=11&sumlev=140). Select  `P1. Total Population` and download as CSV. 
![step5a](/tilemill/assets/pages/addsqlite-5a.png)
6. For a smooth import into a SQLite database, we need to clean up the headers within the CSV. Open the CSV in a text editor. The first line is the header line. Within the header line, remove any periods ("."). Save your changes. 
![step6a](/tilemill/assets/pages/addsqlite-6a.png)
7. Open SQLite Browser and create a new database by choosing **File > New** from the menu. Save your new database as `dc-census-data.sqlite`. A create table window will pop up and ask you to create a table, click `Cancel` to by-pass this step.
8. Choose **File > Import > Table from CSV File** from the menu. Locate the `all_140_in_11.P1.csv` file and click **Open**.
9. Enter `data` in the **New table name** field and check the box for **Extract field names from first line**. Click **Create**. Save your changes.  
![step9a](/tilemill/assets/pages/addsqlite-9a.png)
10. Move your newly creating SQLite files to the `/Mapbox/Data/` folder for use in TileMill. 

## Adding SQLite layers

1. To begin, we'll work with an existing TileMill project. We've called ours `dc-census`.
{'fraction_non_alphanumeric': 0.10305548110426159, 'fraction_numerical': 0.01514339319217368, 'mean_word_length': 4.443471918307805, 'pattern_counts': {'":': 0, '<': 3, '<?xml version=': 0, '>': 9, 'https://': 0, 'lorem ipsum': 0, 'www.': 7, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 12, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '7140928', 'n_tokens_mistral': 2348, 'n_tokens_neox': 2188, 'n_words': 941}