Mercurial > gemma

comparison schema/gemma.sql @ 1983:f9f1babe52ae

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Fix area generation from multipolygon input In case the waterway axis representing a stretch intersects distinct parts of the input area, a multipolygon has to be returned. Before, an arbitrary polygon was selected due to the used function not being set-returning. In passing, consistently name respective columns in schema.
author Tom Gottfried <tom@intevation.de>
date Wed, 23 Jan 2019 16:25:43 +0100
parents d966f03ea819
children 48001472e1d8 8eeb0b5eb340
comparison
equal deleted inserted replaced
1982:a7e47a9d890b 1983:f9f1babe52ae
349 349
350 CREATE TABLE stretches ( 350 CREATE TABLE stretches (
351 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, 351 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
352 name varchar NOT NULL, 352 name varchar NOT NULL,
353 stretch isrsrange NOT NULL, 353 stretch isrsrange NOT NULL,
354 -- TODO: make it a (MULTI)-LINESTRING. 354 area geography(MULTIPOLYGON, 4326) NOT NULL,
355 -- POLYGON is chosen for the convinience
356 -- of re-using ISRSrange_area.
357 geom geography(POLYGON, 4326) NOT NULL,
358 objnam varchar NOT NULL, 355 objnam varchar NOT NULL,
359 nobjnam varchar, 356 nobjnam varchar,
360 date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, 357 date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
361 source_organization varchar NOT NULL, 358 source_organization varchar NOT NULL,
362 staging_done boolean NOT NULL DEFAULT false, 359 staging_done boolean NOT NULL DEFAULT false,
374 CREATE VIEW stretches_geoserver AS SELECT 371 CREATE VIEW stretches_geoserver AS SELECT
375 id, 372 id,
376 name, 373 name,
377 (stretch).lower::varchar as lower, 374 (stretch).lower::varchar as lower,
378 (stretch).upper::varchar as upper, 375 (stretch).upper::varchar as upper,
379 geom::Geometry(POLYGON, 4326), 376 area::Geometry(MULTIPOLYGON, 4326),
380 objnam, 377 objnam,
381 nobjnam, 378 nobjnam,
382 date_info, 379 date_info,
383 source_organization, 380 source_organization,
384 (SELECT string_agg(country_code, ', ') 381 (SELECT string_agg(country_code, ', ')
440 fk_g_fid isrs NOT NULL REFERENCES gauges, 437 fk_g_fid isrs NOT NULL REFERENCES gauges,
441 -- XXX: DRC references "ch. 3.1.1", which does not exist in document. 438 -- XXX: DRC references "ch. 3.1.1", which does not exist in document.
442 objnam varchar, 439 objnam varchar,
443 nobjnm varchar, 440 nobjnm varchar,
444 stretch isrsrange NOT NULL, 441 stretch isrsrange NOT NULL,
445 area geography(POLYGON, 4326) NOT NULL, 442 area geography(MULTIPOLYGON, 4326) NOT NULL,
446 rb char(2) NOT NULL REFERENCES countries, -- from rb_lb in interface 443 rb char(2) NOT NULL REFERENCES countries, -- from rb_lb in interface
447 lb char(2) NOT NULL REFERENCES countries, -- from rb_lb in interface 444 lb char(2) NOT NULL REFERENCES countries, -- from rb_lb in interface
448 responsible_country char(2) NOT NULL REFERENCES countries, 445 responsible_country char(2) NOT NULL REFERENCES countries,
449 revisiting_time smallint NOT NULL, 446 revisiting_time smallint NOT NULL,
450 limiting varchar NOT NULL REFERENCES limiting_factors, 447 limiting varchar NOT NULL REFERENCES limiting_factors,