dive4elements/river: flys-backend/doc/schema/postgresql-spatial.sql annotate

annotate flys-backend/doc/schema/postgresql-spatial.sql @ 4221:480de0dbca8e

Extended location input helper. The locationpicker has now an attribute whether the input is distance or location to display one or two clickable columns. Replaced the record click handler with cell click handler.

author	Raimund Renkert <rrenkert@intevation.de>
date	Tue, 23 Oct 2012 13:17:20 +0200
parents	f5912365619c
children	28f992c0f937

rev	line source
1230 e2deda3b77a7 Introduced support for FLYS spatial data. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	1 BEGIN;
e2deda3b77a7 Introduced support for FLYS spatial data. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	2
1238 35740d675866 Added mappings to existing data in file system (based on river Saar); added TODOs for missing tables/mappings Hans Plum <hans.plum@intevation.de> parents: 1237 diff changeset	3 -- Geodaesie/Flussachse+km/achse
1240 2ef8b67bd68a Replaced SERIALs with SEQUENCES in PostgreSQL spatial schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 1239 diff changeset	4 CREATE SEQUENCE RIVER_AXES_ID_SEQ;
1230 e2deda3b77a7 Introduced support for FLYS spatial data. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	5 CREATE TABLE river_axes (
1240 2ef8b67bd68a Replaced SERIALs with SEQUENCES in PostgreSQL spatial schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 1239 diff changeset	6 id int PRIMARY KEY NOT NULL,
1230 e2deda3b77a7 Introduced support for FLYS spatial data. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	7 river_id int REFERENCES rivers(id),
3948 883dd49d5e74 Adapted PostgreSQL spatial schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2797 diff changeset	8 kind int NOT NULL DEFAULT 0,
883dd49d5e74 Adapted PostgreSQL spatial schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2797 diff changeset	9 name VARCHAR(64),
883dd49d5e74 Adapted PostgreSQL spatial schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2797 diff changeset	10 path VARCHAR(256)
1230 e2deda3b77a7 Introduced support for FLYS spatial data. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	11 );
3950 82e931f88137 Adapted postgresql spatial schema and fixed a bug in the geo importer. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 3948 diff changeset	12 SELECT AddGeometryColumn('river_axes', 'geom', 31467, 'LINESTRING', 2);
1240 2ef8b67bd68a Replaced SERIALs with SEQUENCES in PostgreSQL spatial schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 1239 diff changeset	13 ALTER TABLE river_axes ALTER COLUMN id SET DEFAULT NEXTVAL('RIVER_AXES_ID_SEQ');
1230 e2deda3b77a7 Introduced support for FLYS spatial data. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	14
e2deda3b77a7 Introduced support for FLYS spatial data. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	15
2340 c4f090a00178 Added missing schemas. Bjoern Schilberg <bjoern@intevation.de> parents: 2339 diff changeset	16 -- TODO: TestMe.
2339 b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 1243 diff changeset	17 -- Geodaesie/Flussachse+km/km.shp
2340 c4f090a00178 Added missing schemas. Bjoern Schilberg <bjoern@intevation.de> parents: 2339 diff changeset	18 CREATE SEQUENCE RIVER_AXES_KM_ID_SEQ;
c4f090a00178 Added missing schemas. Bjoern Schilberg <bjoern@intevation.de> parents: 2339 diff changeset	19 CREATE TABLE river_axes_km (
c4f090a00178 Added missing schemas. Bjoern Schilberg <bjoern@intevation.de> parents: 2339 diff changeset	20 id int PRIMARY KEY NOT NULL,
c4f090a00178 Added missing schemas. Bjoern Schilberg <bjoern@intevation.de> parents: 2339 diff changeset	21 river_id int REFERENCES rivers(id),
3948 883dd49d5e74 Adapted PostgreSQL spatial schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2797 diff changeset	22 km NUMERIC NOT NULL,
883dd49d5e74 Adapted PostgreSQL spatial schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2797 diff changeset	23 name VARCHAR(64),
883dd49d5e74 Adapted PostgreSQL spatial schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2797 diff changeset	24 path VARCHAR(256)
2340 c4f090a00178 Added missing schemas. Bjoern Schilberg <bjoern@intevation.de> parents: 2339 diff changeset	25 );
3950 82e931f88137 Adapted postgresql spatial schema and fixed a bug in the geo importer. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 3948 diff changeset	26 SELECT AddGeometryColumn('river_axes_km', 'geom', 31467, 'POINT', 2);
2340 c4f090a00178 Added missing schemas. Bjoern Schilberg <bjoern@intevation.de> parents: 2339 diff changeset	27 ALTER TABLE river_axes_km ALTER COLUMN id SET DEFAULT NEXTVAL('RIVER_AXES_KM_ID_SEQ');
2339 b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 1243 diff changeset	28
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 1243 diff changeset	29
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 1243 diff changeset	30 --Geodaesie/Querprofile/QP-Spuren/qps.shp
1240 2ef8b67bd68a Replaced SERIALs with SEQUENCES in PostgreSQL spatial schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 1239 diff changeset	31 CREATE SEQUENCE CROSS_SECTION_TRACKS_ID_SEQ;
1230 e2deda3b77a7 Introduced support for FLYS spatial data. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	32 CREATE TABLE cross_section_tracks (
1240 2ef8b67bd68a Replaced SERIALs with SEQUENCES in PostgreSQL spatial schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 1239 diff changeset	33 id int PRIMARY KEY NOT NULL,
1230 e2deda3b77a7 Introduced support for FLYS spatial data. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	34 river_id int REFERENCES rivers(id),
e2deda3b77a7 Introduced support for FLYS spatial data. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	35 km NUMERIC NOT NULL,
3948 883dd49d5e74 Adapted PostgreSQL spatial schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2797 diff changeset	36 z NUMERIC NOT NULL DEFAULT 0,
883dd49d5e74 Adapted PostgreSQL spatial schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2797 diff changeset	37 name VARCHAR(64),
883dd49d5e74 Adapted PostgreSQL spatial schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2797 diff changeset	38 path VARCHAR(256)
1230 e2deda3b77a7 Introduced support for FLYS spatial data. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	39 );
3950 82e931f88137 Adapted postgresql spatial schema and fixed a bug in the geo importer. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 3948 diff changeset	40 SELECT AddGeometryColumn('cross_section_tracks', 'geom', 31467, 'LINESTRING', 2);
1240 2ef8b67bd68a Replaced SERIALs with SEQUENCES in PostgreSQL spatial schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 1239 diff changeset	41 ALTER TABLE cross_section_tracks ALTER COLUMN id SET DEFAULT NEXTVAL('CROSS_SECTION_TRACKS_ID_SEQ');
1230 e2deda3b77a7 Introduced support for FLYS spatial data. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	42
e2deda3b77a7 Introduced support for FLYS spatial data. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	43
1238 35740d675866 Added mappings to existing data in file system (based on river Saar); added TODOs for missing tables/mappings Hans Plum <hans.plum@intevation.de> parents: 1237 diff changeset	44 -- Geodaesie/Linien/rohre-und-spreen
1240 2ef8b67bd68a Replaced SERIALs with SEQUENCES in PostgreSQL spatial schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 1239 diff changeset	45 CREATE SEQUENCE LINES_ID_SEQ;
1230 e2deda3b77a7 Introduced support for FLYS spatial data. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	46 CREATE TABLE lines (
1240 2ef8b67bd68a Replaced SERIALs with SEQUENCES in PostgreSQL spatial schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 1239 diff changeset	47 id int PRIMARY KEY NOT NULL,
1230 e2deda3b77a7 Introduced support for FLYS spatial data. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	48 river_id int REFERENCES rivers(id),
2365 60532f75396b Adapted 'lines' relation and made lines queryable for a given river. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2363 diff changeset	49 kind VARCHAR(16) NOT NULL,
3948 883dd49d5e74 Adapted PostgreSQL spatial schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2797 diff changeset	50 z NUMERIC DEFAULT 0,
883dd49d5e74 Adapted PostgreSQL spatial schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2797 diff changeset	51 name VARCHAR(64),
883dd49d5e74 Adapted PostgreSQL spatial schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2797 diff changeset	52 path VARCHAR(256)
1230 e2deda3b77a7 Introduced support for FLYS spatial data. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	53 );
3950 82e931f88137 Adapted postgresql spatial schema and fixed a bug in the geo importer. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 3948 diff changeset	54 SELECT AddGeometryColumn('lines', 'geom', 31467, 'LINESTRING', 3);
1240 2ef8b67bd68a Replaced SERIALs with SEQUENCES in PostgreSQL spatial schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 1239 diff changeset	55 ALTER TABLE lines ALTER COLUMN id SET DEFAULT NEXTVAL('LINES_ID_SEQ');
1230 e2deda3b77a7 Introduced support for FLYS spatial data. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	56 -- 'kind':
e2deda3b77a7 Introduced support for FLYS spatial data. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	57 -- 0: ROHR1
e2deda3b77a7 Introduced support for FLYS spatial data. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	58 -- 1: DAMM
e2deda3b77a7 Introduced support for FLYS spatial data. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	59
e2deda3b77a7 Introduced support for FLYS spatial data. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	60
1238 35740d675866 Added mappings to existing data in file system (based on river Saar); added TODOs for missing tables/mappings Hans Plum <hans.plum@intevation.de> parents: 1237 diff changeset	61 -- Geodaesie/Bauwerke/Wehre.shp
1240 2ef8b67bd68a Replaced SERIALs with SEQUENCES in PostgreSQL spatial schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 1239 diff changeset	62 CREATE SEQUENCE BUILDINGS_ID_SEQ;
1230 e2deda3b77a7 Introduced support for FLYS spatial data. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	63 CREATE TABLE buildings (
1240 2ef8b67bd68a Replaced SERIALs with SEQUENCES in PostgreSQL spatial schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 1239 diff changeset	64 id int PRIMARY KEY NOT NULL,
1230 e2deda3b77a7 Introduced support for FLYS spatial data. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	65 river_id int REFERENCES rivers(id),
3948 883dd49d5e74 Adapted PostgreSQL spatial schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2797 diff changeset	66 name VARCHAR(256),
883dd49d5e74 Adapted PostgreSQL spatial schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2797 diff changeset	67 path VARCHAR(256)
1230 e2deda3b77a7 Introduced support for FLYS spatial data. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	68 );
3950 82e931f88137 Adapted postgresql spatial schema and fixed a bug in the geo importer. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 3948 diff changeset	69 SELECT AddGeometryColumn('buildings', 'geom', 31467, 'LINESTRING', 2);
1240 2ef8b67bd68a Replaced SERIALs with SEQUENCES in PostgreSQL spatial schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 1239 diff changeset	70 ALTER TABLE buildings ALTER COLUMN id SET DEFAULT NEXTVAL('BUILDINGS_ID_SEQ');
1230 e2deda3b77a7 Introduced support for FLYS spatial data. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	71
e2deda3b77a7 Introduced support for FLYS spatial data. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	72
1238 35740d675866 Added mappings to existing data in file system (based on river Saar); added TODOs for missing tables/mappings Hans Plum <hans.plum@intevation.de> parents: 1237 diff changeset	73 -- Geodaesie/Festpunkte/Festpunkte.shp
1240 2ef8b67bd68a Replaced SERIALs with SEQUENCES in PostgreSQL spatial schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 1239 diff changeset	74 CREATE SEQUENCE FIXPOINTS_ID_SEQ;
1230 e2deda3b77a7 Introduced support for FLYS spatial data. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	75 CREATE TABLE fixpoints (
1240 2ef8b67bd68a Replaced SERIALs with SEQUENCES in PostgreSQL spatial schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 1239 diff changeset	76 id int PRIMARY KEY NOT NULL,
1230 e2deda3b77a7 Introduced support for FLYS spatial data. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	77 river_id int REFERENCES rivers(id),
e2deda3b77a7 Introduced support for FLYS spatial data. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	78 x int,
e2deda3b77a7 Introduced support for FLYS spatial data. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	79 y int,
e2deda3b77a7 Introduced support for FLYS spatial data. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	80 km NUMERIC NOT NULL,
3948 883dd49d5e74 Adapted PostgreSQL spatial schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2797 diff changeset	81 HPGP VARCHAR(2),
883dd49d5e74 Adapted PostgreSQL spatial schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2797 diff changeset	82 name VARCHAR(64),
883dd49d5e74 Adapted PostgreSQL spatial schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2797 diff changeset	83 path VARCHAR(256)
1230 e2deda3b77a7 Introduced support for FLYS spatial data. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	84 );
3950 82e931f88137 Adapted postgresql spatial schema and fixed a bug in the geo importer. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 3948 diff changeset	85 SELECT AddGeometryColumn('fixpoints', 'geom', 31467, 'POINT', 2);
1240 2ef8b67bd68a Replaced SERIALs with SEQUENCES in PostgreSQL spatial schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 1239 diff changeset	86 ALTER TABLE fixpoints ALTER COLUMN id SET DEFAULT NEXTVAL('FIXPOINTS_ID_SEQ');
1230 e2deda3b77a7 Introduced support for FLYS spatial data. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: diff changeset	87
1236 324cb486db9f Added relation and hibernate classes for DGMs. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 1230 diff changeset	88
1238 35740d675866 Added mappings to existing data in file system (based on river Saar); added TODOs for missing tables/mappings Hans Plum <hans.plum@intevation.de> parents: 1237 diff changeset	89 -- Hydrologie/Hydr. Grenzen/talaue.shp
1240 2ef8b67bd68a Replaced SERIALs with SEQUENCES in PostgreSQL spatial schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 1239 diff changeset	90 CREATE SEQUENCE FLOODPLAIN_ID_SEQ;
1237 774dff3ee546 Added relation and hibernate classes for floodplains (german 'Talaue'). Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 1236 diff changeset	91 CREATE TABLE floodplain (
1240 2ef8b67bd68a Replaced SERIALs with SEQUENCES in PostgreSQL spatial schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 1239 diff changeset	92 id int PRIMARY KEY NOT NULL,
3948 883dd49d5e74 Adapted PostgreSQL spatial schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2797 diff changeset	93 river_id int REFERENCES rivers(id),
883dd49d5e74 Adapted PostgreSQL spatial schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2797 diff changeset	94 name VARCHAR(64),
883dd49d5e74 Adapted PostgreSQL spatial schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2797 diff changeset	95 path VARCHAR(256)
1237 774dff3ee546 Added relation and hibernate classes for floodplains (german 'Talaue'). Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 1236 diff changeset	96 );
3950 82e931f88137 Adapted postgresql spatial schema and fixed a bug in the geo importer. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 3948 diff changeset	97 SELECT AddGeometryColumn('floodplain', 'geom', 31467, 'POLYGON', 2);
1240 2ef8b67bd68a Replaced SERIALs with SEQUENCES in PostgreSQL spatial schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 1239 diff changeset	98 ALTER TABLE floodplain ALTER COLUMN id SET DEFAULT NEXTVAL('FLOODPLAIN_ID_SEQ');
1237 774dff3ee546 Added relation and hibernate classes for floodplains (german 'Talaue'). Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 1236 diff changeset	99
774dff3ee546 Added relation and hibernate classes for floodplains (german 'Talaue'). Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 1236 diff changeset	100
1238 35740d675866 Added mappings to existing data in file system (based on river Saar); added TODOs for missing tables/mappings Hans Plum <hans.plum@intevation.de> parents: 1237 diff changeset	101 -- Geodaesie/Hoehenmodelle/*
1240 2ef8b67bd68a Replaced SERIALs with SEQUENCES in PostgreSQL spatial schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 1239 diff changeset	102 CREATE SEQUENCE DEM_ID_SEQ;
2ef8b67bd68a Replaced SERIALs with SEQUENCES in PostgreSQL spatial schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 1239 diff changeset	103 CREATE TABLE dem (
2ef8b67bd68a Replaced SERIALs with SEQUENCES in PostgreSQL spatial schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 1239 diff changeset	104 id int PRIMARY KEY NOT NULL,
1236 324cb486db9f Added relation and hibernate classes for DGMs. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 1230 diff changeset	105 river_id int REFERENCES rivers(id),
324cb486db9f Added relation and hibernate classes for DGMs. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 1230 diff changeset	106 -- XXX Should we use the ranges table instead?
3951 89ada0b9083f Modified the schema for DGMs; added converter for CSV to SQL for DGM information. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 3950 diff changeset	107 name VARCHAR(64),
89ada0b9083f Modified the schema for DGMs; added converter for CSV to SQL for DGM information. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 3950 diff changeset	108 lower NUMERIC,
89ada0b9083f Modified the schema for DGMs; added converter for CSV to SQL for DGM information. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 3950 diff changeset	109 upper NUMERIC,
3961 f5912365619c Adapted the spatial schema for DEMs and the DgmSqlConverter. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 3951 diff changeset	110 year_from VARCHAR(32) NOT NULL,
f5912365619c Adapted the spatial schema for DEMs and the DgmSqlConverter. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 3951 diff changeset	111 year_to VARCHAR(32) NOT NULL,
f5912365619c Adapted the spatial schema for DEMs and the DgmSqlConverter. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 3951 diff changeset	112 projection VARCHAR(32) NOT NULL,
3951 89ada0b9083f Modified the schema for DGMs; added converter for CSV to SQL for DGM information. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 3950 diff changeset	113 elevation_state VARCHAR(32),
89ada0b9083f Modified the schema for DGMs; added converter for CSV to SQL for DGM information. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 3950 diff changeset	114 format VARCHAR(32),
89ada0b9083f Modified the schema for DGMs; added converter for CSV to SQL for DGM information. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 3950 diff changeset	115 border_break BOOLEAN NOT NULL DEFAULT FALSE,
89ada0b9083f Modified the schema for DGMs; added converter for CSV to SQL for DGM information. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 3950 diff changeset	116 resolution VARCHAR(16),
89ada0b9083f Modified the schema for DGMs; added converter for CSV to SQL for DGM information. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 3950 diff changeset	117 description VARCHAR(256),
89ada0b9083f Modified the schema for DGMs; added converter for CSV to SQL for DGM information. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 3950 diff changeset	118 path VARCHAR(256)
1236 324cb486db9f Added relation and hibernate classes for DGMs. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 1230 diff changeset	119 );
1240 2ef8b67bd68a Replaced SERIALs with SEQUENCES in PostgreSQL spatial schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 1239 diff changeset	120 ALTER TABLE dem ALTER COLUMN id SET DEFAULT NEXTVAL('DEM_ID_SEQ');
1236 324cb486db9f Added relation and hibernate classes for DGMs. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 1230 diff changeset	121
1238 35740d675866 Added mappings to existing data in file system (based on river Saar); added TODOs for missing tables/mappings Hans Plum <hans.plum@intevation.de> parents: 1237 diff changeset	122
1241 f68a0504dfb6 Postgresql spatial scheme extended. Bjoern Schilberg <bjoern@intevation.de> parents: 1240 diff changeset	123 -- Hydrologie/Einzugsgebiete/EZG.shp
f68a0504dfb6 Postgresql spatial scheme extended. Bjoern Schilberg <bjoern@intevation.de> parents: 1240 diff changeset	124 CREATE SEQUENCE CATCHMENT_ID_SEQ;
f68a0504dfb6 Postgresql spatial scheme extended. Bjoern Schilberg <bjoern@intevation.de> parents: 1240 diff changeset	125 CREATE TABLE catchment (
f68a0504dfb6 Postgresql spatial scheme extended. Bjoern Schilberg <bjoern@intevation.de> parents: 1240 diff changeset	126 id int PRIMARY KEY NOT NULL,
f68a0504dfb6 Postgresql spatial scheme extended. Bjoern Schilberg <bjoern@intevation.de> parents: 1240 diff changeset	127 river_id int REFERENCES rivers(id),
2339 b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 1243 diff changeset	128 area NUMERIC,
3948 883dd49d5e74 Adapted PostgreSQL spatial schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2797 diff changeset	129 name VARCHAR(256),
883dd49d5e74 Adapted PostgreSQL spatial schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2797 diff changeset	130 path VARCHAR(256)
1241 f68a0504dfb6 Postgresql spatial scheme extended. Bjoern Schilberg <bjoern@intevation.de> parents: 1240 diff changeset	131 );
3950 82e931f88137 Adapted postgresql spatial schema and fixed a bug in the geo importer. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 3948 diff changeset	132 SELECT AddGeometryColumn('catchment','geom',31467,'POLYGON',2);
1241 f68a0504dfb6 Postgresql spatial scheme extended. Bjoern Schilberg <bjoern@intevation.de> parents: 1240 diff changeset	133 ALTER TABLE catchment ALTER COLUMN id SET DEFAULT NEXTVAL('CATCHMENT_ID_SEQ');
f68a0504dfb6 Postgresql spatial scheme extended. Bjoern Schilberg <bjoern@intevation.de> parents: 1240 diff changeset	134
f68a0504dfb6 Postgresql spatial scheme extended. Bjoern Schilberg <bjoern@intevation.de> parents: 1240 diff changeset	135
2339 b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 1243 diff changeset	136 --Hydrologie/HW-Schutzanlagen/hws.shp
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 1243 diff changeset	137 CREATE SEQUENCE HWS_ID_SEQ;
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 1243 diff changeset	138 CREATE TABLE hws (
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 1243 diff changeset	139 id int PRIMARY KEY NOT NULL,
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 1243 diff changeset	140 river_id int REFERENCES rivers(id),
b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 1243 diff changeset	141 hws_facility VARCHAR(256),
3948 883dd49d5e74 Adapted PostgreSQL spatial schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2797 diff changeset	142 type VARCHAR(256),
883dd49d5e74 Adapted PostgreSQL spatial schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2797 diff changeset	143 name VARCHAR(64),
883dd49d5e74 Adapted PostgreSQL spatial schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2797 diff changeset	144 path VARCHAR(256)
2339 b28ab244a77d Harmonized oracle and postgresql spatial schema. Bjoern Schilberg <bjoern@intevation.de> parents: 1243 diff changeset	145 );
3950 82e931f88137 Adapted postgresql spatial schema and fixed a bug in the geo importer. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 3948 diff changeset	146 SELECT AddGeometryColumn('hws','geom',31467,'LINESTRING',2);
2376 918877ee70e7 backend: Fixed spatial table hws. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2365 diff changeset	147 ALTER TABLE hws ALTER COLUMN id SET DEFAULT NEXTVAL('HWS_ID_SEQ');
1241 f68a0504dfb6 Postgresql spatial scheme extended. Bjoern Schilberg <bjoern@intevation.de> parents: 1240 diff changeset	148
f68a0504dfb6 Postgresql spatial scheme extended. Bjoern Schilberg <bjoern@intevation.de> parents: 1240 diff changeset	149
2797 e41d03bf9807 Adapted the PostgreSQL schema for floodmaps. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2796 diff changeset	150 --
2796 f3b4e32b5db6 Improved the db schema for storing existing floodmaps. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2376 diff changeset	151 --Hydrologie/UeSG
2797 e41d03bf9807 Adapted the PostgreSQL schema for floodmaps. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2796 diff changeset	152 --
e41d03bf9807 Adapted the PostgreSQL schema for floodmaps. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2796 diff changeset	153 -- 'kind' can be one of:
e41d03bf9807 Adapted the PostgreSQL schema for floodmaps. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2796 diff changeset	154 -- 200 = Messung
e41d03bf9807 Adapted the PostgreSQL schema for floodmaps. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2796 diff changeset	155 -- 111 = Berechnung->Aktuell->BfG
e41d03bf9807 Adapted the PostgreSQL schema for floodmaps. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2796 diff changeset	156 -- 112 = Berechnung->Aktuell->Land
e41d03bf9807 Adapted the PostgreSQL schema for floodmaps. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2796 diff changeset	157 -- 121 = Berechnung->Potenziell->BfG
e41d03bf9807 Adapted the PostgreSQL schema for floodmaps. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2796 diff changeset	158 -- 122 = Berechnung->Potenziell->Land
e41d03bf9807 Adapted the PostgreSQL schema for floodmaps. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2796 diff changeset	159 --
3948 883dd49d5e74 Adapted PostgreSQL spatial schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2797 diff changeset	160 CREATE SEQUENCE FLOODMAPS_ID_SEQ;
2796 f3b4e32b5db6 Improved the db schema for storing existing floodmaps. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2376 diff changeset	161 CREATE TABLE floodmaps (
f3b4e32b5db6 Improved the db schema for storing existing floodmaps. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2376 diff changeset	162 id int PRIMARY KEY NOT NULL,
f3b4e32b5db6 Improved the db schema for storing existing floodmaps. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2376 diff changeset	163 river_id int REFERENCES rivers(id),
2797 e41d03bf9807 Adapted the PostgreSQL schema for floodmaps. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2796 diff changeset	164 name varchar(64) NOT NULL,
2796 f3b4e32b5db6 Improved the db schema for storing existing floodmaps. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2376 diff changeset	165 kind int NOT NULL,
2797 e41d03bf9807 Adapted the PostgreSQL schema for floodmaps. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2796 diff changeset	166 diff real,
2796 f3b4e32b5db6 Improved the db schema for storing existing floodmaps. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2376 diff changeset	167 count int,
2797 e41d03bf9807 Adapted the PostgreSQL schema for floodmaps. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2796 diff changeset	168 area real,
3948 883dd49d5e74 Adapted PostgreSQL spatial schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2797 diff changeset	169 perimeter real,
883dd49d5e74 Adapted PostgreSQL spatial schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2797 diff changeset	170 path VARCHAR(256)
2796 f3b4e32b5db6 Improved the db schema for storing existing floodmaps. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2376 diff changeset	171 );
3950 82e931f88137 Adapted postgresql spatial schema and fixed a bug in the geo importer. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 3948 diff changeset	172 SELECT AddGeometryColumn('floodmaps', 'geom', 31467, 'MULTIPOLYGON', 2);
2797 e41d03bf9807 Adapted the PostgreSQL schema for floodmaps. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2796 diff changeset	173 ALTER TABLE floodmaps DROP CONSTRAINT enforce_geotype_geom;
e41d03bf9807 Adapted the PostgreSQL schema for floodmaps. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2796 diff changeset	174 ALTER TABLE floodmaps ADD CONSTRAINT enforce_geotype_geom CHECK (geometrytype(geom) = 'POLYGON'::text OR geometrytype(geom) = 'MULTIPOLYGON'::text);
3948 883dd49d5e74 Adapted PostgreSQL spatial schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2797 diff changeset	175 ALTER TABLE floodmaps ALTER COLUMN id SET DEFAULT NEXTVAL('FLOODMAPS_ID_SEQ');
2796 f3b4e32b5db6 Improved the db schema for storing existing floodmaps. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2376 diff changeset	176
3948 883dd49d5e74 Adapted PostgreSQL spatial schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2797 diff changeset	177
883dd49d5e74 Adapted PostgreSQL spatial schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2797 diff changeset	178 CREATE SEQUENCE HYDR_BOUNDARIES_ID_SEQ;
883dd49d5e74 Adapted PostgreSQL spatial schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2797 diff changeset	179 CREATE TABLE hydr_boundaries (
883dd49d5e74 Adapted PostgreSQL spatial schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2797 diff changeset	180 id int PRIMARY KEY NOT NULL,
883dd49d5e74 Adapted PostgreSQL spatial schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2797 diff changeset	181 river_id int REFERENCES rivers(id),
883dd49d5e74 Adapted PostgreSQL spatial schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2797 diff changeset	182 name VARCHAR(255),
883dd49d5e74 Adapted PostgreSQL spatial schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2797 diff changeset	183 kind int,
883dd49d5e74 Adapted PostgreSQL spatial schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2797 diff changeset	184 path VARCHAR(256)
883dd49d5e74 Adapted PostgreSQL spatial schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2797 diff changeset	185 );
3950 82e931f88137 Adapted postgresql spatial schema and fixed a bug in the geo importer. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 3948 diff changeset	186 SELECT AddGeometryColumn('hydr_boundaries','geom',31467,'LINESTRING',3);
3948 883dd49d5e74 Adapted PostgreSQL spatial schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2797 diff changeset	187 ALTER TABLE hydr_boundaries ALTER COLUMN id SET DEFAULT NEXTVAL('HYDR_BOUNDARIES_ID_SEQ');
883dd49d5e74 Adapted PostgreSQL spatial schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2797 diff changeset	188
883dd49d5e74 Adapted PostgreSQL spatial schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2797 diff changeset	189
883dd49d5e74 Adapted PostgreSQL spatial schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2797 diff changeset	190 CREATE SEQUENCE HYDR_BOUNDARIES_POLY_ID_SEQ;
883dd49d5e74 Adapted PostgreSQL spatial schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2797 diff changeset	191 CREATE TABLE hydr_boundaries_poly (
883dd49d5e74 Adapted PostgreSQL spatial schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2797 diff changeset	192 id int PRIMARY KEY NOT NULL,
883dd49d5e74 Adapted PostgreSQL spatial schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2797 diff changeset	193 river_id int REFERENCES rivers(id),
883dd49d5e74 Adapted PostgreSQL spatial schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2797 diff changeset	194 name VARCHAR(255),
883dd49d5e74 Adapted PostgreSQL spatial schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2797 diff changeset	195 kind int,
883dd49d5e74 Adapted PostgreSQL spatial schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2797 diff changeset	196 path VARCHAR(256)
883dd49d5e74 Adapted PostgreSQL spatial schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2797 diff changeset	197 );
3950 82e931f88137 Adapted postgresql spatial schema and fixed a bug in the geo importer. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 3948 diff changeset	198 SELECT AddGeometryColumn('hydr_boundaries_poly','geom',31467,'POLYGON',3);
3948 883dd49d5e74 Adapted PostgreSQL spatial schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2797 diff changeset	199 ALTER TABLE hydr_boundaries_poly ALTER COLUMN id SET DEFAULT NEXTVAL('HYDR_BOUNDARIES_POLY_ID_SEQ');
883dd49d5e74 Adapted PostgreSQL spatial schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2797 diff changeset	200
883dd49d5e74 Adapted PostgreSQL spatial schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2797 diff changeset	201
883dd49d5e74 Adapted PostgreSQL spatial schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2797 diff changeset	202 CREATE SEQUENCE GAUGE_LOCATION_ID_SEQ;
883dd49d5e74 Adapted PostgreSQL spatial schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2797 diff changeset	203 CREATE TABLE gauge_location (
883dd49d5e74 Adapted PostgreSQL spatial schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2797 diff changeset	204 id int PRIMARY KEY NOT NULL,
883dd49d5e74 Adapted PostgreSQL spatial schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2797 diff changeset	205 river_id int REFERENCES rivers(id),
883dd49d5e74 Adapted PostgreSQL spatial schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2797 diff changeset	206 name VARCHAR(255),
883dd49d5e74 Adapted PostgreSQL spatial schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2797 diff changeset	207 path VARCHAR(256)
883dd49d5e74 Adapted PostgreSQL spatial schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2797 diff changeset	208 );
3950 82e931f88137 Adapted postgresql spatial schema and fixed a bug in the geo importer. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 3948 diff changeset	209 SELECT AddGeometryColumn('gauge_location','geom',31467,'POINT',2);
3948 883dd49d5e74 Adapted PostgreSQL spatial schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2797 diff changeset	210 ALTER TABLE gauge_location ALTER COLUMN id SET DEFAULT NEXTVAL('GAUGE_LOCATION_ID_SEQ');
883dd49d5e74 Adapted PostgreSQL spatial schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2797 diff changeset	211
883dd49d5e74 Adapted PostgreSQL spatial schema. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 2797 diff changeset	212 COMMIT;

Mercurial > dive4elements > river

annotate flys-backend/doc/schema/postgresql-spatial.sql @ 4221:480de0dbca8e