5

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

  • I also run into out-of-memory problems... But the machine it's running on has 70GB of RAM... so that should be enough i suppose?!? Any help is really really appreciated! – Georg Aug 12 '14 at 9:22
  • Could you post the sql queries you are using in tilemill to create the layer? Im guessing this has to do with fetching way to many lines at low zoom levels. – Jesse Crocker Aug 13 '14 at 18:03
0
+50

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.
  • Awesome... Thanks for your answer... That makes absolute sense, yes! Do you know a way on how to break long line-geometries in Postgres/Postgis into smaller ones? I already only display "useful" contour lines depending on the zoom level... I have 100m Lines in one table and the others in an other table... But do you really think that a few contour lines can take up like 70GBs of RAM? – Georg Aug 13 '14 at 14:05
  • ST_Split could be useful (see above). 70GB of RAM is huge! even for a dataset of contour lines across Europe. Give it a try - maybe there is another problem! – julien Aug 13 '14 at 14:17
  • I am playing around with ST_Line_Substring. As I am not that good in Postgres... Could I write an SQL statement that iterates over all my contour lines and splits them and the inserts them into an other table? Or will I have to write a little helper app for that? – Georg Aug 13 '14 at 14:45
  • Alright... I am a Postgres noop... but I just discovered how to make own functions... I'll play around with that a bit... and if it really helps I will be soooo extremely happy to give you the reward for this question! :) Merci beaucoup!!!! – Georg Aug 13 '14 at 15:10
  • Maybe these slides could be of use? They're for polygons, but at least could provide a starting point for your SQLing. – Kevin R Dyke Aug 13 '14 at 16:59
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.

  • I guess you are true... I's probably like Julien pointed out in his anwser... I have only tested my solution with a quite small dataset and it seems to work quite well... still excited to see how it performs on the big (all of europe) dataset... – Georg Aug 14 '14 at 20:23
  • I use these queries on 10 meters contours for the whole world, so they should work for you. I've tried doing the simplifying in the database as Julien suggested, and it didnt help. I'd rather have that load in the rendering process than slow down postgres. As for using ST_Split, unless your rendering your contours from really large or really high res DEMS that shouldn't be an issue. Contour lines rendered from 1/3 arc second 1 degree squares are really not that large in most terrain. – Jesse Crocker Aug 14 '14 at 21:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.