[OSM-dev] OSM Database schema

[Tom Hughes]

Not quite - the history tables have all versions including
the current version.

So when a change is made to a node the current_nodes table is
changed to reflect the new details and a new record is added to
nodes with a new version number and the same details.

Yes, that is denormalised, but all real world databases are to
some extent ;-)

Tom

On 09/01/2020 11:54, Lorenzo Stucchi wrote:
> So the meaning is that the last version of a node is saved in a table called node_current, instead, all the old version are saved into a node table that has the number of the version?
> So the node is created in the current_node table, when it is modified the raw is moved into the nodes table and the current_nodes is updated with the new change. It is correct?
> 
> Thanks for pointing out this,
> Lorenzo
> 
>> Il giorno 9 gen 2020, alle ore 12:45, Tom Hughes <tom at compton.nu> ha scritto:
>>
>> Because 99% of the time all that is needed is the current version
>> of the object.
>>
>> Now you could probably do something clever with a flag to mark the
>> current version which was included in the index, or as a condition
>> on a partial index, so that you could efficiently find the current
>> versions but bear in mind this schema originated many years ago on
>> a different database engine with less capabilities.
>>
>> Basically a lot of what you see is history rather than design.
>>
>> Tom
>>
>> On 09/01/2020 11:39, Lorenzo Stucchi wrote:
>>> Thanks to both, for the redaction I forgot to add them, I initially skipped and after I forgot to put them.
>>> Thanks also Maarteen for the quick explanation of the parameters.
>>> Can you quickly explain the reason for having double tables for the element, like nodes and current_nodes? It is also related to the change in the license?
>>> Thanks,
>>> Lorenzo
>>>> Il giorno 9 gen 2020, alle ore 12:27, Tom Hughes <tom at compton.nu> ha scritto:
>>>>
>>>> There are redactions as well, when data has had to be removed and hidden
>>>> from the history for copyright reasons or whatever. There is a list:
>>>>
>>>> https://www.openstreetmap.org/redactions
>>>>
>>>> Tom
>>>>
>>>> On 09/01/2020 11:17, Maarten Deen wrote:
>>>>> Redaction_id will have bearing on the redaction bot https://wiki.openstreetmap.org/wiki/OSMF_Redaction_Bot
>>>>> Background: when OSM changed to ODbl, all changes made by people who did not agree had to be redacted.
>>>>> visible in the changeset will be the same as for node/way/relation: you can delete an item, and when it is deleted, visible=0.
>>>>> Maarten
>>>>> On 2020-01-09 11:29, Lorenzo Stucchi wrote:
>>>>>> Dear all,
>>>>>>
>>>>>> After the discussion that I started about the database schema I tried
>>>>>> to create a wiki page that explains it, I started the page on my user
>>>>>> wiki-page [1]. I started with few tables, but some elements present in
>>>>>> the tables are not so clear to me.
>>>>>>
>>>>>> So If you wanna try to contribute to that page, since a description of
>>>>>> the database can be provided to everyone. I will continue to modify it
>>>>>> ,trying to understand all the tables.
>>>>>>
>>>>>> Thanks to everyone that will help, or just make a suggestion about it.
>>>>>>
>>>>>>
>>>>>> Best,
>>>>>> Lorenzo
>>>>>>
>>>>>> [1]
>>>>>> https://wiki.openstreetmap.org/wiki/User:LorenzoStucchi/Description_DatabaseSchema
>>>>>>
>>>>>>
>>>>>>> Il giorno 4 gen 2020, alle ore 23:01, Martin Koppenhoefer
>>>>>>> <dieterdreist at gmail.com> ha scritto:
>>>>>>>
>>>>>>> sent from a phone
>>>>>>>
>>>>>>>> On 4. Jan 2020, at 17:28, Jean Marie Falisse <fa003029 at skynet.be>
>>>>>>>> wrote:
>>>>>>>>
>>>>>>>> Is it still true that in the OSM database, areas are not
>>>>>>>> represented as such?
>>>>>>>
>>>>>>> areas can be represented as areas through multipolygon relations
>>>>>>> which are always areas or by help of an additional tag
>>>>>>> (area=yes/no), or through plausibility (tags and their combinations
>>>>>>> may imply an area or not). There isn’t a dedicated area object,
>>>>>>> maybe this is what you meant. Areas are represented with ways, and
>>>>>>> tags or relations are required to define the ways as areas.
>>>>>>>
>>>>>>>> That would mean, for instance, that a pedestrian zone, let’s say
>>>>>>>> a big square in a city, cannot be made to be crossed diagonally
>>>>>>>> when used in a route planner. Am I right?
>>>>>>>
>>>>>>> typically routing engines operate on graphs, i.e. they do not route
>>>>>>> diagonally across areas, but this isn’t related to the question
>>>>>>> whether there is a dedicated datatype for areas or not.
>>>>>>>
>>>>>>> Cheers Martin
>>>>>>> _______________________________________________
>>>>>>> dev mailing list
>>>>>>> dev at openstreetmap.org
>>>>>>> https://lists.openstreetmap.org/listinfo/dev
>>>>>> _______________________________________________
>>>>>> dev mailing list
>>>>>> dev at openstreetmap.org
>>>>>> https://lists.openstreetmap.org/listinfo/dev
>>>>> _______________________________________________
>>>>> dev mailing list
>>>>> dev at openstreetmap.org
>>>>> https://lists.openstreetmap.org/listinfo/dev
>>>>
>>>>
>>>> -- 
>>>> Tom Hughes (tom at compton.nu)
>>>> http://compton.nu/
>>
>>
>> -- 
>> Tom Hughes (tom at compton.nu)
>> http://compton.nu/
> 


-- 
Tom Hughes (tom at compton.nu)
http://compton.nu/