New subject: keeping the new osm database updated

11 May 2010


      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

introduction: the new osm database