[OSM-dev] Alternative PostgreSQL Schema
brett at bretth.com
Wed Oct 24 15:02:04 BST 2007
Patrick Weber wrote:
> Just a few comments from a general database perspective. I dont have
> PostgreSQL experience, but some SQL Server development experience. So
> forgive my ignorance if I point out obvious stuff.
I've used SQL Server a little and never used PostgreSQL so I'm probably
less qualified :-)
> Brett Henderson wrote:
>> 2. No duplication of data between history tables and current tables
>> other than the primary key and the visible flag.
> So the current tables are physical tables, not Views? Why the
> duplication of data, you could just create a View on the main
> nodes/way/relation table with only the current visible members?
An OSM data element's visible status may change many times over its
lifecycle. A visible flag of false is a logical delete, but records can
be undeleted on a subsequent version. The visible flag on the history
tables doesn't indicate which data is current. If set to true, the
record was a create or modify, if false it was a delete. OSM data types
can be created, modified, deleted, re-created, modified, and so on
throughout their lifecycle so just looking at the visible flag isn't
enough. You actually need to look at the latest versioned record and
then check the visible flag to know whether it is "current". It's a
fairly complicated query, not unlike what osmosis does when taking a
snapshot at a point in time. It's possible but likely to be very slow.
I originally started playing with a "current" flag on current records
but there was no simple way that I could find of constraining a single
current record per osm entity. In other words, for a given node I
couldn't find a way of making sure only one record in its history was
The other reason for the current table is that it provides a basis for
referential integrity between osm types. For example, a way can refer
to a node. But to implement this you need to consider that a single
version of a way refers to the latest version of a node. If you make
changes to the node resulting in a new node version you want the way to
point to the new version not the old one. If the node was deleted (ie.
a new record with visible flag set to false), you want that to result in
a referential integrity violation. Assuming the current table is
updated every time a new record is inserted in the main table, all of
the above conditions are met.
Having said all that, if there's another way to achieve the same result
with some additional constraints and a view then it would be worth
>> 3. Transaction support. This comes for free with PostgreSQL.
>> An ER diagram is available here:
>> I don't have a script yet because it was quicker to draw a diagram
>> than to actually create all of the table definitions.
>> The history tables cntain the only full copy of data.
>> Current tables only allow a single version of an entity to be active
>> at one time.
> geo spatial indexing sounds great. how far could one then go with
> spatial queries as well?
My knowledge of the geo-spatial stuff is very limited at the moment so
no idea :-) All I know is that for some reason a point can't be indexed
directly but some trickery with the "circle" function allows you to work
around that. In theory it should provide something similar to the
existing quad tiles in MySQL but I have no idea if that's how it will
work out. It might also be possible to add some geo-spatial information
to ways but that might be moving beyond the realms of the current OSM
>> * Current tables require a visible field which only exists so that
>> only visible data can be added to the current table. Can't think of
>> a better way to do this.
> Could you just get rid of visible all together and rely on the version
You could possibly replace a "current" flag with the version field, but
not the visible flag.
Hopefully that all makes sense.
More information about the dev