--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/flys-backend/doc/schema/postgresql-spatial.sql	Fri Sep 28 12:15:04 2012 +0200
@@ -0,0 +1,212 @@
+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;