Mercurial > gemma

comparison schema/search.sql @ 1109:74a75a5ce770

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Added geonames data and extended search for villages/cities. The free data from geonames https://download.geonames.org/ is imported into the gemma data base (for the relevant states) and used to search for cities and villages. This data might be replaced by data from the RIS-index later on..?
author Sascha Wilde <wilde@intevation.de>
date Mon, 05 Nov 2018 13:07:16 +0100
parents
children 6590208e3ee1
comparison
equal deleted inserted replaced
1108:5adee8b0e29f 1109:74a75a5ce770
1 CREATE FUNCTION search_bottlenecks(search_string text) RETURNS jsonb
2 LANGUAGE plpgsql
3 AS $$
4 DECLARE
5 _result jsonb;
6 BEGIN
7 SELECT COALESCE(json_agg(r),'[]')
8 INTO _result
9 FROM (SELECT objnam AS name,
10 ST_AsGeoJSON(ST_Centroid(area))::json AS geom,
11 'bottleneck' AS type
12 FROM waterway.bottlenecks
13 WHERE objnam ILIKE '%' || search_string || '%'
14 ORDER BY name) r;
15 RETURN _result;
16 END;
17 $$;
18
19 CREATE FUNCTION search_cities(search_string text) RETURNS jsonb
20 LANGUAGE plpgsql
21 AS $$
22 DECLARE
23 _result jsonb;
24 BEGIN
25 SELECT COALESCE(json_agg(r),'[]')
26 INTO _result
27 FROM (SELECT name || ' (' || country_code || ')' AS name,
28 ST_AsGeoJSON(location)::json AS geom,
29 'city' AS type
30 FROM waterway.geonames
31 WHERE feature_code IN ('PPLA', 'PPLA1', 'PPLA2', 'PPLA3', 'PPLC')
32 AND (name ILIKE '%' || search_string || '%'
33 OR alternatenames ~* ('(^|,)' || search_string || '($|,)'))
34 ORDER BY array_position(ARRAY['PPLC', 'PPLA', 'PPLA1', 'PPLA2', 'PPLA3'],
35 feature_code::text),
36 name) r;
37 RETURN _result;
38 END;
39 $$;
40
41 CREATE FUNCTION search_most(search_string text) RETURNS jsonb
42 LANGUAGE plpgsql
43 AS $$
44 BEGIN
45 RETURN search_bottlenecks(search_string) || search_cities(search_string);
46 END;
47 $$;