As we’ve worked through in adding CSV data and shapefiles to a TileMill project in previous articles here, you can also add data from a PostGIS database.
PostGIS is an extension for PostgreSQL databases for storing, manipulating, and retrieving spatial data. PostGIS (along with Mapnik) powers the main maps on openstreetmap.org (OSM) and open.mapquest.com.
If you are not familiar with PostGIS or PostgreSQL, check out the documentation for both PostgreSQL and PostGIS. These resources are great places to start if you are setting up a database for the first time.
For this example, we’ll cover the basics of creating a simple PostGIS database and making a connection to the database in TileMill. We’ll also cover joining data to add attribute data to your features. We’ll use Census data for the District of Columbia Census Tracts for this example.
District of Columbiafor Census Tract (2010). Choose
District of Columbiain the final menu. A zipped shapefile will be downloaded.
P1. Total Populationand download as CSV.
[prefix]/share/contrib/. See the PostGIS reference manual for installation details.
createdb dc-census createlang plpgsql dc-census psql -d dc-census -f postgis.sql psql -d dc-census -f spatial_ref_sys.sql
shp2pgsql -c -D -s 4269 -I tl_2010_1101_tract10.shp dc_census_tracts | psql -d dc-census
CREATE TABLE dc_census_data (GEOID varchar(11), SUMLEV varchar(3), STATE varchar(2), COUNTY varchar(3), CBSA varchar(5), CSA varchar(3), NECTA integer, CNECTA integer, NAME varchar(30), POP100 integer, HU100 integer, POP1002000 integer, HU1002000 integer, P001001 integer, P0010012000 integer);
cat all_140_in_11.P1.csv | psql -d dc-census -c 'COPY dc_census_data FROM STDIN WITH CSV HEADER'
psql dc-census. You can see your new tables by typing,
Create a new TileMill project called
Click to add a new layer.
Change the add layer type to PostGIS. You will see the fields change.
dc-census in the ID field.
Enter the connection parameters to connect with your local PostGIS database. You need to provide
dbname information. Here since we’re working on a database locally,
Port=5432, and we’ve named our database,
dc-census. Use the username and password for the authentication that is set up for your database.
Enter the Table or subquery information to access your data. This is a query to select the data from your PostGIS database. This field acts as a subquery so the information must be entered in a subquery fashion:
( SELECT * from dc_census_tracts ) AS tracts. The name you specify after “AS” is arbitrary and does not affect the
ID field you gave at the top. Here we’ve entered,
( SELECT * from dc_census_tracts ) as tracts, which selects all the columns in the table. To only select the columns you want to call from our database table, omit the
* and enter the column names directly.
Enter the extent of your data. The coordinate system you use should match your data. The correct order is
left bottom right top. This can be determined easily through a SQL query in your PostGIS database. In our example database, we would run the query:
SELECT ST_EXTENT(the_geom) from dc_census_tracts
Enter the Unique Key Field for your database feature. This is the database field containing a unique key for your table or feature. See below for a note about indexing and optimizing your PostGIS database.
Enter the Geometry Field for your database feature. If you are using the shp2pgsql data loader for PostGIS, this default field name will be
Select the Spatial Reference System (SRS) for your feature. This will be the projection your data is in PostGIS. Since the Census TIGER/Line data is projected in the NAD83 geographic projection (EPSG:4269), we will use custom parameters here for TileMill to assume the correct datum and ellipsoid for projecting data (we have provided the correct custom parameters here, but can also be obtained from here).
Select Custom and enter
+proj=longlat +ellps=GRS80 +datum=NAD83 +no_defs.
Click Save and Style to save your connection and style with the default CartoCSS settings.
Preview the result in the map preview pane.
From within the Editor window of the
dc-census project, toggle on the layer selector and click Edit next to the
dc-census layer you just added in the previous section.
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
Here we are creating a SQL left join of the attribute data,
dc_census_datawith our geographic feature table,
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.