Re: [OSM-dev] OSM Database schema

[Lorenzo Stucchi via dev]

Thank you, Martin.

This is exactly what I was searching for. In the meanwhile, I put all the table 
in the wiki table if someone is interested in complete the description of the 
full schema. 

Best,
Lorenzo

> Il giorno 14 gen 2020, alle ore 11:25, Martin Raifer  ha 
> scritto:
> 
> I believe the formula just implements the interleaving of the X and Y
> coordinates used in the QuadTiles[1] indexing schema in an efficient
> way by using some clever bit operations.
> 
> [1] https://wiki.openstreetmap.org/wiki/QuadTiles#Quadtile_implementation
> 
> On Tue, Jan 14, 2020 at 11:11 AM Lorenzo Stucchi
>  wrote:
>> 
>> Hi all,
>> 
>> I have almost complete the explanation of the schema regarding the element 
>> (node, way and relation) I have just one last element to be understood.
>> 
>> The “tile” in the node table. I found in the code a function called 
>> “public.tile_for_point” [1] and seems that should be the one that do this 
>> process. But I’m not able to understood which is the output of the function, 
>> in particular what this part of code does:
>> 
>> x := (x | (x << 8)) & 16711935; — 0x00FF00FF
>> 
>> Thanks for all the reply.
>> 
>> Best,
>> Lorenzo
>> 
>> [1] 
>> https://github.com/openstreetmap/openstreetmap-website/blob/751e8ad32c864f50eae9e9359d74a11dc44de007/db/structure.sql#L160
>> 
>> Il giorno 10 gen 2020, alle ore 15:51, Lorenzo Stucchi 
>>  ha scritto:
>> 
>> Thanks for the reply.
>> 
>> Is this the formula [1] ?
>> 
>> Best,
>> Lorenzo
>> 
>> [1] 
>> https://github.com/openstreetmap/openstreetmap-website/blob/751e8ad32c864f50eae9e9359d74a11dc44de007/db/structure.sql#L160
>> 
>> Il giorno 10 gen 2020, alle ore 15:22, Frederik Ramm  
>> ha scritto:
>> 
>> Hi,
>> 
>> On 10.01.20 15:08, Lorenzo Stucchi wrote:
>> 
>> What does it mean the “timestamp” present in the table (“node", “way" and 
>> “relation")?
>> 
>> 
>> When the object was last changed.
>> 
>> What does it mean the “tile” element present in the “node" table?
>> 
>> 
>> An integer derived from the lat/lon of the node using a mathematical
>> formula.
>> 
>> What does it mean the “sequence_id” in the “way_nodes” table and 
>> “relation_member” table?
>> 
>> 
>> An integer used to store the ordering of the nodes/members (which node
>> is the first, the second, ... in the way).
>> 
>> Bye
>> Frederik
>> 
>> --
>> Frederik Ramm  ##  eMail frede...@remote.org  ##  N49°00'09" E008°23'33"
>> 
>> ___
>> dev mailing list
>> dev@openstreetmap.org
>> https://lists.openstreetmap.org/listinfo/dev
>> 
>> 
>> ___
>> dev mailing list
>> dev@openstreetmap.org
>> https://lists.openstreetmap.org/listinfo/dev
>> 
>> 
>> ___
>> dev mailing list
>> dev@openstreetmap.org
>> https://lists.openstreetmap.org/listinfo/dev

___
dev mailing list
dev@openstreetmap.org
https://lists.openstreetmap.org/listinfo/dev

Re: [OSM-dev] OSM Database schema

[Martin Raifer]

I believe the formula just implements the interleaving of the X and Y
coordinates used in the QuadTiles[1] indexing schema in an efficient
way by using some clever bit operations.

[1] https://wiki.openstreetmap.org/wiki/QuadTiles#Quadtile_implementation

On Tue, Jan 14, 2020 at 11:11 AM Lorenzo Stucchi
 wrote:
>
> Hi all,
>
> I have almost complete the explanation of the schema regarding the element 
> (node, way and relation) I have just one last element to be understood.
>
> The “tile” in the node table. I found in the code a function called 
> “public.tile_for_point” [1] and seems that should be the one that do this 
> process. But I’m not able to understood which is the output of the function, 
> in particular what this part of code does:
>
> x := (x | (x << 8)) & 16711935; — 0x00FF00FF
>
> Thanks for all the reply.
>
> Best,
> Lorenzo
>
> [1] 
> https://github.com/openstreetmap/openstreetmap-website/blob/751e8ad32c864f50eae9e9359d74a11dc44de007/db/structure.sql#L160
>
> Il giorno 10 gen 2020, alle ore 15:51, Lorenzo Stucchi 
>  ha scritto:
>
> Thanks for the reply.
>
> Is this the formula [1] ?
>
> Best,
> Lorenzo
>
> [1] 
> https://github.com/openstreetmap/openstreetmap-website/blob/751e8ad32c864f50eae9e9359d74a11dc44de007/db/structure.sql#L160
>
> Il giorno 10 gen 2020, alle ore 15:22, Frederik Ramm  ha 
> scritto:
>
> Hi,
>
> On 10.01.20 15:08, Lorenzo Stucchi wrote:
>
> What does it mean the “timestamp” present in the table (“node", “way" and 
> “relation")?
>
>
> When the object was last changed.
>
> What does it mean the “tile” element present in the “node" table?
>
>
> An integer derived from the lat/lon of the node using a mathematical
> formula.
>
> What does it mean the “sequence_id” in the “way_nodes” table and 
> “relation_member” table?
>
>
> An integer used to store the ordering of the nodes/members (which node
> is the first, the second, ... in the way).
>
> Bye
> Frederik
>
> --
> Frederik Ramm  ##  eMail frede...@remote.org  ##  N49°00'09" E008°23'33"
>
> ___
> dev mailing list
> dev@openstreetmap.org
> https://lists.openstreetmap.org/listinfo/dev
>
>
> ___
> dev mailing list
> dev@openstreetmap.org
> https://lists.openstreetmap.org/listinfo/dev
>
>
> ___
> dev mailing list
> dev@openstreetmap.org
> https://lists.openstreetmap.org/listinfo/dev

___
dev mailing list
dev@openstreetmap.org
https://lists.openstreetmap.org/listinfo/dev

Re: [OSM-dev] OSM Database schema

[Lorenzo Stucchi]

Hi all,

I have almost complete the explanation of the schema regarding the element 
(node, way and relation) I have just one last element to be understood.

The “tile” in the node table. I found in the code a function called 
“public.tile_for_point” [1] and seems that should be the one that do this 
process. But I’m not able to understood which is the output of the function, in 
particular what this part of code does:

x := (x | (x << 8)) & 16711935; — 0x00FF00FF

Thanks for all the reply.

Best,
Lorenzo

[1] 
https://github.com/openstreetmap/openstreetmap-website/blob/751e8ad32c864f50eae9e9359d74a11dc44de007/db/structure.sql#L160

Il giorno 10 gen 2020, alle ore 15:51, Lorenzo Stucchi 
mailto:lorenzostucch...@outlook.it>> ha scritto:

Thanks for the reply.

Is this the formula [1] ?

Best,
Lorenzo

[1] 
https://github.com/openstreetmap/openstreetmap-website/blob/751e8ad32c864f50eae9e9359d74a11dc44de007/db/structure.sql#L160

Il giorno 10 gen 2020, alle ore 15:22, Frederik Ramm 
mailto:frede...@remote.org>> ha scritto:

Hi,

On 10.01.20 15:08, Lorenzo Stucchi wrote:
What does it mean the “timestamp” present in the table (“node", “way" and 
“relation")?

When the object was last changed.

What does it mean the “tile” element present in the “node" table?

An integer derived from the lat/lon of the node using a mathematical
formula.

What does it mean the “sequence_id” in the “way_nodes” table and 
“relation_member” table?

An integer used to store the ordering of the nodes/members (which node
is the first, the second, ... in the way).

Bye
Frederik

--
Frederik Ramm  ##  eMail frede...@remote.org  ##  
N49°00'09" E008°23'33"

___
dev mailing list
dev@openstreetmap.org
https://lists.openstreetmap.org/listinfo/dev

___
dev mailing list
dev@openstreetmap.org
https://lists.openstreetmap.org/listinfo/dev

___
dev mailing list
dev@openstreetmap.org
https://lists.openstreetmap.org/listinfo/dev

Re: [OSM-dev] OSM Database schema

[Lorenzo Stucchi]

Thanks for the reply.

Is this the formula [1] ?

Best,
Lorenzo

[1] 
https://github.com/openstreetmap/openstreetmap-website/blob/751e8ad32c864f50eae9e9359d74a11dc44de007/db/structure.sql#L160

Il giorno 10 gen 2020, alle ore 15:22, Frederik Ramm 
mailto:frede...@remote.org>> ha scritto:

Hi,

On 10.01.20 15:08, Lorenzo Stucchi wrote:
What does it mean the “timestamp” present in the table (“node", “way" and 
“relation")?

When the object was last changed.

What does it mean the “tile” element present in the “node" table?

An integer derived from the lat/lon of the node using a mathematical
formula.

What does it mean the “sequence_id” in the “way_nodes” table and 
“relation_member” table?

An integer used to store the ordering of the nodes/members (which node
is the first, the second, ... in the way).

Bye
Frederik

--
Frederik Ramm  ##  eMail frede...@remote.org  ##  
N49°00'09" E008°23'33"

___
dev mailing list
dev@openstreetmap.org
https://lists.openstreetmap.org/listinfo/dev

___
dev mailing list
dev@openstreetmap.org
https://lists.openstreetmap.org/listinfo/dev

Re: [OSM-dev] OSM Database schema

[Frederik Ramm]

Hi,

On 10.01.20 15:08, Lorenzo Stucchi wrote:
> What does it mean the “timestamp” present in the table (“node", “way" and 
> “relation")?

When the object was last changed.

> What does it mean the “tile” element present in the “node" table?

An integer derived from the lat/lon of the node using a mathematical
formula.

> What does it mean the “sequence_id” in the “way_nodes” table and 
> “relation_member” table?

An integer used to store the ordering of the nodes/members (which node
is the first, the second, ... in the way).

Bye
Frederik

-- 
Frederik Ramm  ##  eMail frede...@remote.org  ##  N49°00'09" E008°23'33"

___
dev mailing list
dev@openstreetmap.org
https://lists.openstreetmap.org/listinfo/dev

Re: [OSM-dev] OSM Database schema

[Lorenzo Stucchi]

Dear all,

I have almost complete the description of the table that regards the element 
(node, way and relation). I have doubts remaining.

What does it mean the “timestamp” present in the table (“node", “way" and 
“relation")?

What does it mean the “tile” element present in the “node" table?

What does it mean the “sequence_id” in the “way_nodes” table and 
“relation_member” table?

Thanks to all for the replies.

Best,
Lorenzo


> Il giorno 9 gen 2020, alle ore 12:54, Lorenzo Stucchi 
>  ha scritto:
> 
> 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  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  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
>>>  ha scritto:
>>> 
>>> sent from a phone
>>> 
 On 4. Jan 2020, at 17:28, Jean Marie Falisse 
 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@openstreetmap.org
>>> https://lists.openstreetmap.org/listinfo/dev
>> ___
>> dev mailing list
>> dev@openstreetmap.org
>> 

Re: [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  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  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
 ha scritto:

sent from a phone


On 4. Jan 2020, at 17:28, Jean Marie Falisse 
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@openstreetmap.org
https://lists.openstreetmap.org/listinfo/dev

___
dev mailing list
dev@openstreetmap.org
https://lists.openstreetmap.org/listinfo/dev

___
dev mailing list
dev@openstreetmap.org
https://lists.openstreetmap.org/listinfo/dev



--
Tom Hughes (t...@compton.nu)
http://compton.nu/



--
Tom Hughes (t...@compton.nu)
http://compton.nu/





--
Tom Hughes (t...@compton.nu)
http://compton.nu/

___
dev mailing list
dev@openstreetmap.org
https://lists.openstreetmap.org/listinfo/dev

Re: [OSM-dev] OSM Database schema

[Lorenzo Stucchi]

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  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  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
>>  ha scritto:
>> 
>> sent from a phone
>> 
>>> On 4. Jan 2020, at 17:28, Jean Marie Falisse 
>>> 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@openstreetmap.org
>> https://lists.openstreetmap.org/listinfo/dev
> ___
> dev mailing list
> dev@openstreetmap.org
> https://lists.openstreetmap.org/listinfo/dev
 ___
 dev mailing list
 dev@openstreetmap.org
 https://lists.openstreetmap.org/listinfo/dev
>>> 
>>> 
>>> -- 
>>> Tom Hughes (t...@compton.nu)
>>> http://compton.nu/
> 
> 
> -- 
> Tom Hughes (t...@compton.nu)
> http://compton.nu/

___
dev mailing list
dev@openstreetmap.org
https://lists.openstreetmap.org/listinfo/dev

Re: [OSM-dev] OSM Database schema

[Tom Hughes]

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  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
 ha scritto:

sent from a phone


On 4. Jan 2020, at 17:28, Jean Marie Falisse 
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@openstreetmap.org
https://lists.openstreetmap.org/listinfo/dev

___
dev mailing list
dev@openstreetmap.org
https://lists.openstreetmap.org/listinfo/dev

___
dev mailing list
dev@openstreetmap.org
https://lists.openstreetmap.org/listinfo/dev



--
Tom Hughes (t...@compton.nu)
http://compton.nu/





--
Tom Hughes (t...@compton.nu)
http://compton.nu/

___
dev mailing list
dev@openstreetmap.org
https://lists.openstreetmap.org/listinfo/dev

Re: [OSM-dev] OSM Database schema

[Lorenzo Stucchi]

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  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
  ha scritto:
 
 sent from a phone
 
> On 4. Jan 2020, at 17:28, Jean Marie Falisse 
> 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@openstreetmap.org
 https://lists.openstreetmap.org/listinfo/dev
>>> ___
>>> dev mailing list
>>> dev@openstreetmap.org
>>> https://lists.openstreetmap.org/listinfo/dev
>> ___
>> dev mailing list
>> dev@openstreetmap.org
>> https://lists.openstreetmap.org/listinfo/dev
> 
> 
> -- 
> Tom Hughes (t...@compton.nu)
> http://compton.nu/

___
dev mailing list
dev@openstreetmap.org
https://lists.openstreetmap.org/listinfo/dev

Re: [OSM-dev] OSM Database schema

[Tom Hughes]

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
 ha scritto:

sent from a phone


On 4. Jan 2020, at 17:28, Jean Marie Falisse 
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@openstreetmap.org
https://lists.openstreetmap.org/listinfo/dev

___
dev mailing list
dev@openstreetmap.org
https://lists.openstreetmap.org/listinfo/dev


___
dev mailing list
dev@openstreetmap.org
https://lists.openstreetmap.org/listinfo/dev



--
Tom Hughes (t...@compton.nu)
http://compton.nu/

___
dev mailing list
dev@openstreetmap.org
https://lists.openstreetmap.org/listinfo/dev

Re: [OSM-dev] OSM Database schema

[Maarten Deen]

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
 ha scritto:

sent from a phone


On 4. Jan 2020, at 17:28, Jean Marie Falisse 
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@openstreetmap.org
https://lists.openstreetmap.org/listinfo/dev

___
dev mailing list
dev@openstreetmap.org
https://lists.openstreetmap.org/listinfo/dev


___
dev mailing list
dev@openstreetmap.org
https://lists.openstreetmap.org/listinfo/dev

Re: [OSM-dev] OSM Database schema

[Gerd Petermann]

Hi Lorenzo,

my understanding of the fields with ??:
- I think the version column was explained already. The combination of the 
version and the *_id together with the type (Node, Way,Relation) builds the 
uniquie id which identifies an object. So, you can have a way with id 2 and 
version 4 and a node with id 2 and version 4, but you cannot have two different 
nodes with id 2 and version 4.
- The sequence_id is needed to be able to restore the order of referenced 
objects.
- The timestamp fields probably contain the timestamp at which the object 
(version+id) was added to the database, but may also
be the timestamp at which the changeset was closed.
- num_changes : not sure, but I think it should be the number of changed or 
added objects in one changeset

Gerd


Von: Lorenzo Stucchi 
Gesendet: Donnerstag, 9. Januar 2020 11:29
An: dev@openstreetmap.org
Betreff: Re: [OSM-dev] OSM Database schema

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 
mailto:dieterdre...@gmail.com>> ha scritto:



sent from a phone

On 4. Jan 2020, at 17:28, Jean Marie Falisse 
mailto:fa003...@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@openstreetmap.org<mailto:dev@openstreetmap.org>
https://lists.openstreetmap.org/listinfo/dev


___
dev mailing list
dev@openstreetmap.org
https://lists.openstreetmap.org/listinfo/dev

Re: [OSM-dev] OSM Database schema

[Lorenzo Stucchi]

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 
mailto:dieterdre...@gmail.com>> ha scritto:



sent from a phone

On 4. Jan 2020, at 17:28, Jean Marie Falisse 
mailto:fa003...@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@openstreetmap.org
https://lists.openstreetmap.org/listinfo/dev

___
dev mailing list
dev@openstreetmap.org
https://lists.openstreetmap.org/listinfo/dev

Re: [OSM-dev] OSM Database schema

[Martin Koppenhoefer]

sent from a phone

> On 4. Jan 2020, at 17:28, Jean Marie Falisse  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@openstreetmap.org
https://lists.openstreetmap.org/listinfo/dev

Re: [OSM-dev] OSM Database schema

[Mateusz Konieczny]

AFAIK it never started and it was always at stage "it would be nice if someone 
would do something,
for example write some plan how this upgrade would be coordinated".

4 Jan 2020, 19:38 by fa003...@skynet.be:

> Ok, thanks.
>
> Does that also mean that this Area datatype task in “> 
> https://wiki.openstreetmap.org/wiki/Top_Ten_Tasks> ” is abandoned?
>
> Jean Marie Falisse
>
>
>
>> Le 4 janv. 2020 à 19:18, Stefan Keller <>> sfkel...@gmail.com>> > a écrit :
>>
>> Am Sa., 4. Jan. 2020 um 18:18 Uhr schrieb mmd <>> mmd@gmail.com>> >:
>>
>>> ... As an example check out:
>>> https://wiki.openstreetmap.org/wiki/User:Maxbe/Routen_%C3%BCber_Fl%C3%A4chen
>>>
>>
>> See also the research work from my lab >> https://eprints.hsr.ch/625/>>  and
>> https://github.com/PlazaRoute/plazaroute>>  .
>>
>> :Stefan
>>
>> Am Sa., 4. Jan. 2020 um 18:18 Uhr schrieb mmd <>> mmd@gmail.com>> >:
>>
>>>
>>> On 2020-01-04 17:22, Jean Marie Falisse wrote:
>>>
 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.

>>>
>>> Getting a bit off topic here... You can calculate some artificial ways
>>> and feed that into some router. As an example check out:
>>> https://wiki.openstreetmap.org/wiki/User:Maxbe/Routen_%C3%BCber_Fl%C3%A4chen
>>>
>>> --
>>>
>>>
>>>
>>> ___
>>> dev mailing list
>>> dev@openstreetmap.org
>>> https://lists.openstreetmap.org/listinfo/dev
>>>
>>
>> ___
>> dev mailing list
>> dev@openstreetmap.org
>> https://lists.openstreetmap.org/listinfo/dev
>>

___
dev mailing list
dev@openstreetmap.org
https://lists.openstreetmap.org/listinfo/dev

Re: [OSM-dev] OSM Database schema

[Jean Marie Falisse]

Ok, thanks.

Does that also mean that this Area datatype task in 
“https://wiki.openstreetmap.org/wiki/Top_Ten_Tasks” is abandoned?

Jean Marie Falisse


> Le 4 janv. 2020 à 19:18, Stefan Keller  a écrit :
> 
> Am Sa., 4. Jan. 2020 um 18:18 Uhr schrieb mmd :
>> ... As an example check out:
>> https://wiki.openstreetmap.org/wiki/User:Maxbe/Routen_%C3%BCber_Fl%C3%A4chen
> 
> See also the research work from my lab https://eprints.hsr.ch/625/ and
> https://github.com/PlazaRoute/plazaroute .
> 
> :Stefan
> 
> Am Sa., 4. Jan. 2020 um 18:18 Uhr schrieb mmd :
>> 
>> On 2020-01-04 17:22, Jean Marie Falisse wrote:
>>> 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.
>> 
>> Getting a bit off topic here... You can calculate some artificial ways
>> and feed that into some router. As an example check out:
>> https://wiki.openstreetmap.org/wiki/User:Maxbe/Routen_%C3%BCber_Fl%C3%A4chen
>> 
>> --
>> 
>> 
>> 
>> ___
>> dev mailing list
>> dev@openstreetmap.org
>> https://lists.openstreetmap.org/listinfo/dev
> 
> ___
> dev mailing list
> dev@openstreetmap.org
> https://lists.openstreetmap.org/listinfo/dev

___
dev mailing list
dev@openstreetmap.org
https://lists.openstreetmap.org/listinfo/dev

Re: [OSM-dev] OSM Database schema

[Stefan Keller]

Am Sa., 4. Jan. 2020 um 18:18 Uhr schrieb mmd :
> ... As an example check out:
> https://wiki.openstreetmap.org/wiki/User:Maxbe/Routen_%C3%BCber_Fl%C3%A4chen

See also the research work from my lab https://eprints.hsr.ch/625/ and
https://github.com/PlazaRoute/plazaroute .

:Stefan

Am Sa., 4. Jan. 2020 um 18:18 Uhr schrieb mmd :
>
> On 2020-01-04 17:22, Jean Marie Falisse wrote:
> > 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.
>
> Getting a bit off topic here... You can calculate some artificial ways
> and feed that into some router. As an example check out:
> https://wiki.openstreetmap.org/wiki/User:Maxbe/Routen_%C3%BCber_Fl%C3%A4chen
>
> --
>
>
>
> ___
> dev mailing list
> dev@openstreetmap.org
> https://lists.openstreetmap.org/listinfo/dev

___
dev mailing list
dev@openstreetmap.org
https://lists.openstreetmap.org/listinfo/dev

Re: [OSM-dev] OSM Database schema

[mmd]

On 2020-01-04 17:22, Jean Marie Falisse wrote:
> 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.

Getting a bit off topic here... You can calculate some artificial ways
and feed that into some router. As an example check out:
https://wiki.openstreetmap.org/wiki/User:Maxbe/Routen_%C3%BCber_Fl%C3%A4chen

-- 



___
dev mailing list
dev@openstreetmap.org
https://lists.openstreetmap.org/listinfo/dev

Re: [OSM-dev] OSM Database schema

[Jean Marie Falisse]

Hi,

Best wishes to all !

I am new to this list.

Lorenzo, I will be very interested in reading the result of your work.

Is it still true that in the OSM database, areas are not represented as such? 
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?

That would mean, Dear Lorenzo, that you are 藍 a designated volunteer for the 
database overhaul project !  See Area datatype on 
“https://wiki.openstreetmap.org/wiki/Top_Ten_Tasks”.

Just kidding. Still, I wouldn’t mind to be involved in this, especially if some 
programs could be written in the most beautiful programming language ever, 
Haskell of course. What you can do with this beast is just awesome.

Hope I didn’t disturb too much. Put it on the account of the enthusiasm of the 
youth!    Thanks for reading.

JMF


Jean Marie Falisse
Berensheide, 3
B 1170 Watermael-Boitsfort
Belgique
+32 2 673 32 22
+32 479 509899
jmfali...@acm.org
http://member.acm.org/~jmfalisse

> Le 4 janv. 2020 à 11:40, mmd  a écrit :
> 
> On 2020-01-04 09:59, Lorenzo Stucchi wrote:
>> Thank you Pascal. This is can be more precise than just I looking at the
>> visualisation [1].
> 
> I'm not aware of a comprehensive data model internals documentation
> beyond the actual source code in
> https://github.com/openstreetmap/openstreetmap-website, in particular
> the app/*/api directory
> 
>> 
>> But, for example, what is the “timestamp” in the “node" table? 
> 
> It refers to the current time in UTC when the node create/update/delete
> operation was executed. Please see
> https://github.com/openstreetmap/openstreetmap-website/blob/master/app/models/node.rb#L243
> 
> 
>> And what is the “redactions” table? 
> 
> It contains a list of reasons why an object has been redacted, see
> https://www.openstreetmap.org/redactions - the actual objects (such as
> nodes, ways, relations) refer to this redaction table via the redaction_id.
> 
>> Or, why in the “way_nodes” table there is
>> “version", but also it is just a link to the “nodes” table that contains
>> also the “version” attribute.
> 
> Basically there are two different sets of tables: the current_* ones,
> and the historic ones. "way_nodes" belongs to the historic set of
> tables, and you need the version attribute to know that a node was part
> of say version 2 of way 12345. On the other hand, the current_way_nodes
> table has no version attribute, as it assumes that it refers to the
> latest version of an object.
> 
>> 
>> For this reason, I was trying to look to an explanation of this schema
>> if it exists.
> 
> I think one good way to find out more about those different tables is to
> install a local version of the Rails port and do some edits via
> iD/JOSM/Potlatch. The Rails port is quite verbose and lists every single
> database operation on the console.
> 
> 
> -- 
> 
> 
> ___
> dev mailing list
> dev@openstreetmap.org
> https://lists.openstreetmap.org/listinfo/dev

___
dev mailing list
dev@openstreetmap.org
https://lists.openstreetmap.org/listinfo/dev

Re: [OSM-dev] OSM Database schema

[mmd]

On 2020-01-04 09:59, Lorenzo Stucchi wrote:
> Thank you Pascal. This is can be more precise than just I looking at the
> visualisation [1].

I'm not aware of a comprehensive data model internals documentation
beyond the actual source code in
https://github.com/openstreetmap/openstreetmap-website, in particular
the app/*/api directory

> 
> But, for example, what is the “timestamp” in the “node" table? 

It refers to the current time in UTC when the node create/update/delete
operation was executed. Please see
https://github.com/openstreetmap/openstreetmap-website/blob/master/app/models/node.rb#L243


> And what is the “redactions” table? 

It contains a list of reasons why an object has been redacted, see
https://www.openstreetmap.org/redactions - the actual objects (such as
nodes, ways, relations) refer to this redaction table via the redaction_id.

 > Or, why in the “way_nodes” table there is
> “version", but also it is just a link to the “nodes” table that contains
> also the “version” attribute.

Basically there are two different sets of tables: the current_* ones,
and the historic ones. "way_nodes" belongs to the historic set of
tables, and you need the version attribute to know that a node was part
of say version 2 of way 12345. On the other hand, the current_way_nodes
table has no version attribute, as it assumes that it refers to the
latest version of an object.

> 
> For this reason, I was trying to look to an explanation of this schema
> if it exists.

I think one good way to find out more about those different tables is to
install a local version of the Rails port and do some edits via
iD/JOSM/Potlatch. The Rails port is quite verbose and lists every single
database operation on the console.


-- 


___
dev mailing list
dev@openstreetmap.org
https://lists.openstreetmap.org/listinfo/dev

Re: [OSM-dev] OSM Database schema

[Lorenzo Stucchi]

Thank you Pascal. This is can be more precise than just I looking at the 
visualisation [1].

But, for example, what is the “timestamp” in the “node" table? And what is the 
“redactions” table? Or, why in the “way_nodes” table there is “version", but 
also it is just a link to the “nodes” table that contains also the “version” 
attribute.

For this reason, I was trying to look to an explanation of this schema if it 
exists.

Thanks,
Stucchi Lorenzo

[1] https://wiki.openstreetmap.org/w/images/5/58/OSM_DB_Schema_2016-12-13.svg

Il giorno 4 gen 2020, alle ore 09:36, Pascal Neis 
mailto:pas...@neis-one.org>> ha scritto:

Maybe this is what you are searching for?
https://github.com/openstreetmap/openstreetmap-website/blob/master/db/structure.sql



Am 04.01.20 um 09:21 schrieb Lorenzo Stucchi:
Hi Stefan,
Thank you for your links, but I was searching for more detailed information. I 
knew how OpenStreetMap works, and I'm contributing by years.
I know how the basic database schema works and for this reason I wrote here to 
understood if someone has more detailed explanation of all the table that I 
sent in the first mail. My idea was to explain this table, but some names are 
not so clear. The general description of the database in different paper is 
base on the response of the API, but this is not the real structure of the 
database, so, for this reason, I was searching for this more detailed 
description.
Thanks,
Stucchi Lorenzo
Il giorno 3 gen 2020, alle ore 21:13, Stefan Keller  ha 
scritto:

Ciao Lorenzo

I assume you're interested in a software developers perspective.
For this I've found e.g. "An Introduction to OpenStreetMap" by Mele
Sax-Barnett 2014 [1].
This is a little outdated but seems to be a rather road online overview.
In order to be up-to-date replace following links there:
* ignore e.g. http://market.weogeo.com/  -  and replace with
https://osmaxx.hsr.ch/
* Replace TileMill -  with https://maputnik.github.io/
* Replace slide "The Future: Vector tiles"  - e.g. with
https://openmaptiles.org/

That's all incomplete and just scratching the surface.
So pls. read up first what OSM is and - before all - edit something in
OSM (e.g. building addresses or shops you know) in order to understand
how OSM works.

:Stefan

[1] http://pdxmele.com/intro-osm/OSM_intro_workshop.pdf

Am Fr., 3. Jan. 2020 um 16:36 Uhr schrieb Lorenzo Stucchi
:

Dear all,

For my master thesis, I will work with OpenStreetMap, and I would like to 
describe the database structure of OSM accurately.

I found at this page [1] an image that describes the schema of the database, 
but elements in the table are not described. Instead, I found in this page [2] 
a description of the Node element, but there are not all the elements present 
in the image [1].

Exist a page that describes all the database schema?

Thank you very much,
Stucchi Lorenzo


[1] https://wiki.openstreetmap.org/wiki/Rails_port/Database_schema
[2] https://wiki.openstreetmap.org/wiki/Node
___
dev mailing list
dev@openstreetmap.org
https://lists.openstreetmap.org/listinfo/dev
___
dev mailing list
dev@openstreetmap.org
https://lists.openstreetmap.org/listinfo/dev


___
dev mailing list
dev@openstreetmap.org
https://lists.openstreetmap.org/listinfo/dev

Re: [OSM-dev] OSM Database schema

[Lorenzo Stucchi]

Hi Stefan,

Thank you for your links, but I was searching for more detailed information. I 
knew how OpenStreetMap works, and I'm contributing by years. 

I know how the basic database schema works and for this reason I wrote here to 
understood if someone has more detailed explanation of all the table that I 
sent in the first mail. My idea was to explain this table, but some names are 
not so clear. The general description of the database in different paper is 
base on the response of the API, but this is not the real structure of the 
database, so, for this reason, I was searching for this more detailed 
description.

Thanks,
Stucchi Lorenzo


> Il giorno 3 gen 2020, alle ore 21:13, Stefan Keller  ha 
> scritto:
> 
> Ciao Lorenzo
> 
> I assume you're interested in a software developers perspective.
> For this I've found e.g. "An Introduction to OpenStreetMap" by Mele
> Sax-Barnett 2014 [1].
> This is a little outdated but seems to be a rather road online overview.
> In order to be up-to-date replace following links there:
> * ignore e.g. http://market.weogeo.com/  -  and replace with
> https://osmaxx.hsr.ch/
> * Replace TileMill -  with https://maputnik.github.io/
> * Replace slide "The Future: Vector tiles"  - e.g. with
> https://openmaptiles.org/
> 
> That's all incomplete and just scratching the surface.
> So pls. read up first what OSM is and - before all - edit something in
> OSM (e.g. building addresses or shops you know) in order to understand
> how OSM works.
> 
> :Stefan
> 
> [1] http://pdxmele.com/intro-osm/OSM_intro_workshop.pdf
> 
> Am Fr., 3. Jan. 2020 um 16:36 Uhr schrieb Lorenzo Stucchi
> :
>> 
>> Dear all,
>> 
>> For my master thesis, I will work with OpenStreetMap, and I would like to 
>> describe the database structure of OSM accurately.
>> 
>> I found at this page [1] an image that describes the schema of the database, 
>> but elements in the table are not described. Instead, I found in this page 
>> [2] a description of the Node element, but there are not all the elements 
>> present in the image [1].
>> 
>> Exist a page that describes all the database schema?
>> 
>> Thank you very much,
>> Stucchi Lorenzo
>> 
>> 
>> [1] https://wiki.openstreetmap.org/wiki/Rails_port/Database_schema
>> [2] https://wiki.openstreetmap.org/wiki/Node
>> ___
>> dev mailing list
>> dev@openstreetmap.org
>> https://lists.openstreetmap.org/listinfo/dev


___
dev mailing list
dev@openstreetmap.org
https://lists.openstreetmap.org/listinfo/dev

Re: [OSM-dev] OSM Database schema

[Stefan Keller]

Ciao Lorenzo

I assume you're interested in a software developers perspective.
For this I've found e.g. "An Introduction to OpenStreetMap" by Mele
Sax-Barnett 2014 [1].
This is a little outdated but seems to be a rather road online overview.
In order to be up-to-date replace following links there:
* ignore e.g. http://market.weogeo.com/  -  and replace with
https://osmaxx.hsr.ch/
* Replace TileMill -  with https://maputnik.github.io/
* Replace slide "The Future: Vector tiles"  - e.g. with
https://openmaptiles.org/

That's all incomplete and just scratching the surface.
So pls. read up first what OSM is and - before all - edit something in
OSM (e.g. building addresses or shops you know) in order to understand
how OSM works.

:Stefan

[1] http://pdxmele.com/intro-osm/OSM_intro_workshop.pdf

Am Fr., 3. Jan. 2020 um 16:36 Uhr schrieb Lorenzo Stucchi
:
>
> Dear all,
>
> For my master thesis, I will work with OpenStreetMap, and I would like to 
> describe the database structure of OSM accurately.
>
> I found at this page [1] an image that describes the schema of the database, 
> but elements in the table are not described. Instead, I found in this page 
> [2] a description of the Node element, but there are not all the elements 
> present in the image [1].
>
> Exist a page that describes all the database schema?
>
> Thank you very much,
> Stucchi Lorenzo
>
>
> [1] https://wiki.openstreetmap.org/wiki/Rails_port/Database_schema
> [2] https://wiki.openstreetmap.org/wiki/Node
> ___
> dev mailing list
> dev@openstreetmap.org
> https://lists.openstreetmap.org/listinfo/dev

___
dev mailing list
dev@openstreetmap.org
https://lists.openstreetmap.org/listinfo/dev