Hi folks,
I'm proud to announce the availability of a new osm database that holds all nodes, ways and polygons with all used tags used in osm.
It uses the postgresql hstore extension to form a "tags" column which is an indexed hash array of all tags. despite that it has the "ways"-column known from regular osm2pgsql databases and the normal tag-columns that are listed in osm2pgsql's default.style.
Because of this combined scheme it is adequate for a lot of queries. To take use of the indexes you'll need to follow some rules. I'll point out some of them and also give some examples on how to use the new scheme.
I'll switch the diff-update process from the classic scheme to the new combined scheme in the next days and drop the old scheme (tables starting with planet_osm) in about two weeks if no one protests. I'll take some days to catch up.
== a query in a (relative) small area == in this case the geometry-index is the most important one and you can use the tags-column as you wish:
SELECT tags FROM planet_point WHERE (tags->'man_made' = 'surveillance') AND way && ST_Transform(ST_SetSRID(ST_MakeBox2D( ST_Point(8.17,50.04), ST_Point(8.36,49.95) ), 4326),900913);
== a query on a rarely used tag in a large area == in such a case you want to use the index on the tags-column to identify the nodes. the index only knows "which node has which tag", means it doesn't know anything about the values. So to take use of the index you need two different conditions: one to filter for the tag and a second one for the tags value:
SELECT tags FROM planet_point WHERE (tags ? 'club-mate') AND (tags->'club-mate' = 'yes')
== a query on a heavy used tag == in the case of a heavy used tag (eg. amenity), the gin-index on the hstore column does not work very good, especially when the tag has a such huge number of different values, because (as we learned before) the index doesn't know anything about the value. In such a case its better to use the regular columns. anyway such a query is always a bad idea, if you don't specify a small enough bounding box, so don't run this query, please:
SELECT tags FROM planet_point WHERE amenity = 'restaurant'
== live statistics == a very cool feature of the hstore column is that you can get live tag usage statistics from it. please don't run this on the whole planet before talking about it on this list, as it will may drive the database-server down, but for small or medium sized regions, it's really cool:
SELECT key, count(*) FROM ( SELECT (each(tags)).key FROM planet_point WHERE way && ST_Transform(ST_SetSRID(ST_MakeBox2D( ST_Point(8.17,50.04), ST_Point(8.36,49.95) ), 4326),900913) ) AS stat GROUP BY key ORDER BY count DESC, key LIMIT 20;
== rendering == thanks to river we have a quite nice rendeing stack on willow. You can use the command line tool /home/project/o/s/m/osm/bin/render to test your own styles and if they're ready you can mail them to this list so we can configure renderd to render tiles for them.
To show the capabilities of the new hstore scheme in conjunction with the rendering system, I created a style that renders surveillance cctvs on a transparent overlay. It's a quite simple style but it shows how to do things. It's located at /home/project/o/s/m/osm/data/styles/surveillance/surveillance.xml and should be redable for everyone.
to render a map of germany in this style, just log on to willow and call
render --bbox 5.56,55.04,15.35,47.15 --style /home/mazder/styles/surveillance/surveillance.xml --size 2000x2000
it will create a map.png if the current directory. Call /home/project/o/s/m/osm/bin/render --help to learn about the possible parameters.
You'll want to add /home/project/o/s/m/osm/bin/ to your PATH in your .bash_profile so you can just all render without a path.
I also added the style to the renderd config and you can take a look at it under http://toolserver.org/~osm/styles/?zoom=12&lat=52.50546&lon=13.42461... The style is not perfect, the icons are too big but hey - its just a sample.
Thank you for reading. I'm happily looking forward in seeing cool map styles and useful tools. If you got questions, just ask at maps-l@lists.wikimedia.org.
Peter