Mercurial > gemma

annotate schema/search.sql @ 1293:bcfd038b97a2

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
stagingarea in new layout
author Thomas Junk <thomas.junk@intevation.de>
date Thu, 22 Nov 2018 15:05:48 +0100
parents 74a75a5ce770
children 6590208e3ee1
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
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 $$;