Mercurial > dive4elements > river

changeset 2861:b0132e1b9719

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Added further shape importers and added the option to reproject shapes during the import process. flys-backend/trunk@4342 c6561f87-3c4e-4783-a992-168aeb5c3f6f
author Ingo Weinzierl <ingo.weinzierl@intevation.de>
date Thu, 03 May 2012 14:19:52 +0000
parents 2f874d14ac68
children 998f1a7dcfde
files flys-backend/ChangeLog flys-backend/contrib/shpimporter/axis.py flys-backend/contrib/shpimporter/boundaries.py flys-backend/contrib/shpimporter/buildings.py flys-backend/contrib/shpimporter/crosssectiontracks.py flys-backend/contrib/shpimporter/fixpoints.py flys-backend/contrib/shpimporter/floodplains.py flys-backend/contrib/shpimporter/importer.py flys-backend/contrib/shpimporter/km.py flys-backend/contrib/shpimporter/lines.py flys-backend/contrib/shpimporter/shpimporter.py flys-backend/contrib/shpimporter/uesg.py flys-backend/doc/schema/oracle-drop-spatial.sql flys-backend/doc/schema/oracle-spatial.sql
diffstat 14 files changed, 268 insertions(+), 24 deletions(-) [+]
line wrap: on
line diff
--- a/flys-backend/ChangeLog	Wed May 02 08:49:57 2012 +0000
+++ b/flys-backend/ChangeLog	Thu May 03 14:19:52 2012 +0000
@@ -1,3 +1,28 @@
+2012-05-03  Ingo Weinzierl <ingo@intevation.de>
+
+	* doc/schema/oracle-drop-spatial.sql,
+	  doc/schema/oracle-spatial.sql: Added new relations for hydrological
+	  boundaries and appended a 'name' field to relations that had no such
+	  field yet.
+
+	* contrib/shpimporter/floodplains.py,
+	  contrib/shpimporter/boundaries.py: New importers for floodplain and
+	  hydrological boundaries.
+
+	* contrib/shpimporter/lines.py,
+	  contrib/shpimporter/buildings.py,
+	  contrib/shpimporter/uesg.py,
+	  contrib/shpimporter/fixpoints.py,
+	  contrib/shpimporter/axis.py,
+	  contrib/shpimporter/crosssectiontracks.py,
+	  contrib/shpimporter/km.py: Set the 'name' attribute for new features.
+
+	* contrib/shpimporter/importer.py: Some bugfixes and improvements:
+	  geometries are transformed into a destination coordinate system now.
+
+	* contrib/shpimporter/shpimporter.py: Use all importers and defined the
+	  destination srs.
+
 2012-05-02	Sascha L. Teichmann	<sascha.teichmann@intevation.de>
 
 	* src/main/java/de/intevation/flys/model/FastCrossSectionLine.java,
--- a/flys-backend/contrib/shpimporter/axis.py	Wed May 02 08:49:57 2012 +0000
+++ b/flys-backend/contrib/shpimporter/axis.py	Thu May 03 14:19:52 2012 +0000
@@ -27,6 +27,7 @@
     def createNewFeature(self, featureDef, feat, **args):
         newFeat = ogr.Feature(featureDef)
         newFeat.SetGeometry(feat.GetGeometryRef())
+        newFeat.SetField("name", args['name'])
 
         if self.IsFieldSet(feat, "river_id"):
             riverId = feat.GetField("river_id")
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/flys-backend/contrib/shpimporter/boundaries.py	Thu May 03 14:19:52 2012 +0000
@@ -0,0 +1,82 @@
+import ogr
+
+from importer import Importer
+
+TABLE_NAME="hydr_boundaries"
+TABLE_NAME_POLY="hydr_boundaries_poly"
+PATH="Hydrologie/Hydr.Grenzen/Linien"
+
+
+class HydrBoundary(Importer):
+
+    def getPath(self, base):
+        return "%s/%s" % (base, PATH)
+
+
+    def getTablename(self):
+        return TABLE_NAME
+
+
+    def isGeometryValid(self, geomType):
+        return geomType == 2
+
+
+    def isShapeRelevant(self, name, path):
+        return True
+
+
+    def getKind(self, path):
+        if path.find("BfG") > 0:
+            return 1
+        else:
+            return 2
+
+
+    def createNewFeature(self, featureDef, feat, **args):
+        kind  = self.getKind(args['path'])
+
+        newFeat  = ogr.Feature(featureDef)
+        geometry = feat.GetGeometryRef()
+        geometry.SetCoordinateDimension(2)
+
+        newFeat.SetGeometry(geometry)
+        newFeat.SetField("name", args['name'])
+        newFeat.SetField("kind", kind)
+
+        if self.IsFieldSet(feat, "river_id"):
+            newFeat.SetField("river_id", feat.GetField("river_id"))
+        else:
+            newFeat.SetField("river_id", self.river_id)
+
+        return newFeat
+
+
+
+class HydrBoundaryPoly(HydrBoundary):
+
+    def getTablename(self):
+        return TABLE_NAME_POLY
+
+
+    def isGeometryValid(self, geomType):
+        return geomType == 3 or geomType == 6
+
+
+    def createNewFeature(self, featureDef, feat, **args):
+        kind  = self.getKind(args['path'])
+
+        newFeat  = ogr.Feature(featureDef)
+        geometry = feat.GetGeometryRef()
+        geometry.SetCoordinateDimension(2)
+
+        newFeat.SetGeometry(geometry)
+        newFeat.SetField("name", args['name'])
+        newFeat.SetField("kind", kind)
+
+        if self.IsFieldSet(feat, "river_id"):
+            newFeat.SetField("river_id", feat.GetField("river_id"))
+        else:
+            newFeat.SetField("river_id", self.river_id)
+
+        return newFeat
+
--- a/flys-backend/contrib/shpimporter/buildings.py	Wed May 02 08:49:57 2012 +0000
+++ b/flys-backend/contrib/shpimporter/buildings.py	Thu May 03 14:19:52 2012 +0000
@@ -34,9 +34,9 @@
             newFeat.SetField("river_id", self.river_id)
 
         if self.IsFieldSet(feat, "Name"):
-            name = feat.GetField("Name")
-            print "Building name '%s'" % name
-            newFeat.SetField("name", name)
+            newFeat.SetField("name", feat.GetField("Name"))
+        elif self.IsFieldSet(feat, "KWNAAM"):
+            newFeat.SetField("name", feat.GetField("KWNAAM"))
 
         return newFeat
 
--- a/flys-backend/contrib/shpimporter/crosssectiontracks.py	Wed May 02 08:49:57 2012 +0000
+++ b/flys-backend/contrib/shpimporter/crosssectiontracks.py	Thu May 03 14:19:52 2012 +0000
@@ -27,6 +27,7 @@
     def createNewFeature(self, featureDef, feat, **args):
         newFeat = ogr.Feature(featureDef)
         newFeat.SetGeometry(feat.GetGeometryRef())
+        newFeat.SetField("name", args['name'])
 
         if self.IsFieldSet(feat, "river_id"):
             newFeat.SetField("river_id", feat.GetField("river_id"))
--- a/flys-backend/contrib/shpimporter/fixpoints.py	Wed May 02 08:49:57 2012 +0000
+++ b/flys-backend/contrib/shpimporter/fixpoints.py	Thu May 03 14:19:52 2012 +0000
@@ -1,4 +1,4 @@
-import ogr
+import ogr, osr
 
 from importer import Importer
 
@@ -25,8 +25,11 @@
 
 
     def createNewFeature(self, featureDef, feat, **args):
-        newFeat = ogr.Feature(featureDef)
-        newFeat.SetGeometry(feat.GetGeometryRef())
+        newFeat  = ogr.Feature(featureDef)
+        geometry = feat.GetGeometryRef()
+
+        newFeat.SetGeometry(geometry)
+        newFeat.SetField("name", args['name'])
 
         if self.IsFieldSet(feat, "river_id"):
             newFeat.SetField("river_id", feat.GetField("river_id"))
@@ -35,6 +38,8 @@
 
         if self.IsFieldSet(feat, "KM"):
             newFeat.SetField("km", feat.GetFieldAsDouble("KM"))
+        elif self.IsFieldSet(feat, "ELBE_KM"):
+            newFeat.SetField("km", feat.GetFieldAsDouble("ELBE_KM"))
         else:
             return None
 
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/flys-backend/contrib/shpimporter/floodplains.py	Thu May 03 14:19:52 2012 +0000
@@ -0,0 +1,40 @@
+import ogr
+
+from importer import Importer
+
+TABLE_NAME="floodplain"
+PATH="Hydrologie/Hydr.Grenzen"
+
+
+class Floodplain(Importer):
+
+    def getPath(self, base):
+        return "%s/%s" % (base, PATH)
+
+
+    def getTablename(self):
+        return TABLE_NAME
+
+
+    def isGeometryValid(self, geomType):
+        return geomType == 3 or geomType == 6
+
+
+    def isShapeRelevant(self, name, path):
+        return name.find("talaue") >= 0
+
+
+    def createNewFeature(self, featureDef, feat, **args):
+        newFeat  = ogr.Feature(featureDef)
+        geometry = feat.GetGeometryRef()
+
+        newFeat.SetGeometry(geometry)
+        newFeat.SetField("name", args['name'])
+
+        if self.IsFieldSet(feat, "river_id"):
+            newFeat.SetField("river_id", feat.GetField("river_id"))
+        else:
+            newFeat.SetField("river_id", self.river_id)
+
+        return newFeat
+
--- a/flys-backend/contrib/shpimporter/importer.py	Wed May 02 08:49:57 2012 +0000
+++ b/flys-backend/contrib/shpimporter/importer.py	Thu May 03 14:19:52 2012 +0000
@@ -1,10 +1,12 @@
-import ogr
+import ogr, osr
 
 class Importer:
 
-    def __init__(self, dbconn, river_id):
+    def __init__(self, dbconn, river_id, dest_srs):
         self.dbconn   = dbconn
         self.river_id = river_id
+        self.dest_srs = osr.SpatialReference()
+        self.dest_srs.ImportFromEPSG(dest_srs)
 
 
     def getKind(self, path):
@@ -60,6 +62,20 @@
         return self.shape2Database(srcLayer, name, path)
 
 
+    def transform(self, feat):
+        geometry = feat.GetGeometryRef()
+        src_srs  = geometry.GetSpatialReference()
+
+        if src_srs is None:
+            print "Error: No source SRS given! No transformation possible!"
+            return feat
+
+        transformer = osr.CoordinateTransformation(src_srs, self.dest_srs)
+        geometry.Transform(transformer)
+
+        return feat
+
+
     def shape2Database(self, srcLayer, name, path):
         table     = ogr.Open(self.dbconn)
         destLayer = table.GetLayerByName(self.getTablename())
@@ -80,10 +96,15 @@
         geomType    = -1
         success     = 0
         unsupported = 0
+        creationFailed = 0
         featureDef  = destLayer.GetLayerDefn()
 
         for feat in srcLayer:
             geom     = feat.GetGeometryRef()
+
+            if geom is None:
+                continue
+
             geomType = geom.GetGeometryType()
 
             if self.isGeometryValid(geomType):
@@ -93,15 +114,19 @@
                                                 path=path)
 
                 if newFeat is not None:
+                    newFeat = self.transform(newFeat)
                     res = destLayer.CreateFeature(newFeat)
                     if res is None or res > 0:
                         print "Error while inserting feature: %r" % res
                     else:
                         success = success + 1
+                else:
+                    creationFailed = creationFailed + 1
             else:
                 unsupported = unsupported + 1
 
         print "Inserted %i features" % success
+        print "Failed to create %i features" % creationFailed
         print "Found %i unsupported features" % unsupported
 
         try:
--- a/flys-backend/contrib/shpimporter/km.py	Wed May 02 08:49:57 2012 +0000
+++ b/flys-backend/contrib/shpimporter/km.py	Thu May 03 14:19:52 2012 +0000
@@ -27,6 +27,7 @@
     def createNewFeature(self, featureDef, feat, **args):
         newFeat = ogr.Feature(featureDef)
         newFeat.SetGeometry(feat.GetGeometryRef())
+        newFeat.SetField("name", args['name'])
 
         if self.IsFieldSet(feat, "river_id"):
             newFeat.SetField("river_id", feat.GetField("river_id"))
--- a/flys-backend/contrib/shpimporter/lines.py	Wed May 02 08:49:57 2012 +0000
+++ b/flys-backend/contrib/shpimporter/lines.py	Thu May 03 14:19:52 2012 +0000
@@ -17,7 +17,7 @@
 
 
     def isGeometryValid(self, geomType):
-        return geomType == 2
+        return geomType == 2 or geomType == -2147483646
 
 
     def isShapeRelevant(self, name, path):
@@ -25,8 +25,12 @@
 
 
     def createNewFeature(self, featureDef, feat, **args):
-        newFeat = ogr.Feature(featureDef)
-        newFeat.SetGeometry(feat.GetGeometryRef())
+        newFeat  = ogr.Feature(featureDef)
+        geometry = feat.GetGeometryRef()
+        geometry.SetCoordinateDimension(2)
+
+        newFeat.SetGeometry(geometry)
+        newFeat.SetField("name", args['name'])
 
         if self.IsFieldSet(feat, "river_id"):
             newFeat.SetField("river_id", feat.GetField("river_id"))
@@ -36,12 +40,12 @@
         if self.IsFieldSet(feat, "TYP"):
             newFeat.SetField("kind", feat.GetFieldAsDouble("TYP"))
         else:
-            return None
+            newFeat.SetField("kind", "DAMM")
 
         if self.IsFieldSet(feat, "Z"):
             newFeat.SetField("z", feat.GetFieldAsDouble("Z"))
         else:
-            newFeat.SetField("z", 0)
+            newFeat.SetField("z", 9999)
 
         return newFeat
 
--- a/flys-backend/contrib/shpimporter/shpimporter.py	Wed May 02 08:49:57 2012 +0000
+++ b/flys-backend/contrib/shpimporter/shpimporter.py	Thu May 03 14:19:52 2012 +0000
@@ -8,21 +8,28 @@
 from fixpoints import Fixpoint
 from buildings import Building
 from crosssectiontracks import CrosssectionTrack
+from floodplains import Floodplain
+from boundaries import HydrBoundary, HydrBoundaryPoly
 
 DBCONN='OCI:user/pass@host'
 PATH='/path/to/Gewaesser/Elbe'
 RIVER_ID=the_river_id
+DEST_SRS=31467
 
 
 def getImporters():
     return [
-        Axis(DBCONN, RIVER_ID),
-        KM(DBCONN, RIVER_ID),
-        CrosssectionTrack(DBCONN, RIVER_ID),
-        Line(DBCONN, RIVER_ID),
-        Fixpoint(DBCONN, RIVER_ID),
-        Building(DBCONN, RIVER_ID),
-        UESG(DBCONN, RIVER_ID)]
+        Axis(DBCONN, RIVER_ID, DEST_SRS),
+        KM(DBCONN, RIVER_ID, DEST_SRS),
+        CrosssectionTrack(DBCONN, RIVER_ID, DEST_SRS),
+        Line(DBCONN, RIVER_ID, DEST_SRS),
+        Fixpoint(DBCONN, RIVER_ID, DEST_SRS),
+        Building(DBCONN, RIVER_ID, DEST_SRS),
+        Floodplain(DBCONN, RIVER_ID, DEST_SRS),
+        HydrBoundary(DBCONN, RIVER_ID, DEST_SRS),
+        HydrBoundaryPoly(DBCONN, RIVER_ID, DEST_SRS),
+        UESG(DBCONN, RIVER_ID, DEST_SRS)
+        ]
 
 
 if __name__ == '__main__':
--- a/flys-backend/contrib/shpimporter/uesg.py	Wed May 02 08:49:57 2012 +0000
+++ b/flys-backend/contrib/shpimporter/uesg.py	Thu May 03 14:19:52 2012 +0000
@@ -55,22 +55,22 @@
         else:
             riverId = self.river_id
 
-        if feat.IsFieldSet("diff"):
+        if self.IsFieldSet(feat, "diff"):
             diff = feat.GetFieldAsDouble("diff")
         else:
             diff = 0
 
-        if feat.IsFieldSet("count"):
+        if self.IsFieldSet(feat, "count"):
             count = feat.GetFieldAsInteger("count")
         else:
             count = 0
 
-        if feat.IsFieldSet("area"):
+        if self.IsFieldSet(feat, "area"):
             area = feat.GetFieldAsDouble("area")
         else:
             area = 0
 
-        if feat.IsFieldSet("perimeter"):
+        if self.IsFieldSet(feat, "perimeter"):
             perimeter = feat.GetFieldAsDouble("perimeter")
         else:
             perimeter = 0
--- a/flys-backend/doc/schema/oracle-drop-spatial.sql	Wed May 02 08:49:57 2012 +0000
+++ b/flys-backend/doc/schema/oracle-drop-spatial.sql	Thu May 03 14:19:52 2012 +0000
@@ -51,3 +51,13 @@
 DELETE FROM USER_SDO_GEOM_METADATA WHERE TABLE_NAME = 'FLOODMAPS';
 DROP TRIGGER floodmaps_trigger;
 DROP SEQUENCE FLOODMAPS_ID_SEQ;
+
+DROP TABLE hydr_boundaries;
+DELETE FROM USER_SDO_GEOM_METADATA WHERE TABLE_NAME = 'hydr_boundaries';
+DROP TRIGGER hydr_boundaries_trigger;
+DROP SEQUENCE HYDR_BOUNDARIES_ID_SEQ;
+
+DROP TABLE hydr_boundaries_poly;
+DELETE FROM USER_SDO_GEOM_METADATA WHERE TABLE_NAME = 'hydr_boundaries_poly';
+DROP TRIGGER hydr_boundaries_poly_trigger;
+DROP SEQUENCE HYDR_BOUNDARIES_POLY_ID_SEQ;
--- a/flys-backend/doc/schema/oracle-spatial.sql	Wed May 02 08:49:57 2012 +0000
+++ b/flys-backend/doc/schema/oracle-spatial.sql	Thu May 03 14:19:52 2012 +0000
@@ -5,6 +5,7 @@
     GEOM MDSYS.SDO_GEOMETRY,
     river_id NUMBER(38),
     kind     NUMBER(38) DEFAULT 0 NOT NULL,
+    name     VARCHAR(64),
     ID NUMBER PRIMARY KEY NOT NULL
 );
 INSERT INTO USER_SDO_GEOM_METADATA VALUES ('river_axes', 'GEOM', MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',3282450,3912240,0.001),MDSYS.SDO_DIM_ELEMENT('Y',5248260,6100130,0.001),MDSYS.SDO_DIM_ELEMENT('Z',-100000,100000,0.002)), 31467);
@@ -23,6 +24,7 @@
     GEOM MDSYS.SDO_GEOMETRY,
     river_id NUMBER(38),
     km NUMBER(6,3),
+    name     VARCHAR(64),
     ID NUMBER PRIMARY KEY NOT NULL
 );
 INSERT INTO USER_SDO_GEOM_METADATA VALUES ('river_axes_km', 'GEOM', MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',3282450,3912240,0.001),MDSYS.SDO_DIM_ELEMENT('Y',5248260,6100130,0.001),MDSYS.SDO_DIM_ELEMENT('Z',-100000,100000,0.002)), 31467);
@@ -42,6 +44,7 @@
     river_id NUMBER(38),
     km       NUMBER(38,12) NOT NULL,
     z        NUMBER(38,12) DEFAULT 0 NOT NULL,
+    name     VARCHAR(64),
     ID NUMBER PRIMARY KEY NOT NULL
 );
 INSERT INTO USER_SDO_GEOM_METADATA VALUES ('cross_section_tracks', 'GEOM', MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',3282450,3912240,0.001),MDSYS.SDO_DIM_ELEMENT('Y',5248260,6100130,0.001),MDSYS.SDO_DIM_ELEMENT('Z',-100000,100000,0.002)), 31467);
@@ -62,6 +65,7 @@
     river_id NUMBER(38),
     kind     VARCHAR2(16) NOT NULL,
     z        NUMBER(38,12) DEFAULT 0,
+    name     VARCHAR(64),
     ID NUMBER PRIMARY KEY NOT NULL
 );
 INSERT INTO USER_SDO_GEOM_METADATA VALUES ('lines', 'GEOM', MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',3282450,3912240,0.001),MDSYS.SDO_DIM_ELEMENT('Y',5248260,6100130,0.001),MDSYS.SDO_DIM_ELEMENT('Z',-100000,100000,0.002)), 31467);
@@ -106,6 +110,7 @@
     y NUMBER(38,11),
     km NUMBER(38,11) NOT NULL,
     HPGP VARCHAR2(255),
+    name VARCHAR(64),
     ID NUMBER PRIMARY KEY NOT NULL
 );
 INSERT INTO USER_SDO_GEOM_METADATA VALUES ('fixpoints', 'GEOM', MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',3282450,3912240,0.001),MDSYS.SDO_DIM_ELEMENT('Y',5248260,6100130,0.001),MDSYS.SDO_DIM_ELEMENT('Z',-100000,100000,0.002)), 31467);
@@ -123,6 +128,7 @@
     OGR_FID NUMBER(38),
     GEOM MDSYS.SDO_GEOMETRY,
     river_id NUMBER(38),
+    name     VARCHAR(64),
     ID NUMBER PRIMARY KEY NOT NULL
 );
 INSERT INTO USER_SDO_GEOM_METADATA VALUES ('floodplain', 'GEOM', MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',3282450,3912240,0.001),MDSYS.SDO_DIM_ELEMENT('Y',5248260,6100130,0.001),MDSYS.SDO_DIM_ELEMENT('Z',-100000,100000,0.002)), 31467);
@@ -181,6 +187,7 @@
     river_id NUMBER(38),
     hws_facility VARCHAR2(255),
     type VARCHAR2(255),
+    name VARCHAR(64),
     ID NUMBER PRIMARY KEY NOT NULL
 );
 INSERT INTO USER_SDO_GEOM_METADATA VALUES ('hws', 'GEOM', MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',3282450,3912240,0.001),MDSYS.SDO_DIM_ELEMENT('Y',5248260,6100130,0.001),MDSYS.SDO_DIM_ELEMENT('Z',-100000,100000,0.002)), 31467);
@@ -213,3 +220,39 @@
     END;
 /
 CREATE INDEX floodmaps_spatial_idx ON floodmaps(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=MULTIPOLYGON');
+
+
+--Hydrologie/Hydr.Grenzen/Linien
+CREATE SEQUENCE HYDR_BOUNDARIES_ID_SEQ;
+CREATE TABLE hydr_boundaries (
+    OGR_FID NUMBER(38),
+    GEOM MDSYS.SDO_GEOMETRY,
+    river_id NUMBER(38),
+    name VARCHAR(255),
+    kind NUMBER(38),
+    id NUMBER PRIMARY KEY NOT NULL
+);
+INSERT INTO USER_SDO_GEOM_METADATA VALUES ('hydr_boundaries', 'GEOM', MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',3282450,3912240,0.001),MDSYS.SDO_DIM_ELEMENT('Y',5248260,6100130,0.001),MDSYS.SDO_DIM_ELEMENT('Z',-100000,100000,0.002)), 31467);
+CREATE OR REPLACE TRIGGER hydr_boundaries_trigger BEFORE INSERT ON hydr_boundaries FOR each ROW
+    BEGIN
+        SELECT HYDR_BOUNDARIES_ID_SEQ.nextval INTO :new.id FROM dual;
+    END;
+/
+CREATE INDEX hydr_boundaries_idx ON hydr_boundaries(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=LINE');
+
+CREATE SEQUENCE HYDR_BOUNDARIES_POLY_ID_SEQ;
+CREATE TABLE hydr_boundaries_poly (
+    OGR_FID NUMBER(38),
+    GEOM MDSYS.SDO_GEOMETRY,
+    river_id NUMBER(38),
+    name VARCHAR(255),
+    kind NUMBER(38),
+    id NUMBER PRIMARY KEY NOT NULL
+);
+INSERT INTO USER_SDO_GEOM_METADATA VALUES ('hydr_boundaries_poly', 'GEOM', MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',3282450,3912240,0.001),MDSYS.SDO_DIM_ELEMENT('Y',5248260,6100130,0.001),MDSYS.SDO_DIM_ELEMENT('Z',-100000,100000,0.002)), 31467);
+CREATE OR REPLACE TRIGGER hydr_boundaries_poly_trigger BEFORE INSERT ON hydr_boundaries_poly FOR each ROW
+    BEGIN
+        SELECT HYDR_BOUNDARIES_POLY_ID_SEQ.nextval INTO :new.id FROM dual;
+    END;
+/
+CREATE INDEX hydr_boundaries_poly_idx ON hydr_boundaries_poly(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=MULTIPOLYGON');
http://dive4elements.wald.intevation.org