[Tile-serving] [osm2pgsql] Use GeoHash ordering instead of way ordering (#242)

[Paul Norman]

Creating a table as ORDER BY way is known to offer no performance advantages (#87) and in fact ahve losses in some cases. `ST_GeoHash` offers a better way to have geographically nearby data in the same or nearby pages.

Rather than simply doing an order on `ST_GeoHash(ST_Transform(way,4326))`, we can get a total of a 15% gain by using `ST_GeoHash(ST_Transform(ST_Envelope(way),4326),10)`.

##Benchmark Details##

The polygons table from planet-130904.osm.pbf was used, and a new table created.
```sql
CREATE TABLE polygon_test AS 
  SELECT * 
    FROM planet_osm_polygon 
    ORDER BY ST_GeoHash(ST_Transform(ST_Envelope(way),4326),<N>);
```
With this, we can see the change in ORDER time as the number of characters is varied, which leads to the selection of a 10 character geohash.
![image](https://cloud.githubusercontent.com/assets/1190866/5575881/7f84a612-8fa0-11e4-8e6d-a9e3b22e02d7.png)

Base time of `ST_GeoHash(ST_Transform(ST_Envelope(way),4326))` was 1848 seconds, and `ST_GeoHash(ST_Transform(way,4326))` was 2011 seconds.

There is a theoretical basis for preferring a geohash with an even number of characters, which this is.
You can merge this Pull Request by running:

  git pull https://github.com/pnorman/osm2pgsql geohash

Or you can view, comment on it, or merge it online at:

  https://github.com/openstreetmap/osm2pgsql/pull/242

-- Commit Summary --

  * Use GeoHash ordering instead of way ordering

-- File Changes --

    M table.cpp (2)

-- Patch Links --

https://github.com/openstreetmap/osm2pgsql/pull/242.patch
https://github.com/openstreetmap/osm2pgsql/pull/242.diff

---
Reply to this email directly or view it on GitHub:
https://github.com/openstreetmap/osm2pgsql/pull/242
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.openstreetmap.org/pipermail/tile-serving/attachments/20141229/2484753a/attachment.html>