Mercurial > dive4elements > river

comparison backend/contrib/shpimporter/dgm.py @ 5838:5aa05a7a34b7

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Rename modules to more fitting names.
author Sascha L. Teichmann <teichmann@intevation.de>
date Thu, 25 Apr 2013 15:23:37 +0200
parents flys-backend/contrib/shpimporter/dgm.py@8d2c576b5a40
children bb5bd48b80f6
comparison
equal deleted inserted replaced
5837:d9901a08d0a6 5838:5aa05a7a34b7
1 # -*- coding: latin-1 -*-
2
3 import codecs
4 import utils
5 import datetime
6
7 def latin(string):
8 return unicode(string, "latin1")
9
10 import logging
11 logger = logging.getLogger("DGM")
12
13
14 # <dbfield> : (<csvfield>, conversion function)
15 DGM_MAP = {
16 "projection" : "Projektion",
17 "elevation_state" : latin("Höhenstatus"),
18 "format" : "Format",
19 "border_break" : ("Bruchkanten",
20 lambda x: True if x.lower() == "Ja" else False),
21 "resolution" : (latin("Auflösung"), lambda x: x),
22 # "description" :
23 "srid" : "SRID",
24 "path" : ("Pfad_Bestand", lambda x: x),
25 }
26
27 SQL_INSERT_DGT = "INSERT INTO dem (river_id, name," \
28 " time_interval_id, range_id, " + ", ".join(DGM_MAP.keys()) + \
29 ") VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
30 SQL_INSERT_DGT_ORA = "INSERT INTO dem (river_id, name," \
31 " time_interval_id, range_id, " + ", ".join(DGM_MAP.keys()) + \
32 ") VALUES (:s, :s, :s, :s, :s, :s, :s, :s, :s, :s, :s)"
33 SQL_SELECT_TIME_ID = """
34 SELECT id FROM time_intervals WHERE start_time = %s AND stop_time = %s
35 """
36 SQL_INSERT_TIME_ID = """
37 INSERT INTO time_intervals (id, start_time, stop_time) VALUES (%s, %s, %s)
38 """
39 SQL_SELECT_TIME_ID_ORA = """
40 SELECT id FROM time_intervals WHERE start_time = :s AND stop_time = :s
41 """
42 SQL_INSERT_TIME_ID_ORA = """
43 INSERT INTO time_intervals (id, start_time, stop_time) VALUES (:s, :s, :s)
44 """
45 SQL_SELECT_RANGE_ID = """
46 SELECT id FROM ranges WHERE river_id = %s AND a = %s AND b = %s
47 """
48 SQL_INSERT_RANGE_ID = """
49 INSERT INTO ranges (id, river_id, a, b) VALUES (%s, %s, %s, %s)
50 """
51 SQL_SELECT_RANGE_ID_ORA = """
52 SELECT id FROM ranges WHERE river_id = :s AND a = :s AND b = :s
53 """
54 SQL_INSERT_RANGE_ID_ORA = """
55 INSERT INTO ranges (id, river_id, a, b) VALUES (:s, :s, :s, :s)
56 """
57 SQL_NEXT_ID = "select nextval('%s_ID_SEQ')"
58 SQL_NEXT_ID_ORA = "select %s_ID_SEQ.nextval FROM dual"
59
60 def next_id(cur, relation, oracle):
61 if oracle:
62 cur.execute(SQL_NEXT_ID_ORA % relation.upper())
63 else:
64 cur.execute(SQL_NEXT_ID % relation.upper())
65 idx = cur.fetchone()[0]
66 return idx
67
68 def get_range_id(cur, river_id, a, b, oracle):
69 if oracle:
70 cur.execute(SQL_SELECT_RANGE_ID_ORA, (river_id, a, b))
71 else:
72 cur.execute(SQL_SELECT_RANGE_ID, (river_id, a, b))
73 row = cur.fetchone()
74 if row: return row[0]
75 idx = next_id(cur, "ranges", oracle)
76 if oracle:
77 cur.execute(SQL_INSERT_RANGE_ID_ORA, (idx, river_id, a, b))
78 else:
79 cur.execute(SQL_INSERT_RANGE_ID, (idx, river_id, a, b))
80 cur.connection.commit()
81 return idx
82
83 def get_time_interval_id(cur, a, b, oracle):
84 if not a or not b:
85 return None
86 if oracle:
87 cur.execute(SQL_SELECT_TIME_ID_ORA, (a, b))
88 else:
89 cur.execute(SQL_SELECT_TIME_ID, (a, b))
90 row = cur.fetchone()
91 if row: return row[0]
92 idx = next_id(cur, "time_intervals", oracle)
93 if oracle:
94 cur.execute(SQL_INSERT_TIME_ID_ORA, (idx, a, b))
95 else:
96 cur.execute(SQL_INSERT_TIME_ID, (idx, a, b))
97 cur.connection.commit()
98 return idx
99
100 def insertRiverDgm(dbconn, dgmfile, river_name, dry_run, oracle):
101 with codecs.open(dgmfile, "r", "latin1") as csvfile:
102 firstline = csvfile.readline()
103 names = firstline.split(";")
104 namedict = {}
105 field_nr = 0
106 for name in names:
107 namedict[name] = field_nr
108 field_nr += 1
109
110 river_id = utils.getRiverId(dbconn, river_name, oracle)
111 for line in csvfile:
112 fields = line.split(";")
113 if not fields: continue
114 if fields[namedict[latin("Gewässer")]] != \
115 unicode(utils.getUTF8(river_name),'UTF-8'):
116 continue
117 else:
118 values=[]
119 for key, val in DGM_MAP.items():
120 if isinstance(val, tuple):
121 values.append(val[1](fields[namedict[val[0]]]))
122 else:
123 values.append(unicode.encode(
124 fields[namedict[val]], "UTF-8"))
125 km_von = fields[namedict["km_von"]]
126 km_bis = fields[namedict["km_bis"]]
127 year_from = None
128 year_to = None
129 try:
130 year_from = datetime.datetime(
131 int(fields[namedict["Jahr_von"]]), 1, 1)
132 year_to = datetime.datetime(
133 int(fields[namedict["Jahr_bis"]]),1 ,1)
134 except ValueError:
135 logger.warn("Invalid numbers (or none) found in year_from and year_to")
136
137 name = "%s KM %s - %s" % (unicode(river_name, "latin1"), km_von, km_bis)
138 cur = dbconn.cursor()
139 range_id = get_range_id(cur, river_id, float(km_von),
140 float(km_bis), oracle)
141 time_interval_id = get_time_interval_id(cur, year_from,
142 year_to, oracle)
143
144 if oracle:
145 stmt = SQL_INSERT_DGT_ORA
146 else:
147 stmt = SQL_INSERT_DGT
148
149 cur.execute(stmt, [river_id, name, time_interval_id,
150 range_id] + values)
151
152 if not dry_run:
153 dbconn.commit()
154
http://dive4elements.wald.intevation.org