gemma: schema/updates/1300/02.views_to

annotate schema/updates/1300/02.views_to_geoservers.sql @ 5095:e21cbb9768a2

Prevent duplicate fairway areas In principal, there can be only one or no fairway area at each point on the map. Since polygons from real data will often be topologically inexact, just disallow equal geometries. This will also help to avoid importing duplicates with concurrent imports, once the history of fairway dimensions will be preserved.

author	Tom Gottfried <tom@intevation.de>
date	Wed, 25 Mar 2020 18:10:02 +0100
parents	30bb2d819d57
children

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,
4618 0f2c3cb139cc Merge default into geoserver_sql_views Tom Gottfried <tom@intevation.de> parents: 4617 diff changeset	22 wl.n AS gm_n_14d,
4616 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,
4620 f77a6f9216ae Merge default into geoserver_sql_views Tom Gottfried <tom@intevation.de> parents: 4618 diff changeset	34 date_issue,
4616 b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	35 measure_date,
4618 0f2c3cb139cc Merge default into geoserver_sql_views Tom Gottfried <tom@intevation.de> parents: 4617 diff changeset	36 water_level,
0f2c3cb139cc Merge default into geoserver_sql_views Tom Gottfried <tom@intevation.de> parents: 4617 diff changeset	37 count(*) OVER (PARTITION BY location) AS n
4616 b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	38 FROM waterway.gauge_measurements
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	39 -- 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	40 WHERE measure_date
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	41 >= current_timestamp - '14 days 00:15'::interval
4618 0f2c3cb139cc Merge default into geoserver_sql_views Tom Gottfried <tom@intevation.de> parents: 4617 diff changeset	42 ORDER BY location, measure_date DESC) AS wl
4616 b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	43 USING (location)
4618 0f2c3cb139cc Merge default into geoserver_sql_views Tom Gottfried <tom@intevation.de> parents: 4617 diff changeset	44 LEFT JOIN (SELECT DISTINCT ON (location)
0f2c3cb139cc Merge default into geoserver_sql_views Tom Gottfried <tom@intevation.de> parents: 4617 diff changeset	45 location,
4620 f77a6f9216ae Merge default into geoserver_sql_views Tom Gottfried <tom@intevation.de> parents: 4618 diff changeset	46 date_issue,
4618 0f2c3cb139cc Merge default into geoserver_sql_views Tom Gottfried <tom@intevation.de> parents: 4617 diff changeset	47 max(acc) FILTER (WHERE measure_date
0f2c3cb139cc Merge default into geoserver_sql_views Tom Gottfried <tom@intevation.de> parents: 4617 diff changeset	48 <= current_timestamp + '1 day'::interval)
0f2c3cb139cc Merge default into geoserver_sql_views Tom Gottfried <tom@intevation.de> parents: 4617 diff changeset	49 OVER loc_date_issue AS forecast_accuracy_1d,
0f2c3cb139cc Merge default into geoserver_sql_views Tom Gottfried <tom@intevation.de> parents: 4617 diff changeset	50 max(acc) OVER loc_date_issue AS forecast_accuracy_3d
0f2c3cb139cc Merge default into geoserver_sql_views Tom Gottfried <tom@intevation.de> parents: 4617 diff changeset	51 FROM (SELECT location, date_issue, measure_date,
0f2c3cb139cc Merge default into geoserver_sql_views Tom Gottfried <tom@intevation.de> parents: 4617 diff changeset	52 GREATEST(water_level - lower(conf_interval),
0f2c3cb139cc Merge default into geoserver_sql_views Tom Gottfried <tom@intevation.de> parents: 4617 diff changeset	53 upper(conf_interval) - water_level) AS acc
0f2c3cb139cc Merge default into geoserver_sql_views Tom Gottfried <tom@intevation.de> parents: 4617 diff changeset	54 FROM waterway.gauge_predictions
0f2c3cb139cc Merge default into geoserver_sql_views Tom Gottfried <tom@intevation.de> parents: 4617 diff changeset	55 -- consider predictions made within last 14 days ...
0f2c3cb139cc Merge default into geoserver_sql_views Tom Gottfried <tom@intevation.de> parents: 4617 diff changeset	56 WHERE date_issue
0f2c3cb139cc Merge default into geoserver_sql_views Tom Gottfried <tom@intevation.de> parents: 4617 diff changeset	57 >= current_timestamp - '14 days 00:15'::interval
0f2c3cb139cc Merge default into geoserver_sql_views Tom Gottfried <tom@intevation.de> parents: 4617 diff changeset	58 -- ... for the next three days from now
0f2c3cb139cc Merge default into geoserver_sql_views Tom Gottfried <tom@intevation.de> parents: 4617 diff changeset	59 AND measure_date BETWEEN current_timestamp
0f2c3cb139cc Merge default into geoserver_sql_views Tom Gottfried <tom@intevation.de> parents: 4617 diff changeset	60 AND current_timestamp + '3 days'::interval) AS acc
0f2c3cb139cc Merge default into geoserver_sql_views Tom Gottfried <tom@intevation.de> parents: 4617 diff changeset	61 WINDOW loc_date_issue AS (PARTITION BY location, date_issue)
0f2c3cb139cc Merge default into geoserver_sql_views Tom Gottfried <tom@intevation.de> parents: 4617 diff changeset	62 ORDER BY location, date_issue DESC) AS fca
4620 f77a6f9216ae Merge default into geoserver_sql_views Tom Gottfried <tom@intevation.de> parents: 4618 diff changeset	63 -- Show only forecasts issued with latest measurements or later
f77a6f9216ae Merge default into geoserver_sql_views Tom Gottfried <tom@intevation.de> parents: 4618 diff changeset	64 ON fca.location = g.location AND fca.date_issue >= wl.date_issue
4616 b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	65 $$);
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	66
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	67 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	68 ('waterway', 'gauges_geoserver', 4326, $$
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	69 SELECT
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	70 isrs_code,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	71 objname,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	72 geom,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	73 applicability_from_km,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	74 applicability_to_km,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	75 zero_point,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	76 geodref,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	77 date_info,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	78 source_organization,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	79 reference_water_levels,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	80 gm_measuredate,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	81 gm_waterlevel,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	82 gm_n_14d,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	83 forecast_accuracy_3d,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	84 forecast_accuracy_1d
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	85 FROM (
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	86 $$ \|\| (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	87 ) AS gauges_base_view
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	88 WHERE NOT erased
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	89 $$),
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	90 ('waterway', 'sections_geoserver', 4326, $$
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	91 SELECT
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	92 s.id,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	93 s.name,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	94 (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	95 (s.section).upper::varchar as upper,
4622 b03aa1502736 Fixup rev. 4476ec4db818 Tom Gottfried <tom@intevation.de> parents: 4620 diff changeset	96 s.area,
4616 b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	97 s.objnam,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	98 s.nobjnam,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	99 s.date_info,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	100 s.source_organization,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	101 s.staging_done,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	102 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	103 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	104 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	105 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	106 FROM waterway.sections s
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	107 LEFT JOIN (
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	108 $$ \|\| (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	109 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	110 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	111 GROUP BY s.id
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	112 $$),
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	113 ('waterway', 'stretches_geoserver', 4326, $$
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	114 SELECT
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	115 s.id,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	116 s.name,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	117 (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	118 (s.stretch).upper::varchar as upper,
4622 b03aa1502736 Fixup rev. 4476ec4db818 Tom Gottfried <tom@intevation.de> parents: 4620 diff changeset	119 s.area,
4616 b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	120 s.objnam,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	121 s.nobjnam,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	122 s.date_info,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	123 s.source_organization,
4618 0f2c3cb139cc Merge default into geoserver_sql_views Tom Gottfried <tom@intevation.de> parents: 4617 diff changeset	124 (SELECT string_agg(country, ', ')
0f2c3cb139cc Merge default into geoserver_sql_views Tom Gottfried <tom@intevation.de> parents: 4617 diff changeset	125 FROM users.stretch_countries
0f2c3cb139cc Merge default into geoserver_sql_views Tom Gottfried <tom@intevation.de> parents: 4617 diff changeset	126 WHERE stretch_id = s.id) AS countries,
4616 b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	127 s.staging_done,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	128 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	129 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	130 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	131 max(g.forecast_accuracy_1d) AS forecast_accuracy_1d
4618 0f2c3cb139cc Merge default into geoserver_sql_views Tom Gottfried <tom@intevation.de> parents: 4617 diff changeset	132 FROM users.stretches s
4616 b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	133 LEFT JOIN (
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	134 $$ \|\| (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	135 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	136 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	137 GROUP BY s.id
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	138 $$),
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	139 ('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	140 SELECT id,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	141 country,
4622 b03aa1502736 Fixup rev. 4476ec4db818 Tom Gottfried <tom@intevation.de> parents: 4620 diff changeset	142 geom,
4616 b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	143 related_enc,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	144 hectom,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	145 catdis,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	146 position_code
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	147 FROM waterway.distance_marks
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	148 $$),
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	149 ('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	150 SELECT
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	151 isrs_asText(location_code) AS location,
4622 b03aa1502736 Fixup rev. 4476ec4db818 Tom Gottfried <tom@intevation.de> parents: 4620 diff changeset	152 geom,
4616 b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	153 related_enc,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	154 (location_code).hectometre
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	155 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	156 $$),
4623 30bb2d819d57 Merge default into geoserver_sql_views Tom Gottfried <tom@intevation.de> parents: 4622 diff changeset	157 ('waterway', 'sounding_results_areas_geoserver', 4326, $$
4616 b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	158 SELECT bottleneck_id,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	159 date_info,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	160 height,
4623 30bb2d819d57 Merge default into geoserver_sql_views Tom Gottfried <tom@intevation.de> parents: 4622 diff changeset	161 areas
30bb2d819d57 Merge default into geoserver_sql_views Tom Gottfried <tom@intevation.de> parents: 4622 diff changeset	162 FROM waterway.sounding_results_iso_areas ia
30bb2d819d57 Merge default into geoserver_sql_views Tom Gottfried <tom@intevation.de> parents: 4622 diff changeset	163 JOIN waterway.sounding_results sr ON sr.id = ia.sounding_result_id
4616 b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	164 $$),
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	165 ('waterway', 'bottlenecks_geoserver', 4326, $$
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	166 SELECT
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	167 b.id,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	168 b.bottleneck_id,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	169 b.objnam,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	170 b.nobjnm,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	171 b.area,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	172 b.rb,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	173 b.lb,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	174 b.responsible_country,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	175 b.revisiting_time,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	176 b.limiting,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	177 b.date_info,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	178 b.source_organization,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	179 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	180 g.reference_water_levels,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	181 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	182 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	183 g.gm_measuredate,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	184 g.gm_waterlevel,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	185 g.gm_n_14d,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	186 srl.date_max,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	187 g.forecast_accuracy_3d,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	188 g.forecast_accuracy_1d
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	189 FROM waterway.bottlenecks b
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	190 LEFT JOIN (
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	191 $$ \|\| (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	192 ) AS g
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	193 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	194 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	195 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	196 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	197 FROM waterway.fairway_availability
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, 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	199 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	200 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	201 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	202 FROM waterway.sounding_results
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	203 GROUP BY bottleneck_id
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	204 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	205 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	206 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	207 $$),
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	208 ('waterway', 'bottleneck_overview', 4326, $$
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	209 SELECT
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	210 objnam AS name,
4622 b03aa1502736 Fixup rev. 4476ec4db818 Tom Gottfried <tom@intevation.de> parents: 4620 diff changeset	211 ST_Centroid(area) AS point,
4616 b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	212 (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	213 (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	214 sr.current::text,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	215 responsible_country
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	216 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	217 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	218 FROM waterway.sounding_results
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	219 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	220 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	221 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	222 ORDER BY objnam
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	223 $$),
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	224 ('waterway', 'sounding_differences', 4326, $$
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	225 SELECT
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	226 sd.id AS id,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	227 bn.objnam AS objnam,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	228 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	229 srs.date_info AS subtrahend,
4623 30bb2d819d57 Merge default into geoserver_sql_views Tom Gottfried <tom@intevation.de> parents: 4622 diff changeset	230 sdia.height AS height,
30bb2d819d57 Merge default into geoserver_sql_views Tom Gottfried <tom@intevation.de> parents: 4622 diff changeset	231 sdia.areas AS areas
4616 b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	232 FROM caching.sounding_differences sd
4623 30bb2d819d57 Merge default into geoserver_sql_views Tom Gottfried <tom@intevation.de> parents: 4622 diff changeset	233 JOIN caching.sounding_differences_iso_areas sdia
30bb2d819d57 Merge default into geoserver_sql_views Tom Gottfried <tom@intevation.de> parents: 4622 diff changeset	234 ON sd.id = sdia.sounding_differences_id
4616 b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	235 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	236 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	237 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	238 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	239 JOIN waterway.bottlenecks bn
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	240 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	241 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	242 $$)
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	243 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	244 srid = EXCLUDED.srid,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	245 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	246
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	247 DROP VIEW
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	248 waterway.gauges_base_view,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	249 waterway.gauges_geoserver,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	250 waterway.distance_marks_geoserver,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	251 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	252 waterway.bottlenecks_geoserver,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	253 waterway.stretches_geoserver,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	254 waterway.sections_geoserver,
b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	255 waterway.bottleneck_overview,
4623 30bb2d819d57 Merge default into geoserver_sql_views Tom Gottfried <tom@intevation.de> parents: 4622 diff changeset	256 waterway.sounding_results_areas_geoserver,
4616 b605e91f08f0 Use GeoServer's SQL view feature instead of in-database VIEWs Tom Gottfried <tom@intevation.de> parents: diff changeset	257 waterway.sounding_differences;

Mercurial > gemma

annotate schema/updates/1300/02.views_to_geoservers.sql @ 5095:e21cbb9768a2