From 1e2b95fb6c47ae3920bb233390a40afbf2004a8a Mon Sep 17 00:00:00 2001
From: Mark Hofstetter <mark@hofstetter.at>
Date: Thu, 11 Apr 2024 09:14:06 +0000
Subject: [PATCH] Bug 31652: Add geo-search
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit

This patch adds geosearch to Koha (using Elasticsearch 7). ElasticSearch
search_mappings get new types to store lat/lon, which can be indexed
from MARC 034$s and 034$t. There is a small change to the DB to allow a
new value in search_field.type ENUM.

The QueryBuilder is extended to allow for building advanced
ElasticSearch Querys (eg geo_distance) that cannot be represented in a
simple string query. The UI for searching (including showing the results
on a OSM/Leaflet map) is implemented in a separate plugin
(https://github.com/HKS3/HKS3GeoSearch)

Test Plan:

* make sure you're running ElasticSearch 7
  (eg via `curl http://es:9200?pretty | grep number`)
* apply patch
* Set system preference "SaerchEngine" to "Elasticsearch"
* got to a Framework, check Editor for 034$s and 034$t and save
* got to some books (in the correct framework) and enter some lat and lon into 034$s and 034$t (for example lat=48.216, lon=16.395)
* Run the elasticsearch indexer, maybe limited on the books you edited (-bn 123 -bn 456):
  misc/search_tools/rebuild_elasticsearch.pl -b -v
* You can check if the indexing worked by inspecting the document in elasticsearch:
  * get the biblionumber (eg 123)
  * curl http://es:9200/koha_kohadev_biblios/_doc/123?pretty | grep -A5 geolocation
  * You should get back a JSON fragment containing the lat/lon you stored
* You can query elasticsearch directly:
  * Run the following curl command, but adapt the value for lat/lng and/or the distance (in meters)
  * curl -X GET "http://es:9200/koha_kohadev_biblios/_search?pretty" -H 'Content-Type: application/json' -d '{"query": {"bool":{"must":{"match_all":{}},"filter":{"geo_distance":{"distance":100000,"geolocation":{"lat":48.2,"lon":16.4}}}}}}'
* To run the search via Koha, you need to either install and use https://github.com/HKS3/HKS3GeoSearch or create a handcrafted query string:
  * handcrafted query string:
    * /cgi-bin/koha/opac-search.pl?advsearch=1&idx=geolocation&q=lat:48.25+lng:18.35+distance:100km&do=Search
  * HKS3GeoSearch
    * install the plugin and enable it
    * got to OPAC / Advanced Search
    * There is a new input box "Geographic Search" where you can enter lat/long/radius
    * On the search result page a map is shown with pins for each found biblioitem

Sponsored-by: ZAMG - Zentralanstalt für Meterologie und Geodynamik, Austria - https://www.zamg.ac.at/
Sponsored-by: Geosphere - https://www.geosphere.at/
---
 Koha/SearchEngine/Elasticsearch.pm            | 22 ++++++++
 .../Elasticsearch/QueryBuilder.pm             | 51 ++++++++++++++++++-
 Koha/SearchEngine/Elasticsearch/Search.pm     |  5 ++
 .../elasticsearch/field_config.yaml           |  2 +
 .../searchengine/elasticsearch/mappings.yaml  | 18 +++++++
 .../atomicupdate/bug_31652_add_geo_search.pl  | 23 +++++++++
 installer/data/mysql/kohastructure.sql        |  6 +--
 .../modules/admin/preferences/searching.pref  |  7 +++
 .../searchengine/elasticsearch/mappings.tt    |  5 ++
 .../SearchEngine/Elasticsearch/QueryBuilder.t | 39 +++++++++++++-
 10 files changed, 173 insertions(+), 5 deletions(-)
 create mode 100755 installer/data/mysql/atomicupdate/bug_31652_add_geo_search.pl

diff --git a/Koha/SearchEngine/Elasticsearch.pm b/Koha/SearchEngine/Elasticsearch.pm
index 3f26cbc027..54f3ea8f72 100644
--- a/Koha/SearchEngine/Elasticsearch.pm
+++ b/Koha/SearchEngine/Elasticsearch.pm
@@ -212,6 +212,12 @@ sub get_elasticsearch_mappings {
                     $es_type = 'year';
                 } elsif ($type eq 'callnumber') {
                     $es_type = 'cn_sort';
+                } elsif ($type eq 'geo_point') {
+                    $es_type = 'geo_point';
+                }
+
+                if ($type eq 'geo_point') {
+                    $name =~ s/_(lat|lon)$//;
                 }
 
                 if ($search) {
@@ -735,6 +741,19 @@ sub marc_records_to_documents {
             }
         }
 
+        foreach my $field (@{$rules->{geo_point}}) {
+            next unless $record_document->{$field};
+            my $geofield = $field;
+            $geofield =~ s/_(lat|lon)$//;
+            my $axis = $1;
+            my $vals = $record_document->{$field};
+            for my $i (0 .. @$vals - 1) {
+                my $val = $record_document->{$field}[$i];
+                $record_document->{$geofield}[$i]{$axis} = $val;
+            }
+            delete $record_document->{$field};
+        }
+
         # Remove duplicate values and collapse sort fields
         foreach my $field (keys %{$record_document}) {
             if (ref($record_document->{$field}) eq 'ARRAY') {
@@ -1070,6 +1089,9 @@ sub _get_marc_mapping_rules {
         elsif ($type eq 'isbn') {
             push @{$rules->{isbn}}, $name;
         }
+        elsif ($type eq 'geo_point') {
+            push @{$rules->{geo_point}}, $name;
+        }
         elsif ($type eq 'boolean') {
             # boolean gets special handling, if value doesn't exist for a field,
             # it is set to false
diff --git a/Koha/SearchEngine/Elasticsearch/QueryBuilder.pm b/Koha/SearchEngine/Elasticsearch/QueryBuilder.pm
index 35ab4b7f91..be88164b91 100644
--- a/Koha/SearchEngine/Elasticsearch/QueryBuilder.pm
+++ b/Koha/SearchEngine/Elasticsearch/QueryBuilder.pm
@@ -137,6 +137,7 @@ our %index_field_convert = (
 );
 my $field_name_pattern = '[\w\-]+';
 my $multi_field_pattern = "(?:\\.$field_name_pattern)*";
+my $es_advanced_searches = [];
 
 =head2 get_index_field_convert
 
@@ -245,6 +246,8 @@ sub build_query {
         or $display_library_facets eq 'holding' ) {
         $res->{aggregations}{holdingbranch} = { terms => { field => "holdingbranch__facet", size => $size } };
     }
+
+    $res = _rebuild_to_es_advanced_query($res) if @$es_advanced_searches ;
     return $res;
 }
 
@@ -929,6 +932,17 @@ operand.
 
 sub _create_query_string {
     my ( $self, @queries ) = @_;
+    $es_advanced_searches = [];
+    my @string_queries;
+    foreach my $q (@queries) {
+        if ($q->{field} && $q->{field} eq 'geolocation') {
+            push(@$es_advanced_searches, $q);
+        } else {
+            push(@string_queries, $q)
+        }
+    }
+    
+    @queries = @string_queries;
 
     map {
         my $otor  = $_->{operator} ? $_->{operator} . ' ' : '';
@@ -1082,7 +1096,6 @@ sub _fix_limit_special_cases {
 
     my @new_lim;
     foreach my $l (@$limits) {
-
         # This is set up by opac-search.pl
         if ( $l =~ /^yr,st-numeric,ge[=:]/ ) {
             my ( $start, $end ) =
@@ -1338,4 +1351,40 @@ sub _search_fields {
     }
 }
 
+sub _rebuild_to_es_advanced_query {
+    my ($res) = @_;
+    my $query_string = $res->{query}->{query_string};
+    $query_string->{query} = '*' unless $query_string->{query};
+    delete $res->{query}->{query_string};
+
+    my %filter;
+    for my $advanced_query (@$es_advanced_searches) {
+        if ( $advanced_query->{field} eq 'geolocation') {
+            my ($lat, $lon, $distance) = map { $_ =~ /:(.*)\*/ } split('\s+', $advanced_query->{operand});
+            $filter{geo_distance} = {
+                distance => $distance,
+                geolocation => {
+                    lat => $lat,
+                    lon => $lon,
+                }
+            };
+        }
+        else {
+            warn "unknown advanced ElasticSearch query: ".join(', ',%$advanced_query);
+        }
+    }
+
+    $res->{query} = {
+        bool => {
+             must => {
+                 query_string =>  $query_string
+             },
+             filter => \%filter,
+        }
+    };
+
+    return $res;
+}
+
+
 1;
diff --git a/Koha/SearchEngine/Elasticsearch/Search.pm b/Koha/SearchEngine/Elasticsearch/Search.pm
index 933b52f79d..4918ed824d 100644
--- a/Koha/SearchEngine/Elasticsearch/Search.pm
+++ b/Koha/SearchEngine/Elasticsearch/Search.pm
@@ -91,6 +91,11 @@ sub search {
         $query->{from} = $page * $query->{size};
     }
     my $elasticsearch = $self->get_elasticsearch();
+
+    # FixMe: investigate where empty query_string is coming from
+    delete $query->{query}->{query_string} if
+      $query->{query}->{query_string} && !%{$query->{query}->{query_string}};
+
     my $results = eval {
         $elasticsearch->search(
             index => $self->index_name,
diff --git a/admin/searchengine/elasticsearch/field_config.yaml b/admin/searchengine/elasticsearch/field_config.yaml
index c87cbcae8d..41adcef7ed 100644
--- a/admin/searchengine/elasticsearch/field_config.yaml
+++ b/admin/searchengine/elasticsearch/field_config.yaml
@@ -41,6 +41,8 @@ search:
       ci_raw:
         type: keyword
         normalizer: icu_folding_normalizer
+  geo_point:
+     type: geo_point
   default:
     type: text
     analyzer: analyzer_standard
diff --git a/admin/searchengine/elasticsearch/mappings.yaml b/admin/searchengine/elasticsearch/mappings.yaml
index f9f9a042c5..6ae0ca5291 100644
--- a/admin/searchengine/elasticsearch/mappings.yaml
+++ b/admin/searchengine/elasticsearch/mappings.yaml
@@ -1855,6 +1855,24 @@ biblios:
     opac: 1
     staff_client: 1
     type: ''
+  geolocation_lat:
+    label: geolocation_lat
+    mappings:
+      - facet: ''
+        marc_field: 034s
+        marc_type: marc21
+        sort: 0
+        suggestible: ''
+    type: geo_point
+  geolocation_lon:
+    label: geolocation_lon
+    mappings:
+      - facet: ''
+        marc_field: 034t
+        marc_type: marc21
+        sort: 0
+        suggestible: ''
+    type: geo_point
   holdingbranch:
     facet_order: 8
     label: holdinglibrary
diff --git a/installer/data/mysql/atomicupdate/bug_31652_add_geo_search.pl b/installer/data/mysql/atomicupdate/bug_31652_add_geo_search.pl
new file mode 100755
index 0000000000..4a6c9ea3e3
--- /dev/null
+++ b/installer/data/mysql/atomicupdate/bug_31652_add_geo_search.pl
@@ -0,0 +1,23 @@
+use Modern::Perl;
+
+return {
+    bug_number  => "31652",
+    description => "Add geo-search: new value for search_field.type enum",
+    up          => sub {
+        my ($args) = @_;
+        my ( $dbh, $out ) = @$args{qw(dbh out)};
+
+        # Do you stuffs here
+        $dbh->do(
+            q{ alter table search_field MODIFY COLUMN type enum('','string','date','number','boolean','sum','isbn','stdno','year','callnumber','geo_point') }
+        );
+
+        # Print useful stuff here
+        say $out "Added new value 'geo_point' to search_field.type enum";
+        $dbh->do(
+            q{INSERT IGNORE INTO systempreferences ( 'variable', 'value', 'options', 'explanation', 'type' ) VALUES ('GeoSearchEnabled', '0', NULL, 'Enable GeoSearch Feature via Elasticsearch', 'YesNo')
+        say $out "Added new system preference 'GeoSearchEnabled'";
+12      }
+        );
+    },
+};
diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql
index 16310f0f44..69bddee6bc 100644
--- a/installer/data/mysql/kohastructure.sql
+++ b/installer/data/mysql/kohastructure.sql
@@ -5603,9 +5603,9 @@ DROP TABLE IF EXISTS `search_field`;
 /*!40101 SET character_set_client = utf8 */;
 CREATE TABLE `search_field` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
-  `name` varchar(255) NOT NULL COMMENT 'the name of the field as it will be stored in the search engine',
-  `label` varchar(255) NOT NULL COMMENT 'the human readable name of the field, for display',
-  `type` enum('','string','date','number','boolean','sum','isbn','stdno','year','callnumber') NOT NULL COMMENT 'what type of data this holds, relevant when storing it in the search engine',
+  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'the name of the field as it will be stored in the search engine',
+  `label` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'the human readable name of the field, for display',
+  `type` enum('','string','date','number','boolean','sum','isbn','stdno','year','callnumber','geo_point') COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'what type of data this holds, relevant when storing it in the search engine',
   `weight` decimal(5,2) DEFAULT NULL,
   `facet_order` tinyint(4) DEFAULT NULL COMMENT 'the order place of the field in facet list if faceted',
   `staff_client` tinyint(1) NOT NULL DEFAULT 1,
diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/admin/preferences/searching.pref b/koha-tmpl/intranet-tmpl/prog/en/modules/admin/preferences/searching.pref
index 86b9f54ae1..fa8edcc2c3 100644
--- a/koha-tmpl/intranet-tmpl/prog/en/modules/admin/preferences/searching.pref
+++ b/koha-tmpl/intranet-tmpl/prog/en/modules/admin/preferences/searching.pref
@@ -94,6 +94,13 @@ Searching:
                   1: Enable
                   0: Disable
             - "the option for staff with permission to create/edit custom saved search filters."
+        -
+            - pref: GeoSearchEnabled
+              type: boolean
+              choices:
+                  1: Enable
+                  0: Disable
+            - 'GeoSearch via Elasticsearch'
     Search form:
         -
             - pref: LoadSearchHistoryToTheFirstLoggedUser
diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/admin/searchengine/elasticsearch/mappings.tt b/koha-tmpl/intranet-tmpl/prog/en/modules/admin/searchengine/elasticsearch/mappings.tt
index 625afa4998..aa76b3b65a 100644
--- a/koha-tmpl/intranet-tmpl/prog/en/modules/admin/searchengine/elasticsearch/mappings.tt
+++ b/koha-tmpl/intranet-tmpl/prog/en/modules/admin/searchengine/elasticsearch/mappings.tt
@@ -222,6 +222,11 @@ a.add, a.delete {
                                             [% ELSE %]
                                                 <option value="callnumber">Call Number</option>
                                             [% END %]
+                                            [% IF search_field.type == "geo_point" %]
+                                              <option value="geo_point" selected="selected">Geo Point</option>
+                                            [% ELSE %]
+                                              <option value="geo_point">Geo Point</option>
+                                            [% END %]
                                         </select>
                                     </td>
                                         <td data-order="[% search_field.weight | html %]">
diff --git a/t/Koha/SearchEngine/Elasticsearch/QueryBuilder.t b/t/Koha/SearchEngine/Elasticsearch/QueryBuilder.t
index a507b2e065..89086a6e1b 100755
--- a/t/Koha/SearchEngine/Elasticsearch/QueryBuilder.t
+++ b/t/Koha/SearchEngine/Elasticsearch/QueryBuilder.t
@@ -17,7 +17,7 @@
 
 use Modern::Perl;
 
-use Test::More tests => 6;
+use Test::More tests => 7;
 use t::lib::Mocks;
 
 use_ok('Koha::SearchEngine::Elasticsearch::QueryBuilder');
@@ -303,4 +303,41 @@ subtest '_join_queries' => sub {
     is($query, '(homebranch:foo) AND itype:(BOOK OR EBOOK) AND location:(SHELF)', 'should join "mc-" parts with AND if not the same field');
 };
 
+subtest '_create_query_string' => sub {
+    plan tests => 2;
+
+    my $params = {
+        index => $Koha::SearchEngine::Elasticsearch::BIBLIOS_INDEX,
+    };
+    my $qb = Koha::SearchEngine::Elasticsearch::QueryBuilder->new($params);
+
+    my @queries;
+    my $normal_query = [
+        {
+            'operand'  => 'perl*',
+            'operator' => undef
+        }
+    ];
+
+    @queries = $qb->_create_query_string(@$normal_query);
+    my $expect = ['(perl*)'];
+
+    is( @queries, @$expect, 'expected search structure' );
+
+    my $geo_query = [
+        {
+            'operator' => undef,
+            'field'    => 'geolocation',
+            'type'     => undef,
+            'operand'  => 'lat:48.25* lng:16.35* distance:100km*'
+        }
+    ];
+
+    @queries = $qb->_create_query_string(@$geo_query);
+    my $expect_geo = [];
+    is( @queries, @$expect_geo, 'expected geo search structure => empty normal search string' );
+
+};
+
+
 1;
-- 
2.39.2