Mercurial > gemma
changeset 3734:1504856c9378
Moved rhm search to db.
This unifies the approach to feature search. Now everything is
searched via the search_most() PLPGSQL function.
author | Sascha Wilde <wilde@intevation.de> |
---|---|
date | Mon, 24 Jun 2019 11:46:20 +0200 |
parents | b1fe61b58048 |
children | 96230eadf96e |
files | pkg/controllers/search.go schema/search_functions.sql |
diffstat | 2 files changed, 29 insertions(+), 41 deletions(-) [+] |
line wrap: on
line diff
--- a/pkg/controllers/search.go Mon Jun 24 11:22:16 2019 +0200 +++ b/pkg/controllers/search.go Mon Jun 24 11:46:20 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
--- a/schema/search_functions.sql Mon Jun 24 11:22:16 2019 +0200 +++ b/schema/search_functions.sql Mon Jun 24 11:46:20 2019 +0200 @@ -4,13 +4,34 @@ -- 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 <wilde@intevation.de> +CREATE OR REPLACE FUNCTION search_hectometre(search_string text) RETURNS jsonb + LANGUAGE plpgsql + 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, + replace(concat(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; + ELSE + _result='[]'; + END IF; + RETURN _result; +END; +$$; + CREATE OR REPLACE FUNCTION search_bottlenecks(search_string text) RETURNS jsonb LANGUAGE plpgsql AS $$ @@ -105,7 +126,8 @@ LANGUAGE plpgsql 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_cities(search_string);