Mercurial > gemma

annotate schema/updates/1316/01.search_for_bottleneck_id.sql @ 5217:70d83b7d36ef new-fwa

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Track the number days LDCs are not available at bottlenecks.
author Sascha L. Teichmann <sascha.teichmann@intevation.de>
date Mon, 11 May 2020 19:52:25 +0200
parents 7cbe5d32a614
children
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
4874
7cbe5d32a614 Added search for bottleneck_id.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
1 CREATE OR REPLACE FUNCTION search_bottlenecks(search_string text) RETURNS jsonb
7cbe5d32a614 Added search for bottleneck_id.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
2 LANGUAGE plpgsql STABLE PARALLEL SAFE
7cbe5d32a614 Added search for bottleneck_id.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
3 AS $$
7cbe5d32a614 Added search for bottleneck_id.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
4 DECLARE
7cbe5d32a614 Added search for bottleneck_id.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
5 _result jsonb;
7cbe5d32a614 Added search for bottleneck_id.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
6 BEGIN
7cbe5d32a614 Added search for bottleneck_id.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
7 SELECT COALESCE(json_agg(r),'[]')
7cbe5d32a614 Added search for bottleneck_id.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
8 INTO _result
7cbe5d32a614 Added search for bottleneck_id.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
9 FROM (SELECT objnam AS name,
7cbe5d32a614 Added search for bottleneck_id.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
10 ST_AsGeoJSON(ST_Envelope(area::geometry))::json AS geom,
7cbe5d32a614 Added search for bottleneck_id.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
11 'bottleneck' AS type,
7cbe5d32a614 Added search for bottleneck_id.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
12 bottleneck_id AS location
7cbe5d32a614 Added search for bottleneck_id.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
13 FROM waterway.bottlenecks
7cbe5d32a614 Added search for bottleneck_id.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
14 WHERE (objnam ILIKE '%' || search_string || '%'
7cbe5d32a614 Added search for bottleneck_id.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
15 OR bottleneck_id ILIKE '%' || search_string || '%')
7cbe5d32a614 Added search for bottleneck_id.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
16 AND validity @> now()
7cbe5d32a614 Added search for bottleneck_id.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
17 ORDER BY name) r;
7cbe5d32a614 Added search for bottleneck_id.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
18 RETURN _result;
7cbe5d32a614 Added search for bottleneck_id.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
19 END;
7cbe5d32a614 Added search for bottleneck_id.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
20 $$;