Unlike traditional GIS data, which usually uses just a couple of attributes per spacial object, openstreetmaps comes with a free tagging scheme. In traditional database design it is impossible to store this type of data in a single table thus adding the need for joins in any single SQL request.
Starting from Version 9.0 PostgreSQL will however include an extension called hstore which is available as a backport for current Versions of PostgreSQL (8.3 and 8.4) and even as a debian package.
Using this extension an additional key/value table is no longer needed.
Already back in March I commited a patch for osm2pgsl which would allow for storage of tag objects inside such a hstore column.
An hstore is basically what is known as hash (perl) or dictionary (python) in scripting languages. A datatype for storage of key value pairs – well suited for storage of OSM data tags.
Talking about python I took me quite some time to figure out how to convert a hstore result from an SQL query so here is how it works:
q="select (each(tags)).key,(each(tags)).value from ... where ..." cur.execute(q) rows = cur.fetchall() hstore=dict(rows)
Back to the actual hstore issue…
In the meantime people are actually starting to use this stuff and MaZder even wrote a HOWTO document (currently in german language only) on howto set up such a database.
So here are the projects currently using hstore that I am aware of:
- Map Styles on the Wikimedia Toolserver
- Open Link Map
- Mail and Phone Map
- Open Brewpub Map (my own pet project)