Mercurial > gemma
comparison schema/updates/0301/02.search_functions.sql @ 3970:32e9ae5210f9
Moved 3.0.1 db migration to 0301 to have a four digit pattern.
author | Sascha L. Teichmann <sascha.teichmann@intevation.de> |
---|---|
date | Wed, 17 Jul 2019 10:32:48 +0200 |
parents | schema/updates/3.0.1/02.search_functions.sql@7015fb18d536 |
children |
comparison
equal
deleted
inserted
replaced
3969:7015fb18d536 | 3970:32e9ae5210f9 |
---|---|
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 $$; |