Custom OSM maps with TileMill

Spent the morning working on creating custom maps of Haida Gwaii. One workflow for doing this involves pulling down OSM data for BC, subsetting it to Haida Gwaii, building up a PostgreSQL database and then building a TileMill project from there.

postgres@basalt /tmp/osmosis $ createdb bc_osm
postgres@basalt /tmp/osmosis $ psql -d bc_osm
psql (9.3.5)
Type "help" for help.

bc_osm=# create extension postgis;
CREATE EXTENSION
bc_osm=# create extension hstore;
CREATE EXTENSION
bc_osm=# grant all privileges on database bc_osm to james;
GRANT
bc_osm=# \q
postgres@basalt /tmp/osmosis $ exit
james@basalt /tmp/osmosis $ psql -d bc_osm -f script/pgsnapshot_schema_0.6.sql
psql:script/pgsnapshot_schema_0.6.sql:4: NOTICE:  table "actions" does not exist, skipping
DROP TABLE
psql:script/pgsnapshot_schema_0.6.sql:5: NOTICE:  table "users" does not exist, skipping
DROP TABLE
psql:script/pgsnapshot_schema_0.6.sql:6: NOTICE:  table "nodes" does not exist, skipping
DROP TABLE
psql:script/pgsnapshot_schema_0.6.sql:7: NOTICE:  table "ways" does not exist, skipping
DROP TABLE
psql:script/pgsnapshot_schema_0.6.sql:8: NOTICE:  table "way_nodes" does not exist, skipping
DROP TABLE
psql:script/pgsnapshot_schema_0.6.sql:9: NOTICE:  table "relations" does not exist, skipping
DROP TABLE
psql:script/pgsnapshot_schema_0.6.sql:10: NOTICE:  table "relation_members" does not exist, skipping
DROP TABLE
psql:script/pgsnapshot_schema_0.6.sql:11: NOTICE:  table "schema_info" does not exist, skipping
DROP TABLE
psql:script/pgsnapshot_schema_0.6.sql:14: NOTICE:  function osmosisupdate() does not exist, skipping
DROP FUNCTION
CREATE TABLE
CREATE TABLE
CREATE TABLE
               addgeometrycolumn                
------------------------------------------------
 public.nodes.geom SRID:4326 TYPE:POINT DIMS:2 
(1 row)

CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
INSERT 0 1
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
CREATE INDEX
CREATE INDEX
CREATE INDEX
CLUSTER
CREATE FUNCTION
CREATE FUNCTION
james@basalt /tmp/osmosis $ psql -d bc_osm -f script/pgsnapshot_schema_0.6_linestring.sql 
                   addgeometrycolumn                    
--------------------------------------------------------
 public.ways.linestring SRID:4326 TYPE:GEOMETRY DIMS:2 
(1 row)

CREATE INDEX
CLUSTER
james@basalt /tmp/osmosis $ psql -d bc_osm -f script/pgsnapshot_schema_0.6_bbox.sql 
                addgeometrycolumn                 
--------------------------------------------------
 public.ways.bbox SRID:4326 TYPE:GEOMETRY DIMS:2 
(1 row)

CREATE INDEX
CLUSTER
CREATE FUNCTION
CREATE AGGREGATE
james@basalt /tmp/osmosis $ bin/osmosis --read-pbf file="/home/james/data/gis/osm/british-columbia-latest.osm.pbf" --bounding-box left="134.2529297" right="-130.3857422" top="54.5083265" bottom="51.6316573" --write-pgsql user="james" database="bc_osm" password='****'
Oct 05, 2014 10:22:23 AM org.openstreetmap.osmosis.core.Osmosis run
INFO: Osmosis Version 0.43.1
Oct 05, 2014 10:22:23 AM org.openstreetmap.osmosis.core.Osmosis run
INFO: Preparing pipeline.
Oct 05, 2014 10:22:23 AM org.openstreetmap.osmosis.core.Osmosis run
INFO: Launching pipeline execution.
Oct 05, 2014 10:22:23 AM org.openstreetmap.osmosis.core.Osmosis run
INFO: Pipeline executing, waiting for completion.
Oct 05, 2014 10:23:53 AM org.openstreetmap.osmosis.core.Osmosis run
INFO: Pipeline complete.
Oct 05, 2014 10:23:53 AM org.openstreetmap.osmosis.core.Osmosis run
INFO: Total execution time: 89892 milliseconds.
james@basalt ~/code/git/osmosis-multipolygon $ psql -d bc_osm -f assemble_multipolygon.sql 
CREATE FUNCTION
james@basalt ~/code/git/osmosis-multipolygon $ psql -d bc_osm -f build_multipolygon.sql 
CREATE FUNCTION
james@basalt ~/code/git/osmosis-multipolygon $ psql -d bc_osm -f make_multipolygon.sql 
CREATE FUNCTION
james@basalt ~/code/git/osmosis-multipolygon $ psql -d bc_osm -f tags_merge.sql 
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
james@basalt ~/code/git/osmosis-multipolygon $ psql -d bc_osm -f update.sql 
CREATE FUNCTION
james@basalt ~/code/git/osmosis-multipolygon $ psql -d bc_osm -f create.sql 
psql:create.sql:1: NOTICE:  table "multipolygons" does not exist, skipping
DROP TABLE
CREATE TABLE
                     addgeometrycolumn                     
-----------------------------------------------------------
 public.multipolygons.geom SRID:4326 TYPE:GEOMETRY DIMS:2 
(1 row)

CREATE INDEX

Mostly works great, until you run into rendering a multi-polygon that is actually a single polygon with more than one outer linestring (due to the constraint of 2000 nodes per way). You can try and use osmosis-multipolygon:

james@basalt ~/code/git/osmosis-multipolygon $ psql -d bc_osm -f assemble.sql 
psql:assemble.sql:4: NOTICE:  0 elements input array
CONTEXT:  PL/pgSQL function make_multipolygon(geometry[]) line 55 at RETURN
PL/pgSQL function build_multipolygon(geometry[],geometry[]) line 17 at assignment
SQL statement "SELECT build_multipolygon(
    (select array_agg((select linestring from ways where ways.id=outer_id)) from unnest(outer_members) outer_id),
    (select array_agg((select linestring from ways where ways.id=member_id)) from relation_members where relation_id=id and member_type='W' and member_role in ('inner', 'enclave') group by relation_id))"
PL/pgSQL function assemble_multipolygon(bigint) line 40 at assignment
psql:assemble.sql:4: NOTICE:  0 elements input array
CONTEXT:  PL/pgSQL function make_multipolygon(geometry[]) line 55 at RETURN
PL/pgSQL function build_multipolygon(geometry[],geometry[]) line 17 at assignment
SQL statement "SELECT build_multipolygon(
    (select array_agg((select linestring from ways where ways.id=outer_id)) from unnest(outer_members) outer_id),
    (select array_agg((select linestring from ways where ways.id=member_id)) from relation_members where relation_id=id and member_type='W' and member_role in ('inner', 'enclave') group by relation_id))"
PL/pgSQL function assemble_multipolygon(bigint) line 40 at assignment
psql:assemble.sql:4: NOTICE:  0 elements input array
CONTEXT:  PL/pgSQL function make_multipolygon(geometry[]) line 55 at RETURN
PL/pgSQL function build_multipolygon(geometry[],geometry[]) line 17 at assignment
SQL statement "SELECT build_multipolygon(
    (select array_agg((select linestring from ways where ways.id=outer_id)) from unnest(outer_members) outer_id),
    (select array_agg((select linestring from ways where ways.id=member_id)) from relation_members where relation_id=id and member_type='W' and member_role in ('inner', 'enclave') group by relation_id))"
PL/pgSQL function assemble_multipolygon(bigint) line 40 at assignment
psql:assemble.sql:4: NOTICE:  0 elements input array
CONTEXT:  PL/pgSQL function make_multipolygon(geometry[]) line 55 at RETURN
PL/pgSQL function build_multipolygon(geometry[],geometry[]) line 17 at assignment
SQL statement "SELECT build_multipolygon(
    (select array_agg((select linestring from ways where ways.id=outer_id)) from unnest(outer_members) outer_id),
    (select array_agg((select linestring from ways where ways.id=member_id)) from relation_members where relation_id=id and member_type='W' and member_role in ('inner', 'enclave') group by relation_id))"
PL/pgSQL function assemble_multipolygon(bigint) line 40 at assignment
psql:assemble.sql:4: NOTICE:  0 elements input array
CONTEXT:  PL/pgSQL function make_multipolygon(geometry[]) line 55 at RETURN
PL/pgSQL function build_multipolygon(geometry[],geometry[]) line 17 at assignment
SQL statement "SELECT build_multipolygon(
    (select array_agg((select linestring from ways where ways.id=outer_id)) from unnest(outer_members) outer_id),
    (select array_agg((select linestring from ways where ways.id=member_id)) from relation_members where relation_id=id and member_type='W' and member_role in ('inner', 'enclave') group by relation_id))"
PL/pgSQL function assemble_multipolygon(bigint) line 40 at assignment
psql:assemble.sql:4: NOTICE:  0 elements input array
CONTEXT:  PL/pgSQL function make_multipolygon(geometry[]) line 55 at RETURN
PL/pgSQL function build_multipolygon(geometry[],geometry[]) line 17 at assignment
SQL statement "SELECT build_multipolygon(
    (select array_agg((select linestring from ways where ways.id=outer_id)) from unnest(outer_members) outer_id),
    (select array_agg((select linestring from ways where ways.id=member_id)) from relation_members where relation_id=id and member_type='W' and member_role in ('inner', 'enclave') group by relation_id))"
PL/pgSQL function assemble_multipolygon(bigint) line 40 at assignment
psql:assemble.sql:4: NOTICE:  0 elements input array
CONTEXT:  PL/pgSQL function make_multipolygon(geometry[]) line 55 at RETURN
PL/pgSQL function build_multipolygon(geometry[],geometry[]) line 17 at assignment
SQL statement "SELECT build_multipolygon(
    (select array_agg((select linestring from ways where ways.id=outer_id)) from unnest(outer_members) outer_id),
    (select array_agg((select linestring from ways where ways.id=member_id)) from relation_members where relation_id=id and member_type='W' and member_role in ('inner', 'enclave') group by relation_id))"
PL/pgSQL function assemble_multipolygon(bigint) line 40 at assignment
psql:assemble.sql:4: NOTICE:  0 elements input array
CONTEXT:  PL/pgSQL function make_multipolygon(geometry[]) line 55 at RETURN
PL/pgSQL function build_multipolygon(geometry[],geometry[]) line 17 at assignment
SQL statement "SELECT build_multipolygon(
    (select array_agg((select linestring from ways where ways.id=outer_id)) from unnest(outer_members) outer_id),
    (select array_agg((select linestring from ways where ways.id=member_id)) from relation_members where relation_id=id and member_type='W' and member_role in ('inner', 'enclave') group by relation_id))"
PL/pgSQL function assemble_multipolygon(bigint) line 40 at assignment
psql:assemble.sql:4: NOTICE:  0 elements input array
CONTEXT:  PL/pgSQL function make_multipolygon(geometry[]) line 55 at RETURN
PL/pgSQL function build_multipolygon(geometry[],geometry[]) line 17 at assignment
SQL statement "SELECT build_multipolygon(
    (select array_agg((select linestring from ways where ways.id=outer_id)) from unnest(outer_members) outer_id),
    (select array_agg((select linestring from ways where ways.id=member_id)) from relation_members where relation_id=id and member_type='W' and member_role in ('inner', 'enclave') group by relation_id))"
PL/pgSQL function assemble_multipolygon(bigint) line 40 at assignment
psql:assemble.sql:4: NOTICE:  0 elements input array
CONTEXT:  PL/pgSQL function make_multipolygon(geometry[]) line 55 at RETURN
PL/pgSQL function build_multipolygon(geometry[],geometry[]) line 17 at assignment
SQL statement "SELECT build_multipolygon(
    (select array_agg((select linestring from ways where ways.id=outer_id)) from unnest(outer_members) outer_id),
    (select array_agg((select linestring from ways where ways.id=member_id)) from relation_members where relation_id=id and member_type='W' and member_role in ('inner', 'enclave') group by relation_id))"
PL/pgSQL function assemble_multipolygon(bigint) line 40 at assignment
psql:assemble.sql:4: NOTICE:  multipolygon consists of 2827 (more then 256) inner members ... ignore
CONTEXT:  SQL statement "SELECT build_multipolygon(
    (select array_agg((select linestring from ways where ways.id=outer_id)) from unnest(outer_members) outer_id),
    (select array_agg((select linestring from ways where ways.id=member_id)) from relation_members where relation_id=id and member_type='W' and member_role in ('inner', 'enclave') group by relation_id))"
PL/pgSQL function assemble_multipolygon(bigint) line 40 at assignment

But it appears to fail on the large polygons anyways. At least, after running this and switching store to the new table, none of the large polygons even rendered (probably b/c they were not inserted).

Let’s try a different tack and use osm2pgsql. It seem to be more modern and more maintained.

postgres@basalt /home/james/code/git/osmosis-multipolygon $ createdb hg_osm
postgres@basalt /home/james/code/git/osmosis-multipolygon $ psql -d hg_osm
psql (9.3.5)
Type "help" for help.

hg_osm=# create extension postgis;
CREATE EXTENSION
hg_osm=# create extension hstore;
CREATE EXTENSION
hg_osm=# grant all privileges on database hg_osm to james;
GRANT
hg_osm=# \q
james@basalt ~/code/git/osmosis-multipolygon $ osm2pgsql --create --database hg_osm -j -l --bbox -134.2529272,51.6316573,-130.3857422,54.5083265 /home/james/data/gis/osm/british-columbia-latest.osm.pbf
osm2pgsql SVN version 0.84.0 (64bit id space)

Using projection SRS 4326 (Latlong)
Applying Bounding box: -134.252927,51.631657 to -130.385742,54.508327
Setting up table: planet_osm_point
NOTICE:  table "planet_osm_point" does not exist, skipping
NOTICE:  table "planet_osm_point_tmp" does not exist, skipping
Setting up table: planet_osm_line
NOTICE:  table "planet_osm_line" does not exist, skipping
NOTICE:  table "planet_osm_line_tmp" does not exist, skipping
Setting up table: planet_osm_polygon
NOTICE:  table "planet_osm_polygon" does not exist, skipping
NOTICE:  table "planet_osm_polygon_tmp" does not exist, skipping
Setting up table: planet_osm_roads
NOTICE:  table "planet_osm_roads" does not exist, skipping
NOTICE:  table "planet_osm_roads_tmp" does not exist, skipping
Using built-in tag processing pipeline
Allocating memory for dense node cache
Allocating dense node cache in one big chunk
Allocating memory for sparse node cache
Sharing dense sparse
Node-cache: cache=800MB, maxblocks=102400*8192, allocation method=3
Mid: Ram, scale=10000000

Reading in file: /home/james/data/gis/osm/british-columbia-latest.osm.pbf
Processing: Node(2352k 470.4k/s) Way(2107k 175.60k/s) Relation(17870 2552.86/s)  parse time: 24s

Node stats: total(2352081), max(3040762750) in 5s
Way stats: total(2107220), max(306291660) in 12s
Relation stats: total(17871), max(4085024) in 7s
Committing transaction for planet_osm_point
Committing transaction for planet_osm_line
Committing transaction for planet_osm_polygon
Committing transaction for planet_osm_roads

Writing way (2107k)
Committing transaction for planet_osm_point
Committing transaction for planet_osm_line
Committing transaction for planet_osm_polygon
Committing transaction for planet_osm_roads

Writing relation (17858)
Sorting data and creating indexes for planet_osm_point
Sorting data and creating indexes for planet_osm_line
node cache: stored: 2352081(100.00%), storage efficiency: 63.23% (dense blocks: 2050, sparse nodes: 810237), hit rate: 5.25%
Sorting data and creating indexes for planet_osm_roads
Sorting data and creating indexes for planet_osm_polygon
Analyzing planet_osm_roads finished
Analyzing planet_osm_point finished
Copying planet_osm_roads to cluster by geometry finished
Creating geometry index on  planet_osm_roads
Creating indexes on  planet_osm_roads finished
All indexes on  planet_osm_roads created  in 0s
Completed planet_osm_roads
Copying planet_osm_point to cluster by geometry finished
Creating geometry index on  planet_osm_point
Creating indexes on  planet_osm_point finished
All indexes on  planet_osm_point created  in 0s
Completed planet_osm_point
Analyzing planet_osm_polygon finished
Copying planet_osm_polygon to cluster by geometry finished
Creating geometry index on  planet_osm_polygon
Creating indexes on  planet_osm_polygon finished
All indexes on  planet_osm_polygon created  in 0s
Completed planet_osm_polygon
Analyzing planet_osm_line finished
Copying planet_osm_line to cluster by geometry finished
Creating geometry index on  planet_osm_line
Creating indexes on  planet_osm_line finished
All indexes on  planet_osm_line created  in 2s
Completed planet_osm_line

Osm2pgsql took 40s overall