[OSM-dev] Deriving Change Sets

Tom Hughes tom at compton.nu
Fri Jun 29 14:02:35 BST 2007

In message <46850086.3080405 at bretth.com>
        Brett Henderson <brett at bretth.com> wrote:

> Nodes
> If I just read the current_nodes table for records with timestamp values 
> within my range, I will receive a list of nodes that have been updated 
> in my time interval.  This will tell me which nodes have been deleted in 
> that time (where visible = 0), but for all other records I can't tell 
> the difference between a create and a modify.
> If I read the nodes table for records with timestamp values within my 
> range, I have the same problem because I still can't tell the difference 
> between creates and modifies.
> The only approach I can think of is as follows:
> - Get the ids of all nodes that have been modified in the time interval 
> using the current_nodes table.
> - For *each* id in the list, read all of its history from the nodes 
> table (ordered by timestamp) so that I can tell the difference between a 
> create and modify.

That sounds about right.

> Segments
> The same problems for nodes apply to segments.
> Ways
> Ways have a version column on the ways table which solves the problems 
> occurring for nodes and segments.  I can tell the difference between a 
> create and a modify based on the fact that the version of a create 
> record is always "1".
> Obtaining the records from the related tables (way_segments and 
> way_tags) is a little bit tricky but presumably I can get this data 
> using left outer joins to those tables.

You should be able to, yes. Both have id (and in the case of the 
history tables) version columns that can be used to join to them.

> If it is not possible to do this without a version column on the nodes 
> and segments tables, is there any reason (other than time and effort) 
> for a version column not to be added?

I don't know what the history of this is - presumably it is just that
ways were created later by which time somebody thought that an
explicit version was better? or was it just the need to join to
the tag and segment tables that led to it?

One problem with the version stuff is that it relies on MySQL, and
indeed on the ways table being a MyISAM table because it uses an
auto increment column as the second component of an index to get a
separate sequence for each id.

Adding version to nodes and segments is possible - the big problem
would be assigning versions to the existing records as there are
places where there are multiple history records for an object with
the same timestamp and hence no well defined ordering of them. It
would also probably need a chunk of downtime while the conversion
was being done.


Tom Hughes (tom at compton.nu)

More information about the dev mailing list