move transliteration of housenumbers into indexing

Housenumbers are now saved in transliterated form in the housenumber
column. This saves the transliteration step during lookup.

diff --git a/lib-php/SearchDescription.php b/lib-php/SearchDescription.php
index 2b39443f6e79f1b86b1571520ac0c1d4369cf6bc..dd20550214325b952452ec98de3bf4a96351071e 100644 (file)
--- a/lib-php/SearchDescription.php
+++ b/lib-php/SearchDescription.php
@@ -621,7 +621,7 @@ class SearchDescription
             $aOrder[0] .= '  SELECT place_id';
             $aOrder[0] .= '  FROM placex';
             $aOrder[0] .= '  WHERE parent_place_id = search_name.place_id';
             $aOrder[0] .= '  SELECT place_id';
             $aOrder[0] .= '  FROM placex';
             $aOrder[0] .= '  WHERE parent_place_id = search_name.place_id';

-            $aOrder[0] .= "    AND transliteration(housenumber) ~* E'".$sHouseNumberRegex."'";
+            $aOrder[0] .= "    AND housenumber ~* E'".$sHouseNumberRegex."'";

             $aOrder[0] .= '  LIMIT 1';
             $aOrder[0] .= ') ';
             // also housenumbers from interpolation lines table are needed
             $aOrder[0] .= '  LIMIT 1';
             $aOrder[0] .= ') ';
             // also housenumbers from interpolation lines table are needed


@@ -751,7 +751,7 @@ class SearchDescription
         $sHouseNumberRegex = '\\\\m'.$this->sHouseNumber.'\\\\M';
         $sSQL = 'SELECT place_id FROM placex ';
         $sSQL .= 'WHERE parent_place_id in ('.$sPlaceIDs.')';
         $sHouseNumberRegex = '\\\\m'.$this->sHouseNumber.'\\\\M';
         $sSQL = 'SELECT place_id FROM placex ';
         $sSQL .= 'WHERE parent_place_id in ('.$sPlaceIDs.')';

-        $sSQL .= "  AND transliteration(housenumber) ~* E'".$sHouseNumberRegex."'";
+        $sSQL .= "  AND housenumber ~* E'".$sHouseNumberRegex."'";

         $sSQL .= $this->oContext->excludeSQL(' AND place_id');
 
         Debug::printSQL($sSQL);
         $sSQL .= $this->oContext->excludeSQL(' AND place_id');
 
         Debug::printSQL($sSQL);

diff --git a/lib-sql/functions/placex_triggers.sql b/lib-sql/functions/placex_triggers.sql
index 086ba9300f8145d317b7e915894aa4c514f9864d..6b334ef01fe273f54ad778f153d8263378ad8f5b 100644 (file)
--- a/lib-sql/functions/placex_triggers.sql
+++ b/lib-sql/functions/placex_triggers.sql
@@ -680,6 +680,7 @@ BEGIN
         NEW.housenumber := NEW.address->'housenumber';
         i := getorcreate_housenumber_id(make_standard_name(NEW.housenumber));
       END IF;
         NEW.housenumber := NEW.address->'housenumber';
         i := getorcreate_housenumber_id(make_standard_name(NEW.housenumber));
       END IF;

+      NEW.housenumber := transliteration(NEW.housenumber);

 
       addr_street := NEW.address->'street';
       addr_place := NEW.address->'place';
 
       addr_street := NEW.address->'street';
       addr_place := NEW.address->'place';

diff --git a/test/bdd/db/query/normalization.feature b/test/bdd/db/query/normalization.feature
index 5215d2f9bb104cccd1c504f5034ce3fbce11efdd..8a324a229b86bfaa450a35d5d095bed647d483d3 100644 (file)
--- a/test/bdd/db/query/normalization.feature
+++ b/test/bdd/db/query/normalization.feature
@@ -164,12 +164,42 @@ Feature: Import and search of names
          | N        | 1      | <nr>, Main St |
 
     Examples:
          | N        | 1      | <nr>, Main St |
 
     Examples:

-      | nr |
-      | 1  |
-      | 3456 |
-      | 1 a |
-      | 56b |
-      | 1 A |
-      | 2號 |
-      | 1Б  |
-      | 1 к1 |
+        | nr |
+        | 1  |
+        | 3456 |
+        | 1 a |
+        | 56b |
+        | 1 A |
+        | 2號 |
+        | 1Б  |
+        | 1 к1 |
+        | 23-123 |
+
+    Scenario Outline: Housenumbers in lists are found
+        Given the grid
+            | 1 |  |   |  | 2 |
+            |   |  | 9 |  |   |
+        And the places
+            | osm | class   | type    | name    | geometry |
+            | W1  | highway | primary | Main St | 1,2      |
+        And the places
+            | osm | class    | type | housenr   | geometry |
+            | N1  | building | yes  | <nr-list> | 9        |
+        When importing
+        And searching for "Main St <nr>"
+        Then results contain
+         | osm_type | osm_id | name |
+         | N        | 1      | <nr-list>, Main St |
+
+    Examples:
+        | nr-list    | nr |
+        | 1,2,3      | 1  |
+        | 1,2,3      | 2  |
+        | 1, 2, 3    | 3  |
+        | 45 ;67;3   | 45 |
+        | 45 ;67;3   | 67 |
+        | 1a;1k      | 1a |
+        | 1a;1k      | 1k |
+        | 34/678     | 34 |
+        | 34/678     | 678 |
+        | 34/678     | 34/678 |