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 ♦
30.9k●44●279●412
accept rate:
18%