Mercurial > gemma
annotate schema/search.sql @ 1141:a473d91b0856
merge
author | Markus Kottlaender <markus@intevation.de> |
---|---|
date | Mon, 12 Nov 2018 14:48:18 +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 $$; |