Mercurial > gemma

annotate schema/updates/1432/01.search_functions-time.sql @ 5267:aca4bf7af270

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
client: remove mapState from import statement
author Fadi Abbud <fadi.abbud@intevation.de>
date Wed, 10 Jun 2020 16:33:10 +0200
parents a6e5bb85f0a7
children
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
5130
a6e5bb85f0a7 Added new (optional) parameter "time" to search end point.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
1 -- This is Free Software under GNU Affero General Public License v >= 3.0
a6e5bb85f0a7 Added new (optional) parameter "time" to search end point.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
2 -- without warranty, see README.md and license for details.
a6e5bb85f0a7 Added new (optional) parameter "time" to search end point.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
3
a6e5bb85f0a7 Added new (optional) parameter "time" to search end point.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
4 -- SPDX-License-Identifier: AGPL-3.0-or-later
a6e5bb85f0a7 Added new (optional) parameter "time" to search end point.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
5 -- License-Filename: LICENSES/AGPL-3.0.txt
a6e5bb85f0a7 Added new (optional) parameter "time" to search end point.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
6
a6e5bb85f0a7 Added new (optional) parameter "time" to search end point.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
7 -- Copyright (C) 2018,2019,2020 by via donau
a6e5bb85f0a7 Added new (optional) parameter "time" to search end point.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
8 -- – Österreichische Wasserstraßen-Gesellschaft mbH
a6e5bb85f0a7 Added new (optional) parameter "time" to search end point.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
9 -- Software engineering by Intevation GmbH
a6e5bb85f0a7 Added new (optional) parameter "time" to search end point.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
10
a6e5bb85f0a7 Added new (optional) parameter "time" to search end point.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
11 -- Author(s):
a6e5bb85f0a7 Added new (optional) parameter "time" to search end point.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
12 -- * Sascha Wilde <wilde@intevation.de>
a6e5bb85f0a7 Added new (optional) parameter "time" to search end point.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
13
a6e5bb85f0a7 Added new (optional) parameter "time" to search end point.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
14 -- This update adds the new argument "search_time" to
a6e5bb85f0a7 Added new (optional) parameter "time" to search end point.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
15 -- search_bottlenecks() and the meta function search_most().
a6e5bb85f0a7 Added new (optional) parameter "time" to search end point.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
16
a6e5bb85f0a7 Added new (optional) parameter "time" to search end point.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
17 DROP FUNCTION search_bottlenecks(text);
a6e5bb85f0a7 Added new (optional) parameter "time" to search end point.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
18 CREATE OR REPLACE FUNCTION search_bottlenecks(
a6e5bb85f0a7 Added new (optional) parameter "time" to search end point.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
19 search_string text,
a6e5bb85f0a7 Added new (optional) parameter "time" to search end point.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
20 search_time timestamp with time zone)
a6e5bb85f0a7 Added new (optional) parameter "time" to search end point.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
21 RETURNS jsonb
a6e5bb85f0a7 Added new (optional) parameter "time" to search end point.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
22 LANGUAGE plpgsql STABLE PARALLEL SAFE
a6e5bb85f0a7 Added new (optional) parameter "time" to search end point.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
23 AS $$
a6e5bb85f0a7 Added new (optional) parameter "time" to search end point.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
24 DECLARE
a6e5bb85f0a7 Added new (optional) parameter "time" to search end point.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
25 _result jsonb;
a6e5bb85f0a7 Added new (optional) parameter "time" to search end point.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
26 BEGIN
a6e5bb85f0a7 Added new (optional) parameter "time" to search end point.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
27 SELECT COALESCE(json_agg(r),'[]')
a6e5bb85f0a7 Added new (optional) parameter "time" to search end point.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
28 INTO _result
a6e5bb85f0a7 Added new (optional) parameter "time" to search end point.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
29 FROM (SELECT objnam AS name,
a6e5bb85f0a7 Added new (optional) parameter "time" to search end point.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
30 ST_AsGeoJSON(ST_Envelope(area::geometry))::json AS geom,
a6e5bb85f0a7 Added new (optional) parameter "time" to search end point.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
31 'bottleneck' AS type,
a6e5bb85f0a7 Added new (optional) parameter "time" to search end point.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
32 bottleneck_id AS location
a6e5bb85f0a7 Added new (optional) parameter "time" to search end point.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
33 FROM waterway.bottlenecks
a6e5bb85f0a7 Added new (optional) parameter "time" to search end point.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
34 WHERE (objnam ILIKE '%' || search_string || '%'
a6e5bb85f0a7 Added new (optional) parameter "time" to search end point.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
35 OR bottleneck_id ILIKE '%' || search_string || '%')
a6e5bb85f0a7 Added new (optional) parameter "time" to search end point.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
36 AND validity @> search_time
a6e5bb85f0a7 Added new (optional) parameter "time" to search end point.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
37 ORDER BY name) r;
a6e5bb85f0a7 Added new (optional) parameter "time" to search end point.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
38 RETURN _result;
a6e5bb85f0a7 Added new (optional) parameter "time" to search end point.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
39 END;
a6e5bb85f0a7 Added new (optional) parameter "time" to search end point.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
40 $$;
a6e5bb85f0a7 Added new (optional) parameter "time" to search end point.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
41
a6e5bb85f0a7 Added new (optional) parameter "time" to search end point.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
42 DROP FUNCTION search_most(text);
a6e5bb85f0a7 Added new (optional) parameter "time" to search end point.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
43 CREATE OR REPLACE FUNCTION search_most(
a6e5bb85f0a7 Added new (optional) parameter "time" to search end point.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
44 search_string text,
a6e5bb85f0a7 Added new (optional) parameter "time" to search end point.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
45 search_time timestamp with time zone)
a6e5bb85f0a7 Added new (optional) parameter "time" to search end point.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
46 RETURNS jsonb
a6e5bb85f0a7 Added new (optional) parameter "time" to search end point.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
47 LANGUAGE plpgsql STABLE PARALLEL SAFE
a6e5bb85f0a7 Added new (optional) parameter "time" to search end point.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
48 AS $$
a6e5bb85f0a7 Added new (optional) parameter "time" to search end point.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
49 BEGIN
a6e5bb85f0a7 Added new (optional) parameter "time" to search end point.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
50 RETURN search_hectometre(search_string)
a6e5bb85f0a7 Added new (optional) parameter "time" to search end point.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
51 || search_bottlenecks(search_string,
a6e5bb85f0a7 Added new (optional) parameter "time" to search end point.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
52 COALESCE(search_time, current_timestamp))
a6e5bb85f0a7 Added new (optional) parameter "time" to search end point.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
53 || search_gauges(search_string)
a6e5bb85f0a7 Added new (optional) parameter "time" to search end point.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
54 || search_sections(search_string)
a6e5bb85f0a7 Added new (optional) parameter "time" to search end point.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
55 || search_stretches(search_string)
a6e5bb85f0a7 Added new (optional) parameter "time" to search end point.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
56 || search_cities(search_string);
a6e5bb85f0a7 Added new (optional) parameter "time" to search end point.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
57 END;
a6e5bb85f0a7 Added new (optional) parameter "time" to search end point.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
58 $$;