2

I'm adding polylines (elevation curves) to a world map. Said polylines are stored in a PostgreSQL database from gdal_contour tool. The geometry columns (LineString, 3857) has a GIST index and a CLUSTER on it. When Mapnik renders tiles, the GIST index is used.

Two bottlenecks are causing my map to be extremely slow :

  1. Even though GIST index is used, the SELECT statement takes several seconds (usually around 30 seconds) to fetch results.
  2. When the SELECT statement is done, renderd takes even more time to render the tiles (around 90 seconds).

My first guess is, the contour lines are quite long, and for most of these, a big part of the geometry is probably outside of the tile. I'm guessing Mapnik works with the entire shape, even if only a small part should be drawn on the tile. If I'm right, how should I split (or import) my contour shapes correctly, to work with smaller parts instead? (I need to draw text along the polyline with text-spacing at 500, so lines should not be shorter than this).

My second guess is, since the table is quite big (currently 45 341 726 rows), the GIST index is not enough (but could be sufficient if the polylines are splitted maybe?). I'm not sure how to handle this, but I'm wondering if there's a way to split the index in several parts, and index the splitted index to speed things up? Or is this a case when GeoHash would be helpful?

The polyline layer is defined as follows :

- id: contour100
    geometry: linestring
    Datasource:
      type: "postgis"
      dbname: "gis"
      key_field: ""
      geometry_field: "wkb_geometry"
      extent: "-20037508,-20037508,20037508,20037508"
      table: (SELECT
            wkb_geometry, height
            FROM contour100
            WHERE wkb_geometry && !bbox!
          ) AS c100
    properties:
      minzoom: 12

The server has 128 GB of RAM, and Xeon E5-1660v3 processor. The database is stored on NVMe drives, so the problem has to come from a wrong configuration on my side.

  • Better make it one question, either speeding up the PostGIS query OR the rendering. – bugmenot123 May 13 at 13:24
  • Actually, I don't know which one should be addressed first, and having both these bottlenecks in one question gives context to my problem. Since the render time is what's most time consuming, I'd try to speed up rendering first, but maybe it's slow because of the query returning too much data. My question is about what I should be doing first. – user2700551 May 13 at 14:11
  • It is easier to control the query time alone I'd say. Best clarify how much data it is too. – bugmenot123 May 13 at 14:39
  • 45 341 726 rows representing as many contour lines at a 100 meters intervals covering the world. One complete polyline per row. – user2700551 May 13 at 21:48
  • And how much of that do you get in per query approximately? – bugmenot123 May 14 at 8:50
1

As you correctly identified, you need to cut up the contour lines. This is a common issue with contour lines in any GIS. The process to do this is also called "dicing", and usually involves a regular grid overlay to cut up the very long contour lines.

I think all you need to know is in this web page:

http://www.bostongis.com/blog/index.php?/archives/35-Map-Dicing-and-other-stuff.html

Make sure you properly re-index and analyze the layer after having it diced.

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.