# HG changeset patch # User Bjoern Schilberg # Date 1316774545 0 # Node ID b28ab244a77d1f6e90643152725d7ebe0b370b52 # Parent e69ef36290ae4f17815fbbd4ee7f65e5f67ad8b7 Harmonized oracle and postgresql spatial schema. flys-backend/trunk@2817 c6561f87-3c4e-4783-a992-168aeb5c3f6f diff -r e69ef36290ae -r b28ab244a77d flys-backend/ChangeLog --- a/flys-backend/ChangeLog Thu Sep 22 12:10:08 2011 +0000 +++ b/flys-backend/ChangeLog Fri Sep 23 10:42:25 2011 +0000 @@ -1,3 +1,8 @@ +2011-09-22 Bjoern Schilberg + + * doc/schema/postgresql-spatial.sql, doc/schema/oracle-spatial.sql: + Harmonized oracle and postgresql spatial schema. + 2011-09-22 Bjoern Schilberg * doc/schema/oracle-spatial.sql: Harmonized Extent for Saar and Mosel. Some cleanups. diff -r e69ef36290ae -r b28ab244a77d flys-backend/doc/schema/oracle-spatial.sql --- a/flys-backend/doc/schema/oracle-spatial.sql Thu Sep 22 12:10:08 2011 +0000 +++ b/flys-backend/doc/schema/oracle-spatial.sql Fri Sep 23 10:42:25 2011 +0000 @@ -1,3 +1,122 @@ +-- Geodaesie/Flussachse+km/achse +CREATE SEQUENCE RIVER_AXES_ID_SEQ; +CREATE TABLE river_axes( + OGR_FID NUMBER(38), + GEOM MDSYS.SDO_GEOMETRY, + river_id NUMBER(38), + kind NUMBER(38) DEFAULT 0 NOT NULL, + 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',2457864.326387,2634771.191263,0.001),MDSYS.SDO_DIM_ELEMENT('Y',5297459.306295,5586961.449130,0.001),MDSYS.SDO_DIM_ELEMENT('Z',-100000,100000,0.002)), 31466); +CREATE OR REPLACE TRIGGER river_axes_trigger BEFORE INSERT ON river_axes FOR each ROW + BEGIN + SELECT RIVER_AXES_ID_SEQ.nextval INTO :new.id FROM dual; + END; +/ +--CREATE INDEX river_axes_spatial_idx ON river_axes(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=LINE'); + + +-- Geodaesie/Flussachse+km/km.shp +CREATE SEQUENCE RIVER_AXES_KM_ID_SEQ; +CREATE TABLE river_axes_km( + OGR_FID NUMBER(38), + GEOM MDSYS.SDO_GEOMETRY, + river_id NUMBER(38), + km NUMBER(6,3), + 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',2457864.326387,2634771.191263,0.001),MDSYS.SDO_DIM_ELEMENT('Y',5297459.306295,5586961.449130,0.001),MDSYS.SDO_DIM_ELEMENT('Z',-100000,100000,0.002)), 31466); +CREATE OR REPLACE TRIGGER river_axes_km_trigger BEFORE INSERT ON river_axes_km FOR each ROW + BEGIN + SELECT river_axes_km_ID_SEQ.nextval INTO :new.id FROM dual; + END; +/ +--CREATE INDEX river_axes_km_spatial_idx ON river_axes_km(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=point'); + + +--Geodaesie/Querprofile/QP-Spuren/qps.shp +CREATE SEQUENCE CROSS_SECTION_TRACKS_ID_SEQ; +CREATE TABLE cross_section_tracks ( + OGR_FID NUMBER(38), + GEOM MDSYS.SDO_GEOMETRY, + river_id NUMBER(38), + km NUMBER(38,12) NOT NULL, + z NUMBER(38,12) DEFAULT 0 NOT NULL, + 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',2457864.326387,2634771.191263,0.001),MDSYS.SDO_DIM_ELEMENT('Y',5297459.306295,5586961.449130,0.001),MDSYS.SDO_DIM_ELEMENT('Z',-100000,100000,0.002)), 31466); +CREATE OR REPLACE TRIGGER cross_section_tracks_trigger BEFORE INSERT ON cross_section_tracks FOR each ROW + BEGIN + SELECT CROSS_SECTION_TRACKS_ID_SEQ.nextval INTO :new.id FROM dual; + END; +/ +--CREATE INDEX CrossSectionTracks_spatial_idx ON cross_section_tracks(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=LINE'); + + +-- TODO +-- Geodaesie/Linien/rohre-und-spreen + + +-- Geodaesie/Bauwerke/Wehre.shp +CREATE SEQUENCE BUILDINGS_ID_SEQ; +CREATE TABLE buildings( + OGR_FID NUMBER(38), + GEOM MDSYS.SDO_GEOMETRY, + river_id NUMBER(38), + name VARCHAR2(255), + ID NUMBER PRIMARY KEY NOT NULL +); +INSERT INTO USER_SDO_GEOM_METADATA VALUES ('buildings', 'GEOM', MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',2457864.326387,2634771.191263,0.001),MDSYS.SDO_DIM_ELEMENT('Y',5297459.306295,5586961.449130,0.001),MDSYS.SDO_DIM_ELEMENT('Z',-100000,100000,0.002)), 31466); +CREATE OR REPLACE TRIGGER buildings_trigger BEFORE INSERT ON buildings FOR each ROW + BEGIN + SELECT BUILDINGS_ID_SEQ.nextval INTO :new.id FROM dual; + END; +/ +--CREATE INDEX buildings_spatial_idx ON buildings(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=LINE'); + + +-- Geodaesie/Festpunkte/Festpunkte.shp +CREATE SEQUENCE FIXPOINTS_ID_SEQ; +CREATE TABLE fixpoints ( + OGR_FID NUMBER(38), + GEOM MDSYS.SDO_GEOMETRY, + river_id NUMBER(38), + x NUMBER(38,11), + y NUMBER(38,11), + km NUMBER(38,11) NOT NULL, + HPGP VARCHAR2(255), + ID NUMBER PRIMARY KEY NOT NULL +); +INSERT INTO USER_SDO_GEOM_METADATA VALUES ('fixpoints', 'GEOM', MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',2457864.326387,2634771.191263,0.001),MDSYS.SDO_DIM_ELEMENT('Y',5297459.306295,5586961.449130,0.001),MDSYS.SDO_DIM_ELEMENT('Z',-100000,100000,0.002)), 31466); +CREATE OR REPLACE TRIGGER fixpoints_trigger BEFORE INSERT ON fixpoints FOR each ROW + BEGIN + SELECT FIXPOINTS_ID_SEQ.nextval INTO :new.id FROM dual; + END; +/ +--CREATE INDEX fixpoints_spatial_idx ON fixpoints(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=POINT'); + + +-- Hydrologie/Hydr. Grenzen/talaue.shp +CREATE SEQUENCE FLOODPLAIN_ID_SEQ; +CREATE TABLE floodplain( + OGR_FID NUMBER(38), + GEOM MDSYS.SDO_GEOMETRY, + river_id NUMBER(38), + ID NUMBER PRIMARY KEY NOT NULL +); +INSERT INTO USER_SDO_GEOM_METADATA VALUES ('floodplain', 'GEOM', MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',2457864.326387,2634771.191263,0.001),MDSYS.SDO_DIM_ELEMENT('Y',5297459.306295,5586961.449130,0.001),MDSYS.SDO_DIM_ELEMENT('Z',-100000,100000,0.002)), 31466); +CREATE OR REPLACE TRIGGER floodplain_trigger BEFORE INSERT ON floodplain FOR each ROW + BEGIN + SELECT FLOODPLAIN_ID_SEQ.nextval INTO :new.id FROM dual; + END; +/ +--CREATE INDEX floodplain_spatial_idx ON floodplain(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=POLYGON'); + + +-- TODO +-- Geodaesie/Hoehenmodelle/* + + -- Hydrologie/Einzugsgebiete/EZG.shp CREATE SEQUENCE CATCHMENT_ID_SEQ; CREATE TABLE catchment( @@ -17,96 +136,6 @@ / --CREATE INDEX catchment_spatial_idx ON catchment(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=polygon'); --- Geodaesie/Flussachse+km/km.shp -CREATE SEQUENCE RIVER_AXES_KM_ID_SEQ; -CREATE TABLE river_axes_km( - OGR_FID NUMBER(38), - GEOM MDSYS.SDO_GEOMETRY, - river_id NUMBER(38), - km NUMBER(6,3), - 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',2457864.326387,2634771.191263,0.001),MDSYS.SDO_DIM_ELEMENT('Y',5297459.306295,5586961.449130,0.001),MDSYS.SDO_DIM_ELEMENT('Z',-100000,100000,0.002)), 31466); -CREATE OR REPLACE TRIGGER river_axes_km_trigger BEFORE INSERT ON river_axes_km FOR each ROW - BEGIN - SELECT river_axes_km_ID_SEQ.nextval INTO :new.id FROM dual; - END; -/ ---CREATE INDEX river_axes_km_spatial_idx ON river_axes_km(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=point'); - --- Geodaesie/Bauwerke/Wehre.shp -CREATE SEQUENCE BUILDINGS_ID_SEQ; -CREATE TABLE buildings( - OGR_FID NUMBER(38), - GEOM MDSYS.SDO_GEOMETRY, - river_id NUMBER(38), - name VARCHAR2(255), - ID NUMBER PRIMARY KEY NOT NULL -); -INSERT INTO USER_SDO_GEOM_METADATA VALUES ('buildings', 'GEOM', MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',2457864.326387,2634771.191263,0.001),MDSYS.SDO_DIM_ELEMENT('Y',5297459.306295,5586961.449130,0.001),MDSYS.SDO_DIM_ELEMENT('Z',-100000,100000,0.002)), 31466); -CREATE OR REPLACE TRIGGER buildings_trigger BEFORE INSERT ON buildings FOR each ROW - BEGIN - SELECT BUILDINGS_ID_SEQ.nextval INTO :new.id FROM dual; - END; -/ ---CREATE INDEX buildings_spatial_idx ON buildings(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=LINE'); - --- Geodaesie/Festpunkte/Festpunkte.shp -CREATE SEQUENCE FIXPOINTS_ID_SEQ; -CREATE TABLE fixpoints ( - OGR_FID NUMBER(38), - GEOM MDSYS.SDO_GEOMETRY, - river_id NUMBER(38), - x NUMBER(38,11), - y NUMBER(38,11), - km NUMBER(38,11) NOT NULL, - HPGP VARCHAR2(255), - ID NUMBER PRIMARY KEY NOT NULL -); ---Extent: (2539388.036000, 5450896.688000) - (2575586.296000, 5507370.606000) -INSERT INTO USER_SDO_GEOM_METADATA VALUES ('fixpoints', 'GEOM', MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',2457864.326387,2634771.191263,0.001),MDSYS.SDO_DIM_ELEMENT('Y',5297459.306295,5586961.449130,0.001),MDSYS.SDO_DIM_ELEMENT('Z',-100000,100000,0.002)), 31466); -CREATE OR REPLACE TRIGGER fixpoints_trigger BEFORE INSERT ON fixpoints FOR each ROW - BEGIN - SELECT FIXPOINTS_ID_SEQ.nextval INTO :new.id FROM dual; - END; -/ ---CREATE INDEX fixpoints_spatial_idx ON fixpoints(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=POINT'); - --- Geodaesie/Flussachse+km/achse -CREATE SEQUENCE RIVER_AXES_ID_SEQ; -CREATE TABLE river_axes( - OGR_FID NUMBER(38), - GEOM MDSYS.SDO_GEOMETRY, - river_id NUMBER(38), - kind NUMBER(38) DEFAULT 0 NOT NULL, - 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',2457864.326387,2634771.191263,0.001),MDSYS.SDO_DIM_ELEMENT('Y',5297459.306295,5586961.449130,0.001),MDSYS.SDO_DIM_ELEMENT('Z',-100000,100000,0.002)), 31466); -CREATE OR REPLACE TRIGGER river_axes_trigger BEFORE INSERT ON river_axes FOR each ROW - BEGIN - SELECT RIVER_AXES_ID_SEQ.nextval INTO :new.id FROM dual; - END; -/ ---CREATE INDEX river_axes_spatial_idx ON river_axes(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=LINE'); - ---Geodaesie/Querprofile/QP-Spuren/qps.shp -CREATE SEQUENCE CROSS_SECTION_TRACKS_ID_SEQ; -CREATE TABLE cross_section_tracks ( - OGR_FID NUMBER(38), - GEOM MDSYS.SDO_GEOMETRY, - river_id NUMBER(38), - km NUMBER(38,12) NOT NULL, - z NUMBER(38,12) DEFAULT 0 NOT NULL, - 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',2457864.326387,2634771.191263,0.001),MDSYS.SDO_DIM_ELEMENT('Y',5297459.306295,5586961.449130,0.001),MDSYS.SDO_DIM_ELEMENT('Z',-100000,100000,0.002)), 31466); -CREATE OR REPLACE TRIGGER cross_section_tracks_trigger BEFORE INSERT ON cross_section_tracks FOR each ROW - BEGIN - SELECT CROSS_SECTION_TRACKS_ID_SEQ.nextval INTO :new.id FROM dual; - END; -/ ---CREATE INDEX CrossSectionTracks_spatial_idx ON cross_section_tracks(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=LINE'); - --Hydrologie/HW-Schutzanlagen/hws.shp CREATE SEQUENCE HWS_ID_SEQ; CREATE TABLE hws( @@ -124,19 +153,3 @@ END; / --CREATE INDEX hws_spatial_idx ON hws(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=LINE'); - --- Hydrologie/Hydr. Grenzen/talaue.shp -CREATE SEQUENCE FLOODPLAIN_ID_SEQ; -CREATE TABLE floodplain( - OGR_FID NUMBER(38), - GEOM MDSYS.SDO_GEOMETRY, - river_id NUMBER(38), - ID NUMBER PRIMARY KEY NOT NULL -); -INSERT INTO USER_SDO_GEOM_METADATA VALUES ('floodplain', 'GEOM', MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',2457864.326387,2634771.191263,0.001),MDSYS.SDO_DIM_ELEMENT('Y',5297459.306295,5586961.449130,0.001),MDSYS.SDO_DIM_ELEMENT('Z',-100000,100000,0.002)), 31466); -CREATE OR REPLACE TRIGGER floodplain_trigger BEFORE INSERT ON floodplain FOR each ROW - BEGIN - SELECT FLOODPLAIN_ID_SEQ.nextval INTO :new.id FROM dual; - END; -/ ---CREATE INDEX floodplain_spatial_idx ON floodplain(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=POLYGON'); diff -r e69ef36290ae -r b28ab244a77d flys-backend/doc/schema/postgresql-spatial.sql --- a/flys-backend/doc/schema/postgresql-spatial.sql Thu Sep 22 12:10:08 2011 +0000 +++ b/flys-backend/doc/schema/postgresql-spatial.sql Fri Sep 23 10:42:25 2011 +0000 @@ -11,7 +11,11 @@ ALTER TABLE river_axes ALTER COLUMN id SET DEFAULT NEXTVAL('RIVER_AXES_ID_SEQ'); --- Geodaesie/Querprofile/* +-- TODO +-- Geodaesie/Flussachse+km/km.shp + + +--Geodaesie/Querprofile/QP-Spuren/qps.shp CREATE SEQUENCE CROSS_SECTION_TRACKS_ID_SEQ; CREATE TABLE cross_section_tracks ( id int PRIMARY KEY NOT NULL, @@ -43,7 +47,7 @@ CREATE TABLE buildings ( id int PRIMARY KEY NOT NULL, river_id int REFERENCES rivers(id), - name VARCHAR(50) + name VARCHAR(256) ); SELECT AddGeometryColumn('buildings', 'geom', 31466, 'LINESTRING', 2); ALTER TABLE buildings ALTER COLUMN id SET DEFAULT NEXTVAL('BUILDINGS_ID_SEQ'); @@ -88,92 +92,28 @@ -- Hydrologie/Einzugsgebiete/EZG.shp - -- Hinweise zu ezg_saar.shp wird nicht importiert: - -- CLASS: Integer (8.0) KLAEREN: wir die benoetigt? - -- AREA: Real (19.8) laesst sich auch durch EZG.shp bestimmen - -- PERIMETER: Real (19.8) laesst sich auch durch EZG.shp bestimmen - CREATE SEQUENCE CATCHMENT_ID_SEQ; CREATE TABLE catchment ( id int PRIMARY KEY NOT NULL, river_id int REFERENCES rivers(id), - "area" numeric, - "name" VARCHAR(80) + area NUMERIC, + name VARCHAR(256) ); SELECT AddGeometryColumn('catchment','geom',31466,'POLYGON',2); ALTER TABLE catchment ALTER COLUMN id SET DEFAULT NEXTVAL('CATCHMENT_ID_SEQ'); --- Hydrologie/HW-Schutzanlagen --- Wird nicht benoetigt, stattdessen verwenden wir --- Gewaesser/Saar/Geodaesie/Linien/rohre-und-sperren.shp - -- hws.shp beinhaltet die Geometrien von: - -- HWS-Lisdorf.shp -- hws_anlage - -- HWS-Mettlach.shp -- maßnahme -> hws_anlage - -- HWS-Rehlingen.shp -- hw -> hws_anlage - -- HWS_Saarburg.shp -- höhe? bauart? - -- HWS-Schoden-Rhl-Pf.shp -- hws_anlage - -- HWS_Schoden.shp --höhe? bauart? - -- HWS-Serrig.shp --hws_anlage --- CREATE SEQUENCE HWS_EZG_ID_SEQ; --- CREATE TABLE hws ( --- id int PRIMARY KEY NOT NULL, --- oid int, --- river_id int REFERENCES rivers(id), --- hws_facility VARCHAR(40), --- typ VARCHAR(254) --- ); --- SELECT AddGeometryColumn('hws','geom',31466,'MULTILINESTRING',2); --- ALTER TABLE hw ALTER COLUMN id SET DEFAULT NEXTVAL('HWS_ID_SEQ'); --- Hydrologie/Hydr. Grenzen/Linien +--Hydrologie/HW-Schutzanlagen/hws.shp +CREATE SEQUENCE HWS_ID_SEQ; +CREATE TABLE hws ( + id int PRIMARY KEY NOT NULL, + oid int, + river_id int REFERENCES rivers(id), + hws_facility VARCHAR(256), + typ VARCHAR(256) +); +SELECT AddGeometryColumn('hws','geom',31466,'MULTILINESTRING',2); +ALTER TABLE hw ALTER COLUMN id SET DEFAULT NEXTVAL('HWS_ID_SEQ'); --- Hydrologie/Streckendaten - -- pegellage_saar.shp -CREATE SEQUENCE LEVELPOSITION_ID_SEQ; -CREATE TABLE levelposition ( - id int PRIMARY KEY NOT NULL, - river_id int REFERENCES rivers(id), - x numeric(10,0), - y numeric(10,0), - name varchar(254) -); -SELECT AddGeometryColumn('levelposition','geom','31466','POINT',2); -ALTER TABLE levelposition ALTER COLUMN id SET DEFAULT NEXTVAL('LEVELPOSITION_ID_SEQ'); - --- Hydrologie/UeSG/Berechnung - -- Berechnung/Aktuell/BfG -CREATE SEQUENCE COMPUTATIONS_BFG_ID_SEQ; -CREATE TABLE computations_bfg ( - id int PRIMARY KEY NOT NULL, - river_id int REFERENCES rivers(id), - section varchar(254), - area float8, - perimeter float8 -); -SELECT AddGeometryColumn('computations_bfg','geom','31466','MULTIPOLYGON',2); -ALTER TABLE computations_bfg ALTER COLUMN id SET DEFAULT NEXTVAL('COMPUTATIONS_BFG_ID_SEQ'); - - -- Berechnung/Aktuell/Land -CREATE SEQUENCE COMPUTATIONS_COUNTRY_ID_SEQ; -CREATE TABLE computations_country( - id int PRIMARY KEY NOT NULL, - river_id int REFERENCES rivers(id), - text varchar(254) -); -SELECT AddGeometryColumn('computations_contry','geom','31466','MULTILINESTRING',2); -ALTER TABLE computations_country ALTER COLUMN id SET DEFAULT NEXTVAL('COMPUTATIONS_COUNTRY_ID_SEQ'); - - --- Hydrologie/UeSG/Messung -CREATE SEQUENCE MEASUREMENTS_ID_SEQ; -CREATE TABLE measurements ( - id int PRIMARY KEY NOT NULL, - river_id int REFERENCES rivers(id), - year varchar(254), - oid varchar(40) -); -SELECT AddGeometryColumn('measurement','geom','31466','MULTILINESTRING',2); -ALTER TABLE measurements ALTER COLUMN id SET DEFAULT NEXTVAL('MEASUREMENTS_ID_SEQ'); - END;