Mercurial > gemma

comparison schema/gemma.sql @ 3003:92818da6133d

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Cleanup VIEW definition NULLS LAST is not necessary on non-nullable columns; avoid materializing unused columns in a CTE; use boolean value directly instead of comparing to auto-casted string; gauge_measurements might contain not only waterlevels; avoid unnecessary subselects.
author Tom Gottfried <tom@intevation.de>
date Wed, 10 Apr 2019 18:50:20 +0200
parents b3c3c5b5b7c1
children f394e828a6d2
comparison
equal deleted inserted replaced
3002:46ab2cb39471 3003:92818da6133d
661 GROUP BY bottleneck_id) sr ON sr.bottleneck_id = bn.id 661 GROUP BY bottleneck_id) sr ON sr.bottleneck_id = bn.id
662 ORDER BY objnam 662 ORDER BY objnam
663 663
664 -- Published view for GeoServer 664 -- Published view for GeoServer
665 CREATE VIEW bottlenecks_geoserver AS 665 CREATE VIEW bottlenecks_geoserver AS
666 WITH fairway_availability_latest AS ( 666 WITH
667 SELECT DISTINCT ON (bottleneck_id) bottleneck_id,date_info,critical 667 fairway_availability_latest AS (
668 FROM fairway_availability 668 SELECT DISTINCT ON (bottleneck_id) bottleneck_id, date_info, critical
669 ORDER BY bottleneck_id, date_info DESC NULLS LAST), 669 FROM fairway_availability
670 gauge_measurements_waterlevel AS ( 670 ORDER BY bottleneck_id, date_info DESC),
671 SELECT DISTINCT ON (fk_gauge_id) 671 waterlevel_latest AS (
672 fk_gauge_id, measure_date, predicted, water_level 672 SELECT DISTINCT ON (fk_gauge_id) fk_gauge_id, water_level
673 FROM gauge_measurements WHERE predicted ='false' 673 FROM gauge_measurements
674 ORDER BY fk_gauge_id, measure_date DESC NULLS LAST) 674 WHERE is_waterlevel AND NOT predicted
675 ORDER BY fk_gauge_id, measure_date DESC)
675 SELECT 676 SELECT
676 b.id, 677 b.id,
677 b.bottleneck_id, 678 b.bottleneck_id,
678 b.objnam, 679 b.objnam,
679 b.nobjnm, 680 b.nobjnm,
691 json_strip_nulls(json_object_agg(coalesce(r.depth_reference,'empty'), 692 json_strip_nulls(json_object_agg(coalesce(r.depth_reference,'empty'),
692 r.value)) 693 r.value))
693 AS reference_water_levels, 694 AS reference_water_levels,
694 fal.date_info AS fa_date_info, 695 fal.date_info AS fa_date_info,
695 fal.critical AS fa_critical, 696 fal.critical AS fa_critical,
696 gmw.water_level as gm_waterlevel 697 wl.water_level AS gm_waterlevel
697 FROM bottlenecks b LEFT JOIN gauges g ON b.fk_g_fid = g.location 698 FROM bottlenecks b
698 LEFT JOIN LATERAL ( 699 LEFT JOIN gauges g ON b.fk_g_fid = g.location
699 SELECT gauge_id,depth_reference,value 700 LEFT JOIN gauges_reference_water_levels r ON g.location = r.gauge_id
700 FROM gauges_reference_water_levels 701 LEFT JOIN fairway_availability_latest fal ON b.id = fal.bottleneck_id
701 ) r ON r.gauge_id = b.fk_g_fid 702 LEFT JOIN waterlevel_latest wl ON b.fk_g_fid = wl.fk_gauge_id
702 LEFT JOIN LATERAL ( 703 GROUP BY b.id, g.location, fal.date_info, fal.critical, wl.water_level
703 SELECT bottleneck_id,date_info,critical
704 FROM fairway_availability_latest
705 WHERE b.id=bottleneck_id) fal ON TRUE
706 LEFT JOIN LATERAL (
707 SELECT water_level
708 FROM gauge_measurements_waterlevel
709 WHERE b.fk_g_fid=fk_gauge_id) gmw ON TRUE
710 GROUP BY b.id, g.location, fal.date_info, fal.critical, gmw.water_level;
711 ; 704 ;
712 705
713 -- Configure primary keys for geoserver views 706 -- Configure primary keys for geoserver views
714 INSERT INTO waterway.gt_pk_metadata VALUES 707 INSERT INTO waterway.gt_pk_metadata VALUES
715 ('waterway', 'gauges_geoserver', 'location'), 708 ('waterway', 'gauges_geoserver', 'location'),