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.4246…
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(a)lists.wikimedia.org>rg>.
Peter