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 documentation 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_11001_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,
dc-censusin the ID field.
dbnameinformation. 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.
( SELECT * from dc_census_tracts ) AS tracts. The name you specify after “AS” is arbitrary and does not affect the
IDfield 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.
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
8. 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. 9. Enter the Geometry Field for your database feature. If you are using the shp2pgsql data loader for PostGIS, this default field name will be
the_geom. 10. 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.
11. Click Save and Style to save your connection and style with the default CartoCSS settings. 12. Preview the result in the map preview pane.
dc-censusproject, toggle on the layer selector and click Edit next to the
dc-censuslayer you just added in the previous section.
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,
escon your keyboard to exit out of the table view. You can now begin to use conditional styles or add additional data to your interactivity.
Note: Indexing and Optimizing PostGIS data
To achieve fast and optimized results in TileMill, use good database management by indexing your data tables with both a unique index on your row ID and a gist index on your geometry column. See the Building Indexes section of the PostGIS manual for a beginning reference.