|
|
| Line 1: |
Line 1: |
| − | {{GlossaryTermTemp}} | + | {{GlossaryTermTemp |
| − | The following database rules explain the usage of the database called oedb, which will become a part of the openmod internet presence. For further details see also [[Database|here]].<br/><br/><br/> | + | |SubtermOf=Database |
| − | | + | }} |
| − | = Open Data only<br/> =
| + | The following database rules explain the basic principles of the database called oedb, which will become a part of the openmod internet presence. For further details see also [[Database|here]].<br/> |
| − | | + | |
| − | <span lang="EN-US">Do not upload restricted data into the database!</span>
| + | |
| − | | + | |
| | | | |
| | + | = Open Data<br/> = |
| | | | |
| | + | *Only use data with suitable licences<br/> |
| | + | *<span lang="EN-US">Do not upload restricted data into the database</span><br/> |
| | | | |
| | = Data Documentation<br/> = | | = Data Documentation<br/> = |
| | | | |
| − | *All data included in the databases has be documented! On this wiki page you learn, how to do so.<br/> | + | *All data included in the databases has be documented! On this wiki page you learn how to do so<br/> |
| − | *All abreviations have to be documentated in the glossary of the openmod wiki. | + | *All abreviations have to be documentated in the [[Category:Glossary|Glossary]] of the openmod wiki<br/> |
| − | | + | |
| − | <br/> | + | |
| − | | + | |
| | | | |
| | = Naming of Data<br/> = | | = Naming of Data<br/> = |
| | | | |
| − | The data in the database is organised in schemata and tables, which names are important to find around.<br/><br/> | + | *The nomenclature should follow a logical structure<br/> |
| − | | + | *There are different levels of naming<br/> |
| − | <br/> | + | |
| | | | |
| | == Database Name<br/> == | | == Database Name<br/> == |
| | | | |
| − | The name of the database is '''oedb.'''<br/> | + | *The name of the database is <br/> |
| − | | + | |
| − | <br/>
| + | |
| − | | + | |
| | | | |
| | == Database Schema<br/> == | | == Database Schema<br/> == |
| | | | |
| − | The structure of the database is also realised via schemata.
| + | *There are schemas to structure tables |
| − | | + | *The following schemas are already in existence:<br/> |
| − | The naming follows the following rules:<br/> | + | |
| − | | + | |
| − | *always lower case<br/>
| + | |
| − | *no points, no commas
| + | |
| − | *no spaces
| + | |
| − | *no dates
| + | |
| − | *use underscores
| + | |
| − | | + | |
| − | <br/>
| + | |
| − | | + | |
| − | *The schema is categorized into the the type of the schema:<br/>
| + | |
| | **demand<br/> | | **demand<br/> |
| | **economic | | **economic |
| Line 50: |
Line 32: |
| | **environmental | | **environmental |
| | **grid | | **grid |
| | + | **openstreetmap |
| | **political_boundary | | **political_boundary |
| | + | **scenario |
| | **social | | **social |
| | **supply | | **supply |
| − | **scenario
| |
| | **weather | | **weather |
| − | **model_draft | + | **<u>model_draft</u> |
| − | **reference<br/> | + | **<u>reference</u><br/> |
| | | | |
| − | <br/>
| |
| | | | |
| − | Further schema categories or change can be discussed under Discussion.
| |
| − |
| |
| − | <br/>
| |
| | | | |
| | + | Further schema categories or changes can be [https://github.com/openego/oeplatform/issues discussed on GitHub]. There is a whitelist. |
| | | | |
| | == Database Table == | | == Database Table == |
| Line 77: |
Line 57: |
| | The table name consists of | | The table name consists of |
| | | | |
| − | #name starts with the source or model name (e.g. zensus, ego) | + | #name starts with the source, project or model name (e.g. zensus, eGo) |
| | #main value (e.g. population) | | #main value (e.g. population) |
| | #if separated by [attribute] (e.g. by_gender) | | #if separated by [attribute] (e.g. by_gender) |
| Line 84: |
Line 64: |
| | Example: ''zensus_population_by_gender_per_mun''<br/><br/> | | Example: ''zensus_population_by_gender_per_mun''<br/><br/> |
| | | | |
| − | <br/>
| + | Remember to put your abbreviations in the [[Category:Glossary|Glossary.]] |
| − | | + | |
| − | <br/>
| + | |
| | | | |
| | = Data Integrity<br/> = | | = Data Integrity<br/> = |
| Line 123: |
Line 101: |
| | <br/> | | <br/> |
| | | | |
| | + | <br/> |
| | | | |
| | = Data Referencing<br/> = | | = Data Referencing<br/> = |
| Line 138: |
Line 117: |
| | <br/> | | <br/> |
| | | | |
| − | === <s>Comments on Schema<br/></s> === | + | === <s>Comments on Schema</s><br/> === |
| | | | |
| − | <s>Schemata are used in the database in order to structure tables, functions, assign assignment of permissions and much more. For the documentation purpose a string in json format is set as comment on schema. The attribute names begins with an <u>upper case</u> and are given by the following definition for schemata<span style="font-weight: normal">. </span><br/></s> | + | <s>Schemata are used in the database in order to structure tables, functions, assign assignment of permissions and much more. For the documentation purpose a string in json format is set as comment on schema. The attribute names begins with an <u>upper case</u> and are given by the following definition for schemata<span style="font-weight: normal">.</span></s><br/> |
| | | | |
| − | <s><br/></s>
| + | <br/> |
| | | | |
| | {| style="width: 643px" cellspacing="1" cellpadding="1" | | {| style="width: 643px" cellspacing="1" cellpadding="1" |
| − | |+ <s>Metadata Documentation of schemata<br/></s> | + | |+ <s>Metadata Documentation of schemata</s><br/> |
| | |- valign="top" | | |- valign="top" |
| − | ! scope="col" style="border-width: 1px medium 1px 1px; border-style: solid none solid solid; border-color: rgb(0, 0, 0) -moz-use-text-color rgb(0, 0, 0) rgb(0, 0, 0); padding: 0.1cm 0cm 0.1cm 0.1cm; width: 198px; height: 15px" | <div><s>Attribute (level 1)<br/></s></div> | + | ! scope="col" style="border-width: 1px medium 1px 1px; border-style: solid none solid solid; border-color: rgb(0, 0, 0) -moz-use-text-color rgb(0, 0, 0) rgb(0, 0, 0); padding: 0.1cm 0cm 0.1cm 0.1cm; width: 198px; height: 15px" | <div><s>Attribute (level 1)</s><br/></div> |
| − | ! scope="col" style="border-width: 1px medium 1px 1px; border-style: solid none solid solid; border-color: rgb(0, 0, 0) -moz-use-text-color rgb(0, 0, 0) rgb(0, 0, 0); padding: 0.1cm 0cm 0.1cm 0.1cm; width: 131px" | <div><s>Attribute (level 2)<br/></s></div> | + | ! scope="col" style="border-width: 1px medium 1px 1px; border-style: solid none solid solid; border-color: rgb(0, 0, 0) -moz-use-text-color rgb(0, 0, 0) rgb(0, 0, 0); padding: 0.1cm 0cm 0.1cm 0.1cm; width: 131px" | <div><s>Attribute (level 2)</s><br/></div> |
| − | ! scope="col" style="width: 291px; border: 1px solid rgb(0, 0, 0); padding: 0.1cm" | <div><s>Description<br/></s></div> | + | ! scope="col" style="width: 291px; border: 1px solid rgb(0, 0, 0); padding: 0.1cm" | <div><s>Description</s><br/></div> |
| | |- valign="top" | | |- valign="top" |
| − | | style="height: 15px; width: 168px; border-width: medium medium 1px 1px; border-style: none none solid solid; border-color: -moz-use-text-color -moz-use-text-color rgb(0, 0, 0) rgb(0, 0, 0); padding: 0cm 0cm 0.1cm 0.1cm; width: 198px" | <div><s>Name<br/></s></div> | + | | style="height: 15px; width: 168px; border-width: medium medium 1px 1px; border-style: none none solid solid; border-color: -moz-use-text-color -moz-use-text-color rgb(0, 0, 0) rgb(0, 0, 0); padding: 0cm 0cm 0.1cm 0.1cm; width: 198px" | <div><s>Name</s><br/></div> |
| − | | style="border-width: medium medium 1px 1px; border-style: none none solid solid; border-color: -moz-use-text-color -moz-use-text-color rgb(0, 0, 0) rgb(0, 0, 0); padding: 0cm 0cm 0.1cm 0.1cm; width: 131px; height: 15px" | <div><s><br/></s></div> | + | | style="border-width: medium medium 1px 1px; border-style: none none solid solid; border-color: -moz-use-text-color -moz-use-text-color rgb(0, 0, 0) rgb(0, 0, 0); padding: 0cm 0cm 0.1cm 0.1cm; width: 131px; height: 15px" | <div><br/></div> |
| | | style="width: 291px; border-width: medium 1px 1px; border-style: none solid solid; border-color: -moz-use-text-color rgb(0, 0, 0) rgb(0, 0, 0); padding: 0cm 0.1cm 0.1cm; height: 15px" | <div><s>Original name of the data set</s></div> | | | style="width: 291px; border-width: medium 1px 1px; border-style: none solid solid; border-color: -moz-use-text-color rgb(0, 0, 0) rgb(0, 0, 0); padding: 0cm 0.1cm 0.1cm; height: 15px" | <div><s>Original name of the data set</s></div> |
| | |- valign="top" | | |- valign="top" |
| | | style="width: 168px; border-width: medium medium 1px 1px; border-style: none none solid solid; border-color: -moz-use-text-color -moz-use-text-color rgb(0, 0, 0) rgb(0, 0, 0); padding: 0cm 0cm 0.1cm 0.1cm; width: 198px" | <div><s>Description</s></div> | | | style="width: 168px; border-width: medium medium 1px 1px; border-style: none none solid solid; border-color: -moz-use-text-color -moz-use-text-color rgb(0, 0, 0) rgb(0, 0, 0); padding: 0cm 0cm 0.1cm 0.1cm; width: 198px" | <div><s>Description</s></div> |
| − | | style="border-width: medium medium 1px 1px; border-style: none none solid solid; border-color: -moz-use-text-color -moz-use-text-color rgb(0, 0, 0) rgb(0, 0, 0); padding: 0cm 0cm 0.1cm 0.1cm; width: 131px" | <div><s><br/></s></div> | + | | style="border-width: medium medium 1px 1px; border-style: none none solid solid; border-color: -moz-use-text-color -moz-use-text-color rgb(0, 0, 0) rgb(0, 0, 0); padding: 0cm 0cm 0.1cm 0.1cm; width: 131px" | <div><br/></div> |
| | | style="width: 291px; border-width: medium 1px 1px; border-style: none solid solid; border-color: -moz-use-text-color rgb(0, 0, 0) rgb(0, 0, 0); padding: 0cm 0.1cm 0.1cm" | <div><s>Short description of the data set.</s></div> | | | style="width: 291px; border-width: medium 1px 1px; border-style: none solid solid; border-color: -moz-use-text-color rgb(0, 0, 0) rgb(0, 0, 0); padding: 0cm 0.1cm 0.1cm" | <div><s>Short description of the data set.</s></div> |
| | |- valign="top" | | |- valign="top" |
| | | style="width: 168px; border-width: medium medium 1px 1px; border-style: none none solid solid; border-color: -moz-use-text-color -moz-use-text-color rgb(0, 0, 0) rgb(0, 0, 0); padding: 0cm 0cm 0.1cm 0.1cm; width: 198px" | <div><s>Changes</s></div> | | | style="width: 168px; border-width: medium medium 1px 1px; border-style: none none solid solid; border-color: -moz-use-text-color -moz-use-text-color rgb(0, 0, 0) rgb(0, 0, 0); padding: 0cm 0cm 0.1cm 0.1cm; width: 198px" | <div><s>Changes</s></div> |
| − | | style="border-width: medium medium 1px 1px; border-style: none none solid solid; border-color: -moz-use-text-color -moz-use-text-color rgb(0, 0, 0) rgb(0, 0, 0); padding: 0cm 0cm 0.1cm 0.1cm; width: 131px" | <div><s><br/></s></div> | + | | style="border-width: medium medium 1px 1px; border-style: none none solid solid; border-color: -moz-use-text-color -moz-use-text-color rgb(0, 0, 0) rgb(0, 0, 0); padding: 0cm 0cm 0.1cm 0.1cm; width: 131px" | <div><br/></div> |
| | | style="width: 291px; border-width: medium 1px 1px; border-style: none solid solid; border-color: -moz-use-text-color rgb(0, 0, 0) rgb(0, 0, 0); padding: 0cm 0.1cm 0.1cm" | <div><s>Information of changes and user</s></div> | | | style="width: 291px; border-width: medium 1px 1px; border-style: none solid solid; border-color: -moz-use-text-color rgb(0, 0, 0) rgb(0, 0, 0); padding: 0cm 0.1cm 0.1cm" | <div><s>Information of changes and user</s></div> |
| | |- valign="top" | | |- valign="top" |
| − | | rowspan="4" style="width: 168px; border-width: medium medium 1px 1px; border-style: none none solid solid; border-color: -moz-use-text-color -moz-use-text-color rgb(0, 0, 0) rgb(0, 0, 0); padding: 0cm 0cm 0.1cm 0.1cm; width: 198px" | <div><s><br/></s></div> | + | | rowspan="4" style="width: 168px; border-width: medium medium 1px 1px; border-style: none none solid solid; border-color: -moz-use-text-color -moz-use-text-color rgb(0, 0, 0) rgb(0, 0, 0); padding: 0cm 0cm 0.1cm 0.1cm; width: 198px" | <div><br/></div> |
| | | style="border-width: medium medium 1px 1px; border-style: none none solid solid; border-color: -moz-use-text-color -moz-use-text-color rgb(0, 0, 0) rgb(0, 0, 0); padding: 0cm 0cm 0.1cm 0.1cm; width: 131px" | <div><s>Name</s></div> | | | style="border-width: medium medium 1px 1px; border-style: none none solid solid; border-color: -moz-use-text-color -moz-use-text-color rgb(0, 0, 0) rgb(0, 0, 0); padding: 0cm 0cm 0.1cm 0.1cm; width: 131px" | <div><s>Name</s></div> |
| | | style="width: 291px; border-width: medium 1px 1px; border-style: none solid solid; border-color: -moz-use-text-color rgb(0, 0, 0) rgb(0, 0, 0); padding: 0cm 0.1cm 0.1cm" | <div><s>Name of Person created or changed dataset</s></div> | | | style="width: 291px; border-width: medium 1px 1px; border-style: none solid solid; border-color: -moz-use-text-color rgb(0, 0, 0) rgb(0, 0, 0); padding: 0cm 0.1cm 0.1cm" | <div><s>Name of Person created or changed dataset</s></div> |
| Line 170: |
Line 149: |
| | | style="width: 291px; border-width: medium 1px 1px; border-style: none solid solid; border-color: -moz-use-text-color rgb(0, 0, 0) rgb(0, 0, 0); padding: 0cm 0.1cm 0.1cm" | <div><s>E-mail Address</s></div> | | | style="width: 291px; border-width: medium 1px 1px; border-style: none solid solid; border-color: -moz-use-text-color rgb(0, 0, 0) rgb(0, 0, 0); padding: 0cm 0.1cm 0.1cm" | <div><s>E-mail Address</s></div> |
| | |- valign="top" | | |- valign="top" |
| − | | style="border-width: medium medium 1px 1px; border-style: none none solid solid; border-color: -moz-use-text-color -moz-use-text-color rgb(0, 0, 0) rgb(0, 0, 0); padding: 0cm 0cm 0.1cm 0.1cm; width: 131px" | <div><s>Date<br/></s></div> | + | | style="border-width: medium medium 1px 1px; border-style: none none solid solid; border-color: -moz-use-text-color -moz-use-text-color rgb(0, 0, 0) rgb(0, 0, 0); padding: 0cm 0cm 0.1cm 0.1cm; width: 131px" | <div><s>Date</s><br/></div> |
| | | style="width: 291px; border-width: medium 1px 1px; border-style: none solid solid; border-color: -moz-use-text-color rgb(0, 0, 0) rgb(0, 0, 0); padding: 0cm 0.1cm 0.1cm" | <div><s>Date of changes</s></div> | | | style="width: 291px; border-width: medium 1px 1px; border-style: none solid solid; border-color: -moz-use-text-color rgb(0, 0, 0) rgb(0, 0, 0); padding: 0cm 0.1cm 0.1cm" | <div><s>Date of changes</s></div> |
| | |- valign="top" | | |- valign="top" |
| − | | style="border-width: medium medium 1px 1px; border-style: none none solid solid; border-color: -moz-use-text-color -moz-use-text-color rgb(0, 0, 0) rgb(0, 0, 0); padding: 0cm 0cm 0.1cm 0.1cm; width: 131px" | <div><s>Comment<br/></s></div> | + | | style="border-width: medium medium 1px 1px; border-style: none none solid solid; border-color: -moz-use-text-color -moz-use-text-color rgb(0, 0, 0) rgb(0, 0, 0); padding: 0cm 0cm 0.1cm 0.1cm; width: 131px" | <div><s>Comment</s><br/></div> |
| | | style="width: 291px; border-width: medium 1px 1px; border-style: none solid solid; border-color: -moz-use-text-color rgb(0, 0, 0) rgb(0, 0, 0); padding: 0cm 0.1cm 0.1cm" | <div><s>Comment of changes</s></div> | | | style="width: 291px; border-width: medium 1px 1px; border-style: none solid solid; border-color: -moz-use-text-color rgb(0, 0, 0) rgb(0, 0, 0); padding: 0cm 0.1cm 0.1cm" | <div><s>Comment of changes</s></div> |
| | |- valign="top" | | |- valign="top" |
| − | | style="width: 168px; border-width: medium medium 1px 1px; border-style: none none solid solid; border-color: -moz-use-text-color -moz-use-text-color rgb(0, 0, 0) rgb(0, 0, 0); padding: 0cm 0cm 0.1cm 0.1cm; width: 198px" | <div><s>Note<br/></s></div> | + | | style="width: 168px; border-width: medium medium 1px 1px; border-style: none none solid solid; border-color: -moz-use-text-color -moz-use-text-color rgb(0, 0, 0) rgb(0, 0, 0); padding: 0cm 0cm 0.1cm 0.1cm; width: 198px" | <div><s>Note</s><br/></div> |
| − | | style="border-width: medium medium 1px 1px; border-style: none none solid solid; border-color: -moz-use-text-color -moz-use-text-color rgb(0, 0, 0) rgb(0, 0, 0); padding: 0cm 0cm 0.1cm 0.1cm; width: 131px" | <div><s><br/></s></div> | + | | style="border-width: medium medium 1px 1px; border-style: none none solid solid; border-color: -moz-use-text-color -moz-use-text-color rgb(0, 0, 0) rgb(0, 0, 0); padding: 0cm 0cm 0.1cm 0.1cm; width: 131px" | <div><br/></div> |
| − | | style="width: 291px; border-width: medium 1px 1px; border-style: none solid solid; border-color: -moz-use-text-color rgb(0, 0, 0) rgb(0, 0, 0); padding: 0cm 0.1cm 0.1cm" | <div><s>Field for notes or ToDo which are not done yet<br/></s></div> | + | | style="width: 291px; border-width: medium 1px 1px; border-style: none solid solid; border-color: -moz-use-text-color rgb(0, 0, 0) rgb(0, 0, 0); padding: 0cm 0.1cm 0.1cm" | <div><s>Field for notes or ToDo which are not done yet</s><br/></div> |
| | |} | | |} |
| | | | |
| − | <s><br/>[https://github.com/openego/data_processing/blob/master/data_documentation/documentaion_on_schema_example.sql SQL Json Example]</s>
| + | <br/><s>[https://github.com/openego/data_processing/blob/master/data_documentation/documentaion_on_schema_example.sql SQL Json Example]</s> |
| | | | |
| | <br/> | | <br/> |
The following database rules explain the basic principles of the database called oedb, which will become a part of the openmod internet presence. For further details see also here.
"Data integrity refers to maintaining and assuring the accuracy and consistency of data over its entire life-cycle." (Wikipedia) These two aspects belong to the data quality criteria, the data in the oedb should meet.
There are several useful attributes on a table users have to know in order to work with the contained data efficiently. Therefore, ensuring the quality of these meta data is as important as ensuring the quality of the data itself (see also here).
Comments on a table are json-dictionary as strings that describe the overall structure of the corresponding table. For every field the corresponding unit (in case of numerical values) and a description that specifies the contained data should be attached. The attribute names begins with an upper case and are given by the following definition of Database Tables. The Json meta documentation string is stored as table comment on the database table.
Original Data (orig) are defined as data from one source which are not further processed (see: Database Schema). The metadata Documentation for those data sets are set as a comment on the table. Following meta data are proposed:
In addition to the meta documentation described above, data tables (blue table in figure) which include data from several sources get a ref_id column. The ref_id links each row with a json documentation string (ref_data_json) in the database table public.reference_to_entries (green table in figure).This table also includes the ref_data_json column which links to all sources of the row to be referenced (blue). The ref_data_json string includes one jabref_entries_id for each source used. The jabref_entries_id is matched by one entries_id belonging to a source stored in the public.entries table (orange table).
Each row in the reference_to_entries table (green) contains a ref_data_json documentation string which links to the meta data in the Jabref table public.entries (orange).
The comments on a row aim to enhance the reproducabilty of data that is present in the database by stating the underlying data that was used to compute this row as well as the applied computational methods.
During these processes some kinds of uncertainities might occur as well as the decissions (assumtions) that were made to deal with them. Examplatory, some values might be missing amid this row and a linear development during this period was assumed to fix this gap. Each row has to be annotated by a json dictionary that must contain the following fields:
