This is a static archive of our old OSM Help Site. Please post any new questions and answers at community.openstreetmap.org.

Optimising Mapnik PostGIS queries

1

In my Mapnik stylesheet, I have a number of PostGIS queries like this:

SELECT ST_AsBinary("way") AS geom,"highway","ref" FROM ( SELECT way, highway, ref FROM planet_osm_line WHERE highway IN ('motorway','trunk','motorway_link','trunk_link','primary','primary_link') ORDER BY z_order ) AS data WHERE "way" && ST_SetSRID('BOX3D(616388.1960919927 5938851.34964824,792499.1092611345 6114962.262817382)'::box3d, 900913);

I can partly optimise this by creating an index on way and highway:

CREATE INDEX geo_z11 ON planet_osm_line USING gist(way) WHERE highway IN ('motorway','trunk','motorway_link','trunk_link','primary','primary_link')

However, the ORDER BY z_order is still slow, particularly in metatiles with large amounts of data.

Ideally I would like to change the index to USING GIST(way, z_order ASC) WHERE.... Unfortunately the btree_gist extension (required for this) doesn't support ASC/DESC.

Are there any other ways to speed the sorting?

asked 16 Jul '14, 23:39

Richard's gravatar image

Richard ♦
30.9k44279412
accept rate: 18%