Mercurial > gemma
diff 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 |
line wrap: on
line diff
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/schema/search.sql Mon Nov 05 13:07:16 2018 +0100 @@ -0,0 +1,47 @@ +CREATE FUNCTION search_bottlenecks(search_string text) RETURNS jsonb + LANGUAGE plpgsql + AS $$ +DECLARE + _result jsonb; +BEGIN + SELECT COALESCE(json_agg(r),'[]') + INTO _result + FROM (SELECT objnam AS name, + ST_AsGeoJSON(ST_Centroid(area))::json AS geom, + 'bottleneck' AS type + FROM waterway.bottlenecks + WHERE objnam ILIKE '%' || search_string || '%' + ORDER BY name) r; + RETURN _result; +END; +$$; + +CREATE FUNCTION search_cities(search_string text) RETURNS jsonb + LANGUAGE plpgsql + AS $$ +DECLARE + _result jsonb; +BEGIN + SELECT COALESCE(json_agg(r),'[]') + INTO _result + FROM (SELECT name || ' (' || country_code || ')' AS name, + ST_AsGeoJSON(location)::json AS geom, + 'city' AS type + FROM waterway.geonames + WHERE feature_code IN ('PPLA', 'PPLA1', 'PPLA2', 'PPLA3', 'PPLC') + AND (name ILIKE '%' || search_string || '%' + OR alternatenames ~* ('(^|,)' || search_string || '($|,)')) + ORDER BY array_position(ARRAY['PPLC', 'PPLA', 'PPLA1', 'PPLA2', 'PPLA3'], + feature_code::text), + name) r; + RETURN _result; +END; +$$; + +CREATE FUNCTION search_most(search_string text) RETURNS jsonb + LANGUAGE plpgsql + AS $$ +BEGIN + RETURN search_bottlenecks(search_string) || search_cities(search_string); +END; +$$;