Mercurial > gemma

comparison schema/gemma.sql @ 3008:f394e828a6d2

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Separate view definitions for GeoServer from general schema definition That way OR REPLACE can be used which makes development on the views a lot easier, since the views can be altered without having to set up a completely new database each time.
author Tom Gottfried <tom@intevation.de>
date Thu, 11 Apr 2019 12:01:27 +0200
parents 92818da6133d
children c8ded555c2a8
comparison
equal deleted inserted replaced
3007:792d4476d5d5 3008:f394e828a6d2
293 depth_reference varchar NOT NULL, -- REFERENCES depth_references, 293 depth_reference varchar NOT NULL, -- REFERENCES depth_references,
294 PRIMARY KEY (gauge_id, depth_reference), 294 PRIMARY KEY (gauge_id, depth_reference),
295 value int NOT NULL 295 value int NOT NULL
296 ) 296 )
297 297
298 CREATE VIEW gauges_geoserver AS
299 SELECT
300 g.location,
301 isrs_asText(g.location) AS isrs_code,
302 g.objname,
303 g.geom,
304 g.applicability_from_km,
305 g.applicability_to_km,
306 g.validity,
307 g.zero_point,
308 g.geodref,
309 g.date_info,
310 g.source_organization,
311 json_strip_nulls(json_object_agg(coalesce(r.depth_reference,'empty'),
312 r.value))
313 AS reference_water_levels
314 FROM gauges g LEFT JOIN LATERAL (
315 SELECT gauge_id, depth_reference, value
316 FROM gauges_reference_water_levels
317 ) r ON r.gauge_id = g.location
318 GROUP BY g.location
319
320 CREATE TABLE gauge_measurements ( 298 CREATE TABLE gauge_measurements (
321 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, 299 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
322 fk_gauge_id isrs NOT NULL REFERENCES gauges, 300 fk_gauge_id isrs NOT NULL REFERENCES gauges,
323 measure_date timestamp with time zone NOT NULL, 301 measure_date timestamp with time zone NOT NULL,
324 country_code char(2) NOT NULL REFERENCES countries, 302 country_code char(2) NOT NULL REFERENCES countries,
385 catdis smallint REFERENCES catdis, 363 catdis smallint REFERENCES catdis,
386 position_code char(2) REFERENCES position_codes, 364 position_code char(2) REFERENCES position_codes,
387 related_enc varchar(12) 365 related_enc varchar(12)
388 ) 366 )
389 367
390 -- A table to help geoserver serve the distance marks as WFS 1.1.0.
391 -- At least geoserver-2.13.2 does not serve type geography correctly
392 -- and does not serve the location_code as isrs type
393 CREATE VIEW distance_marks_geoserver AS
394 SELECT location_code,
395 isrs_asText(location_code) AS location,
396 geom::Geometry(POINT, 4326),
397 related_enc,
398 (location_code).hectometre
399 FROM distance_marks_virtual
400
401 CREATE VIEW distance_marks_ashore_geoserver AS
402 SELECT id,
403 country,
404 geom::Geometry(POINT, 4326),
405 related_enc,
406 hectom,
407 catdis,
408 position_code
409 FROM distance_marks
410
411 -- We need to configure primary keys for the views used by 368 -- We need to configure primary keys for the views used by
412 -- geoserver for wfs, otherwise it will generate ids on the fly, 369 -- geoserver for wfs, otherwise it will generate ids on the fly,
413 -- which will change for the same feature... 370 -- which will change for the same feature...
414 -- See 371 -- See
415 -- https://docs.geoserver.org/stable/en/user/data/database/primarykey.html 372 -- https://docs.geoserver.org/stable/en/user/data/database/primarykey.html
436 date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, 393 date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
437 source_organization varchar NOT NULL, 394 source_organization varchar NOT NULL,
438 staging_done boolean NOT NULL DEFAULT false, 395 staging_done boolean NOT NULL DEFAULT false,
439 UNIQUE(name, staging_done) 396 UNIQUE(name, staging_done)
440 ) 397 )
398 CREATE TRIGGER stretches_date_info
399 BEFORE UPDATE ON stretches
400 FOR EACH ROW EXECUTE PROCEDURE update_date_info()
441 401
442 CREATE TABLE stretch_countries ( 402 CREATE TABLE stretch_countries (
443 stretches_id int NOT NULL REFERENCES stretches(id) 403 stretches_id int NOT NULL REFERENCES stretches(id)
444 ON DELETE CASCADE, 404 ON DELETE CASCADE,
445 country_code char(2) NOT NULL REFERENCES countries(country_code), 405 country_code char(2) NOT NULL REFERENCES countries(country_code),
446 UNIQUE(stretches_id, country_code) 406 UNIQUE(stretches_id, country_code)
447 ) 407 )
448
449 -- Published view for GeoServer
450 CREATE VIEW stretches_geoserver AS SELECT
451 id,
452 name,
453 (stretch).lower::varchar as lower,
454 (stretch).upper::varchar as upper,
455 area::Geometry(MULTIPOLYGON, 4326),
456 objnam,
457 nobjnam,
458 date_info,
459 source_organization,
460 (SELECT string_agg(country_code, ', ')
461 FROM stretch_countries
462 WHERE stretches_id = id) AS countries,
463 staging_done
464 FROM stretches
465
466
467 CREATE TRIGGER sections_stretches_date_info
468 BEFORE UPDATE ON stretches
469 FOR EACH ROW EXECUTE PROCEDURE update_date_info()
470 408
471 CREATE TABLE waterway_profiles ( 409 CREATE TABLE waterway_profiles (
472 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, 410 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
473 location isrs NOT NULL REFERENCES distance_marks_virtual, 411 location isrs NOT NULL REFERENCES distance_marks_virtual,
474 geom geography(linestring, 4326), 412 geom geography(linestring, 4326),
576 lines geography(multilinestring, 4326) NOT NULL, 514 lines geography(multilinestring, 4326) NOT NULL,
577 -- TODO: generate valid simple features and add constraint: 515 -- TODO: generate valid simple features and add constraint:
578 -- CHECK(ST_IsSimple(CAST(lines AS geometry))), 516 -- CHECK(ST_IsSimple(CAST(lines AS geometry))),
579 PRIMARY KEY (sounding_result_id, height) 517 PRIMARY KEY (sounding_result_id, height)
580 ) 518 )
581 -- A view to help geoserver serve contour lines.
582 -- At least geoserver-2.13.2 does not serve type geography correctly
583 CREATE VIEW sounding_results_contour_lines_geoserver AS
584 SELECT bottleneck_id,
585 date_info,
586 height,
587 CAST(lines AS geometry(multilinestring, 4326)) AS lines
588 FROM sounding_results_contour_lines cl
589 JOIN sounding_results sr
590 ON sr.id = cl.sounding_result_id
591
592 -- 519 --
593 -- Fairway availability 520 -- Fairway availability
594 -- 521 --
595 CREATE TABLE fairway_availability ( 522 CREATE TABLE fairway_availability (
596 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, 523 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
645 OR forecast_generation_time IS NOT NULL), 572 OR forecast_generation_time IS NOT NULL),
646 value_lifetime timestamp with time zone, 573 value_lifetime timestamp with time zone,
647 CHECK(measure_type = 'minimum guaranteed' 574 CHECK(measure_type = 'minimum guaranteed'
648 OR value_lifetime IS NOT NULL) 575 OR value_lifetime IS NOT NULL)
649 ) 576 )
650
651 CREATE VIEW bottleneck_overview AS
652 SELECT
653 objnam AS name,
654 ST_Centroid(area)::Geometry(POINT, 4326) AS point,
655 (lower(stretch)).hectometre AS from,
656 (upper(stretch)).hectometre AS to,
657 sr.current::text,
658 responsible_country
659 FROM bottlenecks bn LEFT JOIN (
660 SELECT bottleneck_id, max(date_info) AS current FROM sounding_results
661 GROUP BY bottleneck_id) sr ON sr.bottleneck_id = bn.id
662 ORDER BY objnam
663
664 -- Published view for GeoServer
665 CREATE VIEW bottlenecks_geoserver AS
666 WITH
667 fairway_availability_latest AS (
668 SELECT DISTINCT ON (bottleneck_id) bottleneck_id, date_info, critical
669 FROM fairway_availability
670 ORDER BY bottleneck_id, date_info DESC),
671 waterlevel_latest AS (
672 SELECT DISTINCT ON (fk_gauge_id) fk_gauge_id, water_level
673 FROM gauge_measurements
674 WHERE is_waterlevel AND NOT predicted
675 ORDER BY fk_gauge_id, measure_date DESC)
676 SELECT
677 b.id,
678 b.bottleneck_id,
679 b.objnam,
680 b.nobjnm,
681 b.stretch,
682 b.area,
683 b.rb,
684 b.lb,
685 b.responsible_country,
686 b.revisiting_time,
687 b.limiting,
688 b.date_info,
689 b.source_organization,
690 g.location AS gauge_isrs_code,
691 g.objname AS gauge_objname,
692 json_strip_nulls(json_object_agg(coalesce(r.depth_reference,'empty'),
693 r.value))
694 AS reference_water_levels,
695 fal.date_info AS fa_date_info,
696 fal.critical AS fa_critical,
697 wl.water_level AS gm_waterlevel
698 FROM bottlenecks b
699 LEFT JOIN gauges g ON b.fk_g_fid = g.location
700 LEFT JOIN gauges_reference_water_levels r ON g.location = r.gauge_id
701 LEFT JOIN fairway_availability_latest fal ON b.id = fal.bottleneck_id
702 LEFT JOIN waterlevel_latest wl ON b.fk_g_fid = wl.fk_gauge_id
703 GROUP BY b.id, g.location, fal.date_info, fal.critical, wl.water_level
704 ; 577 ;
705 578
706 -- Configure primary keys for geoserver views 579 -- Configure primary keys for geoserver views
707 INSERT INTO waterway.gt_pk_metadata VALUES 580 INSERT INTO waterway.gt_pk_metadata VALUES
708 ('waterway', 'gauges_geoserver', 'location'), 581 ('waterway', 'gauges_geoserver', 'location'),
830 lines geography(multilinestring, 4326) NOT NULL, 703 lines geography(multilinestring, 4326) NOT NULL,
831 PRIMARY KEY (sounding_differences_id, height) 704 PRIMARY KEY (sounding_differences_id, height)
832 ) 705 )
833 ; 706 ;
834 707
835 CREATE VIEW waterway.sounding_differences AS SELECT
836 sd.id AS id,
837 bn.objnam AS objnam,
838 srm.date_info AS minuend,
839 srs.date_info AS subtrahend,
840 sdcl.height AS height,
841 CAST(sdcl.lines AS geometry(multilinestring, 4326)) AS lines
842 FROM
843 caching.sounding_differences sd JOIN
844 caching.sounding_differences_contour_lines sdcl ON sd.id = sdcl.sounding_differences_id JOIN
845 waterway.sounding_results srm ON sd.minuend = srm.id JOIN
846 waterway.sounding_results srs ON sd.subtrahend = srs.id JOIN
847 waterway.bottlenecks bn ON srm.bottleneck_id = bn.id;
848
849 COMMIT; 708 COMMIT;