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 ♦
29.2k40259390
accept rate: 19%

Be the first one to answer this question!
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×326
×128
×26

question asked: 16 Jul '14, 23:39

question was seen: 5,113 times

last updated: 16 Jul '14, 23:39

powered by OSQA