Optimize Tilemill export of project with contour-lines (and fix out of memory problems)

Question

I have a project in Tilemill (based on OSM-Bright) that covers Europe and also displays Contour-Lines. My problem is that the performance of Tilemill is pretty bad when displaying the contour lines and that the export (even only from a very small sample area) fails all the time with out-of-memory problems.

My contours are in a postgres DB imported by gdal_contour and the table looks like this (default gdal_contour result, but I added the is_100m column and indexed it):

CREATE TABLE "public"."cont" ( 
  "ogc_fid" INTEGER DEFAULT nextval('cont_ogc_fid_seq'::regclass) NOT NULL UNIQUE, 
  "wkb_geometry" "public"."geometry", 
  "id" NUMERIC( 8, 0 ), 
  "height" NUMERIC( 12, 3 ), 
  "is_100m" INTEGER DEFAULT '0',
 PRIMARY KEY ( "ogc_fid" )
 );
CREATE INDEX "cont_geom_idx" ON "public"."cont" USING gist( "wkb_geometry" );
CREATE INDEX "is_100m_idx" ON "public"."cont" USING btree( "is_100m" ASC NULLS LAST );

The is_100m column is used for displaying 100m lines. So in Tilemill I display them differently (a bit thicker and with labels).

Is there any problem with my indexes?

The machine I am running on has the following specs:

OS: Debian Jessie/Sid in a VM (Host Kernel: 2.6.32-5 (Debian oldstable)) RAM: Host 100GB, the VM tilemill is running on: 59GB CPU: 2x Intel(R) Xeon(R) CPU X5650 (2.67GHz) -> 12 Cores. tilemill: v0.10.1-305-gb69b633 (github) nodejs: 0.10.29~dfsg-1 (Debian) postres: 9.3.4

Answer 1

Im guessing the problem might be that your filtering contour lines at the rendering phase, instead of when fetching them from the database. The best strategy for making mapnik/tileMill run fast is to never fetch anything from the database that isnt going to be rendered. Here is the sql query I use for contours:

(SELECT geom, height, idx
     FROM contours
     WHERE 
     !scale_denominator! < 35000 OR /* All contours at zoom 14*/
     (!scale_denominator! < 140000 AND idx >= 5) OR /* zoom 12 and 13 */
     (!scale_denominator! < 600000 AND idx=10) /* zoom 10 and 11*/
    ) AS data

I have a field idx, that is similar to your is_100m field, but it contains either 1, 2, 5 or 10. With your is_100m query you could use a query like the following:

(SELECT geometry, height,
     FROM contours
     WHERE 
     !scale_denominator! < 35000 OR /* All contours at zoom 14*/
     (!scale_denominator! < 600000 AND is_100m = 1)
    ) AS data

You might also want to create a partial index, for just the is_100m contours

CREATE INDEX idx_contours_100m ON contours USING GIST(geometry) WHERE is_100m=1

You didn't mention what projection your contours are stored in. If they are not stored in epsg:3785 you may want to reimport them, or reproject them, so that they are not converted on every query.

Answer 2

It is tricky to use spatial indexes for contour lines: contour lines are usually very long with complex shapes - they are also usually close to each other. Consequently, their envelopes are very large and intersects a lot, which makes spatial indexes based on features' envelopes not so efficient.

I suspect tilemill retrieves all contour lines intersecting a given tile to render them. The memory overflow is certainly a consequence of the fact that too many contour lines are intersecting some tiles.

My advice:

1. Test what happens when the contours are cut into smaller pieces (with smaller envelopes). You could use the ST_Split function. The spatial indexes should become far more efficient then. Hopefully.
2. For each zoom level, choose a contour interval so that only few relevant contour lines are selected and rendered. Simplify them according to the zoom level. See here.