Mercurial > gemma

comparison schema/updates/3.0.1/02.search_functions.sql @ 3969:7015fb18d536

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Moved db migrations to 3.0.1 to a new folder under updates. Replace the fragile symbolic link with a copy.
author Sascha L. Teichmann <sascha.teichmann@intevation.de>
date Wed, 17 Jul 2019 10:29:02 +0200
parents schema/search_functions.sql@33180c25bd87
children
comparison
equal deleted inserted replaced
3962:f68529ff47a4 3969:7015fb18d536
1 -- This is Free Software under GNU Affero General Public License v >= 3.0
2 -- without warranty, see README.md and license for details.
3
4 -- SPDX-License-Identifier: AGPL-3.0-or-later
5 -- License-Filename: LICENSES/AGPL-3.0.txt
6
7 -- Copyright (C) 2018,2019 by via donau
8 -- – Österreichische Wasserstraßen-Gesellschaft mbH
9 -- Software engineering by Intevation GmbH
10
11 -- Author(s):
12 -- * Sascha Wilde <wilde@intevation.de>
13
14 CREATE OR REPLACE FUNCTION search_hectometre(search_string text) RETURNS jsonb
15 LANGUAGE plpgsql STABLE PARALLEL SAFE
16 AS $$
17 DECLARE
18 _result jsonb;
19 BEGIN
20 IF search_string SIMILAR TO '[0-9]+' THEN
21 SELECT COALESCE(json_agg(r),'[]')
22 INTO _result
23 FROM (SELECT (location_code).hectometre || ' rhm' AS name,
24 isrs_asText(location_code) AS locationcode,
25 wwname AS location,
26 ST_AsGeoJSON(geom)::json AS geom, 'rhm' AS type
27 FROM waterway.distance_marks_virtual
28 WHERE (location_code).hectometre = search_string::int) r;
29 ELSIF is_ISRSstring(search_string) THEN
30 SELECT COALESCE(json_agg(r),'[]')
31 INTO _result
32 FROM (SELECT (location_code).hectometre || ' rhm' AS name,
33 isrs_asText(location_code) AS locationcode,
34 wwname AS location,
35 ST_AsGeoJSON(geom)::json AS geom, 'rhm' AS type
36 FROM waterway.distance_marks_virtual
37 WHERE location_code = isrs_fromText(search_string)) r;
38 ELSE
39 _result='[]';
40 END IF;
41 RETURN _result;
42 END;
43 $$;
44
45 CREATE OR REPLACE FUNCTION search_bottlenecks(search_string text) RETURNS jsonb
46 LANGUAGE plpgsql STABLE PARALLEL SAFE
47 AS $$
48 DECLARE
49 _result jsonb;
50 BEGIN
51 SELECT COALESCE(json_agg(r),'[]')
52 INTO _result
53 FROM (SELECT objnam AS name,
54 ST_AsGeoJSON(ST_Envelope(area::geometry))::json AS geom,
55 'bottleneck' AS type
56 FROM waterway.bottlenecks
57 WHERE objnam ILIKE '%' || search_string || '%'
58 ORDER BY name) r;
59 RETURN _result;
60 END;
61 $$;
62
63 CREATE OR REPLACE FUNCTION search_cities(search_string text) RETURNS jsonb
64 LANGUAGE plpgsql STABLE PARALLEL SAFE
65 AS $$
66 DECLARE
67 _result jsonb;
68 BEGIN
69 SELECT COALESCE(json_agg(r),'[]')
70 INTO _result
71 FROM (SELECT name,
72 country_code AS location,
73 ST_AsGeoJSON(location)::json AS geom,
74 'city' AS type
75 FROM waterway.geonames
76 WHERE feature_code IN ('PPLA', 'PPLA1', 'PPLA2', 'PPLA3', 'PPLC')
77 AND (name ILIKE '%' || search_string || '%'
78 OR alternatenames ~* ('(^|,)' || search_string || '($|,)'))
79 ORDER BY array_position(ARRAY['PPLC', 'PPLA', 'PPLA1', 'PPLA2', 'PPLA3'],
80 feature_code::text),
81 name) r;
82 RETURN _result;
83 END;
84 $$;
85
86 CREATE OR REPLACE FUNCTION search_gauges(search_string text) RETURNS jsonb
87 LANGUAGE plpgsql STABLE PARALLEL SAFE
88 AS $$
89 DECLARE
90 _result jsonb;
91 BEGIN
92 IF is_ISRSstring(search_string) THEN
93 SELECT COALESCE(json_agg(r),'[]')
94 INTO _result
95 FROM (
96 SELECT objname AS name,
97 ST_AsGeoJSON(geom)::json AS geom,
98 'gauge' AS type,
99 isrs_asText(location) AS locationcode
100 FROM waterway.gauges
101 WHERE NOT erased AND location = isrs_fromText(search_string)
102 ORDER BY name) r;
103 ELSE
104 SELECT COALESCE(json_agg(r),'[]')
105 INTO _result
106 FROM (
107 SELECT objname AS name,
108 ST_AsGeoJSON(geom)::json AS geom,
109 'gauge' AS type,
110 isrs_asText(location) AS locationcode
111 FROM waterway.gauges
112 WHERE NOT erased AND objname ILIKE '%' || search_string || '%'
113 ORDER BY name) r;
114 END IF;
115 RETURN _result;
116 END;
117 $$;
118
119 CREATE OR REPLACE FUNCTION search_sections(search_string text) RETURNS jsonb
120 LANGUAGE plpgsql STABLE PARALLEL SAFE
121 AS $$
122 DECLARE
123 _result jsonb;
124 BEGIN
125 SELECT COALESCE(json_agg(r),'[]')
126 INTO _result
127 FROM (SELECT id,
128 objnam AS name,
129 ST_AsGeoJSON(ST_Envelope(area::geometry))::json AS geom,
130 'section' AS type
131 FROM waterway.sections
132 WHERE objnam ILIKE '%' || search_string || '%'
133 OR nobjnam ILIKE '%' || search_string || '%'
134 ORDER BY name) r;
135 RETURN _result;
136 END;
137 $$;
138
139 CREATE OR REPLACE FUNCTION search_stretches(search_string text) RETURNS jsonb
140 LANGUAGE plpgsql STABLE PARALLEL SAFE
141 AS $$
142 DECLARE
143 _result jsonb;
144 BEGIN
145 SELECT COALESCE(json_agg(r),'[]')
146 INTO _result
147 FROM (SELECT id,
148 objnam AS name,
149 ST_AsGeoJSON(ST_Envelope(area::geometry))::json AS geom,
150 'stretch' AS type
151 FROM waterway.stretches
152 WHERE objnam ILIKE '%' || search_string || '%'
153 OR nobjnam ILIKE '%' || search_string || '%'
154 ORDER BY name) r;
155 RETURN _result;
156 END;
157 $$;
158
159 CREATE OR REPLACE FUNCTION search_most(search_string text) RETURNS jsonb
160 LANGUAGE plpgsql STABLE PARALLEL SAFE
161 AS $$
162 BEGIN
163 RETURN search_hectometre(search_string)
164 || search_bottlenecks(search_string)
165 || search_gauges(search_string)
166 || search_sections(search_string)
167 || search_stretches(search_string)
168 || search_cities(search_string);
169 END;
170 $$;