Mercurial > gemma
changeset 3008:f394e828a6d2
Separate view definitions for GeoServer from general schema definition
That way OR REPLACE can be used which makes development on the
views a lot easier, since the views can be altered without having
to set up a completely new database each time.
| author | Tom Gottfried <tom@intevation.de> |
|---|---|
| date | Thu, 11 Apr 2019 12:01:27 +0200 |
| parents | 792d4476d5d5 |
| children | ddc4d54b8331 |
| files | schema/gemma.sql schema/geoserver_views.sql schema/install-db.sh |
| diffstat | 3 files changed, 145 insertions(+), 144 deletions(-) [+] |
line wrap: on
line diff
--- a/schema/gemma.sql Thu Apr 11 11:55:17 2019 +0200 +++ b/schema/gemma.sql Thu Apr 11 12:01:27 2019 +0200 @@ -295,28 +295,6 @@ value int NOT NULL ) - CREATE VIEW gauges_geoserver 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, - json_strip_nulls(json_object_agg(coalesce(r.depth_reference,'empty'), - r.value)) - AS reference_water_levels - FROM gauges g LEFT JOIN LATERAL ( - SELECT gauge_id, depth_reference, value - FROM gauges_reference_water_levels - ) r ON r.gauge_id = g.location - GROUP BY g.location - CREATE TABLE gauge_measurements ( id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, fk_gauge_id isrs NOT NULL REFERENCES gauges, @@ -387,27 +365,6 @@ related_enc varchar(12) ) - -- A table to help geoserver serve the distance marks as WFS 1.1.0. - -- At least geoserver-2.13.2 does not serve type geography correctly - -- and does not serve the location_code as isrs type - CREATE VIEW distance_marks_geoserver AS - SELECT location_code, - isrs_asText(location_code) AS location, - geom::Geometry(POINT, 4326), - related_enc, - (location_code).hectometre - FROM distance_marks_virtual - - CREATE VIEW distance_marks_ashore_geoserver AS - SELECT id, - country, - geom::Geometry(POINT, 4326), - related_enc, - hectom, - catdis, - position_code - FROM distance_marks - -- We need to configure primary keys for the views used by -- geoserver for wfs, otherwise it will generate ids on the fly, -- which will change for the same feature... @@ -438,6 +395,9 @@ staging_done boolean NOT NULL DEFAULT false, UNIQUE(name, staging_done) ) + CREATE TRIGGER stretches_date_info + BEFORE UPDATE ON stretches + FOR EACH ROW EXECUTE PROCEDURE update_date_info() CREATE TABLE stretch_countries ( stretches_id int NOT NULL REFERENCES stretches(id) @@ -446,28 +406,6 @@ UNIQUE(stretches_id, country_code) ) - -- Published view for GeoServer - CREATE VIEW stretches_geoserver AS SELECT - id, - name, - (stretch).lower::varchar as lower, - (stretch).upper::varchar as upper, - area::Geometry(MULTIPOLYGON, 4326), - objnam, - nobjnam, - date_info, - source_organization, - (SELECT string_agg(country_code, ', ') - FROM stretch_countries - WHERE stretches_id = id) AS countries, - staging_done - FROM stretches - - - CREATE TRIGGER sections_stretches_date_info - BEFORE UPDATE ON stretches - FOR EACH ROW EXECUTE PROCEDURE update_date_info() - CREATE TABLE waterway_profiles ( id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, location isrs NOT NULL REFERENCES distance_marks_virtual, @@ -578,17 +516,6 @@ -- CHECK(ST_IsSimple(CAST(lines AS geometry))), PRIMARY KEY (sounding_result_id, height) ) - -- A view to help geoserver serve contour lines. - -- At least geoserver-2.13.2 does not serve type geography correctly - CREATE VIEW sounding_results_contour_lines_geoserver AS - SELECT bottleneck_id, - date_info, - height, - CAST(lines AS geometry(multilinestring, 4326)) AS lines - FROM sounding_results_contour_lines cl - JOIN sounding_results sr - ON sr.id = cl.sounding_result_id - -- -- Fairway availability -- @@ -647,60 +574,6 @@ CHECK(measure_type = 'minimum guaranteed' OR value_lifetime IS NOT NULL) ) - - CREATE VIEW bottleneck_overview AS - SELECT - objnam AS name, - ST_Centroid(area)::Geometry(POINT, 4326) AS point, - (lower(stretch)).hectometre AS from, - (upper(stretch)).hectometre AS to, - sr.current::text, - responsible_country - FROM bottlenecks bn LEFT JOIN ( - SELECT bottleneck_id, max(date_info) AS current FROM sounding_results - GROUP BY bottleneck_id) sr ON sr.bottleneck_id = bn.id - ORDER BY objnam - - -- Published view for GeoServer - CREATE VIEW bottlenecks_geoserver AS - WITH - fairway_availability_latest AS ( - SELECT DISTINCT ON (bottleneck_id) bottleneck_id, date_info, critical - FROM fairway_availability - ORDER BY bottleneck_id, date_info DESC), - waterlevel_latest AS ( - SELECT DISTINCT ON (fk_gauge_id) fk_gauge_id, water_level - FROM gauge_measurements - WHERE is_waterlevel AND NOT predicted - ORDER BY fk_gauge_id, measure_date DESC) - SELECT - b.id, - b.bottleneck_id, - b.objnam, - b.nobjnm, - b.stretch, - b.area, - b.rb, - b.lb, - b.responsible_country, - b.revisiting_time, - b.limiting, - b.date_info, - b.source_organization, - g.location AS gauge_isrs_code, - g.objname AS gauge_objname, - json_strip_nulls(json_object_agg(coalesce(r.depth_reference,'empty'), - r.value)) - AS reference_water_levels, - fal.date_info AS fa_date_info, - fal.critical AS fa_critical, - wl.water_level AS gm_waterlevel - FROM bottlenecks b - LEFT JOIN gauges g ON b.fk_g_fid = g.location - LEFT JOIN gauges_reference_water_levels r ON g.location = r.gauge_id - LEFT JOIN fairway_availability_latest fal ON b.id = fal.bottleneck_id - LEFT JOIN waterlevel_latest wl ON b.fk_g_fid = wl.fk_gauge_id - GROUP BY b.id, g.location, fal.date_info, fal.critical, wl.water_level ; -- Configure primary keys for geoserver views @@ -832,18 +705,4 @@ ) ; -CREATE VIEW waterway.sounding_differences AS SELECT - sd.id AS id, - bn.objnam AS objnam, - srm.date_info AS minuend, - srs.date_info AS subtrahend, - sdcl.height AS height, - CAST(sdcl.lines AS geometry(multilinestring, 4326)) AS lines -FROM - caching.sounding_differences sd JOIN - caching.sounding_differences_contour_lines sdcl ON sd.id = sdcl.sounding_differences_id JOIN - waterway.sounding_results srm ON sd.minuend = srm.id JOIN - waterway.sounding_results srs ON sd.subtrahend = srs.id JOIN - waterway.bottlenecks bn ON srm.bottleneck_id = bn.id; - COMMIT;
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/schema/geoserver_views.sql Thu Apr 11 12:01:27 2019 +0200 @@ -0,0 +1,141 @@ +CREATE OR REPLACE VIEW waterway.gauges_geoserver 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, + json_strip_nulls(json_object_agg(coalesce(r.depth_reference,'empty'), + r.value)) + AS reference_water_levels + FROM waterway.gauges g LEFT JOIN LATERAL ( + SELECT gauge_id, depth_reference, value + FROM waterway.gauges_reference_water_levels + ) r ON r.gauge_id = g.location + GROUP BY g.location; + +CREATE OR REPLACE VIEW waterway.distance_marks_geoserver AS + SELECT location_code, + isrs_asText(location_code) AS location, + geom::Geometry(POINT, 4326), + related_enc, + (location_code).hectometre + FROM waterway.distance_marks_virtual; + +CREATE OR REPLACE VIEW waterway.distance_marks_ashore_geoserver AS + SELECT id, + country, + geom::Geometry(POINT, 4326), + related_enc, + hectom, + catdis, + position_code + FROM waterway.distance_marks; + +CREATE OR REPLACE VIEW waterway.bottlenecks_geoserver AS + WITH + fairway_availability_latest AS ( + SELECT DISTINCT ON (bottleneck_id) bottleneck_id, date_info, critical + FROM waterway.fairway_availability + ORDER BY bottleneck_id, date_info DESC), + waterlevel_latest AS ( + SELECT DISTINCT ON (fk_gauge_id) fk_gauge_id, water_level + FROM waterway.gauge_measurements + WHERE is_waterlevel AND NOT predicted + ORDER BY fk_gauge_id, measure_date DESC) + SELECT + b.id, + b.bottleneck_id, + b.objnam, + b.nobjnm, + b.stretch, + b.area, + b.rb, + b.lb, + b.responsible_country, + b.revisiting_time, + b.limiting, + b.date_info, + b.source_organization, + g.location AS gauge_isrs_code, + g.objname AS gauge_objname, + json_strip_nulls(json_object_agg(coalesce(r.depth_reference,'empty'), + r.value)) + AS reference_water_levels, + fal.date_info AS fa_date_info, + fal.critical AS fa_critical, + wl.water_level AS gm_waterlevel + FROM waterway.bottlenecks b + LEFT JOIN waterway.gauges g + ON b.fk_g_fid = g.location + LEFT JOIN waterway.gauges_reference_water_levels r + ON g.location = r.gauge_id + LEFT JOIN fairway_availability_latest fal + ON b.id = fal.bottleneck_id + LEFT JOIN waterlevel_latest wl + ON b.fk_g_fid = wl.fk_gauge_id + GROUP BY b.id, g.location, fal.date_info, fal.critical, wl.water_level; + +CREATE OR REPLACE VIEW waterway.stretches_geoserver AS + SELECT + id, + name, + (stretch).lower::varchar as lower, + (stretch).upper::varchar as upper, + area::Geometry(MULTIPOLYGON, 4326), + objnam, + nobjnam, + date_info, + source_organization, + (SELECT string_agg(country_code, ', ') + FROM waterway.stretch_countries + WHERE stretches_id = id) AS countries, + staging_done + FROM waterway.stretches; + +CREATE OR REPLACE VIEW waterway.sounding_results_contour_lines_geoserver AS + SELECT bottleneck_id, + date_info, + height, + CAST(lines AS geometry(multilinestring, 4326)) AS lines + FROM waterway.sounding_results_contour_lines cl + JOIN waterway.sounding_results sr ON sr.id = cl.sounding_result_id; + +CREATE OR REPLACE VIEW waterway.bottleneck_overview AS + SELECT + objnam AS name, + ST_Centroid(area)::Geometry(POINT, 4326) AS point, + (lower(stretch)).hectometre AS from, + (upper(stretch)).hectometre AS to, + sr.current::text, + responsible_country + FROM waterway.bottlenecks bn LEFT JOIN ( + SELECT bottleneck_id, max(date_info) AS current + FROM waterway.sounding_results + GROUP BY bottleneck_id) sr ON sr.bottleneck_id = bn.id + ORDER BY objnam; + +CREATE OR REPLACE VIEW waterway.sounding_differences AS + SELECT + sd.id AS id, + bn.objnam AS objnam, + srm.date_info AS minuend, + srs.date_info AS subtrahend, + sdcl.height AS height, + CAST(sdcl.lines AS geometry(multilinestring, 4326)) AS lines + FROM + caching.sounding_differences sd + JOIN caching.sounding_differences_contour_lines sdcl + ON sd.id = sdcl.sounding_differences_id + JOIN waterway.sounding_results srm + ON sd.minuend = srm.id + JOIN waterway.sounding_results srs + ON sd.subtrahend = srs.id + JOIN waterway.bottlenecks bn + ON srm.bottleneck_id = bn.id;
--- a/schema/install-db.sh Thu Apr 11 11:55:17 2019 +0200 +++ b/schema/install-db.sh Thu Apr 11 12:01:27 2019 +0200 @@ -132,6 +132,7 @@ -f "$BASEDIR/manage_users.sql" \ -f "$BASEDIR/auth.sql" \ -f "$BASEDIR/isrs_functions.sql" \ + -f "$BASEDIR/geoserver_views.sql" \ -f "$BASEDIR/default_sysconfig.sql"