Mercurial > gemma
annotate schema/search.sql @ 1234:1a5564655f2a
refac: Sidebar reorganized
In order to make context switches between administrative tasks
which are map related and those which are system related, we now have
a category "administration" and "systemadministration".
The Riverbedmorphology does nothing than display the map, so it is
renamed to that (map). In case the context of "systemadministration"
is chosen, the "map" brings you just back to the map.
author | Thomas Junk <thomas.junk@intevation.de> |
---|---|
date | Tue, 20 Nov 2018 09:54:53 +0100 |
parents | 74a75a5ce770 |
children | 6590208e3ee1 |
rev | line source |
---|---|
1109
74a75a5ce770
Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
1 CREATE FUNCTION search_bottlenecks(search_string text) RETURNS jsonb |
74a75a5ce770
Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
2 LANGUAGE plpgsql |
74a75a5ce770
Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
3 AS $$ |
74a75a5ce770
Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
4 DECLARE |
74a75a5ce770
Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
5 _result jsonb; |
74a75a5ce770
Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
6 BEGIN |
74a75a5ce770
Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
7 SELECT COALESCE(json_agg(r),'[]') |
74a75a5ce770
Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
8 INTO _result |
74a75a5ce770
Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
9 FROM (SELECT objnam AS name, |
74a75a5ce770
Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
10 ST_AsGeoJSON(ST_Centroid(area))::json AS geom, |
74a75a5ce770
Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
11 'bottleneck' AS type |
74a75a5ce770
Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
12 FROM waterway.bottlenecks |
74a75a5ce770
Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
13 WHERE objnam ILIKE '%' || search_string || '%' |
74a75a5ce770
Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
14 ORDER BY name) r; |
74a75a5ce770
Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
15 RETURN _result; |
74a75a5ce770
Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
16 END; |
74a75a5ce770
Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
17 $$; |
74a75a5ce770
Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
18 |
74a75a5ce770
Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
19 CREATE FUNCTION search_cities(search_string text) RETURNS jsonb |
74a75a5ce770
Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
20 LANGUAGE plpgsql |
74a75a5ce770
Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
21 AS $$ |
74a75a5ce770
Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
22 DECLARE |
74a75a5ce770
Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
23 _result jsonb; |
74a75a5ce770
Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
24 BEGIN |
74a75a5ce770
Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
25 SELECT COALESCE(json_agg(r),'[]') |
74a75a5ce770
Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
26 INTO _result |
74a75a5ce770
Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
27 FROM (SELECT name || ' (' || country_code || ')' AS name, |
74a75a5ce770
Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
28 ST_AsGeoJSON(location)::json AS geom, |
74a75a5ce770
Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
29 'city' AS type |
74a75a5ce770
Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
30 FROM waterway.geonames |
74a75a5ce770
Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
31 WHERE feature_code IN ('PPLA', 'PPLA1', 'PPLA2', 'PPLA3', 'PPLC') |
74a75a5ce770
Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
32 AND (name ILIKE '%' || search_string || '%' |
74a75a5ce770
Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
33 OR alternatenames ~* ('(^|,)' || search_string || '($|,)')) |
74a75a5ce770
Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
34 ORDER BY array_position(ARRAY['PPLC', 'PPLA', 'PPLA1', 'PPLA2', 'PPLA3'], |
74a75a5ce770
Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
35 feature_code::text), |
74a75a5ce770
Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
36 name) r; |
74a75a5ce770
Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
37 RETURN _result; |
74a75a5ce770
Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
38 END; |
74a75a5ce770
Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
39 $$; |
74a75a5ce770
Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
40 |
74a75a5ce770
Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
41 CREATE FUNCTION search_most(search_string text) RETURNS jsonb |
74a75a5ce770
Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
42 LANGUAGE plpgsql |
74a75a5ce770
Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
43 AS $$ |
74a75a5ce770
Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
44 BEGIN |
74a75a5ce770
Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
45 RETURN search_bottlenecks(search_string) || search_cities(search_string); |
74a75a5ce770
Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
46 END; |
74a75a5ce770
Added geonames data and extended search for villages/cities.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
47 $$; |