Mercurial > gemma

comparison schema/gemma.sql @ 1929:f538d9a23329

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Better suited GeoServer view for waterway.stretches.
author Sascha L. Teichmann <sascha.teichmann@intevation.de>
date Mon, 21 Jan 2019 11:44:31 +0100
parents 32c56e6c089a
children f7a35ba9f409
comparison
equal deleted inserted replaced
1928:76ca071cb006 1929:f538d9a23329
367 stretches_id int NOT NULL REFERENCES stretches(id) 367 stretches_id int NOT NULL REFERENCES stretches(id)
368 ON DELETE CASCADE, 368 ON DELETE CASCADE,
369 country_code char(2) NOT NULL REFERENCES countries(country_code), 369 country_code char(2) NOT NULL REFERENCES countries(country_code),
370 UNIQUE(stretches_id, country_code) 370 UNIQUE(stretches_id, country_code)
371 ) 371 )
372
373 -- Published view for GeoServer
374 CREATE VIEW stretches_geoserver AS SELECT
375 id,
376 name,
377 (stretch).lower::varchar as lower,
378 (stretch).upper::varchar as upper,
379 geom::Geometry(POLYGON, 4326),
380 objnam,
381 nobjnam,
382 date_info,
383 source_organization,
384 (SELECT string_agg(country_code, ', ')
385 FROM stretch_countries
386 WHERE stretches_id = id) AS countries,
387 staging_done
388 FROM stretches
389
372 390
373 CREATE TRIGGER sections_stretches_date_info 391 CREATE TRIGGER sections_stretches_date_info
374 BEFORE UPDATE ON stretches 392 BEFORE UPDATE ON stretches
375 FOR EACH ROW EXECUTE PROCEDURE update_date_info() 393 FOR EACH ROW EXECUTE PROCEDURE update_date_info()
376 394