[OSM-dev] Alternative PostgreSQL Schema

Brett Henderson 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:
>> http://www.bretth.com/osm/pgsql_erd1.jpg
>> 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 
data model.
>> * 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 
> field?
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 mailing list