Mercurial > dive4elements > river

comparison flys-backend/doc/schema/oracle-spatial.sql @ 1253:a75e7d922942

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Deactivated spatial indexes. flys-backend/trunk@2747 c6561f87-3c4e-4783-a992-168aeb5c3f6f
author Bjoern Schilberg <bjoern@intevation.de>
date Wed, 14 Sep 2011 14:49:53 +0000
parents f124c99077b5
children e1fb4a1e0b4a
comparison
equal deleted inserted replaced
1252:d00a50c69a67 1253:a75e7d922942
1 BEGIN;
2
3
4 -- Hydrologie/Einzugsgebiete/EZG.shp 1 -- Hydrologie/Einzugsgebiete/EZG.shp
5 2
6 CREATE SEQUENCE CATCHMENT_ID_SEQ; 3 CREATE SEQUENCE CATCHMENT_ID_SEQ;
7 CREATE TABLE catchment( 4 CREATE TABLE catchment(
8 OGR_FID NUMBER(38), 5 OGR_FID NUMBER(38),
19 BEGIN 16 BEGIN
20 SELECT CATCHMENT_ID_SEQ.nextval INTO :new.id FROM dual; 17 SELECT CATCHMENT_ID_SEQ.nextval INTO :new.id FROM dual;
21 END; 18 END;
22 / 19 /
23 --/opt/gdal-1.8.0/bin/ogr2ogr -f OCI OCI:flys3/flys3@localhost EZG.shp -sql 'SELECT 1 AS river_id,AREA as area, GEBIETZBEZ as name FROM EZG' -nln catchment -append 20 --/opt/gdal-1.8.0/bin/ogr2ogr -f OCI OCI:flys3/flys3@localhost EZG.shp -sql 'SELECT 1 AS river_id,AREA as area, GEBIETZBEZ as name FROM EZG' -nln catchment -append
24 CREATE INDEX catchment_spatial_idx ON catchment(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=polygon'); 21 --CREATE INDEX catchment_spatial_idx ON catchment(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=polygon');
25 22
26 -- Geodaesie/Flussachse+km/km.shp 23 -- Geodaesie/Flussachse+km/km.shp
27 CREATE SEQUENCE RIVER_AXES_KM_ID_SEQ; 24 CREATE SEQUENCE RIVER_AXES_KM_ID_SEQ;
28 CREATE TABLE river_axes_km( 25 CREATE TABLE river_axes_km(
29 OGR_FID NUMBER(38), 26 OGR_FID NUMBER(38),
38 BEGIN 35 BEGIN
39 SELECT river_axes_km_ID_SEQ.nextval INTO :new.id FROM dual; 36 SELECT river_axes_km_ID_SEQ.nextval INTO :new.id FROM dual;
40 END; 37 END;
41 / 38 /
42 --/opt/gdal-1.8.0/bin/ogr2ogr -f OCI OCI:flys3/flys3@localhost km.shp -sql 'SELECT 1 AS river_id,km as km FROM km' -nln river_axes_km -append 39 --/opt/gdal-1.8.0/bin/ogr2ogr -f OCI OCI:flys3/flys3@localhost km.shp -sql 'SELECT 1 AS river_id,km as km FROM km' -nln river_axes_km -append
43 CREATE INDEX river_axes_km_spatial_idx ON river_axes_km(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=point'); 40 --CREATE INDEX river_axes_km_spatial_idx ON river_axes_km(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=point');
44 41
45 -- Geodaesie/Bauwerke/Wehre.shp 42 -- Geodaesie/Bauwerke/Wehre.shp
46 CREATE SEQUENCE BUILDINGS_ID_SEQ; 43 CREATE SEQUENCE BUILDINGS_ID_SEQ;
47 CREATE TABLE buildings( 44 CREATE TABLE buildings(
48 OGR_FID NUMBER(38), 45 OGR_FID NUMBER(38),
57 BEGIN 54 BEGIN
58 SELECT BUILDINGS_ID_SEQ.nextval INTO :new.id FROM dual; 55 SELECT BUILDINGS_ID_SEQ.nextval INTO :new.id FROM dual;
59 END; 56 END;
60 / 57 /
61 --/opt/gdal-1.8.0/bin/ogr2ogr -f OCI OCI:flys3/flys3@localhost Wehre.shp -sql 'SELECT 1 AS river_id,Name as name FROM Wehre' -nln buildings -append 58 --/opt/gdal-1.8.0/bin/ogr2ogr -f OCI OCI:flys3/flys3@localhost Wehre.shp -sql 'SELECT 1 AS river_id,Name as name FROM Wehre' -nln buildings -append
62 CREATE INDEX buildings_spatial_idx ON buildings(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=LINE'); 59 --CREATE INDEX buildings_spatial_idx ON buildings(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=LINE');
63 60
64 -- Geodaesie/Festpunkte/Festpunkte.shp 61 -- Geodaesie/Festpunkte/Festpunkte.shp
65 CREATE SEQUENCE FIXPOINTS_ID_SEQ; 62 CREATE SEQUENCE FIXPOINTS_ID_SEQ;
66 CREATE TABLE fixpoints ( 63 CREATE TABLE fixpoints (
67 OGR_FID NUMBER(38), 64 OGR_FID NUMBER(38),
79 BEGIN 76 BEGIN
80 SELECT FIXPOINTS_ID_SEQ.nextval INTO :new.id FROM dual; 77 SELECT FIXPOINTS_ID_SEQ.nextval INTO :new.id FROM dual;
81 END; 78 END;
82 / 79 /
83 --/opt/gdal-1.8.0/bin/ogr2ogr -f OCI OCI:flys3/flys3@localhost Festpunkte.shp -sql 'SELECT 1 AS river_id,x as x,y as y, km as km, HPGP as HPGP FROM Festpunkte' -nln fixpoints -append 80 --/opt/gdal-1.8.0/bin/ogr2ogr -f OCI OCI:flys3/flys3@localhost Festpunkte.shp -sql 'SELECT 1 AS river_id,x as x,y as y, km as km, HPGP as HPGP FROM Festpunkte' -nln fixpoints -append
84 CREATE INDEX fixpoints_spatial_idx ON fixpoints(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=POINT'); 81 --CREATE INDEX fixpoints_spatial_idx ON fixpoints(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=POINT');
85 82
86 -- Geodaesie/Flussachse+km/achse 83 -- Geodaesie/Flussachse+km/achse
87 CREATE SEQUENCE RIVER_AXES_ID_SEQ; 84 CREATE SEQUENCE RIVER_AXES_ID_SEQ;
88 CREATE TABLE river_axes( 85 CREATE TABLE river_axes(
89 OGR_FID NUMBER(38), 86 OGR_FID NUMBER(38),
98 BEGIN 95 BEGIN
99 SELECT RIVER_AXES_ID_SEQ.nextval INTO :new.id FROM dual; 96 SELECT RIVER_AXES_ID_SEQ.nextval INTO :new.id FROM dual;
100 END; 97 END;
101 / 98 /
102 --/opt/gdal-1.8.0/bin/ogr2ogr -f OCI OCI:flys3/flys3@localhost achse.shp -sql 'SELECT 1 AS river_id,0 as kind from achse' -nln river_axes -append 99 --/opt/gdal-1.8.0/bin/ogr2ogr -f OCI OCI:flys3/flys3@localhost achse.shp -sql 'SELECT 1 AS river_id,0 as kind from achse' -nln river_axes -append
103 CREATE INDEX river_axes_spatial_idx ON river_axes(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=LINE'); 100 --CREATE INDEX river_axes_spatial_idx ON river_axes(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=LINE');
104 101
105 --Geodaesie/Querprofile/QP-Spuren/qps.shp 102 --Geodaesie/Querprofile/QP-Spuren/qps.shp
106 CREATE SEQUENCE CROSS_SECTION_TRACKS_ID_SEQ; 103 CREATE SEQUENCE CROSS_SECTION_TRACKS_ID_SEQ;
107 CREATE TABLE cross_section_tracks ( 104 CREATE TABLE cross_section_tracks (
108 OGR_FID NUMBER(38), 105 OGR_FID NUMBER(38),
118 BEGIN 115 BEGIN
119 SELECT CROSS_SECTION_TRACKS_ID_SEQ.nextval INTO :new.id FROM dual; 116 SELECT CROSS_SECTION_TRACKS_ID_SEQ.nextval INTO :new.id FROM dual;
120 END; 117 END;
121 / 118 /
122 -- /opt/gdal-1.8.0/bin/ogr2ogr -f OCI OCI:flys3/flys3@localhost qps.shp -sql 'SELECT 1 AS river_id,KILOMETER as km, ELEVATION as z from qps' -nln cross_section_tracks -append 119 -- /opt/gdal-1.8.0/bin/ogr2ogr -f OCI OCI:flys3/flys3@localhost qps.shp -sql 'SELECT 1 AS river_id,KILOMETER as km, ELEVATION as z from qps' -nln cross_section_tracks -append
123 CREATE INDEX CrossSectionTracks_spatial_idx ON cross_section_tracks(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=LINE'); 120 --CREATE INDEX CrossSectionTracks_spatial_idx ON cross_section_tracks(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=LINE');
124 121
125 --Hydrologie/HW-Schutzanlagen/hws.shp 122 --Hydrologie/HW-Schutzanlagen/hws.shp
126 CREATE SEQUENCE HWS_ID_SEQ; 123 CREATE SEQUENCE HWS_ID_SEQ;
127 CREATE TABLE hws( 124 CREATE TABLE hws(
128 OGR_FID NUMBER(38), 125 OGR_FID NUMBER(38),
138 BEGIN 135 BEGIN
139 SELECT HWS_ID_SEQ.nextval INTO :new.id FROM dual; 136 SELECT HWS_ID_SEQ.nextval INTO :new.id FROM dual;
140 END; 137 END;
141 / 138 /
142 --/opt/gdal-1.8.0/bin/ogr2ogr -f OCI OCI:flys3/flys3@localhost hws.shp -sql 'SELECT 1 AS river_id,HWS_ANLAGE as hws_facility, TYP as type from HWS' -nln hws -append 139 --/opt/gdal-1.8.0/bin/ogr2ogr -f OCI OCI:flys3/flys3@localhost hws.shp -sql 'SELECT 1 AS river_id,HWS_ANLAGE as hws_facility, TYP as type from HWS' -nln hws -append
143 CREATE INDEX hws_spatial_idx ON hws(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=LINE'); 140 --CREATE INDEX hws_spatial_idx ON hws(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=LINE');
144 141
145 -- Hydrologie/Hydr. Grenzen/talaue.shp 142 -- Hydrologie/Hydr. Grenzen/talaue.shp
146 CREATE SEQUENCE FLOODPLAIN_ID_SEQ; 143 CREATE SEQUENCE FLOODPLAIN_ID_SEQ;
147 CREATE TABLE floodplain( 144 CREATE TABLE floodplain(
148 OGR_FID NUMBER(38), 145 OGR_FID NUMBER(38),
156 BEGIN 153 BEGIN
157 SELECT FLOODPLAIN_ID_SEQ.nextval INTO :new.id FROM dual; 154 SELECT FLOODPLAIN_ID_SEQ.nextval INTO :new.id FROM dual;
158 END; 155 END;
159 / 156 /
160 --/opt/gdal-1.8.0/bin/ogr2ogr -f OCI OCI:flys3/flys3@localhost talaue.shp -sql 'SELECT 1 AS river_id from talaue' -nln floodplain -append 157 --/opt/gdal-1.8.0/bin/ogr2ogr -f OCI OCI:flys3/flys3@localhost talaue.shp -sql 'SELECT 1 AS river_id from talaue' -nln floodplain -append
161 CREATE INDEX floodplain_spatial_idx ON floodplain(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=POLYGON'); 158 --CREATE INDEX floodplain_spatial_idx ON floodplain(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=POLYGON');
162
163 END;
http://dive4elements.wald.intevation.org