-
Notifications
You must be signed in to change notification settings - Fork 4
/
Copy pathreeem_db_setup_energypro.sql
228 lines (215 loc) · 10.7 KB
/
reeem_db_setup_energypro.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
/*
EnergyPRO Table Setup
EnergyPRO Input
EnergyPRO Output
__copyright__ = "© Reiner Lemoine Institut"
__license__ = "GNU Affero General Public License Version 3 (AGPL-3.0)"
__url__ = "https://www.gnu.org/licenses/agpl-3.0.en.html"
__author__ = "Ludwig Hülk"
__issue__ = "https://github.com/ReeemProject/reeem_db/issues/8"
* This file is part of project REEEM (https://github.com/ReeemProject/reeem_db).
* It's copyrighted by the contributors recorded in the version control history:
* ReeemProject/reeem_db/database_setup/reeem_db_setup_energypro.sql
*
* SPDX-License-Identifier: AGPL-3.0-or-later
*/
-- EnergyPRO Input
DROP TABLE IF EXISTS model_draft.reeem_energypro_input CASCADE;
CREATE TABLE model_draft.reeem_energypro_input (
"id" serial NOT NULL,
"nid" integer,
"dfid" integer,
"pathway" text,
"framework" text,
"version" text,
"region" text,
"year" smallint,
"field" text,
"category" text,
"indicator" text,
"value" double precision,
"unit" text,
"aggregation" boolean,
"tags" hstore,
"updated" timestamp with time zone,
"source" text,
"schema" text,
CONSTRAINT reeem_energypro_input_pkey PRIMARY KEY (id) );
-- access rights
ALTER TABLE model_draft.reeem_energypro_input OWNER TO reeem_user;
GRANT SELECT ON TABLE model_draft.reeem_energypro_input TO reeem_read WITH GRANT OPTION;
-- metadata
COMMENT ON TABLE model_draft.reeem_energypro_input IS
'{"title": "REEEM EnergyPRO Input",
"description": "Optimization of co- and trigeneration projects",
"language": [ "eng" ],
"spatial":
{"location": "erratic(PL-Warsaw;FI-Helsinki)",
"extent": "none",
"resolution": "city"},
"temporal":
{"reference_date": "2020",
"start": "2020",
"end": "2050",
"resolution": "erratic(2020;2030;2050)"},
"sources": [
{"name": "Times2017",
"description": "TimesPanEU Output data",
"url": "none",
"license": "Open Data Commons Attribution License 1.0 (ODC-BY-1.0)",
"copyright": "© Institut für Energiewirtschaft und Rationelle Energieanwendung (IER) der Universität Stuttgart"},
{"name": "Hast2018",
"description": "Transition to carbon neutral energy systems – implications to district heating in cities",
"url": "none",
"license": "none",
"copyright": "© 2018 IEEE"},
{"name": "Mäkelä2014",
"description": "Open district heating and marginal cost based heat pricing",
"url": "none",
"license": "none",
"copyright": "© Mäkelä"},
{"name": "Helistö2017",
"description": "Sensitivity of electricity prices in energy-only markets with large amounts of zero marginal cost generation",
"url": "https://ieeexplore.ieee.org/stamp/stamp.jsp?arnumber=7981893",
"license": "none",
"copyright": "© 2017 IEEE"},
{"name": "BiPRO2013",
"description": "Support to Member States in improving waste management based on assessment of Member States’ performance",
"url": "http://ec.europa.eu/environment/waste/framework/pdf/Final%20Report%20_130507.pdf",
"license": "none",
"copyright": "© BiPRO GmbH"},
{"name": "Parliament of Finland2016",
"description": "Hallituksen esitys eduskunnalle laeiksi nestemäisten polttoaineiden valmisteverosta sekä sähkön ja eräiden polttoaineiden valmisteverosta annettujen lakien liitteiden muuttamisesta",
"url": "none",
"license": "CC-BY-4.0",
"copyright": "© Parliament of Finland"},
{"name": "European Commission2017",
"description": "Excise duty tables - Part II Energy products and Electricity",
"url": "none",
"license": "none",
"copyright": "© European Commission"},
{"name": "Expert opinion2018",
"description": "Expert opinion",
"url": "none",
"license": "none",
"copyright": "none"}],
"license":
{"id": "CC-BY-4.0",
"name": "Creative Commons Attribution 4.0 International",
"version": "4.0",
"url": "http://creativecommons.org/licenses/by/4.0/legalcode",
"instruction": "You are free: To Share, To Create, To Adapt; As long as you: Attribute!",
"copyright": "© Aalto University"},
"contributors": [
{"name": "Ludee", "email": "none", "date": "2018-10-02", "comment": "Create table"},
{"name": "Ludee", "email": "none", "date": "2018-10-31", "comment": "Update metadata and license"},
{"name": "4lm", "email": "none", "date": "2019-01-31", "comment": "Alter table, add missing columns"},
{"name": "4lm", "email": "none", "date": "2019-05-14", "comment": "Add sources"},
{"name": "Bachibouzouk", "email": "none", "date": "2019-06-19", "comment": "Fix metadata"} ],
"resources": [
{"name": "model_draft.reeem_energypro_input",
"format": "PostgreSQL",
"fields": [
{"name": "id", "description": "Unique identifier", "unit": "none"},
{"name": "nid", "description": "Model id", "unit": "none"},
{"name": "dfid", "description": "Row id", "unit": "none"},
{"name": "pathway", "description": "REEEM pathway", "unit": "none"},
{"name": "framework", "description": "REEEM framework", "unit": "none"},
{"name": "version", "description": "REEEM version", "unit": "none"},
{"name": "region", "description": "Country", "unit": "none"},
{"name": "year", "description": "Year", "unit": "none"},
{"name": "field", "description": "2. classification", "unit": "none"},
{"name": "category", "description": "3. classification", "unit": "none"},
{"name": "indicator", "description": "Parameter name", "unit": "none"},
{"name": "value", "description": "Parameter value", "unit": "unit"},
{"name": "unit", "description": "Parameter unit", "unit": "none"},
{"name": "aggregation", "description": "True if aggregated", "unit": "none"},
{"name": "tags", "description": "Free classification", "unit": "none"},
{"name": "updated", "description": "Timestamp", "unit": "none"},
{"name": "source", "description": "Data source", "unit": "none"},
{"name": "schema", "description": "1. classification", "unit": "none"} ] } ],
"metadata_version": "1.3"}';
-- scenario log (version,io,schema_name,table_name,script_name,comment)
SELECT scenario_log('REEEM','v0.2.0','setup','model_draft','reeem_energypro_input','reeem_db_setup_energypro.sql',' ');
-- EnergyPRO Output
DROP TABLE IF EXISTS model_draft.reeem_energypro_output CASCADE;
CREATE TABLE model_draft.reeem_energypro_output (
"id" serial NOT NULL,
"nid" integer,
"dfid" integer,
"pathway" text,
"framework" text,
"version" text,
"region" text,
"year" smallint,
"month" smallint,
"year_month" text,
"field" text,
"category" text,
"indicator" text,
"value" double precision,
"unit" text,
"aggregation" boolean,
"tags" hstore,
"updated" timestamp with time zone,
"schema" text,
CONSTRAINT reeem_energypro_output_pkey PRIMARY KEY (id) );
-- access rights
ALTER TABLE model_draft.reeem_energypro_output OWNER TO reeem_user;
GRANT SELECT ON TABLE model_draft.reeem_energypro_output TO reeem_read WITH GRANT OPTION;
-- metadata
COMMENT ON TABLE model_draft.reeem_energypro_output IS
'{"title": "REEEM EnergyPRO Output",
"description": "none",
"language": [ "eng" ],
"spatial":
{"location": "erratic(PL-Warsaw;FI-Helsinki)",
"extent": "none",
"resolution": "city"},
"temporal":
{"reference_date": "2020",
"start": "2020",
"end": "2050",
"resolution": "erratic(2020;2030;2050)"},
"sources": [
{"name": "EnergyPro",
"description": "none",
"url": "https://openenergy-platform.org/factsheets/models/147/",
"license": "none",
"copyright": "EMD International A/S"} ],
"license":
{"id": "CC-BY-4.0",
"name": "Creative Commons Attribution 4.0 International",
"version": "4.0",
"url": "http://creativecommons.org/licenses/by/4.0/legalcode",
"instruction": "You are free: To Share, To Create, To Adapt; As long as you: Attribute!",
"copyright": "© Aalto University"},
"contributors": [
{"name": "Ludee", "email": "none", "date": "2018-10-25", "comment": "Create table"},
{"name": "Ludee", "email": "none", "date": "2018-10-31", "comment": "Update metadata and license"},
{"name": "4lm", "email": "none", "date": "2019-01-31", "comment": "Alter table, add missing columns"},
{"name": "4lm", "email": "none", "date": "2019-05-29", "comment": "Update metadata"} ],
"resources": [
{"name": "model_draft.reeem_energypro_output",
"format": "PostgreSQL",
"fields": [
{"name": "id", "description": "Unique identifier", "unit": "none"},
{"name": "nid", "description": "Model id", "unit": "none"},
{"name": "dfid", "description": "Row id", "unit": "none"},
{"name": "pathway", "description": "REEEM pathway", "unit": "none"},
{"name": "framework", "description": "REEEM framework", "unit": "none"},
{"name": "version", "description": "REEEM version", "unit": "none"},
{"name": "region", "description": "Country", "unit": "none"},
{"name": "month", "description": "Month", "unit": "none"},
{"name": "field", "description": "2. classification", "unit": "none"},
{"name": "category", "description": "3. classification", "unit": "none"},
{"name": "indicator", "description": "Parameter name", "unit": "none"},
{"name": "value", "description": "Parameter value", "unit": "unit"},
{"name": "unit", "description": "Parameter unit", "unit": "none"},
{"name": "aggregation", "description": "True if aggregated", "unit": "none"},
{"name": "tags", "description": "Free classification", "unit": "none"},
{"name": "updated", "description": "Timestamp", "unit": "none"},
{"name": "schema", "description": "1. classification", "unit": "none"} ] } ],
"metadata_version": "1.3"}';
-- scenario log (version,io,schema_name,table_name,script_name,comment)
SELECT scenario_log('REEEM','v0.2.0','setup','model_draft','reeem_energypro_output','reeem_db_setup_energypro.sql',' ');