From 68791dd1c3392f9f82ae31075b9ef00a04f1514a Mon Sep 17 00:00:00 2001 From: Mark Hofstetter Date: Thu, 4 Aug 2022 10:00:02 +0200 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/ This is a combination of 17 commits. --- Koha/Schema/Result/SearchField.pm | 7 +-- Koha/SearchEngine/Elasticsearch.pm | 22 +++++++++ Koha/SearchEngine/Elasticsearch/Indexer.pm | 2 +- .../Elasticsearch/QueryBuilder.pm | 46 ++++++++++++++++++- Koha/SearchEngine/Elasticsearch/Search.pm | 5 ++ .../elasticsearch/field_config.yaml | 2 + .../searchengine/elasticsearch/mappings.yaml | 18 ++++++++ .../atomicupdate/bug_31652_add_geo_search.pl | 14 ++++++ installer/data/mysql/kohastructure.sql | 2 +- .../searchengine/elasticsearch/mappings.tt | 5 ++ opac/opac-search.pl | 21 +++++++-- 11 files changed, 135 insertions(+), 9 deletions(-) create mode 100755 installer/data/mysql/atomicupdate/bug_31652_add_geo_search.pl diff --git a/Koha/Schema/Result/SearchField.pm b/Koha/Schema/Result/SearchField.pm index 6cc906831e..55b0572828 100644 --- a/Koha/Schema/Result/SearchField.pm +++ b/Koha/Schema/Result/SearchField.pm @@ -48,7 +48,7 @@ the human readable name of the field, for display =head2 type data_type: 'enum' - extra: {list => ["","string","date","number","boolean","sum","isbn","stdno","year","callnumber"]} + extra: {list => ["","string","date","number","boolean","sum","isbn","stdno","year","callnumber","geo_point"]} is_nullable: 0 what type of data this holds, relevant when storing it in the search engine @@ -109,6 +109,7 @@ __PACKAGE__->add_columns( "stdno", "year", "callnumber", + "geo_point", ], }, is_nullable => 0, @@ -169,8 +170,8 @@ __PACKAGE__->has_many( ); -# Created by DBIx::Class::Schema::Loader v0.07049 @ 2022-07-18 15:10:43 -# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:Uk5JsfPJo0XVvGfMfJg3cg +# Created by DBIx::Class::Schema::Loader v0.07049 @ 2022-09-29 12:18:11 +# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:xtsGkd3Gjnqw8ZDmoeRDzQ __PACKAGE__->add_columns( '+mandatory' => { is_boolean => 1 }, diff --git a/Koha/SearchEngine/Elasticsearch.pm b/Koha/SearchEngine/Elasticsearch.pm index 1fe6fb1c6f..4dcc412585 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) { @@ -729,6 +735,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') { @@ -1052,6 +1071,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/Indexer.pm b/Koha/SearchEngine/Elasticsearch/Indexer.pm index 6101a3a372..7c16a5dcd4 100644 --- a/Koha/SearchEngine/Elasticsearch/Indexer.pm +++ b/Koha/SearchEngine/Elasticsearch/Indexer.pm @@ -117,6 +117,7 @@ sub update_index { } my $documents = $self->marc_records_to_documents($records); + my @body; for (my $i = 0; $i < scalar @$index_record_ids; $i++) { my $id = $index_record_ids->[$i]; @@ -266,7 +267,6 @@ sub update_mappings { my ($self) = @_; my $elasticsearch = $self->get_elasticsearch(); my $mappings = $self->get_elasticsearch_mappings(); - try { my $response = $elasticsearch->indices->put_mapping( index => $self->index_name, diff --git a/Koha/SearchEngine/Elasticsearch/QueryBuilder.pm b/Koha/SearchEngine/Elasticsearch/QueryBuilder.pm index 1791670382..0b2e4294c7 100644 --- a/Koha/SearchEngine/Elasticsearch/QueryBuilder.pm +++ b/Koha/SearchEngine/Elasticsearch/QueryBuilder.pm @@ -136,6 +136,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 @@ -244,6 +245,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; } @@ -908,6 +911,12 @@ operand. sub _create_query_string { my ( $self, @queries ) = @_; + foreach my $q (@queries) { + if ($q->{field} && $q->{field} eq 'geolocation') { + push(@$es_advanced_searches, $q); + } + } + @queries = grep { $_->{field} ne 'geolocation' } @queries; map { my $otor = $_->{operator} ? $_->{operator} . ' ' : ''; @@ -1061,7 +1070,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 ) = @@ -1291,4 +1299,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 0a1bc7ccc9..db3a52ff25 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(); + + # XXX 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 cf90482aa2..8bf93ccdbb 100644 --- a/admin/searchengine/elasticsearch/field_config.yaml +++ b/admin/searchengine/elasticsearch/field_config.yaml @@ -38,6 +38,8 @@ search: search_analyzer: analyzer_phrase raw: type: keyword + 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 33f36852eb..21d5974717 100644 --- a/admin/searchengine/elasticsearch/mappings.yaml +++ b/admin/searchengine/elasticsearch/mappings.yaml @@ -1555,6 +1555,24 @@ biblios: sort: ~ suggestible: '' 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..fcc9ae82e4 --- /dev/null +++ b/installer/data/mysql/atomicupdate/bug_31652_add_geo_search.pl @@ -0,0 +1,14 @@ +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"; + }, +}; diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index 7025f9bbf9..165c0f6d8b 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -4709,7 +4709,7 @@ CREATE TABLE `search_field` ( `id` int(11) NOT NULL AUTO_INCREMENT, `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') COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'what type of data this holds, relevant when storing it in the search engine', + `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/searchengine/elasticsearch/mappings.tt b/koha-tmpl/intranet-tmpl/prog/en/modules/admin/searchengine/elasticsearch/mappings.tt index c120a76ad1..0d5014111e 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 @@ -224,6 +224,11 @@ a.add, a.delete { [% ELSE %] [% END %] + [% IF search_field.type == "geo_point" %] + + [% ELSE %] + + [% END %] diff --git a/opac/opac-search.pl b/opac/opac-search.pl index c0f7ca5d8c..e96dadfda4 100755 --- a/opac/opac-search.pl +++ b/opac/opac-search.pl @@ -106,7 +106,9 @@ my $format = $cgi->param("format") || ''; if ($format =~ /(rss|atom|opensearchdescription)/) { $template_name = 'opac-opensearch.tt'; } -elsif ((@params>=1) || (defined $cgi->param("q") && $cgi->param("q") ne "") || ($cgi->param('multibranchlimit')) || ($cgi->param('limit-yr')) || @searchCategories ) { +elsif ((@params>=1) || (defined $cgi->param("q") && $cgi->param("q") ne "") || + ($cgi->param('multibranchlimit')) || ($cgi->param('limit-yr')) || @searchCategories + || $cgi->param('lat') ){ $template_name = 'opac-results.tt'; } else { @@ -402,8 +404,22 @@ my @operators = $cgi->multi_param('op'); # indexes are query qualifiers, like 'title', 'author', etc. They # can be single or multiple parameters separated by comma: kw,right-Truncation + +if ($params->{'lat'} && $params->{'lng'} && $params->{'distance'} ) { + $params->{q} = sprintf("lat:%s lng:%s distance:%s", + $params->{'lat'}, $params->{'lng'}, $params->{'distance'}); + + $params->{idx} = 'geolocation'; + delete $params->{'lat'}; + delete $params->{'lng'}; + delete $params->{'radius'}; +} + + + my @indexes = $cgi->multi_param('idx'); -@indexes = map { uri_unescape($_) } @indexes; +@indexes = grep { $_ } @indexes; +@indexes = map { uri_unescape($_) } @indexes; # if a simple index (only one) display the index used in the top search box if ($indexes[0] && !$indexes[1]) { @@ -457,7 +473,6 @@ foreach my $limit(@limits) { } $template->param(available => $available); -# append year limits if they exist if ($params->{'limit-yr'}) { if ($params->{'limit-yr'} =~ /\d{4}/) { push @limits, "yr,st-numeric=$params->{'limit-yr'}"; -- 2.25.1