|
1
|
|
|
"""The central module containing all code dealing with importing Zensus data. |
|
2
|
|
|
""" |
|
3
|
|
|
|
|
4
|
|
|
from urllib.request import urlretrieve |
|
5
|
|
|
import os |
|
6
|
|
|
import zipfile |
|
7
|
|
|
|
|
8
|
|
|
from egon.data import db, subprocess |
|
9
|
|
|
import egon.data.config |
|
10
|
|
|
|
|
11
|
|
|
|
|
12
|
|
|
def download_zensus_pop(): |
|
13
|
|
|
"""Download Zensus csv file on population per hectar grid cell.""" |
|
14
|
|
|
data_config = egon.data.config.datasets() |
|
15
|
|
|
zensus_population_config = data_config["zensus_population"][ |
|
16
|
|
|
"original_data" |
|
17
|
|
|
] |
|
18
|
|
|
|
|
19
|
|
|
target_file = os.path.join( |
|
20
|
|
|
os.path.dirname(__file__), zensus_population_config["target"]["path"] |
|
21
|
|
|
) |
|
22
|
|
|
|
|
23
|
|
|
if not os.path.isfile(target_file): |
|
24
|
|
|
urlretrieve(zensus_population_config["source"]["url"], target_file) |
|
25
|
|
|
|
|
26
|
|
|
|
|
27
|
|
|
def population_to_postgres(): |
|
28
|
|
|
"""Import Zensus population data to postgres database""" |
|
29
|
|
|
# Get information from data configuration file |
|
30
|
|
|
data_config = egon.data.config.datasets() |
|
31
|
|
|
zensus_population_orig = data_config["zensus_population"]["original_data"] |
|
32
|
|
|
zensus_population_processed = data_config["zensus_population"]["processed"] |
|
33
|
|
|
input_file = os.path.join( |
|
34
|
|
|
os.path.dirname(__file__), zensus_population_orig["target"]["path"] |
|
35
|
|
|
) |
|
36
|
|
|
|
|
37
|
|
|
# Read database configuration from docker-compose.yml |
|
38
|
|
|
docker_db_config = db.credentials() |
|
39
|
|
|
|
|
40
|
|
|
# Create target schema |
|
41
|
|
|
db.execute_sql( |
|
42
|
|
|
f"CREATE SCHEMA IF NOT EXISTS {zensus_population_processed['schema']};" |
|
43
|
|
|
) |
|
44
|
|
|
|
|
45
|
|
|
qualified_table = ( |
|
46
|
|
|
f"{zensus_population_processed['schema']}" |
|
47
|
|
|
f".{zensus_population_processed['table']}" |
|
48
|
|
|
) |
|
49
|
|
|
|
|
50
|
|
|
# Drop and create target table |
|
51
|
|
|
db.execute_sql(f"DROP TABLE IF EXISTS {qualified_table} CASCADE;") |
|
52
|
|
|
|
|
53
|
|
|
db.execute_sql( |
|
54
|
|
|
f"CREATE TABLE {qualified_table}" |
|
55
|
|
|
""" (gid SERIAL NOT NULL, |
|
56
|
|
|
grid_id character varying(254) NOT NULL, |
|
57
|
|
|
x_mp int, |
|
58
|
|
|
y_mp int, |
|
59
|
|
|
population smallint, |
|
60
|
|
|
geom_point geometry(Point,3035), |
|
61
|
|
|
geom geometry (Polygon, 3035), |
|
62
|
|
|
CONSTRAINT zensus_population_per_ha_pkey PRIMARY KEY (gid) |
|
63
|
|
|
); |
|
64
|
|
|
""" |
|
65
|
|
|
) |
|
66
|
|
|
|
|
67
|
|
|
with zipfile.ZipFile(input_file) as zf: |
|
68
|
|
|
for filename in zf.namelist(): |
|
69
|
|
|
zf.extract(filename) |
|
70
|
|
|
host = ["-h", f"{docker_db_config['HOST']}"] |
|
71
|
|
|
port = ["-p", f"{docker_db_config['PORT']}"] |
|
72
|
|
|
pgdb = ["-d", f"{docker_db_config['POSTGRES_DB']}"] |
|
73
|
|
|
user = ["-U", f"{docker_db_config['POSTGRES_USER']}"] |
|
74
|
|
|
command = [ |
|
75
|
|
|
"-c", |
|
76
|
|
|
rf"\copy {qualified_table} (grid_id, x_mp, y_mp, population)" |
|
77
|
|
|
rf" FROM '{filename}' DELIMITER ';' CSV HEADER;", |
|
78
|
|
|
] |
|
79
|
|
|
subprocess.run( |
|
80
|
|
|
["psql"] + host + port + pgdb + user + command, |
|
81
|
|
|
env={"PGPASSWORD": docker_db_config["POSTGRES_PASSWORD"]}, |
|
82
|
|
|
) |
|
83
|
|
|
|
|
84
|
|
|
os.remove(filename) |
|
|
|
|
|
|
85
|
|
|
|
|
86
|
|
|
db.execute_sql( |
|
87
|
|
|
f"UPDATE {qualified_table} zs" |
|
88
|
|
|
" SET geom_point=ST_SetSRID(ST_MakePoint(zs.x_mp, zs.y_mp), 3035);" |
|
89
|
|
|
) |
|
90
|
|
|
|
|
91
|
|
|
db.execute_sql( |
|
92
|
|
|
f"UPDATE {qualified_table} zs" |
|
93
|
|
|
""" SET geom=ST_SetSRID( |
|
94
|
|
|
(ST_MakeEnvelope(zs.x_mp-50,zs.y_mp-50,zs.x_mp+50,zs.y_mp+50)), |
|
95
|
|
|
3035 |
|
96
|
|
|
); |
|
97
|
|
|
""" |
|
98
|
|
|
) |
|
99
|
|
|
|
|
100
|
|
|
db.execute_sql( |
|
101
|
|
|
"CREATE INDEX destatis_zensus_population_per_ha_geom_idx ON" |
|
102
|
|
|
f" {qualified_table} USING gist (geom);" |
|
103
|
|
|
) |
|
104
|
|
|
|
|
105
|
|
|
db.execute_sql( |
|
106
|
|
|
"CREATE INDEX destatis_zensus_population_per_ha_geom_point_idx ON" |
|
107
|
|
|
f" {qualified_table} USING gist (geom_point);" |
|
108
|
|
|
) |
|
109
|
|
|
|
openego /
eGon-data
Loading history...