# HG changeset patch # User Sascha L. Teichmann # Date 1561373699 -7200 # Node ID fcac7787ec22b536d81af160ea6e91e167e39bf3 # Parent 879c297c47e950b8ebea6a358bb564d50c447767# Parent 71de5ce5a402e77feebab1cb700460ff5f3c3f00 Merged default into concave-hull branch. diff -r 879c297c47e9 -r fcac7787ec22 pkg/controllers/search.go --- a/pkg/controllers/search.go Mon Jun 24 12:54:25 2019 +0200 +++ b/pkg/controllers/search.go Mon Jun 24 12:54:59 2019 +0200 @@ -18,20 +18,12 @@ "database/sql" "net/http" "regexp" - "strconv" "strings" "gemma.intevation.de/gemma/pkg/models" ) const ( - searchHectometreSQL = `SELECT COALESCE(json_agg(r),'[]') -FROM (SELECT (location_code).hectometre || ' rhm' AS name, - replace(concat(location_code), ',','') AS locationcode, - ST_AsGeoJSON(geom)::json AS geom, 'rhm' AS type - FROM waterway.distance_marks_virtual - WHERE (location_code).hectometre = $1) r` - searchMostSQL = `SELECT search_most($1)::text` listBottlenecksSQL = ` @@ -66,37 +58,11 @@ } var result string - - m := rkmRegex.FindStringSubmatch(s.SearchString) - if len(m) != 0 { - // Handle search for river kilometre: - var hectometre int - if hectometre, err = strconv.Atoi(m[1]); err != nil { - return - } - - hectometre *= 10 - if m[3] != "" { - var h int - if h, err = strconv.Atoi(m[3]); err != nil { - return - } - hectometre += h - } - - err = db.QueryRowContext( - req.Context(), - searchHectometreSQL, - hectometre, - ).Scan(&result) - } else { - // Hande search for bottlencks: - err = db.QueryRowContext( - req.Context(), - searchMostSQL, - s.SearchString, - ).Scan(&result) - } + err = db.QueryRowContext( + req.Context(), + searchMostSQL, + s.SearchString, + ).Scan(&result) if err != nil { return diff -r 879c297c47e9 -r fcac7787ec22 schema/isrs.sql --- a/schema/isrs.sql Mon Jun 24 12:54:25 2019 +0200 +++ b/schema/isrs.sql Mon Jun 24 12:54:59 2019 +0200 @@ -154,6 +154,7 @@ DECLARE isrs_len CONSTANT int = 20; BEGIN + isrs_text = upper(isrs_text); IF char_length(isrs_text) <> isrs_len THEN RAISE 'ISRS location code must be % characters long', isrs_len diff -r 879c297c47e9 -r fcac7787ec22 schema/isrs_functions.sql --- a/schema/isrs_functions.sql Mon Jun 24 12:54:25 2019 +0200 +++ b/schema/isrs_functions.sql Mon Jun 24 12:54:59 2019 +0200 @@ -210,3 +210,16 @@ $$ LANGUAGE plpgsql STABLE PARALLEL SAFE; + + +-- Check if a given string looks like an ISRS code +CREATE OR REPLACE FUNCTION is_ISRSstring(str text) RETURNS boolean +AS $$ +BEGIN + str = upper(str); + RETURN (SELECT str SIMILAR TO '[A-Z]{2}[A-Z0-9]{13}[0-9]{5}') + AND is_country(substring(str from 1 for 2)); +END; + $$ + LANGUAGE plpgsql + IMMUTABLE PARALLEL SAFE; diff -r 879c297c47e9 -r fcac7787ec22 schema/search_functions.sql --- a/schema/search_functions.sql Mon Jun 24 12:54:25 2019 +0200 +++ b/schema/search_functions.sql Mon Jun 24 12:54:59 2019 +0200 @@ -4,15 +4,44 @@ -- SPDX-License-Identifier: AGPL-3.0-or-later -- License-Filename: LICENSES/AGPL-3.0.txt --- Copyright (C) 2018 by via donau +-- Copyright (C) 2018,2019 by via donau -- – Österreichische Wasserstraßen-Gesellschaft mbH -- Software engineering by Intevation GmbH -- Author(s): -- * Sascha Wilde +CREATE OR REPLACE FUNCTION search_hectometre(search_string text) RETURNS jsonb + LANGUAGE plpgsql STABLE PARALLEL SAFE + AS $$ +DECLARE + _result jsonb; +BEGIN + IF search_string SIMILAR TO '[0-9]+' THEN + SELECT COALESCE(json_agg(r),'[]') + INTO _result + FROM (SELECT (location_code).hectometre || ' rhm' AS name, + isrs_asText(location_code) AS locationcode, + ST_AsGeoJSON(geom)::json AS geom, 'rhm' AS type + FROM waterway.distance_marks_virtual + WHERE (location_code).hectometre = search_string::int) r; + ELSIF is_ISRSstring(search_string) THEN + SELECT COALESCE(json_agg(r),'[]') + INTO _result + FROM (SELECT (location_code).hectometre || ' rhm' AS name, + isrs_asText(location_code) AS locationcode, + ST_AsGeoJSON(geom)::json AS geom, 'rhm' AS type + FROM waterway.distance_marks_virtual + WHERE location_code = isrs_fromText(search_string)) r; + ELSE + _result='[]'; + END IF; + RETURN _result; +END; +$$; + CREATE OR REPLACE FUNCTION search_bottlenecks(search_string text) RETURNS jsonb - LANGUAGE plpgsql + LANGUAGE plpgsql STABLE PARALLEL SAFE AS $$ DECLARE _result jsonb; @@ -20,7 +49,7 @@ SELECT COALESCE(json_agg(r),'[]') INTO _result FROM (SELECT objnam AS name, - ST_AsGeoJSON(ST_Centroid(area))::json AS geom, + ST_AsGeoJSON(ST_Envelope(area::geometry))::json AS geom, 'bottleneck' AS type FROM waterway.bottlenecks WHERE objnam ILIKE '%' || search_string || '%' @@ -30,7 +59,7 @@ $$; CREATE OR REPLACE FUNCTION search_cities(search_string text) RETURNS jsonb - LANGUAGE plpgsql + LANGUAGE plpgsql STABLE PARALLEL SAFE AS $$ DECLARE _result jsonb; @@ -52,25 +81,38 @@ $$; CREATE OR REPLACE FUNCTION search_gauges(search_string text) RETURNS jsonb - LANGUAGE plpgsql + LANGUAGE plpgsql STABLE PARALLEL SAFE AS $$ DECLARE _result jsonb; BEGIN - SELECT COALESCE(json_agg(r),'[]') - INTO _result - FROM (SELECT objname AS name, - ST_AsGeoJSON(geom)::json AS geom, - 'gauge' AS type - FROM waterway.gauges - WHERE objname ILIKE '%' || search_string || '%' - ORDER BY name) r; + IF is_ISRSstring(search_string) THEN + SELECT COALESCE(json_agg(r),'[]') + INTO _result + FROM ( + SELECT objname AS name, + ST_AsGeoJSON(geom)::json AS geom, + 'gauge' AS type + FROM waterway.gauges + WHERE NOT erased AND location = isrs_fromText(search_string) + ORDER BY name) r; + ELSE + SELECT COALESCE(json_agg(r),'[]') + INTO _result + FROM ( + SELECT objname AS name, + ST_AsGeoJSON(geom)::json AS geom, + 'gauge' AS type + FROM waterway.gauges + WHERE NOT erased AND objname ILIKE '%' || search_string || '%' + ORDER BY name) r; + END IF; RETURN _result; END; $$; CREATE OR REPLACE FUNCTION search_sections(search_string text) RETURNS jsonb - LANGUAGE plpgsql + LANGUAGE plpgsql STABLE PARALLEL SAFE AS $$ DECLARE _result jsonb; @@ -78,7 +120,7 @@ SELECT COALESCE(json_agg(r),'[]') INTO _result FROM (SELECT objnam AS name, - ST_AsGeoJSON(ST_Centroid(area))::json AS geom, + ST_AsGeoJSON(ST_Envelope(area::geometry))::json AS geom, 'section' AS type FROM waterway.sections WHERE objnam ILIKE '%' || search_string || '%' @@ -88,13 +130,34 @@ END; $$; +CREATE OR REPLACE FUNCTION search_stretches(search_string text) RETURNS jsonb + LANGUAGE plpgsql STABLE PARALLEL SAFE + AS $$ +DECLARE + _result jsonb; +BEGIN + SELECT COALESCE(json_agg(r),'[]') + INTO _result + FROM (SELECT objnam AS name, + ST_AsGeoJSON(ST_Envelope(area::geometry))::json AS geom, + 'stretch' AS type + FROM waterway.stretches + WHERE objnam ILIKE '%' || search_string || '%' + OR nobjnam ILIKE '%' || search_string || '%' + ORDER BY name) r; + RETURN _result; +END; +$$; + CREATE OR REPLACE FUNCTION search_most(search_string text) RETURNS jsonb - LANGUAGE plpgsql + LANGUAGE plpgsql STABLE PARALLEL SAFE AS $$ BEGIN - RETURN search_bottlenecks(search_string) + RETURN search_hectometre(search_string) + || search_bottlenecks(search_string) || search_gauges(search_string) || search_sections(search_string) + || search_stretches(search_string) || search_cities(search_string); END; $$;