Mercurial > dive4elements > river

comparison flys-backend/contrib/shpimporter/dgm.py @ 5210:908848b74d7e

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
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
http://dive4elements.wald.intevation.org