The Database Structure of OpenStreetBrowser is based on a pgsql data import with Osmosis. Therefore you get the following tables:
|nodes||id (bigint), version (int), user_id (int), tstamp (timestamp), changeset_id (bigint), geom (geometry, srid 4326)||Holds newest version of all nodes with their geometry in Lat/Long|
|node_tags||node_id (bigint), k (text), v (text)||Holds all tags of all nodes|
This structure is not very efficient, as it would be better to have a "flat" database structure, where you have a table with one line containing all information of one object (say node, line, polygon ...). That's why osm2pgsql imports a database structure like this:
|planet_osm_point||osm_id (bigint), amenity (text), highway (text), cycleway (text), ...., way (geometry, srid 900913)||A table having one column for each tag which was specified before where at least one tag is not null|
The problem with this database layout is, that you have to know which tag-keys you will need before importing the data. Also there's a lot of information missing like the user_id and changeset_id. Good thing: One line per each object.
So the preprocessing of OpenStreetBrowser generates tables with the following layout:
|osm_point||osm_id (text), osm_tags (hstore), osm_way (geom, srid 900913)||Holds every node, which has at least one tag<super>1</super>|
|osm_line||osm_id (text), osm_tags (hstore), osm_way (geom, srid 900913)||Holds every way in the database|
|osm_polygon||osm_id (text), osm_tags (hstore), osm_way (geom, srid 900913)||Holds every closed way (start node equals end node) but is not member of a multipolygon relation as a simple polygon and an id similar to "way_1234" AND every multipolygon relation with an id similar to "rel_4321".|
|osm_rel||osm_id (text), osm_tags (hstore), osm_way (geom, srid 900913)||Holds every relation with the geometry of all relation members (nodes and ways, excluding relations) merged together.|
There might be additional columns, e.g. to speed up queries, but these are the "main" columns. Here's some information about the columns:
|osm_id||An ID based on the original ID, e.g. "node_1234", "way_1234" or "rel_1234". If several objects get merged together by functions they will be collected, seperated by semi colon, e.g.: "node_1234;node_2345;way_123".|
|osm_tags||A hstore object containing all tags.|
|osm_way||A geometry in 900913 SRID.|
As index I mainly use a mulitcolumn (gist) index, of osm_way and osm_tags. Accessing large areas (like all place=city in an area the size of Europe) takes quite a long time, it's better to use only the index on osm_tags and a sequential scan on the location. Accessing an area which was not accessed some time before can take a minute, I guess it has to load the index from disk.