1

I'm trying to add elevation curves on an OpenStreetMap tile server I'm currently building. I created a table in PostgreSQL to store the shapes of the whole world. Structure was defined by gdal_contour tool as follows :

                                                              Table "public.contour100"
    Column    |           Type            | Collation | Nullable |                   Default                   | Storage | Stats target | Description 
--------------+---------------------------+-----------+----------+---------------------------------------------+---------+--------------+-------------
 ogc_fid      | integer                   |           | not null | nextval('contour100_ogc_fid_seq'::regclass) | plain   |              | 
 id           | numeric(8,0)              |           |          |                                             | main    |              | 
 height       | numeric(12,3)             |           |          |                                             | main    |              | 
 wkb_geometry | geometry(LineString,3857) |           |          |                                             | main    |              | 
Indexes:
    "contour100_pkey" PRIMARY KEY, btree (ogc_fid)
    "contour100_wkb_geometry_geom_idx" gist (wkb_geometry)

This table contains 45 341 726 rows.

My CartoCSS YAML project references this table like so :

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

When rendering tiles, Mapnik generates queries that look like this one :

SELECT ST_Transform(wkb_geometry, 3857) AS wkb_geometry, height
FROM contour100
WHERE wkb_geometry && ST_SetSRID('BOX3D(-3.402823466385289e+38 -3.402823466385289e+38,3.402823466385289e+38 3.402823466385289e+38)'::box3d, 3857)

This works and renders my elevation curves nicely, but takes several minutes to process the data.

Using EXPLAIN, PostgreSQL outputs this :

Seq Scan on contour100  (cost=0.00..4827531.68 rows=44987512 width=37)
   Filter: (wkb_geometry && '01030000A011[...]'::geometry)

As you can see, I have a spatial GIST index on my wkb_geometry column, but the query optimizer chooses a sequential scan. Being unfamiliar with geography indexing, I don't understand the reason why a sequential scan is performed.

I've run VACUUM ANALYSE on this table, and tried CLUSTER on the wkb_geometry column as well, but the GIST index was never used.

7
  • 1
    I think once you do a transform the index is useless
    – Ian Turton
    May 9, 2019 at 12:33
  • 1
    The transformed coordinates are not indexed, so a full table scan is required. Try adding a constraint that deprojects your bounding box or adding a covering index on the transformed data.
    – Vince
    May 9, 2019 at 12:38
  • 2
    also, don't transform, you colum is in SRID:3857
    – geozelot
    May 9, 2019 at 12:40
  • 1
    Doh! Yeah, this ^^^^^ too.
    – Vince
    May 9, 2019 at 13:06
  • 3
    Isn't that box rather large BOX3D(-3.402823466385289e+38 -3.402823466385289e+38,3.402823466385289e+38 3.402823466385289e+38)?
    – user30184
    May 9, 2019 at 14:25

1 Answer 1

1

In the end I was just missing the minzoom property in my layer definition. It was defined in the stylesheets only, so the query was executed on every zoom level regardless of it being used for the meta-tile generation.

My layer now looks like so :

- id: contour100
    geometry: linestring
    Datasource:
      type: "postgis"
      dbname: "myDatabase"
      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

Thanks for pointing me to the right direction !

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.