Mercurial > gemma
comparison schema/search.sql @ 1109:74a75a5ce770
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 $$; |