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.

Improve this question
5
  • Better make it one question, either speeding up the PostGIS query OR the rendering.
    – bugmenot123
    May 13, 2019 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, 2019 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, 2019 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, 2019 at 21:48
  • And how much of that do you get in per query approximately?
    – bugmenot123
    May 14, 2019 at 8:50

1 Answer 1

Reset to default
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.

Improve this answer
2

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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