Mercurial > gemma

comparison schema/geoserver_views.sql @ 3454:7f65790b6bd6

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Avoid full sequential scan on measurements for every query Up to PostgreSQL 11, a CTE is always executed to completion, regardless of whether the result is actually needed for the calling query. A sub-select is not 'fenced' that way. Makes some database requests of GeoServer a lot faster.
author Tom Gottfried <tom@intevation.de>
date Fri, 24 May 2019 13:35:26 +0200
parents 030dc48382c9
children ca395be62023
comparison
equal deleted inserted replaced
3453:41349a9c8ce9 3454:7f65790b6bd6
1 CREATE OR REPLACE VIEW waterway.gauges_geoserver AS 1 CREATE OR REPLACE VIEW waterway.gauges_geoserver AS
2 WITH
3 waterlevel_latest AS (
4 SELECT DISTINCT ON (location) location, water_level
5 FROM waterway.gauge_measurements
6 ORDER BY location, measure_date DESC)
7 SELECT 2 SELECT
8 g.location, 3 g.location,
9 isrs_asText(g.location) AS isrs_code, 4 isrs_asText(g.location) AS isrs_code,
10 g.objname, 5 g.objname,
11 g.geom, 6 g.geom,
25 json_strip_nulls(json_object_agg( 20 json_strip_nulls(json_object_agg(
26 coalesce(depth_reference, 'empty'), value)) AS rwls 21 coalesce(depth_reference, 'empty'), value)) AS rwls
27 FROM waterway.gauges_reference_water_levels 22 FROM waterway.gauges_reference_water_levels
28 GROUP BY location, validity) AS r 23 GROUP BY location, validity) AS r
29 USING (location, validity) 24 USING (location, validity)
30 LEFT JOIN waterlevel_latest wl 25 LEFT JOIN (SELECT DISTINCT ON (location) location, water_level
26 FROM waterway.gauge_measurements
27 ORDER BY location, measure_date DESC) AS wl
31 USING (location) 28 USING (location)
32 LEFT JOIN (SELECT location, 29 LEFT JOIN (SELECT location,
33 max(acc) FILTER (WHERE 30 max(acc) FILTER (WHERE
34 measure_date <= current_timestamp + '1 day'::interval) 31 measure_date <= current_timestamp + '1 day'::interval)
35 AS forecast_accuracy_1d, 32 AS forecast_accuracy_1d,