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 :

http://dive4elements.wald.intevation.org