Mercurial > dive4elements > river
comparison flys-backend/contrib/shpimporter/dgm.py @ 5210:908848b74d7e
SCHEME CHANGE: dgm now uses time intervals and ranges
author | Andre Heinecke <aheinecke@intevation.de> |
---|---|
date | Fri, 08 Mar 2013 18:15:48 +0100 |
parents | f459911fdbfb |
children | 76818dc2c937 |
comparison
equal
deleted
inserted
replaced
5209:850237ae9544 | 5210:908848b74d7e |
---|---|
1 # -*- coding: utf-8 -*- | 1 # -*- coding: utf-8 -*- |
2 | 2 |
3 import codecs | 3 import codecs |
4 import utils | 4 import utils |
5 import datetime | |
5 | 6 |
6 def latin(string): | 7 def latin(string): |
7 return unicode(string, "latin1") | 8 return unicode(string, "latin1") |
8 | 9 |
9 # <dbfield> : (<csvfield>, conversion function) | 10 # <dbfield> : (<csvfield>, conversion function) |
10 DGM_MAP = { | 11 DGM_MAP = { |
11 "lower" : ("km_von", lambda x: float(x)), | |
12 "upper" : ("km_bis", lambda x: float(x)), | |
13 "year_from" : "Jahr_von", | |
14 "year_to" : "Jahr_bis", | |
15 "projection" : "Projektion", | 12 "projection" : "Projektion", |
16 "elevation_state" : latin("Höhenstatus"), | 13 "elevation_state" : latin("Höhenstatus"), |
17 "format" : "Format", | 14 "format" : "Format", |
18 "border_break" : ("Bruchkanten", | 15 "border_break" : ("Bruchkanten", |
19 lambda x: True if x.lower() == "Ja" else False), | 16 lambda x: True if x.lower() == "Ja" else False), |
21 # "description" : | 18 # "description" : |
22 "srid" : "SRID", | 19 "srid" : "SRID", |
23 "path" : ("Pfad_Bestand", lambda x: x), | 20 "path" : ("Pfad_Bestand", lambda x: x), |
24 } | 21 } |
25 | 22 |
26 SQL_INSERT_DGT = "INSERT INTO dem (river_id, name, " + ", ".join(DGM_MAP.keys()) + \ | 23 SQL_INSERT_DGT = "INSERT INTO dem (river_id, name," \ |
27 ") VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)" | 24 " time_interval_id, range_id, " + ", ".join(DGM_MAP.keys()) + \ |
28 SQL_INSERT_DGT_ORA = "INSERT INTO dem (river_id, name, " + ", ".join(DGM_MAP.keys()) + \ | 25 ") VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)" |
29 ") VALUES (:s, :s, :s, :s, :s, :s, :s, :s, :s, :s, :s, :s, :s)" | 26 SQL_INSERT_DGT_ORA = "INSERT INTO dem (river_id, name," \ |
27 " time_interval_id, range_id, " + ", ".join(DGM_MAP.keys()) + \ | |
28 ") VALUES (:s, :s, :s, :s, :s, :s, :s, :s, :s, :s, :s)" | |
29 SQL_SELECT_TIME_ID = """ | |
30 SELECT id FROM time_intervals WHERE start_time = %s AND stop_time = %s | |
31 """ | |
32 SQL_INSERT_TIME_ID = """ | |
33 INSERT INTO time_intervals (id, start_time, stop_time) VALUES (%s, %s, %s) | |
34 """ | |
35 SQL_SELECT_TIME_ID_ORA = """ | |
36 SELECT id FROM time_intervals WHERE start_time = :s AND stop_time = :s | |
37 """ | |
38 SQL_INSERT_TIME_ID_ORA = """ | |
39 INSERT INTO time_intervals (id, start_time, stop_time) VALUES (:s, :s, :s) | |
40 """ | |
41 SQL_SELECT_RANGE_ID = """ | |
42 SELECT id FROM ranges WHERE river_id = %s AND a = %s AND b = %s | |
43 """ | |
44 SQL_INSERT_RANGE_ID = """ | |
45 INSERT INTO ranges (id, river_id, a, b) VALUES (%s, %s, %s, %s) | |
46 """ | |
47 SQL_SELECT_RANGE_ID_ORA = """ | |
48 SELECT id FROM ranges WHERE river_id = :s AND a = :s AND b = :s | |
49 """ | |
50 SQL_INSERT_RANGE_ID_ORA = """ | |
51 INSERT INTO ranges (id, river_id, a, b) VALUES (:s, :s, :s, :s) | |
52 """ | |
53 SQL_NEXT_ID = "SELECT coalesce(max(id), -1) + 1 FROM %s" | |
54 SQL_NEXT_ID_ORA = "SELECT coalesce(max(id), -1) + 1 FROM :s" | |
55 | |
56 | |
57 NEXT_IDS = {} | |
58 def next_id(cur, relation, oracle): | |
59 idx = NEXT_IDS.get(relation) | |
60 if idx is None: | |
61 if oracle: | |
62 cur.execute(SQL_NEXT_ID_ORA % relation) | |
63 else: | |
64 cur.execute(SQL_NEXT_ID % relation) | |
65 idx = cur.fetchone()[0] | |
66 NEXT_IDS[relation] = idx + 1 | |
67 return idx | |
68 | |
69 def get_range_id(cur, river_id, a, b, oracle): | |
70 if oracle: | |
71 cur.execute(SQL_SELECT_RANGE_ID_ORA, (river_id, a, b)) | |
72 else: | |
73 cur.execute(SQL_SELECT_RANGE_ID, (river_id, a, b)) | |
74 row = cur.fetchone() | |
75 if row: return row[0] | |
76 idx = next_id(cur, "ranges", oracle) | |
77 if oracle: | |
78 cur.execute(SQL_INSERT_RANGE_ID_ORA, (idx, river_id, a, b)) | |
79 else: | |
80 cur.execute(SQL_INSERT_RANGE_ID, (idx, river_id, a, b)) | |
81 cur.connection.commit() | |
82 return idx | |
83 | |
84 def get_time_interval_id(cur, a, b, oracle): | |
85 if oracle: | |
86 cur.execute(SQL_SELECT_TIME_ID_ORA, (a, b)) | |
87 else: | |
88 cur.execute(SQL_SELECT_TIME_ID, (a, b)) | |
89 row = cur.fetchone() | |
90 if row: return row[0] | |
91 idx = next_id(cur, "time_intervals", oracle) | |
92 if oracle: | |
93 cur.execute(SQL_INSERT_TIME_ID_ORA, (idx, a, b)) | |
94 else: | |
95 cur.execute(SQL_INSERT_TIME_ID, (idx, a, b)) | |
96 cur.connection.commit() | |
97 return idx | |
30 | 98 |
31 def insertRiverDgm(dbconn, dgmfile, river_name, dry_run, oracle): | 99 def insertRiverDgm(dbconn, dgmfile, river_name, dry_run, oracle): |
32 with codecs.open(dgmfile, "r", "latin1") as csvfile: | 100 with codecs.open(dgmfile, "r", "latin1") as csvfile: |
33 firstline = csvfile.readline() | 101 firstline = csvfile.readline() |
34 names = firstline.split(";") | 102 names = firstline.split(";") |
50 if isinstance(val, tuple): | 118 if isinstance(val, tuple): |
51 values.append(val[1](fields[namedict[val[0]]])) | 119 values.append(val[1](fields[namedict[val[0]]])) |
52 else: | 120 else: |
53 values.append(unicode.encode( | 121 values.append(unicode.encode( |
54 fields[namedict[val]], "UTF-8")) | 122 fields[namedict[val]], "UTF-8")) |
55 name = "%s KM %s - %s" % (river_name, fields[namedict["km_von"]], | 123 km_von = fields[namedict["km_von"]] |
56 fields[namedict["km_bis"]]) | 124 km_bis = fields[namedict["km_bis"]] |
125 year_from = datetime.datetime( | |
126 int(fields[namedict["Jahr_von"]]), 1, 1) | |
127 year_to = datetime.datetime( | |
128 int(fields[namedict["Jahr_bis"]]),1 ,1) | |
129 | |
130 name = "%s KM %s - %s" % (river_name, km_von, km_bis) | |
57 cur = dbconn.cursor() | 131 cur = dbconn.cursor() |
132 range_id = get_range_id(cur, river_id, float(km_von), | |
133 float(km_bis), oracle) | |
134 time_interval_id = get_time_interval_id(cur, year_from, | |
135 year_to, oracle) | |
136 | |
58 if oracle: | 137 if oracle: |
59 stmt = SQL_INSERT_DGT_ORA | 138 stmt = SQL_INSERT_DGT_ORA |
60 else: | 139 else: |
61 stmt = SQL_INSERT_DGT | 140 stmt = SQL_INSERT_DGT |
62 | 141 |
63 cur.execute(stmt, [river_id, name] + values) | 142 cur.execute(stmt, [river_id, name, time_interval_id, |
143 range_id] + values) | |
64 | 144 |
65 if not dry_run: | 145 if not dry_run: |
66 dbconn.commit() | 146 dbconn.commit() |
67 | 147 |