Mercurial > dive4elements > river

annotate flys-backend/contrib/shpimporter/dgm.py @ 5036:d6860fca89e4 dami

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Pull information about the backend into dgm. While the api of cx_oracle and psycopg2 is compatible enough, the query syntax is not :(
author Andre Heinecke <aheinecke@intevation.de>
date Wed, 20 Feb 2013 10:40:07 +0100
parents 71e1b96f4794
children 4f46679e13d0
rev   line source
4975
9eea3cd22ee7 Add dgm file reader although an awk script should have been more appropiate
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
1 # -*- coding: utf-8 -*-
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
9eea3cd22ee7 Add dgm file reader although an awk script should have been more appropiate
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
5
9eea3cd22ee7 Add dgm file reader although an awk script should have been more appropiate
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
6 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
7 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
8
9eea3cd22ee7 Add dgm file reader although an awk script should have been more appropiate
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
9 # <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
10 DGM_MAP = {
9eea3cd22ee7 Add dgm file reader although an awk script should have been more appropiate
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
11 "name" : latin("Gewässer"),
9eea3cd22ee7 Add dgm file reader although an awk script should have been more appropiate
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
12 "lower" : ("km_von", lambda x: int(x)),
9eea3cd22ee7 Add dgm file reader although an awk script should have been more appropiate
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
13 "upper" : ("km_bis", lambda x: int(x)),
9eea3cd22ee7 Add dgm file reader although an awk script should have been more appropiate
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
14 "year_from" : "Jahr_von",
9eea3cd22ee7 Add dgm file reader although an awk script should have been more appropiate
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
15 "year_to" : "Jahr_bis",
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",
9eea3cd22ee7 Add dgm file reader although an awk script should have been more appropiate
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
17 "elevation_state" : latin("Höhenstatus"),
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),
9eea3cd22ee7 Add dgm file reader although an awk script should have been more appropiate
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
21 "resolution" : (latin("Auflösung"), 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
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
9eea3cd22ee7 Add dgm file reader although an awk script should have been more appropiate
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
27 SQL_INSERT_DGT = "INSERT INTO dem (river_id, " + ", ".join(DGM_MAP.keys()) + \
4993
71e1b96f4794 Add srid field
Andre Heinecke <aheinecke@intevation.de>
parents: 4975
diff changeset
28 ") VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
5036
d6860fca89e4 Pull information about the backend into dgm.
Andre Heinecke <aheinecke@intevation.de>
parents: 4993
diff changeset
29 SQL_INSERT_DGT_ORA = "INSERT INTO dem (river_id, " + ", ".join(DGM_MAP.keys()) + \
d6860fca89e4 Pull information about the backend into dgm.
Andre Heinecke <aheinecke@intevation.de>
parents: 4993
diff changeset
30 ") VALUES (:s, :s, :s, :s, :s, :s, :s, :s, :s, :s, :s, :s, :s)"
4975
9eea3cd22ee7 Add dgm file reader although an awk script should have been more appropiate
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
31
5036
d6860fca89e4 Pull information about the backend into dgm.
Andre Heinecke <aheinecke@intevation.de>
parents: 4993
diff changeset
32 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
33 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
34 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
35 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
36 namedict = {}
9eea3cd22ee7 Add dgm file reader although an awk script should have been more appropiate
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
37 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
38 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
39 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
40 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
41
5036
d6860fca89e4 Pull information about the backend into dgm.
Andre Heinecke <aheinecke@intevation.de>
parents: 4993
diff changeset
42 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
43 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
44 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
45 if not fields: continue
9eea3cd22ee7 Add dgm file reader although an awk script should have been more appropiate
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
46 if fields[namedict[latin("Gewässer")]] != river_name:
9eea3cd22ee7 Add dgm file reader although an awk script should have been more appropiate
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
47 continue
9eea3cd22ee7 Add dgm file reader although an awk script should have been more appropiate
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
48 else:
9eea3cd22ee7 Add dgm file reader although an awk script should have been more appropiate
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
49 values=[]
9eea3cd22ee7 Add dgm file reader although an awk script should have been more appropiate
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
50 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
51 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
52 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
53 else:
9eea3cd22ee7 Add dgm file reader although an awk script should have been more appropiate
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
54 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
55 fields[namedict[val]], "UTF-8"))
9eea3cd22ee7 Add dgm file reader although an awk script should have been more appropiate
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
56 cur = dbconn.cursor()
5036
d6860fca89e4 Pull information about the backend into dgm.
Andre Heinecke <aheinecke@intevation.de>
parents: 4993
diff changeset
57 if oracle:
d6860fca89e4 Pull information about the backend into dgm.
Andre Heinecke <aheinecke@intevation.de>
parents: 4993
diff changeset
58 stmt = SQL_INSERT_DGT_ORA
d6860fca89e4 Pull information about the backend into dgm.
Andre Heinecke <aheinecke@intevation.de>
parents: 4993
diff changeset
59 else:
d6860fca89e4 Pull information about the backend into dgm.
Andre Heinecke <aheinecke@intevation.de>
parents: 4993
diff changeset
60 stmt = SQL_INSERT_DGT
d6860fca89e4 Pull information about the backend into dgm.
Andre Heinecke <aheinecke@intevation.de>
parents: 4993
diff changeset
61
d6860fca89e4 Pull information about the backend into dgm.
Andre Heinecke <aheinecke@intevation.de>
parents: 4993
diff changeset
62 cur.execute(stmt, [river_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
63
9eea3cd22ee7 Add dgm file reader although an awk script should have been more appropiate
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
64 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
65 dbconn.commit()
9eea3cd22ee7 Add dgm file reader although an awk script should have been more appropiate
Andre Heinecke <aheinecke@intevation.de>
parents:
diff changeset
66
http://dive4elements.wald.intevation.org