Draft description of Database schema
In this page, I will try to describe the OpenStreetMap Database Schema. Fell free to improve my description and complete it.
The source of this description is based on this code, that is visualised in this image (with some error, consider always the code since it is updated).
The idea is to create a section for every table of the database. In the section, a wiki-table will describe the element present in that table.
I started putting a section for every table, but not all are filled, feel free to do it if you want.
If you just want to give a suggestion or a critic, feel free to do it here.
Tables
An element common in different tables is the visible parameter, when an object is created it is set to True (1) when a user wants to delete it, the raw is not deleted but the visible is set to False (0).
Changeset tables
changesets
| Element name |
Type |
Description
|
| id |
big integer |
unique identifier for every changeset
|
| user_id |
big integer |
unique identifier for the user that created the changeset
|
| created_at |
timestamp without time zone |
time at which the changeset was created
|
| min_lat |
integer
|
border of the bounding box in which the change are done
|
| max_lat |
integer
|
| min_lon |
integer
|
| max_lon |
integer
|
| closed_at |
timestamp without time zone |
time at which the changeset was closed. By default, each change operation sets the closed_at at the current timestamp + 1 hour.
Calling the changeset close endpoint will set the closed_at timestamp to NOW.
|
| num_changes |
integer |
number of create/modify/delete operations on nodes/ways/relations in changeset (currently up to 10'000)
|
| Element name |
Type |
Description
|
| id |
integer |
unique identifier of the comment inside a changeset
|
| changeset_id |
big integer |
identifier of the changeset at which the comment belongs
|
| author_id |
big integer |
identifier of the user that create the comment
|
| body |
text |
text of the comment
|
| created_at |
timestamp without time zone |
moment of the creation of the comment
|
| visible |
boolean |
parameter to show or not the changeset comment
|
changeset_tags
| Element name |
Type |
Description
|
| changeset_id |
big integer |
identifier of the changeset at which the tag belongs
|
| k |
character varying |
key of the tag
|
| v |
character varying |
value of the tag
|
Redaction table
Table to link some data that were hidden due to copyright reason or due to change of licence to ODbl.
redactions
| Element name |
Type |
Description
|
| id |
big integer |
unique identifier for every redaction
|
| title |
character varying |
title of the redaction
|
| description |
text |
description of the reasons of the redaction
|
| created_at |
timestamp without time zone |
time of initial creation of the redaction
|
| updated_at |
timestamp without time zone |
time of last change in the redaction
|
| user_id |
big integer |
User id that created this redaction entry
|
| description_format |
public.format_enum |
enumeration that describe the language of the description (can be:'html', 'markdown' (default) or 'text')
|
Element tables
For the element exists two types of tables, a simple one called with just its name (nodes) and another with a prefix current (current_nodes). The second table contains only the latest version of every element, instead the first contains all the history of the different version of the element.
A version of one element is identified uniquely with the combination of the id and the version, that are the primary key of the tables.
The identifier of the current table is no more called with the name of the element (node_id) but only with id.
In the tables below the element that are not present in the current tables are on italics.
Nodes tables
nodes
| Element name |
Type |
Description
|
| node_id |
big integer |
unique identifier for every node
|
| latitude |
integer |
latitude of the node (float value multiplied by 10000000)
|
| longitude |
integer |
longitude of the node (float value multiplied by 10000000)
|
| changeset_id |
big integer |
unique identifier for changeset in which the node is changed
|
| visible |
boolean |
flag to indicate if node is visible (or invisible)
|
| timestamp |
timestamp without time zone |
timestamp of the creation of the node version, based on UTC+0
|
| tile |
big integer |
a value that is the product of the QuadTiles indexing that divides all world in squares. The code is not easy to be understood, but the results it is clear in this image. Actual code resides in the quad_tile gem in the openstreetmap github repo.
|
| version |
big integer |
version of the node
|
| redaction_id |
integer |
identifier of the redaction at which the node is associated
|
node_tags
| Element name |
Type |
Description
|
| node_id |
big integer |
unique identifier of the node at which the tag is associated
|
| k |
character varying |
key of the tag, restricted to 255 unicode characters by the API
|
| v |
character varying |
value of the tag, restricted to 255 unicode characters by the API
|
| version |
big integer |
version of the node at which the tag is associated
|
Ways tables
ways
| Element name |
Type |
Description
|
| way_id |
big integer |
unique identifier for every ways
|
| changeset_id |
big integer |
unique identifier for changeset in which the way is changed
|
| timestamp |
timestamp without time zone |
time of the creation of the way version
|
| version |
big integer |
version of the way
|
| visible |
boolean |
parameter to show or not the way
|
| redaction_id |
integer |
identifier of the redaction at which the way is associated
|
way_nodes
| Element name |
Type |
Description
|
| way_id |
big integer |
unique identifier for the way
|
| node_id |
big integer |
unique identifier for the node
|
| sequence_id |
big integer |
number that identify the order of the node inside the way
|
| version |
big integer |
version of the way at which the node is associated
|
way_tags
| Element name |
Type |
Description
|
| way_id |
big integer |
unique identifier of the way at which the tag is associated
|
| k |
character varying |
key of the tag
|
| v |
character varying |
value of the tag
|
| version |
big integer |
version of the way at which the tag is associated
|
Relation tables
relations
| Element name |
Type |
Description
|
| relation_id |
big integer |
unique identifier for every relation
|
| changeset_id |
big integer |
unique identifier for changeset in which the relation is changed
|
| timestamp |
timestamp without time zone |
time of the creation of the relation version
|
| version |
big integer |
version of the relation
|
| visible |
boolean |
parameter to show or not the relation
|
| redaction_id |
integer |
identifier of the redaction at which the relation is associated
|
relation_members
| Element name |
Type |
Description
|
| relation_id |
big integer |
unique identifier that link to the relation
|
| member_id |
big integer |
unique identifier that link to the element of the relation
|
| member_type |
nwr enumeration |
type of the element of the relation (can be: 'Node', 'Way' or 'Relation')
|
| member_role |
character varying |
role of the element in the relation (can be: 'inner', 'outer' or ... ), actual values are not checked by the API, value may be blank
|
| sequence_id |
integer |
number that identify the order of the member inside the relation
|
| version |
big integer |
version of the relation at which the member is associated
|
relation_tags
| Element name |
Type |
Description
|
| relation_id |
big integer |
unique identifier of the relation at which the tag is associated
|
| k |
character varying |
key of the tag
|
| v |
character varying |
value of the tag
|
| version |
big integer |
version of the relation at which the tag is associated
|
Notes tables
notes
| Element name |
Type |
Description
|
| id |
big integer |
unique identifier for every note
|
| latitude |
integer |
latitude of the note
|
| longitude |
integer |
longitude of the node
|
| tile |
big integer |
??
|
| updated_at |
timestamp without time zone |
time of the last change of the note
|
| created_at |
timestamp without time zone |
time of the creation of the note
|
| status |
note_status_enum |
status of the node (can be: 'open', 'closed' or 'hidden')
|
| closed_at |
timestamp without time zone |
time when the note is closed
|
| Element name |
Type |
Description
|
| id |
big integer |
unique identifier for every note comment
|
| note_id |
big integer |
unique identifier for the note at which the comment is refered
|
| visible |
boolean |
visibility of the note comment
|
| created_at |
timestamp without time zone |
time of the creation of the comment to the note
|
| author_ip |
inet |
IP address of the creator of the comment
|
| author_id |
big integer |
id of the user that created the note if logged in
|
| body |
text |
text of the comment
|
| event |
note_event_enum |
Action that the comment make on the note (can be: 'opened', 'closed', 'reopened', 'commented' or 'hidden')
|
GPX tables
gpx_files
| Element name |
Type |
Description
|
| id |
big integer |
unique identifier for every gpx file
|
| user_id |
big integer |
identifier of the user that create the file
|
| visible |
boolean |
visibility of the gpx file
|
| name |
character varying |
name of the gpx file
|
| size |
big integer |
size of the gpx file
|
| latitude |
double precision |
latitude of the first point in the gpx file
|
| longitude |
double precision |
longitude of the first point in the gpx file
|
| timestamp |
timestamp without time zone |
time when the file was upload ??
|
| description |
character varying |
faculty description of the gpx file
|
| inserted |
boolean |
??
|
| visibility |
gpx_visibility_enum |
visibility of the path of the gpx file (can be: 'private', 'public'(default), 'trackable' or 'identifiable')
|
gpx_file_tags
| Element name |
Type |
Description
|
| id |
big integer |
unique identifier for every gpx tag
|
| gpx_id |
big integer |
id of the gpx at which the tag is associated
|
| tag |
character varying |
??
|
gps_points
| Element name |
Type |
Description
|
| altitude |
double precision |
Height of the gpx point
|
| trackid |
integer |
??
|
| latitude |
integer |
latitude of the gpx point
|
| longitude |
integer |
longitude of the gpx point
|
| gpx_id |
big integer |
id of the gpx file at which the point belongs
|
| timestamp |
timestamp without time zone |
??
|
| tile |
big integer |
??
|
User tables
user
| Element name |
Type |
Description
|
| email |
character varying |
|
| id |
big integer |
|
| pass_crypt |
character varying |
|
| creation_time |
timestamp without time zone |
|
| display_name |
character varying |
|
| data_public |
boolean |
|
| description |
text |
|
| home_lat |
double precision |
|
| home_lon |
double precision |
|
| home_zoom |
smallint |
|
| pass_salt |
character varying |
|
| email_valid |
boolean |
|
| new_email |
character varying |
|
| creation_ip |
character varying |
|
| languages |
character varying |
|
| status |
user_status_enum |
|
| terms_agreed |
timestamp without time zone |
|
| consider_pd |
boolean |
|
| auth_uid |
character varying |
|
| preferred_editor |
character varying |
|
| terms_seen |
boolean |
|
| description_format |
format_enum |
|
| changesets_count |
integer |
|
| traces_count |
integer |
|
| diary_entries_count |
integer |
|
| image_use_gravatar |
boolean |
|
| auth_provider |
character varying |
|
| home_tile |
big integer |
|
| tou_agreed |
timestamp without time zone |
|
user_tokens
| Element name |
Type |
Description
|
| id |
big integer |
|
| user_id |
big integer |
|
| token |
character varying |
|
| expiry |
timestamp without time zone |
|
| referer |
text |
|
user_roles
| Element name |
Type |
Description
|
| id |
integer |
|
| user_id |
big integer |
|
| role |
user_role_enum |
|
| created_at |
timestamp without time zone |
|
| updated_at |
timestamp without time zone |
|
| granter_id |
big integer |
|
user_preferences
| Element name |
Type |
Description
|
| user_id |
big integer |
|
| k |
character varying |
|
| v |
character varying |
|
user_blocks
| Element name |
Type |
Description
|
| id |
integer |
|
| user_id |
big integer |
|
| creator_id |
big integer |
|
| reason |
text |
|
| ends_at |
timestamp without time zone |
|
| needs_view |
boolean |
|
| revoker_id |
big integer |
|
| created_at |
timestamp without time zone |
|
| updated_at |
timestamp without time zone |
|
| reason_format |
format_enum |
|
changesets_subscribers
| Element name |
Type |
Description
|
| subscriber_id |
big integer |
|
| changeset_id |
big integer |
|
messages
| Element name |
Type |
Description
|
| id |
big integer |
|
| from_user_id |
big integer |
|
| title |
character varying |
|
| body |
text |
|
| sent_on |
timestamp without time zone |
|
| message_read |
boolean |
|
| to_user_id |
big integer |
|
| to_user_visible |
boolean |
|
| from_user_visible |
boolean |
|
| body_format |
format_enum |
|
friends
| Element name |
Type |
Description
|
| id |
big integer |
|
| user_id |
big integer |
|
| friend_user_id |
big integer |
|
Diary tables
diary_entries
| Element name |
Type |
Description
|
| id |
big integer |
|
| user_id |
big integer |
|
| title |
character varying |
|
| body |
text |
|
| created_at |
timestamp without time zone |
|
| updated_at |
timestamp without time zone |
|
| latitude |
double precision |
|
| longitude |
double precision |
|
| language_code |
character varying |
|
| visible |
boolean |
|
| body_format |
public.format_enum |
|
| Element name |
Type |
Description
|
| id |
big integer |
|
| diary_entry_id |
big integer |
|
| user_id |
big integer |
|
| body |
text |
|
| created_at |
timestamp without time zone |
|
| updated_at |
timestamp without time zone |
|
| visible |
boolean |
|
| body_format |
public.format_enum |
|
diary_entry_subscriptions
| Element name |
Type |
Description
|
| user_id |
big integer |
|
| diary_entry_id |
big integer |
|
languages
| Element name |
Type |
Description
|
| code |
character varying |
|
| english_name |
character varying |
|
| native_name |
character varying |
|
Client tables
client_applications
| Element name |
Type |
Description
|
| id |
integer |
|
| name |
character varying |
|
| url |
character varying |
|
| support_url |
character varying |
|
| callback_url |
character varying |
|
| key |
character varying(50) |
|
| secret |
character varying(50) |
|
| user_id |
integer |
|
| created_at |
timestamp without time zone |
|
| updated_at |
timestamp without time zone |
|
| allow_read_prefs |
boolean |
|
| allow_write_prefs |
boolean |
|
| allow_write_diary |
boolean |
|
| allow_write_api |
boolean |
|
| allow_read_gpx |
boolean |
|
| allow_write_gpx |
boolean |
|
| allow_write_notes |
boolean |
|
oauth_tokens
| Element name |
Type |
Description
|
| id |
integer |
|
| user_id |
integer |
|
| type |
character varying(20) |
|
| client_application_id |
integer |
|
| token |
character varying(50) |
|
| secret |
character varying(50) |
|
| authorized_at |
timestamp without time zone |
|
| invalidated_at |
timestamp without time zone |
|
| created_at |
timestamp without time zone |
|
| updated_at |
timestamp without time zone |
|
| allow_read_prefs |
boolean |
|
| allow_write_prefs |
boolean |
|
| allow_write_diary |
boolean |
|
| allow_write_api |
boolean |
|
| allow_read_gpx |
boolean |
|
| allow_write_gpx |
boolean |
|
| callback_url |
character varying |
|
| verifier |
character varying(20) |
|
| scope |
character varying |
|
| valid_to |
timestamp without time zone |
|
| allow_write_notes |
boolean |
|
oauth_nonces
| Element name |
Type |
Description
|
| id |
integer |
|
| nonce |
character varying |
|
| timestamp |
integer |
|
| created_at |
timestamp without time zone |
|
| updated_at |
timestamp without time zone |
|
Others
acls
| Element name |
Type |
Description
|
| id |
big integer |
|
| address |
inet |
|
| k |
character varying |
|
| v |
character varying |
|
| domain |
character varying |
|
| mx |
character varying |
|
schema_migration
| Element name |
Type |
Description
|
| version |
character varying |
|
delayed_jobs
| Element name |
Type |
Description
|
| id |
big integer |
|
| priority |
integer |
|
| attempts |
integer |
|
| handler |
text |
|
| last_error |
text |
|
| run_at |
timestamp without time zone |
|
| locked_at |
timestamp without time zone |
|
| failed_at |
timestamp without time zone |
|
| locked_by |
character varying |
|
| queue |
character varying |
|
| created_at |
timestamp without time zone |
|
| updated_at |
timestamp without time zone |
|
active_storage_attachments
| Element name |
Type |
Description
|
| id |
big integer |
|
| name |
character varying |
|
| record_type |
character varying |
|
| record_id |
big integer |
|
| blob_id |
big integer |
|
| created_at |
timestamp without time zone |
|
active_storage_blobs
| Element name |
Type |
Description
|
| id |
big integer |
|
| key |
character varying |
|
| filename |
character varying |
|
| content_type |
character varying |
|
| metadata |
text |
|
| byte_size |
big integer |
|
| checksum |
character varying |
|
| created_at |
timestamp without time zone |
|
ar_internal_metadata
| Element name |
Type |
Description
|
| key |
character varying |
|
| value |
character varying |
|
| created_at |
timestamp(6) without time zone |
|
| updated_at |
timestamp(6) without time zone |
|
issues
| Element name |
Type |
Description
|
| id |
integer |
|
| reportable_type |
character varying |
|
| reportable_id |
integer |
|
| reported_user_id |
integer |
|
| status |
issue_status_enum |
|
| assigned_role |
user_role_enum |
|
| resolved_at |
timestamp without time zone |
|
| resolved_by |
integer |
|
| updated_by |
integer |
|
| reports_count |
integer |
|
| created_at |
timestamp without time zone |
|
| updated_at |
timestamp without time zone |
|
| Element name |
Type |
Description
|
| id |
integer |
|
| issue_id |
integer |
|
| user_id |
integer |
|
| body |
text |
|
| created_at |
timestamp without time zone |
|
| updated_at |
timestamp without time zone |
|
Sources
