Mercurial > gemma

changeset 3743:fcac7787ec22 concave-hull

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Merged default into concave-hull branch.
author Sascha L. Teichmann <sascha.teichmann@intevation.de>
date Mon, 24 Jun 2019 12:54:59 +0200
parents 879c297c47e9 (current diff) 71de5ce5a402 (diff)
children 16c3c0150030
files
diffstat 4 files changed, 99 insertions(+), 56 deletions(-) [+]
line wrap: on
line diff
--- 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
--- 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
--- 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;
--- 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 <wilde@intevation.de>
 
+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;
 $$;