view flys-backend/doc/schema/postgresql-spatial.sql @ 4187:21f4e4b79121

Refactor GaugeDischargeCurveFacet to be able to set a facet name For adding another output of the GaugeDischargeCurveArtifact it is necessary to provide to facet instances with different names. Therefore the GaugeDischargeCurveFacet is extended to set the facet name in the constructor.
author Björn Ricks <bjoern.ricks@intevation.de>
date Fri, 19 Oct 2012 13:25:49 +0200
parents f5912365619c
children 28f992c0f937
line wrap: on
line source
BEGIN;

-- Geodaesie/Flussachse+km/achse
CREATE SEQUENCE RIVER_AXES_ID_SEQ;
CREATE TABLE river_axes (
    id       int PRIMARY KEY NOT NULL,
    river_id int REFERENCES rivers(id),
    kind     int             NOT NULL DEFAULT 0,
    name     VARCHAR(64),
    path     VARCHAR(256)
);
SELECT AddGeometryColumn('river_axes', 'geom', 31467, 'LINESTRING', 2);
ALTER TABLE river_axes ALTER COLUMN id SET DEFAULT NEXTVAL('RIVER_AXES_ID_SEQ');


-- TODO: TestMe.
-- Geodaesie/Flussachse+km/km.shp
CREATE SEQUENCE RIVER_AXES_KM_ID_SEQ;
CREATE TABLE river_axes_km (
    id       int PRIMARY KEY NOT NULL,
    river_id int REFERENCES rivers(id),
    km       NUMERIC NOT NULL,
    name     VARCHAR(64),
    path     VARCHAR(256)
);
SELECT AddGeometryColumn('river_axes_km', 'geom', 31467, 'POINT', 2);
ALTER TABLE river_axes_km ALTER COLUMN id SET DEFAULT NEXTVAL('RIVER_AXES_KM_ID_SEQ');


--Geodaesie/Querprofile/QP-Spuren/qps.shp
CREATE SEQUENCE CROSS_SECTION_TRACKS_ID_SEQ;
CREATE TABLE cross_section_tracks (
    id       int PRIMARY KEY NOT NULL,
    river_id int REFERENCES rivers(id),
    km       NUMERIC NOT NULL,
    z        NUMERIC NOT NULL DEFAULT 0,
    name     VARCHAR(64),
    path     VARCHAR(256)
);
SELECT AddGeometryColumn('cross_section_tracks', 'geom', 31467, 'LINESTRING', 2);
ALTER TABLE cross_section_tracks ALTER COLUMN id SET DEFAULT NEXTVAL('CROSS_SECTION_TRACKS_ID_SEQ');


-- Geodaesie/Linien/rohre-und-spreen
CREATE SEQUENCE LINES_ID_SEQ;
CREATE TABLE lines (
    id       int PRIMARY KEY NOT NULL,
    river_id int REFERENCES rivers(id),
    kind     VARCHAR(16) NOT NULL,
    z        NUMERIC DEFAULT 0,
    name     VARCHAR(64),
    path     VARCHAR(256)
);
SELECT AddGeometryColumn('lines', 'geom', 31467, 'LINESTRING', 3);
ALTER TABLE lines ALTER COLUMN id SET DEFAULT NEXTVAL('LINES_ID_SEQ');
-- 'kind':
-- 0: ROHR1
-- 1: DAMM


-- Geodaesie/Bauwerke/Wehre.shp
CREATE SEQUENCE BUILDINGS_ID_SEQ;
CREATE TABLE buildings (
    id       int PRIMARY KEY NOT NULL,
    river_id int REFERENCES rivers(id),
    name     VARCHAR(256),
    path     VARCHAR(256)
);
SELECT AddGeometryColumn('buildings', 'geom', 31467, 'LINESTRING', 2);
ALTER TABLE buildings ALTER COLUMN id SET DEFAULT NEXTVAL('BUILDINGS_ID_SEQ');


-- Geodaesie/Festpunkte/Festpunkte.shp
CREATE SEQUENCE FIXPOINTS_ID_SEQ;
CREATE TABLE fixpoints (
    id       int PRIMARY KEY NOT NULL,
    river_id int REFERENCES rivers(id),
    x        int,
    y        int,
    km       NUMERIC NOT NULL,
    HPGP     VARCHAR(2),
    name     VARCHAR(64),
    path     VARCHAR(256)
);
SELECT AddGeometryColumn('fixpoints', 'geom', 31467, 'POINT', 2);
ALTER TABLE fixpoints ALTER COLUMN id SET DEFAULT NEXTVAL('FIXPOINTS_ID_SEQ');


-- Hydrologie/Hydr. Grenzen/talaue.shp
CREATE SEQUENCE FLOODPLAIN_ID_SEQ;
CREATE TABLE floodplain (
    id       int PRIMARY KEY NOT NULL,
    river_id int REFERENCES rivers(id),
    name     VARCHAR(64),
    path     VARCHAR(256)
);
SELECT AddGeometryColumn('floodplain', 'geom', 31467, 'POLYGON', 2);
ALTER TABLE floodplain ALTER COLUMN id SET DEFAULT NEXTVAL('FLOODPLAIN_ID_SEQ');


-- Geodaesie/Hoehenmodelle/*
CREATE SEQUENCE DEM_ID_SEQ;
CREATE TABLE dem (
    id       int PRIMARY KEY NOT NULL,
    river_id int REFERENCES rivers(id),
    -- XXX Should we use the ranges table instead?
    name            VARCHAR(64),
    lower           NUMERIC,
    upper           NUMERIC,
    year_from       VARCHAR(32) NOT NULL,
    year_to         VARCHAR(32) NOT NULL,
    projection      VARCHAR(32) 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)
);
ALTER TABLE dem ALTER COLUMN id SET DEFAULT NEXTVAL('DEM_ID_SEQ');


-- Hydrologie/Einzugsgebiete/EZG.shp
CREATE SEQUENCE CATCHMENT_ID_SEQ;
CREATE TABLE catchment (
    id int PRIMARY KEY NOT NULL,
    river_id int REFERENCES rivers(id),
    area NUMERIC,
    name VARCHAR(256),
    path     VARCHAR(256)
);
SELECT AddGeometryColumn('catchment','geom',31467,'POLYGON',2);
ALTER TABLE catchment ALTER COLUMN id SET DEFAULT NEXTVAL('CATCHMENT_ID_SEQ');


--Hydrologie/HW-Schutzanlagen/hws.shp
CREATE SEQUENCE HWS_ID_SEQ;
CREATE TABLE hws (
    id int PRIMARY KEY NOT NULL,
    river_id int REFERENCES rivers(id),
    hws_facility VARCHAR(256),
    type VARCHAR(256),
    name VARCHAR(64),
    path     VARCHAR(256)
);
SELECT AddGeometryColumn('hws','geom',31467,'LINESTRING',2);
ALTER TABLE hws ALTER COLUMN id SET DEFAULT NEXTVAL('HWS_ID_SEQ');


--
--Hydrologie/UeSG
--
-- 'kind' can be one of:
-- 200 = Messung
-- 111 = Berechnung->Aktuell->BfG
-- 112 = Berechnung->Aktuell->Land
-- 121 = Berechnung->Potenziell->BfG
-- 122 = Berechnung->Potenziell->Land
--
CREATE SEQUENCE FLOODMAPS_ID_SEQ;
CREATE TABLE floodmaps (
    id         int PRIMARY KEY NOT NULL,
    river_id   int REFERENCES rivers(id),
    name       varchar(64) NOT NULL,
    kind       int NOT NULL,
    diff       real,
    count      int,
    area       real,
    perimeter  real,
    path     VARCHAR(256)
);
SELECT AddGeometryColumn('floodmaps', 'geom', 31467, 'MULTIPOLYGON', 2);
ALTER TABLE floodmaps DROP CONSTRAINT enforce_geotype_geom;
ALTER TABLE floodmaps ADD CONSTRAINT enforce_geotype_geom CHECK (geometrytype(geom) = 'POLYGON'::text OR geometrytype(geom) = 'MULTIPOLYGON'::text);
ALTER TABLE floodmaps ALTER COLUMN id SET DEFAULT NEXTVAL('FLOODMAPS_ID_SEQ');


CREATE SEQUENCE HYDR_BOUNDARIES_ID_SEQ;
CREATE TABLE hydr_boundaries (
    id         int PRIMARY KEY NOT NULL,
    river_id   int REFERENCES rivers(id),
    name       VARCHAR(255),
    kind       int,
    path       VARCHAR(256)
);
SELECT AddGeometryColumn('hydr_boundaries','geom',31467,'LINESTRING',3);
ALTER TABLE hydr_boundaries ALTER COLUMN id SET DEFAULT NEXTVAL('HYDR_BOUNDARIES_ID_SEQ');


CREATE SEQUENCE HYDR_BOUNDARIES_POLY_ID_SEQ;
CREATE TABLE hydr_boundaries_poly (
    id         int PRIMARY KEY NOT NULL,
    river_id   int REFERENCES rivers(id),
    name       VARCHAR(255),
    kind       int,
    path       VARCHAR(256)
);
SELECT AddGeometryColumn('hydr_boundaries_poly','geom',31467,'POLYGON',3);
ALTER TABLE hydr_boundaries_poly ALTER COLUMN id SET DEFAULT NEXTVAL('HYDR_BOUNDARIES_POLY_ID_SEQ');


CREATE SEQUENCE GAUGE_LOCATION_ID_SEQ;
CREATE TABLE gauge_location (
    id         int PRIMARY KEY NOT NULL,
    river_id   int REFERENCES rivers(id),
    name       VARCHAR(255),
    path       VARCHAR(256)
);
SELECT AddGeometryColumn('gauge_location','geom',31467,'POINT',2);
ALTER TABLE gauge_location ALTER COLUMN id SET DEFAULT NEXTVAL('GAUGE_LOCATION_ID_SEQ');

COMMIT;

http://dive4elements.wald.intevation.org