gemma: schema/updates/1300/02.views_to

annotate schema/updates/1300/02.views_to_geoservers.sql @ 4617:ae840f9eb4c8 geoserver_sql_views

Advance schema version number Do a bigger step to allow more schema changes in default branch before we have to move here (branch geoserver_sql_views) again.

author	Tom Gottfried <tom@intevation.de>
date	Mon, 16 Sep 2019 16:56:11 +0200
parents	schema/updates/1113/02.views_to_geoservers.sql@b605e91f08f0
children	0f2c3cb139cc

rev	line source
4616 b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	1 -- Copied from ../../default_sysconfig.sql
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	2 -- plus conflict resolution to achieve updates
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	3
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	4 CREATE TEMP TABLE base_views (name, def) AS VALUES (
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	5 'gauges_base_view', $$
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	6 SELECT
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	7 g.location,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	8 isrs_asText(g.location) AS isrs_code,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	9 g.objname,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	10 g.geom,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	11 g.applicability_from_km,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	12 g.applicability_to_km,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	13 g.validity,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	14 g.zero_point,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	15 g.geodref,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	16 g.date_info,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	17 g.source_organization,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	18 g.erased,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	19 r.rwls AS reference_water_levels,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	20 wl.measure_date AS gm_measuredate,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	21 wl.water_level AS gm_waterlevel,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	22 wl_14d.n AS gm_n_14d,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	23 fca.forecast_accuracy_3d,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	24 fca.forecast_accuracy_1d
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	25 FROM waterway.gauges g
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	26 LEFT JOIN (SELECT location, validity,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	27 json_strip_nulls(json_object_agg(
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	28 coalesce(depth_reference, 'empty'), value)) AS rwls
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	29 FROM waterway.gauges_reference_water_levels
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	30 GROUP BY location, validity) AS r
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	31 USING (location, validity)
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	32 LEFT JOIN (SELECT DISTINCT ON (location)
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	33 location,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	34 measure_date,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	35 water_level
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	36 FROM waterway.gauge_measurements
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	37 ORDER BY location, measure_date DESC) AS wl
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	38 USING (location)
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	39 LEFT JOIN (SELECT location, count(water_level) AS n
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	40 FROM waterway.gauge_measurements
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	41 -- consider all measurements within 14 days plus a tolerance
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	42 WHERE measure_date
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	43 >= current_timestamp - '14 days 00:15'::interval
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	44 GROUP BY location) AS wl_14d
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	45 USING (location)
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	46 LEFT JOIN (SELECT location,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	47 max(acc) FILTER (WHERE
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	48 measure_date <= current_timestamp + '1 day'::interval)
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	49 AS forecast_accuracy_1d,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	50 max(acc) AS forecast_accuracy_3d
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	51 FROM waterway.gauge_predictions,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	52 GREATEST(water_level - lower(conf_interval),
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	53 upper(conf_interval) - water_level) AS acc (acc)
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	54 WHERE measure_date
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	55 BETWEEN current_timestamp
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	56 AND current_timestamp + '3 days'::interval
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	57 GROUP BY location) AS fca
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	58 USING (location)
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	59 $$);
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	60
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	61 INSERT INTO sys_admin.published_services (schema, name, srid, view_def) VALUES
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	62 ('waterway', 'gauges_geoserver', 4326, $$
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	63 SELECT
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	64 isrs_code,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	65 objname,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	66 geom,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	67 applicability_from_km,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	68 applicability_to_km,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	69 zero_point,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	70 geodref,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	71 date_info,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	72 source_organization,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	73 reference_water_levels,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	74 gm_measuredate,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	75 gm_waterlevel,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	76 gm_n_14d,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	77 forecast_accuracy_3d,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	78 forecast_accuracy_1d
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	79 FROM (
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	80 $$ \|\| (SELECT def FROM base_views WHERE name = 'gauges_base_view') \|\| $$
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	81 ) AS gauges_base_view
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	82 WHERE NOT erased
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	83 $$),
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	84 ('waterway', 'sections_geoserver', 4326, $$
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	85 SELECT
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	86 s.id,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	87 s.name,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	88 (s.section).lower::varchar as lower,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	89 (s.section).upper::varchar as upper,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	90 s.area::Geometry(MULTIPOLYGON, 4326),
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	91 s.objnam,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	92 s.nobjnam,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	93 s.date_info,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	94 s.source_organization,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	95 s.staging_done,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	96 min(g.gm_measuredate) AS gm_measuredate,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	97 min(g.gm_n_14d) AS gm_n_14d,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	98 max(g.forecast_accuracy_3d) AS forecast_accuracy_3d,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	99 max(g.forecast_accuracy_1d) AS forecast_accuracy_1d
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	100 FROM waterway.sections s
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	101 LEFT JOIN (
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	102 $$ \|\| (SELECT def FROM base_views WHERE name = 'gauges_base_view') \|\| $$
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	103 WHERE NOT erased) AS g
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	104 ON g.location <@ s.section
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	105 GROUP BY s.id
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	106 $$),
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	107 ('waterway', 'stretches_geoserver', 4326, $$
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	108 SELECT
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	109 s.id,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	110 s.name,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	111 (s.stretch).lower::varchar as lower,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	112 (s.stretch).upper::varchar as upper,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	113 s.area::Geometry(MULTIPOLYGON, 4326),
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	114 s.objnam,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	115 s.nobjnam,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	116 s.date_info,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	117 s.source_organization,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	118 (SELECT string_agg(country_code, ', ')
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	119 FROM waterway.stretch_countries
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	120 WHERE stretches_id = s.id) AS countries,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	121 s.staging_done,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	122 min(g.gm_measuredate) AS gm_measuredate,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	123 min(g.gm_n_14d) AS gm_n_14d,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	124 max(g.forecast_accuracy_3d) AS forecast_accuracy_3d,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	125 max(g.forecast_accuracy_1d) AS forecast_accuracy_1d
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	126 FROM waterway.stretches s
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	127 LEFT JOIN (
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	128 $$ \|\| (SELECT def FROM base_views WHERE name = 'gauges_base_view') \|\| $$
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	129 WHERE NOT erased) AS g
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	130 ON g.location <@ s.stretch
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	131 GROUP BY s.id
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	132 $$),
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	133 ('waterway', 'distance_marks_ashore_geoserver', 4326, $$
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	134 SELECT id,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	135 country,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	136 geom::Geometry(POINT, 4326),
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	137 related_enc,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	138 hectom,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	139 catdis,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	140 position_code
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	141 FROM waterway.distance_marks
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	142 $$),
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	143 ('waterway', 'distance_marks_geoserver', 4326, $$
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	144 SELECT
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	145 isrs_asText(location_code) AS location,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	146 geom::Geometry(POINT, 4326),
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	147 related_enc,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	148 (location_code).hectometre
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	149 FROM waterway.distance_marks_virtual
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	150 $$),
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	151 ('waterway', 'sounding_results_contour_lines_geoserver', 4326, $$
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	152 SELECT bottleneck_id,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	153 date_info,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	154 height,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	155 CAST(lines AS geometry(multilinestring, 4326)) AS lines
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	156 FROM waterway.sounding_results_contour_lines cl
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	157 JOIN waterway.sounding_results sr ON sr.id = cl.sounding_result_id
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	158 $$),
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	159 ('waterway', 'bottlenecks_geoserver', 4326, $$
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	160 SELECT
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	161 b.id,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	162 b.bottleneck_id,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	163 b.objnam,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	164 b.nobjnm,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	165 b.area,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	166 b.rb,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	167 b.lb,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	168 b.responsible_country,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	169 b.revisiting_time,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	170 b.limiting,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	171 b.date_info,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	172 b.source_organization,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	173 g.objname AS gauge_objname,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	174 g.reference_water_levels,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	175 fal.date_info AS fa_date_info,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	176 fal.critical AS fa_critical,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	177 g.gm_measuredate,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	178 g.gm_waterlevel,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	179 g.gm_n_14d,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	180 srl.date_max,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	181 g.forecast_accuracy_3d,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	182 g.forecast_accuracy_1d
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	183 FROM waterway.bottlenecks b
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	184 LEFT JOIN (
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	185 $$ \|\| (SELECT def FROM base_views WHERE name = 'gauges_base_view') \|\| $$
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	186 ) AS g
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	187 ON b.gauge_location = g.location
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	188 AND g.validity @> current_timestamp
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	189 LEFT JOIN (SELECT DISTINCT ON (bottleneck_id)
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	190 bottleneck_id, date_info, critical
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	191 FROM waterway.fairway_availability
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	192 ORDER BY bottleneck_id, date_info DESC) AS fal
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	193 ON b.bottleneck_id = fal.bottleneck_id
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	194 LEFT JOIN (SELECT DISTINCT ON (bottleneck_id)
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	195 bottleneck_id, max(date_info) AS date_max
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	196 FROM waterway.sounding_results
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	197 GROUP BY bottleneck_id
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	198 ORDER BY bottleneck_id DESC) AS srl
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	199 ON b.bottleneck_id = srl.bottleneck_id
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	200 WHERE b.validity @> current_timestamp
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	201 $$),
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	202 ('waterway', 'bottleneck_overview', 4326, $$
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	203 SELECT
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	204 objnam AS name,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	205 ST_Centroid(area)::Geometry(POINT, 4326) AS point,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	206 (lower(stretch)).hectometre AS from,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	207 (upper(stretch)).hectometre AS to,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	208 sr.current::text,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	209 responsible_country
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	210 FROM waterway.bottlenecks bn LEFT JOIN (
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	211 SELECT bottleneck_id, max(date_info) AS current
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	212 FROM waterway.sounding_results
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	213 GROUP BY bottleneck_id) sr
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	214 ON sr.bottleneck_id = bn.bottleneck_id
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	215 WHERE bn.validity @> current_timestamp
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	216 ORDER BY objnam
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	217 $$),
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	218 ('waterway', 'sounding_differences', 4326, $$
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	219 SELECT
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	220 sd.id AS id,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	221 bn.objnam AS objnam,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	222 srm.date_info AS minuend,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	223 srs.date_info AS subtrahend,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	224 sdcl.height AS height,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	225 CAST(sdcl.lines AS geometry(multilinestring, 4326)) AS lines
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	226 FROM caching.sounding_differences sd
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	227 JOIN caching.sounding_differences_contour_lines sdcl
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	228 ON sd.id = sdcl.sounding_differences_id
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	229 JOIN waterway.sounding_results srm
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	230 ON sd.minuend = srm.id
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	231 JOIN waterway.sounding_results srs
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	232 ON sd.subtrahend = srs.id
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	233 JOIN waterway.bottlenecks bn
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	234 ON srm.bottleneck_id = bn.bottleneck_id
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	235 AND srm.date_info::timestamptz <@ bn.validity
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	236 $$)
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	237 ON CONFLICT (schema, name) DO UPDATE SET
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	238 srid = EXCLUDED.srid,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	239 view_def = EXCLUDED.view_def;
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	240
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	241 DROP VIEW
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	242 waterway.gauges_base_view,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	243 waterway.gauges_geoserver,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	244 waterway.distance_marks_geoserver,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	245 waterway.distance_marks_ashore_geoserver,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	246 waterway.bottlenecks_geoserver,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	247 waterway.stretches_geoserver,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	248 waterway.sections_geoserver,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	249 waterway.sounding_results_contour_lines_geoserver,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	250 waterway.bottleneck_overview,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	251 waterway.sounding_differences;

Mercurial > gemma

annotate schema/updates/1300/02.views_to_geoservers.sql @ 4617:ae840f9eb4c8 geoserver_sql_views