Mercurial > gemma
changeset 4620:f77a6f9216ae geoserver_sql_views
Merge default into geoserver_sql_views
| author | Tom Gottfried <tom@intevation.de> |
|---|---|
| date | Fri, 20 Sep 2019 17:48:47 +0200 |
| parents | 4476ec4db818 (current diff) 9b9f60e26b39 (diff) |
| children | d2eac69ba86b |
| files | schema/default_sysconfig.sql schema/updates/1300/02.views_to_geoservers.sql |
| diffstat | 4 files changed, 79 insertions(+), 3 deletions(-) [+] |
line wrap: on
line diff
--- a/schema/default_sysconfig.sql Fri Sep 20 16:08:15 2019 +0200 +++ b/schema/default_sysconfig.sql Fri Sep 20 17:48:47 2019 +0200 @@ -55,6 +55,7 @@ USING (location, validity) LEFT JOIN (SELECT DISTINCT ON (location) location, + date_issue, measure_date, water_level, count(*) OVER (PARTITION BY location) AS n @@ -66,6 +67,7 @@ USING (location) LEFT JOIN (SELECT DISTINCT ON (location) location, + date_issue, max(acc) FILTER (WHERE measure_date <= current_timestamp + '1 day'::interval) OVER loc_date_issue AS forecast_accuracy_1d, @@ -82,7 +84,8 @@ AND current_timestamp + '3 days'::interval) AS acc WINDOW loc_date_issue AS (PARTITION BY location, date_issue) ORDER BY location, date_issue DESC) AS fca - USING (location) + -- Show only forecasts issued with latest measurements or later + ON fca.location = g.location AND fca.date_issue >= wl.date_issue $$); INSERT INTO sys_admin.published_services (schema, name, srid, view_def) VALUES
--- a/schema/updates/1202/01.fix_data_availability.sql Fri Sep 20 16:08:15 2019 +0200 +++ b/schema/updates/1202/01.fix_data_availability.sql Fri Sep 20 17:48:47 2019 +0200 @@ -1,3 +1,62 @@ -\ir ../../geoserver_views.sql; +CREATE OR REPLACE VIEW waterway.gauges_base_view AS + SELECT + g.location, + isrs_asText(g.location) AS isrs_code, + g.objname, + g.geom, + g.applicability_from_km, + g.applicability_to_km, + g.validity, + g.zero_point, + g.geodref, + g.date_info, + g.source_organization, + g.erased, + r.rwls AS reference_water_levels, + wl.measure_date AS gm_measuredate, + wl.water_level AS gm_waterlevel, + wl.n AS gm_n_14d, + fca.forecast_accuracy_3d, + fca.forecast_accuracy_1d + FROM waterway.gauges g + LEFT JOIN (SELECT location, validity, + json_strip_nulls(json_object_agg( + coalesce(depth_reference, 'empty'), value)) AS rwls + FROM waterway.gauges_reference_water_levels + GROUP BY location, validity) AS r + USING (location, validity) + LEFT JOIN (SELECT DISTINCT ON (location) + location, + date_issue, + measure_date, + water_level, + count(*) OVER (PARTITION BY location) AS n + FROM waterway.gauge_measurements + -- consider all measurements within 14 days plus a tolerance + WHERE measure_date + >= current_timestamp - '14 days 00:15'::interval + ORDER BY location, measure_date DESC) AS wl + USING (location) + LEFT JOIN (SELECT DISTINCT ON (location) + location, + date_issue, + max(acc) FILTER (WHERE measure_date + <= current_timestamp + '1 day'::interval) + OVER loc_date_issue AS forecast_accuracy_1d, + max(acc) OVER loc_date_issue AS forecast_accuracy_3d + FROM (SELECT location, date_issue, measure_date, + GREATEST(water_level - lower(conf_interval), + upper(conf_interval) - water_level) AS acc + FROM waterway.gauge_predictions + -- consider predictions made within last 14 days ... + WHERE date_issue + >= current_timestamp - '14 days 00:15'::interval + -- ... for the next three days from now + AND measure_date BETWEEN current_timestamp + AND current_timestamp + '3 days'::interval) AS acc + WINDOW loc_date_issue AS (PARTITION BY location, date_issue) + ORDER BY location, date_issue DESC) AS fca + -- Show only forecasts issued with latest measurements or later + ON fca.location = g.location AND fca.date_issue >= wl.date_issue; DROP INDEX waterway.gauge_measurements_location_measure_date_desc
--- a/schema/updates/1300/02.views_to_geoservers.sql Fri Sep 20 16:08:15 2019 +0200 +++ b/schema/updates/1300/02.views_to_geoservers.sql Fri Sep 20 17:48:47 2019 +0200 @@ -31,6 +31,7 @@ USING (location, validity) LEFT JOIN (SELECT DISTINCT ON (location) location, + date_issue, measure_date, water_level, count(*) OVER (PARTITION BY location) AS n @@ -42,6 +43,7 @@ USING (location) LEFT JOIN (SELECT DISTINCT ON (location) location, + date_issue, max(acc) FILTER (WHERE measure_date <= current_timestamp + '1 day'::interval) OVER loc_date_issue AS forecast_accuracy_1d, @@ -58,7 +60,8 @@ AND current_timestamp + '3 days'::interval) AS acc WINDOW loc_date_issue AS (PARTITION BY location, date_issue) ORDER BY location, date_issue DESC) AS fca - USING (location) + -- Show only forecasts issued with latest measurements or later + ON fca.location = g.location AND fca.date_issue >= wl.date_issue $$); INSERT INTO sys_admin.published_services (schema, name, srid, view_def) VALUES
--- a/schema/updates/README.md Fri Sep 20 16:08:15 2019 +0200 +++ b/schema/updates/README.md Fri Sep 20 17:48:47 2019 +0200 @@ -22,6 +22,10 @@ SELECT get_schema_version(); ``` +It is an design goal to allow any older version of a gemma data base +to be updated to the most current schema by calling `update-db.sh` +from the current gemma distribution. + ## Directory Layout the `schema/updates` directory contains subdirectories with names @@ -63,6 +67,13 @@ Note that all scripts in one version update are executed in a single transaction by the `update-db.sh` script. + **Important:** all sql scripts in one update directory have to be + self contained and *must not* include any files from the regular + schema definition (or from other update versions). This is + necessary to allow for updates over multiple versions of the schema, + where multiple updates might make incremental changes to the same + functions/files of the schema. + - Modify the scripts in `schema/` (outside the updates directory) to reflect the changes. The schema of a freshly created database using `schema/install-db.sh` must always be identical to the schema of a