Mercurial > dive4elements > river

annotate backend/contrib/shpimporter/dgm.py @ 9095:1b24fdbf7fe2

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Fixed flow velocity query by distinct clause to avoid duplicates
author mschaefer
date Fri, 25 May 2018 16:03:15 +0200
parents bb5bd48b80f6
children
rev   line source
5546
5b50247d4d99 Use latin1 as encoding in the dgm importer to make it easier matching latin1
Andre Heinecke <aheinecke@intevation.de>
parents: 5352
diff changeset
1 # -*- coding: latin-1 -*-
4975
9eea3cd22ee7 Add dgm file reader although an awk script should have been more appropiate
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
2
9eea3cd22ee7 Add dgm file reader although an awk script should have been more appropiate
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
3 import codecs
9eea3cd22ee7 Add dgm file reader although an awk script should have been more appropiate
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
4 import utils
5210
908848b74d7e SCHEME CHANGE: dgm now uses time intervals and ranges
Andre Heinecke <aheinecke@intevation.de>
parents: 5156
diff changeset
5 import datetime
4975
9eea3cd22ee7 Add dgm file reader although an awk script should have been more appropiate
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
6
9eea3cd22ee7 Add dgm file reader although an awk script should have been more appropiate
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
7 def latin(string):
9eea3cd22ee7 Add dgm file reader although an awk script should have been more appropiate
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
8 return unicode(string, "latin1")
9eea3cd22ee7 Add dgm file reader although an awk script should have been more appropiate
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
9
5213
76818dc2c937 Importer: Handle empty year fields in dgm csv
Andre Heinecke <aheinecke@intevation.de>
parents: 5210
diff changeset
10 import logging
76818dc2c937 Importer: Handle empty year fields in dgm csv
Andre Heinecke <aheinecke@intevation.de>
parents: 5210
diff changeset
11 logger = logging.getLogger("DGM")
76818dc2c937 Importer: Handle empty year fields in dgm csv
Andre Heinecke <aheinecke@intevation.de>
parents: 5210
diff changeset
12
76818dc2c937 Importer: Handle empty year fields in dgm csv
Andre Heinecke <aheinecke@intevation.de>
parents: 5210
diff changeset
13
4975
9eea3cd22ee7 Add dgm file reader although an awk script should have been more appropiate
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
14 # <dbfield> : (<csvfield>, conversion function)
9eea3cd22ee7 Add dgm file reader although an awk script should have been more appropiate
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
15 DGM_MAP = {
9eea3cd22ee7 Add dgm file reader although an awk script should have been more appropiate
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
16 "projection" : "Projektion",
5546
5b50247d4d99 Use latin1 as encoding in the dgm importer to make it easier matching latin1
Andre Heinecke <aheinecke@intevation.de>
parents: 5352
diff changeset
17 "elevation_state" : latin("H�henstatus"),
4975
9eea3cd22ee7 Add dgm file reader although an awk script should have been more appropiate
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
18 "format" : "Format",
9eea3cd22ee7 Add dgm file reader although an awk script should have been more appropiate
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
19 "border_break" : ("Bruchkanten",
9eea3cd22ee7 Add dgm file reader although an awk script should have been more appropiate
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
20 lambda x: True if x.lower() == "Ja" else False),
5546
5b50247d4d99 Use latin1 as encoding in the dgm importer to make it easier matching latin1
Andre Heinecke <aheinecke@intevation.de>
parents: 5352
diff changeset
21 "resolution" : (latin("Aufl�sung"), lambda x: x),
4975
9eea3cd22ee7 Add dgm file reader although an awk script should have been more appropiate
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
22 # "description" :
4993
71e1b96f4794 Add srid field
Andre Heinecke <aheinecke@intevation.de>
parents: 4975
diff changeset
23 "srid" : "SRID",
4975
9eea3cd22ee7 Add dgm file reader although an awk script should have been more appropiate
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
24 "path" : ("Pfad_Bestand", lambda x: x),
9eea3cd22ee7 Add dgm file reader although an awk script should have been more appropiate
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
25 }
9eea3cd22ee7 Add dgm file reader although an awk script should have been more appropiate
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
26
5210
908848b74d7e SCHEME CHANGE: dgm now uses time intervals and ranges
Andre Heinecke <aheinecke@intevation.de>
parents: 5156
diff changeset
27 SQL_INSERT_DGT = "INSERT INTO dem (river_id, name," \
908848b74d7e SCHEME CHANGE: dgm now uses time intervals and ranges
Andre Heinecke <aheinecke@intevation.de>
parents: 5156
diff changeset
28 " time_interval_id, range_id, " + ", ".join(DGM_MAP.keys()) + \
908848b74d7e SCHEME CHANGE: dgm now uses time intervals and ranges
Andre Heinecke <aheinecke@intevation.de>
parents: 5156
diff changeset
29 ") VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
908848b74d7e SCHEME CHANGE: dgm now uses time intervals and ranges
Andre Heinecke <aheinecke@intevation.de>
parents: 5156
diff changeset
30 SQL_INSERT_DGT_ORA = "INSERT INTO dem (river_id, name," \
908848b74d7e SCHEME CHANGE: dgm now uses time intervals and ranges
Andre Heinecke <aheinecke@intevation.de>
parents: 5156
diff changeset
31 " time_interval_id, range_id, " + ", ".join(DGM_MAP.keys()) + \
908848b74d7e SCHEME CHANGE: dgm now uses time intervals and ranges
Andre Heinecke <aheinecke@intevation.de>
parents: 5156
diff changeset
32 ") VALUES (:s, :s, :s, :s, :s, :s, :s, :s, :s, :s, :s)"
908848b74d7e SCHEME CHANGE: dgm now uses time intervals and ranges
Andre Heinecke <aheinecke@intevation.de>
parents: 5156
diff changeset
33 SQL_SELECT_TIME_ID = """
908848b74d7e SCHEME CHANGE: dgm now uses time intervals and ranges
Andre Heinecke <aheinecke@intevation.de>
parents: 5156
diff changeset
34 SELECT id FROM time_intervals WHERE start_time = %s AND stop_time = %s
908848b74d7e SCHEME CHANGE: dgm now uses time intervals and ranges
Andre Heinecke <aheinecke@intevation.de>
parents: 5156
diff changeset
35 """
908848b74d7e SCHEME CHANGE: dgm now uses time intervals and ranges
Andre Heinecke <aheinecke@intevation.de>
parents: 5156
diff changeset
36 SQL_INSERT_TIME_ID = """
5351
292f00337015 Revert 5301:7d29c277000e which introduced inconsitencies in the sequences
Andre Heinecke <aheinecke@intevation.de>
parents: 5301
diff changeset
37 INSERT INTO time_intervals (id, start_time, stop_time) VALUES (%s, %s, %s)
5210
908848b74d7e SCHEME CHANGE: dgm now uses time intervals and ranges
Andre Heinecke <aheinecke@intevation.de>
parents: 5156
diff changeset
38 """
908848b74d7e SCHEME CHANGE: dgm now uses time intervals and ranges
Andre Heinecke <aheinecke@intevation.de>
parents: 5156
diff changeset
39 SQL_SELECT_TIME_ID_ORA = """
908848b74d7e SCHEME CHANGE: dgm now uses time intervals and ranges
Andre Heinecke <aheinecke@intevation.de>
parents: 5156
diff changeset
40 SELECT id FROM time_intervals WHERE start_time = :s AND stop_time = :s
908848b74d7e SCHEME CHANGE: dgm now uses time intervals and ranges
Andre Heinecke <aheinecke@intevation.de>
parents: 5156
diff changeset
41 """
908848b74d7e SCHEME CHANGE: dgm now uses time intervals and ranges
Andre Heinecke <aheinecke@intevation.de>
parents: 5156
diff changeset
42 SQL_INSERT_TIME_ID_ORA = """
5351
292f00337015 Revert 5301:7d29c277000e which introduced inconsitencies in the sequences
Andre Heinecke <aheinecke@intevation.de>
parents: 5301
diff changeset
43 INSERT INTO time_intervals (id, start_time, stop_time) VALUES (:s, :s, :s)
5210
908848b74d7e SCHEME CHANGE: dgm now uses time intervals and ranges
Andre Heinecke <aheinecke@intevation.de>
parents: 5156
diff changeset
44 """
908848b74d7e SCHEME CHANGE: dgm now uses time intervals and ranges
Andre Heinecke <aheinecke@intevation.de>
parents: 5156
diff changeset
45 SQL_SELECT_RANGE_ID = """
908848b74d7e SCHEME CHANGE: dgm now uses time intervals and ranges
Andre Heinecke <aheinecke@intevation.de>
parents: 5156
diff changeset
46 SELECT id FROM ranges WHERE river_id = %s AND a = %s AND b = %s
908848b74d7e SCHEME CHANGE: dgm now uses time intervals and ranges
Andre Heinecke <aheinecke@intevation.de>
parents: 5156
diff changeset
47 """
908848b74d7e SCHEME CHANGE: dgm now uses time intervals and ranges
Andre Heinecke <aheinecke@intevation.de>
parents: 5156
diff changeset
48 SQL_INSERT_RANGE_ID = """
5351
292f00337015 Revert 5301:7d29c277000e which introduced inconsitencies in the sequences
Andre Heinecke <aheinecke@intevation.de>
parents: 5301
diff changeset
49 INSERT INTO ranges (id, river_id, a, b) VALUES (%s, %s, %s, %s)
5210
908848b74d7e SCHEME CHANGE: dgm now uses time intervals and ranges
Andre Heinecke <aheinecke@intevation.de>
parents: 5156
diff changeset
50 """
908848b74d7e SCHEME CHANGE: dgm now uses time intervals and ranges
Andre Heinecke <aheinecke@intevation.de>
parents: 5156
diff changeset
51 SQL_SELECT_RANGE_ID_ORA = """
908848b74d7e SCHEME CHANGE: dgm now uses time intervals and ranges
Andre Heinecke <aheinecke@intevation.de>
parents: 5156
diff changeset
52 SELECT id FROM ranges WHERE river_id = :s AND a = :s AND b = :s
908848b74d7e SCHEME CHANGE: dgm now uses time intervals and ranges
Andre Heinecke <aheinecke@intevation.de>
parents: 5156
diff changeset
53 """
908848b74d7e SCHEME CHANGE: dgm now uses time intervals and ranges
Andre Heinecke <aheinecke@intevation.de>
parents: 5156
diff changeset
54 SQL_INSERT_RANGE_ID_ORA = """
5351
292f00337015 Revert 5301:7d29c277000e which introduced inconsitencies in the sequences
Andre Heinecke <aheinecke@intevation.de>
parents: 5301
diff changeset
55 INSERT INTO ranges (id, river_id, a, b) VALUES (:s, :s, :s, :s)
5210
908848b74d7e SCHEME CHANGE: dgm now uses time intervals and ranges
Andre Heinecke <aheinecke@intevation.de>
parents: 5156
diff changeset
56 """
5352
f1009aa7dcd1 Fix id handling in dgm import
Andre Heinecke <aheinecke@intevation.de>
parents: 5351
diff changeset
57 SQL_NEXT_ID = "select nextval('%s_ID_SEQ')"
f1009aa7dcd1 Fix id handling in dgm import
Andre Heinecke <aheinecke@intevation.de>
parents: 5351
diff changeset
58 SQL_NEXT_ID_ORA = "select %s_ID_SEQ.nextval FROM dual"
5210
908848b74d7e SCHEME CHANGE: dgm now uses time intervals and ranges
Andre Heinecke <aheinecke@intevation.de>
parents: 5156
diff changeset
59
5352
f1009aa7dcd1 Fix id handling in dgm import
Andre Heinecke <aheinecke@intevation.de>
parents: 5351
diff changeset
60 def next_id(cur, relation, oracle):
f1009aa7dcd1 Fix id handling in dgm import
Andre Heinecke <aheinecke@intevation.de>
parents: 5351
diff changeset
61 if oracle:
f1009aa7dcd1 Fix id handling in dgm import
Andre Heinecke <aheinecke@intevation.de>
parents: 5351
diff changeset
62 cur.execute(SQL_NEXT_ID_ORA % relation.upper())
f1009aa7dcd1 Fix id handling in dgm import
Andre Heinecke <aheinecke@intevation.de>
parents: 5351
diff changeset
63 else:
f1009aa7dcd1 Fix id handling in dgm import
Andre Heinecke <aheinecke@intevation.de>
parents: 5351
diff changeset
64 cur.execute(SQL_NEXT_ID % relation.upper())
f1009aa7dcd1 Fix id handling in dgm import
Andre Heinecke <aheinecke@intevation.de>
parents: 5351
diff changeset
65 idx = cur.fetchone()[0]
5210
908848b74d7e SCHEME CHANGE: dgm now uses time intervals and ranges
Andre Heinecke <aheinecke@intevation.de>
parents: 5156
diff changeset
66 return idx
908848b74d7e SCHEME CHANGE: dgm now uses time intervals and ranges
Andre Heinecke <aheinecke@intevation.de>
parents: 5156
diff changeset
67
908848b74d7e SCHEME CHANGE: dgm now uses time intervals and ranges
Andre Heinecke <aheinecke@intevation.de>
parents: 5156
diff changeset
68 def get_range_id(cur, river_id, a, b, oracle):
908848b74d7e SCHEME CHANGE: dgm now uses time intervals and ranges
Andre Heinecke <aheinecke@intevation.de>
parents: 5156
diff changeset
69 if oracle:
908848b74d7e SCHEME CHANGE: dgm now uses time intervals and ranges
Andre Heinecke <aheinecke@intevation.de>
parents: 5156
diff changeset
70 cur.execute(SQL_SELECT_RANGE_ID_ORA, (river_id, a, b))
908848b74d7e SCHEME CHANGE: dgm now uses time intervals and ranges
Andre Heinecke <aheinecke@intevation.de>
parents: 5156
diff changeset
71 else:
908848b74d7e SCHEME CHANGE: dgm now uses time intervals and ranges
Andre Heinecke <aheinecke@intevation.de>
parents: 5156
diff changeset
72 cur.execute(SQL_SELECT_RANGE_ID, (river_id, a, b))
908848b74d7e SCHEME CHANGE: dgm now uses time intervals and ranges
Andre Heinecke <aheinecke@intevation.de>
parents: 5156
diff changeset
73 row = cur.fetchone()
908848b74d7e SCHEME CHANGE: dgm now uses time intervals and ranges
Andre Heinecke <aheinecke@intevation.de>
parents: 5156
diff changeset
74 if row: return row[0]
5352
f1009aa7dcd1 Fix id handling in dgm import
Andre Heinecke <aheinecke@intevation.de>
parents: 5351
diff changeset
75 idx = next_id(cur, "ranges", oracle)
5210
908848b74d7e SCHEME CHANGE: dgm now uses time intervals and ranges
Andre Heinecke <aheinecke@intevation.de>
parents: 5156
diff changeset
76 if oracle:
5351
292f00337015 Revert 5301:7d29c277000e which introduced inconsitencies in the sequences
Andre Heinecke <aheinecke@intevation.de>
parents: 5301
diff changeset
77 cur.execute(SQL_INSERT_RANGE_ID_ORA, (idx, river_id, a, b))
5210
908848b74d7e SCHEME CHANGE: dgm now uses time intervals and ranges
Andre Heinecke <aheinecke@intevation.de>
parents: 5156
diff changeset
78 else:
5351
292f00337015 Revert 5301:7d29c277000e which introduced inconsitencies in the sequences
Andre Heinecke <aheinecke@intevation.de>
parents: 5301
diff changeset
79 cur.execute(SQL_INSERT_RANGE_ID, (idx, river_id, a, b))
5210
908848b74d7e SCHEME CHANGE: dgm now uses time intervals and ranges
Andre Heinecke <aheinecke@intevation.de>
parents: 5156
diff changeset
80 cur.connection.commit()
5351
292f00337015 Revert 5301:7d29c277000e which introduced inconsitencies in the sequences
Andre Heinecke <aheinecke@intevation.de>
parents: 5301
diff changeset
81 return idx
5210
908848b74d7e SCHEME CHANGE: dgm now uses time intervals and ranges
Andre Heinecke <aheinecke@intevation.de>
parents: 5156
diff changeset
82
908848b74d7e SCHEME CHANGE: dgm now uses time intervals and ranges
Andre Heinecke <aheinecke@intevation.de>
parents: 5156
diff changeset
83 def get_time_interval_id(cur, a, b, oracle):
5213
76818dc2c937 Importer: Handle empty year fields in dgm csv
Andre Heinecke <aheinecke@intevation.de>
parents: 5210
diff changeset
84 if not a or not b:
76818dc2c937 Importer: Handle empty year fields in dgm csv
Andre Heinecke <aheinecke@intevation.de>
parents: 5210
diff changeset
85 return None
5210
908848b74d7e SCHEME CHANGE: dgm now uses time intervals and ranges
Andre Heinecke <aheinecke@intevation.de>
parents: 5156
diff changeset
86 if oracle:
908848b74d7e SCHEME CHANGE: dgm now uses time intervals and ranges
Andre Heinecke <aheinecke@intevation.de>
parents: 5156
diff changeset
87 cur.execute(SQL_SELECT_TIME_ID_ORA, (a, b))
908848b74d7e SCHEME CHANGE: dgm now uses time intervals and ranges
Andre Heinecke <aheinecke@intevation.de>
parents: 5156
diff changeset
88 else:
908848b74d7e SCHEME CHANGE: dgm now uses time intervals and ranges
Andre Heinecke <aheinecke@intevation.de>
parents: 5156
diff changeset
89 cur.execute(SQL_SELECT_TIME_ID, (a, b))
908848b74d7e SCHEME CHANGE: dgm now uses time intervals and ranges
Andre Heinecke <aheinecke@intevation.de>
parents: 5156
diff changeset
90 row = cur.fetchone()
908848b74d7e SCHEME CHANGE: dgm now uses time intervals and ranges
Andre Heinecke <aheinecke@intevation.de>
parents: 5156
diff changeset
91 if row: return row[0]
5352
f1009aa7dcd1 Fix id handling in dgm import
Andre Heinecke <aheinecke@intevation.de>
parents: 5351
diff changeset
92 idx = next_id(cur, "time_intervals", oracle)
5210
908848b74d7e SCHEME CHANGE: dgm now uses time intervals and ranges
Andre Heinecke <aheinecke@intevation.de>
parents: 5156
diff changeset
93 if oracle:
5351
292f00337015 Revert 5301:7d29c277000e which introduced inconsitencies in the sequences
Andre Heinecke <aheinecke@intevation.de>
parents: 5301
diff changeset
94 cur.execute(SQL_INSERT_TIME_ID_ORA, (idx, a, b))
5210
908848b74d7e SCHEME CHANGE: dgm now uses time intervals and ranges
Andre Heinecke <aheinecke@intevation.de>
parents: 5156
diff changeset
95 else:
5351
292f00337015 Revert 5301:7d29c277000e which introduced inconsitencies in the sequences
Andre Heinecke <aheinecke@intevation.de>
parents: 5301
diff changeset
96 cur.execute(SQL_INSERT_TIME_ID, (idx, a, b))
5210
908848b74d7e SCHEME CHANGE: dgm now uses time intervals and ranges
Andre Heinecke <aheinecke@intevation.de>
parents: 5156
diff changeset
97 cur.connection.commit()
5351
292f00337015 Revert 5301:7d29c277000e which introduced inconsitencies in the sequences
Andre Heinecke <aheinecke@intevation.de>
parents: 5301
diff changeset
98 return idx
4975
9eea3cd22ee7 Add dgm file reader although an awk script should have been more appropiate
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
99
5036
d6860fca89e4 Pull information about the backend into dgm.
Andre Heinecke <aheinecke@intevation.de>
parents: 4993
diff changeset
100 def insertRiverDgm(dbconn, dgmfile, river_name, dry_run, oracle):
4975
9eea3cd22ee7 Add dgm file reader although an awk script should have been more appropiate
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
101 with codecs.open(dgmfile, "r", "latin1") as csvfile:
9eea3cd22ee7 Add dgm file reader although an awk script should have been more appropiate
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
102 firstline = csvfile.readline()
9eea3cd22ee7 Add dgm file reader although an awk script should have been more appropiate
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
103 names = firstline.split(";")
9eea3cd22ee7 Add dgm file reader although an awk script should have been more appropiate
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
104 namedict = {}
9eea3cd22ee7 Add dgm file reader although an awk script should have been more appropiate
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
105 field_nr = 0
9eea3cd22ee7 Add dgm file reader although an awk script should have been more appropiate
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
106 for name in names:
9eea3cd22ee7 Add dgm file reader although an awk script should have been more appropiate
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
107 namedict[name] = field_nr
9eea3cd22ee7 Add dgm file reader although an awk script should have been more appropiate
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
108 field_nr += 1
9eea3cd22ee7 Add dgm file reader although an awk script should have been more appropiate
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
109
5036
d6860fca89e4 Pull information about the backend into dgm.
Andre Heinecke <aheinecke@intevation.de>
parents: 4993
diff changeset
110 river_id = utils.getRiverId(dbconn, river_name, oracle)
4975
9eea3cd22ee7 Add dgm file reader although an awk script should have been more appropiate
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
111 for line in csvfile:
9eea3cd22ee7 Add dgm file reader although an awk script should have been more appropiate
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
112 fields = line.split(";")
9eea3cd22ee7 Add dgm file reader although an awk script should have been more appropiate
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
113 if not fields: continue
5546
5b50247d4d99 Use latin1 as encoding in the dgm importer to make it easier matching latin1
Andre Heinecke <aheinecke@intevation.de>
parents: 5352
diff changeset
114 if fields[namedict[latin("Gew�sser")]] != \
5222
3b86bf214d53 Fix check for river_name in dgm to handle unicode names
Andre Heinecke <aheinecke@intevation.de>
parents: 5213
diff changeset
115 unicode(utils.getUTF8(river_name),'UTF-8'):
4975
9eea3cd22ee7 Add dgm file reader although an awk script should have been more appropiate
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
116 continue
9eea3cd22ee7 Add dgm file reader although an awk script should have been more appropiate
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
117 else:
9eea3cd22ee7 Add dgm file reader although an awk script should have been more appropiate
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
118 values=[]
9eea3cd22ee7 Add dgm file reader although an awk script should have been more appropiate
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
119 for key, val in DGM_MAP.items():
9eea3cd22ee7 Add dgm file reader although an awk script should have been more appropiate
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
120 if isinstance(val, tuple):
9eea3cd22ee7 Add dgm file reader although an awk script should have been more appropiate
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
121 values.append(val[1](fields[namedict[val[0]]]))
9eea3cd22ee7 Add dgm file reader although an awk script should have been more appropiate
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
122 else:
9eea3cd22ee7 Add dgm file reader although an awk script should have been more appropiate
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
123 values.append(unicode.encode(
9eea3cd22ee7 Add dgm file reader although an awk script should have been more appropiate
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
124 fields[namedict[val]], "UTF-8"))
7004
bb5bd48b80f6 DGM-importer: enforce a < b for ranges.
Tom Gottfried <tom.gottfried@intevation.de>
parents: 5838
diff changeset
125 km_von = min(float(fields[namedict["km_von"]]),
bb5bd48b80f6 DGM-importer: enforce a < b for ranges.
Tom Gottfried <tom.gottfried@intevation.de>
parents: 5838
diff changeset
126 float(fields[namedict["km_bis"]]))
bb5bd48b80f6 DGM-importer: enforce a < b for ranges.
Tom Gottfried <tom.gottfried@intevation.de>
parents: 5838
diff changeset
127 km_bis = max(float(fields[namedict["km_von"]]),
bb5bd48b80f6 DGM-importer: enforce a < b for ranges.
Tom Gottfried <tom.gottfried@intevation.de>
parents: 5838
diff changeset
128 float(fields[namedict["km_bis"]]))
5213
76818dc2c937 Importer: Handle empty year fields in dgm csv
Andre Heinecke <aheinecke@intevation.de>
parents: 5210
diff changeset
129 year_from = None
76818dc2c937 Importer: Handle empty year fields in dgm csv
Andre Heinecke <aheinecke@intevation.de>
parents: 5210
diff changeset
130 year_to = None
76818dc2c937 Importer: Handle empty year fields in dgm csv
Andre Heinecke <aheinecke@intevation.de>
parents: 5210
diff changeset
131 try:
76818dc2c937 Importer: Handle empty year fields in dgm csv
Andre Heinecke <aheinecke@intevation.de>
parents: 5210
diff changeset
132 year_from = datetime.datetime(
76818dc2c937 Importer: Handle empty year fields in dgm csv
Andre Heinecke <aheinecke@intevation.de>
parents: 5210
diff changeset
133 int(fields[namedict["Jahr_von"]]), 1, 1)
76818dc2c937 Importer: Handle empty year fields in dgm csv
Andre Heinecke <aheinecke@intevation.de>
parents: 5210
diff changeset
134 year_to = datetime.datetime(
76818dc2c937 Importer: Handle empty year fields in dgm csv
Andre Heinecke <aheinecke@intevation.de>
parents: 5210
diff changeset
135 int(fields[namedict["Jahr_bis"]]),1 ,1)
76818dc2c937 Importer: Handle empty year fields in dgm csv
Andre Heinecke <aheinecke@intevation.de>
parents: 5210
diff changeset
136 except ValueError:
76818dc2c937 Importer: Handle empty year fields in dgm csv
Andre Heinecke <aheinecke@intevation.de>
parents: 5210
diff changeset
137 logger.warn("Invalid numbers (or none) found in year_from and year_to")
5210
908848b74d7e SCHEME CHANGE: dgm now uses time intervals and ranges
Andre Heinecke <aheinecke@intevation.de>
parents: 5156
diff changeset
138
5553
8d2c576b5a40 Convert the river name into unicode
Andre Heinecke <aheinecke@intevation.de>
parents: 5546
diff changeset
139 name = "%s KM %s - %s" % (unicode(river_name, "latin1"), km_von, km_bis)
4975
9eea3cd22ee7 Add dgm file reader although an awk script should have been more appropiate
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
140 cur = dbconn.cursor()
5210
908848b74d7e SCHEME CHANGE: dgm now uses time intervals and ranges
Andre Heinecke <aheinecke@intevation.de>
parents: 5156
diff changeset
141 range_id = get_range_id(cur, river_id, float(km_von),
908848b74d7e SCHEME CHANGE: dgm now uses time intervals and ranges
Andre Heinecke <aheinecke@intevation.de>
parents: 5156
diff changeset
142 float(km_bis), oracle)
908848b74d7e SCHEME CHANGE: dgm now uses time intervals and ranges
Andre Heinecke <aheinecke@intevation.de>
parents: 5156
diff changeset
143 time_interval_id = get_time_interval_id(cur, year_from,
908848b74d7e SCHEME CHANGE: dgm now uses time intervals and ranges
Andre Heinecke <aheinecke@intevation.de>
parents: 5156
diff changeset
144 year_to, oracle)
908848b74d7e SCHEME CHANGE: dgm now uses time intervals and ranges
Andre Heinecke <aheinecke@intevation.de>
parents: 5156
diff changeset
145
5036
d6860fca89e4 Pull information about the backend into dgm.
Andre Heinecke <aheinecke@intevation.de>
parents: 4993
diff changeset
146 if oracle:
d6860fca89e4 Pull information about the backend into dgm.
Andre Heinecke <aheinecke@intevation.de>
parents: 4993
diff changeset
147 stmt = SQL_INSERT_DGT_ORA
d6860fca89e4 Pull information about the backend into dgm.
Andre Heinecke <aheinecke@intevation.de>
parents: 4993
diff changeset
148 else:
d6860fca89e4 Pull information about the backend into dgm.
Andre Heinecke <aheinecke@intevation.de>
parents: 4993
diff changeset
149 stmt = SQL_INSERT_DGT
d6860fca89e4 Pull information about the backend into dgm.
Andre Heinecke <aheinecke@intevation.de>
parents: 4993
diff changeset
150
5210
908848b74d7e SCHEME CHANGE: dgm now uses time intervals and ranges
Andre Heinecke <aheinecke@intevation.de>
parents: 5156
diff changeset
151 cur.execute(stmt, [river_id, name, time_interval_id,
908848b74d7e SCHEME CHANGE: dgm now uses time intervals and ranges
Andre Heinecke <aheinecke@intevation.de>
parents: 5156
diff changeset
152 range_id] + values)
4975
9eea3cd22ee7 Add dgm file reader although an awk script should have been more appropiate
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
153
9eea3cd22ee7 Add dgm file reader although an awk script should have been more appropiate
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
154 if not dry_run:
9eea3cd22ee7 Add dgm file reader although an awk script should have been more appropiate
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
155 dbconn.commit()
9eea3cd22ee7 Add dgm file reader although an awk script should have been more appropiate
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
156
http://dive4elements.wald.intevation.org