--- a/backend/doc/schema/postgresql-spatial.sql	Wed Mar 07 17:36:04 2018 +0100
+++ b/backend/doc/schema/postgresql-spatial.sql	Tue Mar 13 09:42:41 2018 +0100
@@ -1,7 +1,10 @@
 BEGIN;
 
+--FIXME: Adjust precision and scale to reasonable numbers
+--FIXME: Make precision and scale equal for all km columns in the database
+
 CREATE TABLE axis_kinds(
-    id int PRIMARY KEY NOT NULL,
+    id NUMERIC(9,0) PRIMARY KEY NOT NULL,
     name VARCHAR(64)
 );
 INSERT INTO axis_kinds(id, name) VALUES (0, 'Unbekannt');
@@ -11,9 +14,9 @@
 -- 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) ON DELETE CASCADE,
-    kind_id  int REFERENCES axis_kinds(id) NOT NULL DEFAULT 0,
+    id       NUMERIC(9,0) PRIMARY KEY NOT NULL,
+    river_id NUMERIC(9,0) REFERENCES rivers(id) ON DELETE CASCADE,
+    kind_id  NUMERIC(9,0) REFERENCES axis_kinds(id) NOT NULL DEFAULT 0,
     name     VARCHAR(64),
     path     VARCHAR(256)
 );
@@ -25,10 +28,10 @@
 -- 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) ON DELETE CASCADE,
-    km       FLOAT8 NOT NULL,
-    fedstate_km FLOAT8,
+    id       NUMERIC(9,0) PRIMARY KEY NOT NULL,
+    river_id NUMERIC(9,0) REFERENCES rivers(id) ON DELETE CASCADE,
+    km       NUMERIC(7,3) NOT NULL,
+    fedstate_km NUMERIC(7,3),
     name     VARCHAR(64),
     path     VARCHAR(256)
 );
@@ -38,7 +41,7 @@
 
 --Geodaesie/Querprofile/QP-Spuren/qps.shp
 CREATE TABLE cross_section_track_kinds(
-    id int PRIMARY KEY NOT NULL,
+    id NUMERIC(9,0) PRIMARY KEY NOT NULL,
     name VARCHAR(64)
 );
 INSERT INTO cross_section_track_kinds(id, name) VALUES (0, 'Sonstige');
@@ -46,11 +49,11 @@
 
 CREATE SEQUENCE CROSS_SECTION_TRACKS_ID_SEQ;
 CREATE TABLE cross_section_tracks (
-    id       int PRIMARY KEY NOT NULL,
-    river_id int REFERENCES rivers(id) ON DELETE CASCADE,
-    kind_id  int REFERENCES cross_section_track_kinds(id) NOT NULL DEFAULT 0,
-    km       FLOAT8 NOT NULL,
-    z        FLOAT8 NOT NULL DEFAULT 0,
+    id       NUMERIC(9,0) PRIMARY KEY NOT NULL,
+    river_id NUMERIC(9,0) REFERENCES rivers(id) ON DELETE CASCADE,
+    kind_id  NUMERIC(9,0) REFERENCES cross_section_track_kinds(id) NOT NULL DEFAULT 0,
+    km       NUMERIC(16,12) NOT NULL,
+    z        NUMERIC(16,12) NOT NULL DEFAULT 0,
     name     VARCHAR(64),
     path     VARCHAR(256)
 );
@@ -58,7 +61,7 @@
 ALTER TABLE cross_section_tracks ALTER COLUMN id SET DEFAULT NEXTVAL('CROSS_SECTION_TRACKS_ID_SEQ');
 
 CREATE TABLE building_kinds(
-    id int PRIMARY KEY NOT NULL,
+    id NUMERIC(9,0) PRIMARY KEY NOT NULL,
     name VARCHAR(64)
 );
 INSERT INTO building_kinds(id, name) VALUES (0, 'Sonstige');
@@ -69,12 +72,12 @@
 -- Geodaesie/Bauwerke
 CREATE SEQUENCE BUILDINGS_ID_SEQ;
 CREATE TABLE buildings (
-    id       int PRIMARY KEY NOT NULL,
-    river_id int REFERENCES rivers(id) ON DELETE CASCADE,
+    id       NUMERIC(9,0) PRIMARY KEY NOT NULL,
+    river_id NUMERIC(9,0) REFERENCES rivers(id) ON DELETE CASCADE,
     description VARCHAR(256), -- Name taken from attributes,
     name     VARCHAR(256), -- The layername
-    km       FLOAT8,
-    kind_id  int REFERENCES building_kinds(id) NOT NULL DEFAULT 0,
+    km       NUMERIC(15,11),
+    kind_id  NUMERIC(9,0) REFERENCES building_kinds(id) NOT NULL DEFAULT 0,
     path     VARCHAR(256)
 );
 SELECT AddGeometryColumn('buildings', 'geom', 31467, 'LINESTRING', 2);
@@ -84,11 +87,11 @@
 -- Geodaesie/Festpunkte/Festpunkte.shp
 CREATE SEQUENCE FIXPOINTS_ID_SEQ;
 CREATE TABLE fixpoints (
-    id       int PRIMARY KEY NOT NULL,
-    river_id int REFERENCES rivers(id) ON DELETE CASCADE,
-    x        FLOAT8,
-    y        FLOAT8,
-    km       FLOAT8 NOT NULL,
+    id       NUMERIC(9,0) PRIMARY KEY NOT NULL,
+    river_id NUMERIC(9,0) REFERENCES rivers(id) ON DELETE CASCADE,
+    x        NUMERIC(15,11),
+    y        NUMERIC(15,11),
+    km       NUMERIC(15,11) NOT NULL,
     HPGP     VARCHAR(64),
     name     VARCHAR(64),
     path     VARCHAR(256)
@@ -99,7 +102,7 @@
 
 -- Hydrologie/Hydr. Grenzen/talaue.shp
 CREATE TABLE floodplain_kinds(
-    id int PRIMARY KEY NOT NULL,
+    id NUMERIC(9,0) PRIMARY KEY NOT NULL,
     name VARCHAR(64)
 );
 INSERT INTO floodplain_kinds(id, name) VALUES (0, 'Sonstige');
@@ -107,9 +110,9 @@
 
 CREATE SEQUENCE FLOODPLAIN_ID_SEQ;
 CREATE TABLE floodplain (
-    id       int PRIMARY KEY NOT NULL,
-    river_id int REFERENCES rivers(id) ON DELETE CASCADE,
-    kind_id  int REFERENCES floodplain_kinds(id) NOT NULL DEFAULT 0,
+    id       NUMERIC(9,0) PRIMARY KEY NOT NULL,
+    river_id NUMERIC(9,0) REFERENCES rivers(id) ON DELETE CASCADE,
+    kind_id  NUMERIC(9,0) REFERENCES floodplain_kinds(id) NOT NULL DEFAULT 0,
     name     VARCHAR(64),
     path     VARCHAR(256)
 );
@@ -120,16 +123,16 @@
 -- Geodaesie/Hoehenmodelle/*
 CREATE SEQUENCE DEM_ID_SEQ;
 CREATE TABLE dem (
-    id       int PRIMARY KEY NOT NULL,
-    river_id int REFERENCES rivers(id) ON DELETE CASCADE,
+    id       NUMERIC(9,0) PRIMARY KEY NOT NULL,
+    river_id NUMERIC(9,0) REFERENCES rivers(id) ON DELETE CASCADE,
     name             VARCHAR(64),
-    range_id         INT REFERENCES ranges(id) ON DELETE CASCADE,
-    time_interval_id INT REFERENCES time_intervals(id),
+    range_id         NUMERIC(9,0) REFERENCES ranges(id) ON DELETE CASCADE,
+    time_interval_id NUMERIC(9,0) REFERENCES time_intervals(id),
     projection       VARCHAR(32),
-    srid	     int NOT NULL,
+    srid             NUMERIC(9,0) NOT NULL,
     elevation_state  VARCHAR(32),
     format           VARCHAR(32),
-    border_break     BOOLEAN NOT NULL DEFAULT FALSE,
+    border_break     NUMERIC(1,0) NOT NULL DEFAULT 0,
     resolution       VARCHAR(16),
     description      VARCHAR(256),
     path             VARCHAR(256) NOT NULL
@@ -139,7 +142,7 @@
 
 -- Static lookup tables for Hochwasserschutzanlagen
 CREATE TABLE hws_kinds (
-    id int PRIMARY KEY NOT NULL,
+    id NUMERIC(9,0) PRIMARY KEY NOT NULL,
     kind VARCHAR(64) NOT NULL
 );
 INSERT INTO hws_kinds (id, kind) VALUES (1, 'Durchlass');
@@ -147,7 +150,7 @@
 INSERT INTO hws_kinds (id, kind) VALUES (3, 'Graben');
 
 CREATE TABLE fed_states (
-    id int PRIMARY KEY NOT NULL,
+    id NUMERIC(9,0) PRIMARY KEY NOT NULL,
     name VARCHAR(23) NOT NULL
 );
 INSERT INTO fed_states (id, name) VALUES (1, 'Bayern');
@@ -170,19 +173,19 @@
 --Hydrologie/HW-Schutzanlagen/*Linien.shp
 CREATE SEQUENCE HWS_LINES_ID_SEQ;
 CREATE TABLE hws_lines (
-    id int PRIMARY KEY NOT NULL,
-    ogr_fid int,
-    kind_id int REFERENCES hws_kinds(id) DEFAULT 2,
-    fed_state_id int REFERENCES fed_states(id),
-    river_id int REFERENCES rivers(id) ON DELETE CASCADE,
+    id NUMERIC(9,0) PRIMARY KEY NOT NULL,
+    ogr_fid NUMERIC(9,0),
+    kind_id NUMERIC(9,0) REFERENCES hws_kinds(id) DEFAULT 2,
+    fed_state_id NUMERIC(9,0) REFERENCES fed_states(id),
+    river_id NUMERIC(9,0) REFERENCES rivers(id) ON DELETE CASCADE,
     name VARCHAR(256),
     path VARCHAR(256),
-    official INT DEFAULT 0,
+    official NUMERIC(9,0) DEFAULT 0,
     agency VARCHAR(256),
     range VARCHAR(256),
-    shore_side INT DEFAULT 0,
+    shore_side NUMERIC(9,0) DEFAULT 0,
     source VARCHAR(256),
-    status_date TIMESTAMP,
+    status_date TIMESTAMP(0),
     description VARCHAR(256)
 );
 SELECT AddGeometryColumn('hws_lines', 'geom', 31467, 'MULTILINESTRING', 3);
@@ -193,25 +196,25 @@
 --Hydrologie/HW-Schutzanlagen/*Punkte.shp
 CREATE SEQUENCE HWS_POINTS_ID_SEQ;
 CREATE TABLE hws_points (
-    id int PRIMARY KEY NOT NULL,
-    ogr_fid int,
-    kind_id int REFERENCES hws_kinds(id) DEFAULT 2,
-    fed_state_id int REFERENCES fed_states(id),
-    river_id int REFERENCES rivers(id) ON DELETE CASCADE,
+    id NUMERIC(9,0) PRIMARY KEY NOT NULL,
+    ogr_fid NUMERIC(9,0),
+    kind_id NUMERIC(9,0) REFERENCES hws_kinds(id) DEFAULT 2,
+    fed_state_id NUMERIC(9,0) REFERENCES fed_states(id),
+    river_id NUMERIC(9,0) REFERENCES rivers(id) ON DELETE CASCADE,
     name VARCHAR,
     path VARCHAR,
-    official INT DEFAULT 0,
+    official NUMERIC(9,0) DEFAULT 0,
     agency VARCHAR,
     range VARCHAR,
-    shore_side INT DEFAULT 0,
+    shore_side NUMERIC(9,0) DEFAULT 0,
     source VARCHAR,
     status_date VARCHAR,
     description VARCHAR,
-    freeboard FLOAT8,
-    dike_km FLOAT8,
-    z FLOAT8,
-    z_target FLOAT8,
-    rated_level FLOAT8
+    freeboard NUMERIC(19,5),
+    dike_km NUMERIC(19,5),
+    z NUMERIC(19,5),
+    z_target NUMERIC(19,5),
+    rated_level NUMERIC(19,5)
 );
 SELECT AddGeometryColumn('hws_points', 'geom', 31467, 'POINT', 2);
 
@@ -220,7 +223,7 @@
 --
 --Hydrologie/UeSG
 CREATE TABLE floodmap_kinds (
-    id 	     int PRIMARY KEY NOT NULL,
+    id 	     NUMERIC(9,0) PRIMARY KEY NOT NULL,
     name     varchar(64) NOT NULL
 );
 INSERT INTO floodmap_kinds VALUES (200, 'Messung');
@@ -231,14 +234,14 @@
 
 CREATE SEQUENCE FLOODMAPS_ID_SEQ;
 CREATE TABLE floodmaps (
-    id         int PRIMARY KEY NOT NULL,
-    river_id   int REFERENCES rivers(id) ON DELETE CASCADE,
+    id         NUMERIC(9,0) PRIMARY KEY NOT NULL,
+    river_id   NUMERIC(9,0) REFERENCES rivers(id) ON DELETE CASCADE,
     name       varchar(64) NOT NULL,
-    kind       int NOT NULL REFERENCES floodmap_kinds(id),
-    diff       FLOAT8,
-    count      int,
-    area       FLOAT8,
-    perimeter  FLOAT8,
+    kind       NUMERIC(9,0) NOT NULL REFERENCES floodmap_kinds(id),
+    diff       NUMERIC(19,5),
+    count      NUMERIC(9,0),
+    area       NUMERIC(19,5),
+    perimeter  NUMERIC(19,5),
     waterbody  varchar(64),
     path     VARCHAR(256),
     source   varchar(64)
@@ -247,7 +250,7 @@
 ALTER TABLE floodmaps ALTER COLUMN id SET DEFAULT NEXTVAL('FLOODMAPS_ID_SEQ');
 
 CREATE TABLE sectie_kinds (
-    id int PRIMARY KEY NOT NULL,
+    id NUMERIC(9,0) PRIMARY KEY NOT NULL,
     name VARCHAR(64) NOT NULL
 );
 INSERT INTO sectie_kinds (id, name) VALUES (0, 'nicht berücksichtigt');
@@ -256,7 +259,7 @@
 INSERT INTO sectie_kinds (id, name) VALUES (3, 'Vorland');
 
 CREATE TABLE sobek_kinds (
-    id int PRIMARY KEY NOT NULL,
+    id NUMERIC(9,0) PRIMARY KEY NOT NULL,
     name VARCHAR(64) NOT NULL
 );
 INSERT INTO sobek_kinds (id, name) VALUES (0, 'nicht berücksichtigt');
@@ -264,7 +267,7 @@
 INSERT INTO sobek_kinds (id, name) VALUES (2, 'nicht durchströmt');
 
 CREATE TABLE boundary_kinds (
-    id int PRIMARY KEY NOT NULL,
+    id NUMERIC(9,0) PRIMARY KEY NOT NULL,
     name VARCHAR(64) NOT NULL
 );
 INSERT INTO boundary_kinds (id, name) VALUES (0, 'Unbekannt');
@@ -274,12 +277,12 @@
 
 CREATE SEQUENCE HYDR_BOUNDARIES_ID_SEQ;
 CREATE TABLE hydr_boundaries (
-    id         int PRIMARY KEY NOT NULL,
-    river_id   int REFERENCES rivers(id) ON DELETE CASCADE,
+    id         NUMERIC(9,0) PRIMARY KEY NOT NULL,
+    river_id   NUMERIC(9,0) REFERENCES rivers(id) ON DELETE CASCADE,
     name       VARCHAR(255),
-    kind       int REFERENCES boundary_kinds(id) NOT NULL DEFAULT 0,
-    sectie     int REFERENCES sectie_kinds(id),
-    sobek      int REFERENCES sobek_kinds(id),
+    kind       NUMERIC(9,0) REFERENCES boundary_kinds(id) NOT NULL DEFAULT 0,
+    sectie     NUMERIC(9,0) REFERENCES sectie_kinds(id),
+    sobek      NUMERIC(9,0) REFERENCES sobek_kinds(id),
     path       VARCHAR(256)
 );
 SELECT AddGeometryColumn('hydr_boundaries','geom',31467,'MULTILINESTRING',3);
@@ -288,12 +291,12 @@
 
 CREATE SEQUENCE HYDR_BOUNDARIES_POLY_ID_SEQ;
 CREATE TABLE hydr_boundaries_poly (
-    id         int PRIMARY KEY NOT NULL,
-    river_id   int REFERENCES rivers(id) ON DELETE CASCADE,
+    id         NUMERIC(9,0) PRIMARY KEY NOT NULL,
+    river_id   NUMERIC(9,0) REFERENCES rivers(id) ON DELETE CASCADE,
     name       VARCHAR(255),
-    kind       int REFERENCES boundary_kinds(id) NOT NULL DEFAULT 0,
-    sectie     int REFERENCES sectie_kinds(id),
-    sobek      int REFERENCES sobek_kinds(id),
+    kind       NUMERIC(9,0) REFERENCES boundary_kinds(id) NOT NULL DEFAULT 0,
+    sectie     NUMERIC(9,0) REFERENCES sectie_kinds(id),
+    sobek      NUMERIC(9,0) REFERENCES sobek_kinds(id),
     path       VARCHAR(256)
 );
 SELECT AddGeometryColumn('hydr_boundaries_poly','geom',31467,'MULTIPOLYGON',3);
@@ -301,7 +304,7 @@
 
 
 CREATE TABLE jetty_kinds(
-    id int PRIMARY KEY NOT NULL,
+    id NUMERIC(9,0) PRIMARY KEY NOT NULL,
     name VARCHAR(64)
 );
 INSERT INTO jetty_kinds VALUES (0, 'Buhnenkopf');
@@ -310,10 +313,10 @@
 
 CREATE SEQUENCE JETTIES_ID_SEQ;
 CREATE TABLE jetties (
-    id         int PRIMARY KEY NOT NULL,
-    river_id   int REFERENCES rivers(id) ON DELETE CASCADE,
+    id         NUMERIC(9,0) PRIMARY KEY NOT NULL,
+    river_id   NUMERIC(9,0) REFERENCES rivers(id) ON DELETE CASCADE,
     path       VARCHAR(256),
-    kind_id    int REFERENCES jetty_kinds(id),
+    kind_id    NUMERIC(9,0) REFERENCES jetty_kinds(id),
     km         FLOAT8,
     z	       FLOAT8
 );
@@ -322,13 +325,13 @@
 
 CREATE SEQUENCE FLOOD_MARKS_ID_SEQ;
 CREATE TABLE flood_marks (
-    id         int PRIMARY KEY NOT NULL,
-    river_id   int REFERENCES rivers(id) ON DELETE CASCADE,
+    id         NUMERIC(9,0) PRIMARY KEY NOT NULL,
+    river_id   NUMERIC(9,0) REFERENCES rivers(id) ON DELETE CASCADE,
     path       VARCHAR(256),
-    km         FLOAT8,
-    z	       FLOAT8,
+    km         NUMERIC(7,3),
+    z	         NUMERIC(16,12),
     location   VARCHAR(64),
-    year       int
+    year       NUMERIC(4,0)
 );
 SELECT AddGeometryColumn('flood_marks','geom',31467,'POINT',2);
 ALTER TABLE flood_marks ALTER COLUMN id SET DEFAULT NEXTVAL('FLOOD_MARKS_ID_SEQ');