[OSM-dev] [OSM-talk] Getting historic information about usage of a node

Tom Hughes tom at compton.nu
Wed Sep 16 08:36:30 BST 2009

On 16/09/09 01:54, Peter Körner wrote:

>> You would basically have to do something like:
>> - Find that node version in the nodes table
> to get the timestamp (or changeset?), right?

Yes, to get the timestamp. Actually you probably need to get the next 
version of the node (if any) as well so you have a range of timestamps 
when that version of the node was active.

>> - Find all way versions which have ever included that node
>> in the way_nodes table
> As way_nodes does not contain timestamp (changeset) infos you'll have to
> find *all versions* of *all ways* that ever contained that node, but for
> most of the nodes this is not too much i think (guessed: <10 ways in <30
> versions, so in worst case 300 items to do an integer comparison on, 10
> in best case)

Exactly - it's not too bad from a database point of view, as way_nodes 
does have an index on node_id.

>> - Look up those way versions in the ways table and match
>> the timestamps against the node to see if they were using
>> that version of the node or not
> You must go for all ways and get their timestamp (or changeset?) and
> compare it with the timestamp (changeset?) the version of that node was
> changed.

Yes - you want to consider each way version that you found in the 
previous step and see if it's lifetime overlaps with that of the node.

Once again you will need to fetch the next version as well to get the 
timestamp for the end of that way versions lifetime.

> This is no trivial task, I see. However there are some optimizations
> that could be done to make this a little faster:
> - maybe you could use changeset-ids rather then timestamps? I know
> changesets are no transactions and therefore this would not be as
> accurate as with timestamps but if it's faster it would be good
> enough for me ;)

I'm not sure using changesets (a) will work and (b) helps.

The problem is that I'm not sure how well ordered changesets are before 
the 0.6 API started and even after that transactions and overlapping 
changes might produce weird effects.

I don't think it will speed anything up anyway, so there probably isn't 
any point.


Tom Hughes (tom at compton.nu)

More information about the dev mailing list