Mercurial > dive4elements > river
changeset 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 | 850237ae9544 |
| children | 9e11599266c8 |
| files | flys-artifacts/doc/conf/meta-data.xml flys-backend/contrib/shpimporter/dgm.py flys-backend/doc/schema/oracle-spatial.sql flys-backend/doc/schema/postgresql-spatial.sql flys-backend/src/main/java/de/intevation/flys/model/DGM.java |
| diffstat | 5 files changed, 154 insertions(+), 72 deletions(-) [+] |
line wrap: on
line diff
--- a/flys-artifacts/doc/conf/meta-data.xml Fri Mar 08 17:44:12 2013 +0100 +++ b/flys-artifacts/doc/conf/meta-data.xml Fri Mar 08 18:15:48 2013 +0100 @@ -972,12 +972,15 @@ <dems> <dc:context> <dc:statement> - SELECT id AS dem_id, - lower AS dem_lower, - upper AS dem_upper, - name AS name, - projection || ' | ' || year_from || ' - ' || year_to AS info - FROM dem WHERE river_id = ${river_id} + SELECT d.id AS dem_id, + r.a AS dem_lower, + r.b AS dem_upper, + d.name AS name, + d.projection || ' | ' || t.start || ' - ' || t.stop AS info + FROM dem d + JOIN ranges r ON d.range_id r.id + JOIN time_intervals t ON d.time_interval_id t.id + WHERE river_id = ${river_id} </dc:statement> <dc:elements> <dem>
--- 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()
--- a/flys-backend/doc/schema/oracle-spatial.sql Fri Mar 08 17:44:12 2013 +0100 +++ b/flys-backend/doc/schema/oracle-spatial.sql Fri Mar 08 18:15:48 2013 +0100 @@ -130,27 +130,22 @@ --CREATE INDEX floodplain_spatial_idx ON floodplain(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=POLYGON'); --- TODO: Test-Me. Fix Importer-Skript. --- NOTE: It's not a spatial schema! -- Geodaesie/Hoehenmodelle/* CREATE SEQUENCE DEM_ID_SEQ; CREATE TABLE dem ( - ID NUMBER PRIMARY KEY NOT NULL, - river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE, - -- XXX Should we use the ranges table instead? - name VARCHAR(64), - lower NUMBER(19,5), - upper NUMBER(19,5), - year_from VARCHAR(32), - year_to VARCHAR(32), - projection VARCHAR(32), - elevation_state VARCHAR(32), - srid NUMBER NOT NULL, - format VARCHAR(32), - border_break NUMBER(1) DEFAULT 0 NOT NULL, - resolution VARCHAR(16), - description VARCHAR(256), - path VARCHAR(256) NOT NULL UNIQUE + ID NUMBER PRIMARY KEY NOT NULL, + river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE, + name VARCHAR(64), + range_id NUMBER(38) REFERENCES ranges(id); + time_interval_id NUMBER(38) REFERENCES time_intervals(id), + projection VARCHAR(32), + elevation_state VARCHAR(32), + srid NUMBER NOT NULL, + format VARCHAR(32), + border_break NUMBER(1) DEFAULT 0 NOT NULL, + resolution VARCHAR(16), + description VARCHAR(256), + path VARCHAR(256) NOT NULL UNIQUE ); CREATE OR REPLACE TRIGGER dem_trigger BEFORE INSERT ON dem FOR each ROW BEGIN
--- a/flys-backend/doc/schema/postgresql-spatial.sql Fri Mar 08 17:44:12 2013 +0100 +++ b/flys-backend/doc/schema/postgresql-spatial.sql Fri Mar 08 18:15:48 2013 +0100 @@ -95,19 +95,17 @@ id int PRIMARY KEY NOT NULL, river_id int REFERENCES rivers(id) ON DELETE CASCADE, -- XXX Should we use the ranges table instead? - name VARCHAR(64), - lower FLOAT8, - upper FLOAT8, - year_from VARCHAR(32), - year_to VARCHAR(32), - projection VARCHAR(32), - srid int NOT NULL, - elevation_state VARCHAR(32), - format VARCHAR(32), - border_break BOOLEAN NOT NULL DEFAULT FALSE, - resolution VARCHAR(16), - description VARCHAR(256), - path VARCHAR(256) NOT NULL UNIQUE + name VARCHAR(64), + range_id INT REFERENCES ranges(id); + time_interval_id INT REFERENCES time_intervals(id); + projection VARCHAR(32), + srid int NOT NULL, + elevation_state VARCHAR(32), + format VARCHAR(32), + border_break BOOLEAN NOT NULL DEFAULT FALSE, + resolution VARCHAR(16), + description VARCHAR(256), + path VARCHAR(256) NOT NULL UNIQUE ); ALTER TABLE dem ALTER COLUMN id SET DEFAULT NEXTVAL('DEM_ID_SEQ');
--- a/flys-backend/src/main/java/de/intevation/flys/model/DGM.java Fri Mar 08 17:44:12 2013 +0100 +++ b/flys-backend/src/main/java/de/intevation/flys/model/DGM.java Fri Mar 08 18:15:48 2013 +0100 @@ -24,15 +24,15 @@ @Table(name = "dem") public class DGM implements Serializable { - private Integer id; - private Integer srid; + private Integer id; + private Integer srid; - private River river; + private River river; - private BigDecimal lower; - private BigDecimal upper; + private Range range; + private TimeInterval time_interval; - private String path; + private String path; public DGM() { @@ -66,24 +66,6 @@ return river; } - public void setLower(BigDecimal lower) { - this.lower = lower; - } - - @Column(name = "lower") - public BigDecimal getLower() { - return lower; - } - - public void setUpper(BigDecimal upper) { - this.upper = upper; - } - - @Column(name = "upper") - public BigDecimal getUpper() { - return upper; - } - public void setPath(String path) { this.path = path; } @@ -120,8 +102,8 @@ Query query = session.createQuery( "from DGM where river.name =:river and " + - "lower <=:lower and upper >=:lower and " + - "lower <=:upper and upper >=:upper"); + "range.a <=:lower and range.b >=:lower and " + + "range.a <=:upper and range.b >=:upper"); query.setParameter("river", river); query.setParameter("lower", new BigDecimal(lower)); query.setParameter("upper", new BigDecimal(upper)); @@ -130,5 +112,27 @@ return result.isEmpty() ? null : result.get(0); } + + @OneToOne + @JoinColumn(name = "range_id") + public Range getRange() { + return range; + } + + public void setRange(Range range) { + this.range = range; + } + + @OneToOne + @JoinColumn(name = "time_interval_id") + public TimeInterval getTimeInterval() { + return time_interval; + } + + public void setTimeInterval(TimeInterval time_interval) { + this.time_interval = time_interval; + } + + } // vim:set ts=4 sw=4 si et sta sts=4 fenc=utf8 :