Mercurial > gemma
view schema/search.sql @ 1191:b23622905a3f
switched entirely to sass instead of scss
for cleaner code/less lines, just removed all ; and {}
author | Markus Kottlaender <markus@intevation.de> |
---|---|
date | Fri, 16 Nov 2018 14:37:07 +0100 |
parents | 74a75a5ce770 |
children | 6590208e3ee1 |
line wrap: on
line source
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; $$;