Mercurial > dive4elements > river
diff 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 |
line wrap: on
line diff
--- a/flys-backend/contrib/shpimporter/dgm.py Fri Mar 08 17:44:12 2013 +0100 +++ b/flys-backend/contrib/shpimporter/dgm.py Fri Mar 08 18:15:48 2013 +0100 @@ -2,16 +2,13 @@ import codecs import utils +import datetime def latin(string): return unicode(string, "latin1") # <dbfield> : (<csvfield>, conversion function) DGM_MAP = { - "lower" : ("km_von", lambda x: float(x)), - "upper" : ("km_bis", lambda x: float(x)), - "year_from" : "Jahr_von", - "year_to" : "Jahr_bis", "projection" : "Projektion", "elevation_state" : latin("Höhenstatus"), "format" : "Format", @@ -23,10 +20,81 @@ "path" : ("Pfad_Bestand", lambda x: x), } -SQL_INSERT_DGT = "INSERT INTO dem (river_id, name, " + ", ".join(DGM_MAP.keys()) + \ - ") VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)" -SQL_INSERT_DGT_ORA = "INSERT INTO dem (river_id, name, " + ", ".join(DGM_MAP.keys()) + \ - ") VALUES (:s, :s, :s, :s, :s, :s, :s, :s, :s, :s, :s, :s, :s)" +SQL_INSERT_DGT = "INSERT INTO dem (river_id, name," \ + " time_interval_id, range_id, " + ", ".join(DGM_MAP.keys()) + \ + ") VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)" +SQL_INSERT_DGT_ORA = "INSERT INTO dem (river_id, name," \ + " time_interval_id, range_id, " + ", ".join(DGM_MAP.keys()) + \ + ") VALUES (:s, :s, :s, :s, :s, :s, :s, :s, :s, :s, :s)" +SQL_SELECT_TIME_ID = """ +SELECT id FROM time_intervals WHERE start_time = %s AND stop_time = %s +""" +SQL_INSERT_TIME_ID = """ +INSERT INTO time_intervals (id, start_time, stop_time) VALUES (%s, %s, %s) +""" +SQL_SELECT_TIME_ID_ORA = """ +SELECT id FROM time_intervals WHERE start_time = :s AND stop_time = :s +""" +SQL_INSERT_TIME_ID_ORA = """ +INSERT INTO time_intervals (id, start_time, stop_time) VALUES (:s, :s, :s) +""" +SQL_SELECT_RANGE_ID = """ +SELECT id FROM ranges WHERE river_id = %s AND a = %s AND b = %s +""" +SQL_INSERT_RANGE_ID = """ +INSERT INTO ranges (id, river_id, a, b) VALUES (%s, %s, %s, %s) +""" +SQL_SELECT_RANGE_ID_ORA = """ +SELECT id FROM ranges WHERE river_id = :s AND a = :s AND b = :s +""" +SQL_INSERT_RANGE_ID_ORA = """ +INSERT INTO ranges (id, river_id, a, b) VALUES (:s, :s, :s, :s) +""" +SQL_NEXT_ID = "SELECT coalesce(max(id), -1) + 1 FROM %s" +SQL_NEXT_ID_ORA = "SELECT coalesce(max(id), -1) + 1 FROM :s" + + +NEXT_IDS = {} +def next_id(cur, relation, oracle): + idx = NEXT_IDS.get(relation) + if idx is None: + if oracle: + cur.execute(SQL_NEXT_ID_ORA % relation) + else: + cur.execute(SQL_NEXT_ID % relation) + idx = cur.fetchone()[0] + NEXT_IDS[relation] = idx + 1 + return idx + +def get_range_id(cur, river_id, a, b, oracle): + if oracle: + cur.execute(SQL_SELECT_RANGE_ID_ORA, (river_id, a, b)) + else: + cur.execute(SQL_SELECT_RANGE_ID, (river_id, a, b)) + row = cur.fetchone() + if row: return row[0] + idx = next_id(cur, "ranges", oracle) + if oracle: + cur.execute(SQL_INSERT_RANGE_ID_ORA, (idx, river_id, a, b)) + else: + cur.execute(SQL_INSERT_RANGE_ID, (idx, river_id, a, b)) + cur.connection.commit() + return idx + +def get_time_interval_id(cur, a, b, oracle): + if oracle: + cur.execute(SQL_SELECT_TIME_ID_ORA, (a, b)) + else: + cur.execute(SQL_SELECT_TIME_ID, (a, b)) + row = cur.fetchone() + if row: return row[0] + idx = next_id(cur, "time_intervals", oracle) + if oracle: + cur.execute(SQL_INSERT_TIME_ID_ORA, (idx, a, b)) + else: + cur.execute(SQL_INSERT_TIME_ID, (idx, a, b)) + cur.connection.commit() + return idx def insertRiverDgm(dbconn, dgmfile, river_name, dry_run, oracle): with codecs.open(dgmfile, "r", "latin1") as csvfile: @@ -52,15 +120,27 @@ else: values.append(unicode.encode( fields[namedict[val]], "UTF-8")) - name = "%s KM %s - %s" % (river_name, fields[namedict["km_von"]], - fields[namedict["km_bis"]]) + km_von = fields[namedict["km_von"]] + km_bis = fields[namedict["km_bis"]] + year_from = datetime.datetime( + int(fields[namedict["Jahr_von"]]), 1, 1) + year_to = datetime.datetime( + int(fields[namedict["Jahr_bis"]]),1 ,1) + + name = "%s KM %s - %s" % (river_name, km_von, km_bis) cur = dbconn.cursor() + range_id = get_range_id(cur, river_id, float(km_von), + float(km_bis), oracle) + time_interval_id = get_time_interval_id(cur, year_from, + year_to, oracle) + if oracle: stmt = SQL_INSERT_DGT_ORA else: stmt = SQL_INSERT_DGT - cur.execute(stmt, [river_id, name] + values) + cur.execute(stmt, [river_id, name, time_interval_id, + range_id] + values) if not dry_run: dbconn.commit()