From 62984cd847c2c5469f2dc2067f6f667b94f14e70 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. We added a new SystemPreference `GeoSearchEnabled`. If it is set to `true`, we show a new advanced search field that allows for input of lat/lng and distance (radius). On the search result page, a we render a map using leaflet.js with pins for each found item. Therefor we also added a new api endpoint that returns lat/lng for each biblio in the selection. Test Plan: * make sure you're running ElasticSearch 7 (eg via `curl http://es:9200?pretty | grep number`) * apply patch * Set system preference "SearchEngine" 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: * Set system preference "GeoSearchEnabled" to "true" * got to OPAC / Advanced Search * There is a new input box "Geographic Search" where you can enter lat/long/radius, for example lat=48.2, lng=16.3, distance=100km * 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/ Authors: Mark Hofstetter , Thomas Klausner --- Koha/REST/V1/Biblios/Geo.pm | 60 +++++++++++++++++++ Koha/Schema/Result/SearchField.pm | 7 ++- Koha/SearchEngine/Elasticsearch.pm | 22 +++++++ .../Elasticsearch/QueryBuilder.pm | 46 +++++++++++++- Koha/SearchEngine/Elasticsearch/Search.pm | 5 ++ .../elasticsearch/field_config.yaml | 2 + .../searchengine/elasticsearch/mappings.yaml | 18 ++++++ api/v1/swagger/paths/biblios_geosearch.yaml | 49 +++++++++++++++ api/v1/swagger/swagger.yaml | 2 + .../atomicupdate/bug_31652_add_geo_search.pl | 17 ++++++ installer/data/mysql/kohastructure.sql | 6 +- installer/data/mysql/mandatory/sysprefs.sql | 1 + .../modules/admin/preferences/searching.pref | 7 +++ .../searchengine/elasticsearch/mappings.tt | 5 ++ .../opac-tmpl/bootstrap/css/src/opac.scss | 4 ++ .../bootstrap/en/modules/opac-results.tt | 8 +++ .../bootstrap/js/geosearchdisplaymap.js | 39 ++++++++++++ opac/opac-search.pl | 21 ++++++- 18 files changed, 309 insertions(+), 10 deletions(-) create mode 100644 Koha/REST/V1/Biblios/Geo.pm create mode 100644 api/v1/swagger/paths/biblios_geosearch.yaml create mode 100755 installer/data/mysql/atomicupdate/bug_31652_add_geo_search.pl create mode 100644 koha-tmpl/opac-tmpl/bootstrap/js/geosearchdisplaymap.js diff --git a/Koha/REST/V1/Biblios/Geo.pm b/Koha/REST/V1/Biblios/Geo.pm new file mode 100644 index 0000000000..5343eb3fc0 --- /dev/null +++ b/Koha/REST/V1/Biblios/Geo.pm @@ -0,0 +1,60 @@ +package Koha::REST::V1::Biblios::Geo; + +use utf8; +use Mojo::Base 'Mojolicious::Controller'; + +use C4::Context; +use C4::Biblio; +use C4::XSLT; + +use Koha::Biblios; +use Mojo::JSON qw(decode_json encode_json); +use Encode qw(encode_utf8); + +sub post { + my $c = shift->openapi->valid_input or return; + my $biblionumbers = $c->validation->every_param('biblionumber'); + +## my $dbh = C4::Context->dbh; +## +## my $sql= <<'SQL'; +## with search as ( +## select authid, +## ExtractValue(marcxml, '//datafield[@tag="035"]/subfield[@code="a"]') as idn, +## ExtractValue(marcxml, '//datafield[@tag="150"]/subfield[@code="a"]') as fieldvalue, +## gnd_id nid +## from auth_header where authtypecode = 'GENRE/FORM' and origincode = 'rda') +## select * from search where biblionumber in ? +## order by fieldvalue +## SQL +## +## my $query = $dbh->prepare($sql); +## $query->execute($searchterm); +## my $items = $query->fetchall_arrayref({}); + + return $c->render( status => 200, openapi => {data => $biblionumbers, info => 'test'} ); +} + + +sub biblio_coordinates { + my $c = shift->openapi->valid_input or return; + my $biblionumbers = $c->validation->every_param('bn'); + my $data = []; + my $i = 0; + for my $b (@$biblionumbers) { + my $d = {}; + my $marcxml = C4::Biblio::GetXmlBiblio( $b ); + my $record = MARC::Record->new_from_xml( $marcxml, 'UTF-8', 'MARC21' ); + if ( $record->field('034') ) { + $d->{coordinates} = [$record->field('034')->subfield("s"), $record->field('034')->subfield("t")]; + $d->{title} = sprintf("%s", + $b, $record->field('245')->subfield("a")); + push @$data, $d; + $i++; + } + } + return $c->render( status => 200, openapi => {data => $data, count => $i} ); +} + +1; + 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 94193f279f..30d2d6d574 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) { @@ -733,6 +739,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') { @@ -1056,6 +1075,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 e57d1d7b60..3f5c2daa89 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,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} . ' ' : ''; @@ -1082,7 +1091,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 ) = @@ -1335,4 +1343,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 e8e4412b56..7499c53b20 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 e0830506b6..a7ee306fc6 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/api/v1/swagger/paths/biblios_geosearch.yaml b/api/v1/swagger/paths/biblios_geosearch.yaml new file mode 100644 index 0000000000..59e8b24f70 --- /dev/null +++ b/api/v1/swagger/paths/biblios_geosearch.yaml @@ -0,0 +1,49 @@ +--- +/biblios/geo: + get: + x-mojo-to: Biblios::Geo#biblio_coordinates + operationId: getBiblioGeoCoordinates + tags: + - biblios + summary: Get biblio Geo (public) + parameters: + - name: bn + in: query + required: true + description: Embed list sent in path + type: array + produces: + - application/json + responses: + "200": + description: A biblio + "401": + description: Authentication required + schema: + $ref: "../swagger.yaml#/definitions/error" + "403": + description: Access forbidden + schema: + $ref: "../swagger.yaml#/definitions/error" + "404": + description: Biblio not found + schema: + $ref: "../swagger.yaml#/definitions/error" + "406": + description: Not acceptable + schema: + type: array + description: Accepted content-types + items: + type: string + "500": + description: | + Internal server error. Possible `error_code` attribute values: + + * `internal_server_error` + schema: + $ref: "../swagger.yaml#/definitions/error" + "503": + description: Under maintenance + schema: + $ref: "../swagger.yaml#/definitions/error" diff --git a/api/v1/swagger/swagger.yaml b/api/v1/swagger/swagger.yaml index 48d8b5af62..3038c57bb0 100644 --- a/api/v1/swagger/swagger.yaml +++ b/api/v1/swagger/swagger.yaml @@ -191,6 +191,8 @@ paths: $ref: "./paths/biblios_item_groups.yaml#/~1biblios~1{biblio_id}~1item_groups~1{item_group_id}~1items" "/biblios/{biblio_id}/item_groups/{item_group_id}/items/{item_id}": $ref: "./paths/biblios_item_groups.yaml#/~1biblios~1{biblio_id}~1item_groups~1{item_group_id}~1items~1{item_id}" + /public/biblios/geo: + $ref: ./paths/biblios_geosearch.yaml#/~1biblios~1geo "/cash_registers/{cash_register_id}/cashups": $ref: "./paths/cash_registers.yaml#/~1cash_registers~1{cash_register_id}~1cashups" "/cashups/{cashup_id}": 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..5acf2faed4 --- /dev/null +++ b/installer/data/mysql/atomicupdate/bug_31652_add_geo_search.pl @@ -0,0 +1,17 @@ +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 c1f800fe7f..f62ae42ec3 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -5161,9 +5161,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/installer/data/mysql/mandatory/sysprefs.sql b/installer/data/mysql/mandatory/sysprefs.sql index 30197591a8..f2ceec2e10 100644 --- a/installer/data/mysql/mandatory/sysprefs.sql +++ b/installer/data/mysql/mandatory/sysprefs.sql @@ -258,6 +258,7 @@ INSERT INTO systempreferences ( `variable`, `value`, `options`, `explanation`, ` ('FRBRizeEditions','0','','If ON, Koha will query one or more ISBN web services for associated ISBNs and display an Editions tab on the details pages','YesNo'), ('GenerateAuthorityField667', 'Machine generated authority record', NULL, 'When BiblioAddsAuthorities and AutoCreateAuthorities are enabled, use this as a default value for the 667$a field of MARC21 records', 'free'), ('GenerateAuthorityField670', 'Work cat.', NULL, 'When BiblioAddsAuthorities and AutoCreateAuthorities are enabled, use this as a default value for the 670$a field of MARC21 records', 'free'), +('GeoSearchEnabled', '0', NULL, 'Enable GeoSearch Feature via Elasticsearch', 'YesNo'), ('GoogleJackets','0',NULL,'if ON, displays jacket covers from Google Books API','YesNo'), ('GoogleOpenIDConnect', '0', NULL, 'if ON, allows the use of Google OpenID Connect for login', 'YesNo'), ('GoogleOAuth2ClientID', '', NULL, 'Client ID for the web app registered with Google', 'Free'), 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 93084ee641..44adf7290d 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 5a1cf263fc..bde5c5d985 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 @@ -217,6 +217,11 @@ a.add, a.delete { [% ELSE %] [% END %] + [% IF search_field.type == "geo_point" %] + + [% ELSE %] + + [% END %] diff --git a/koha-tmpl/opac-tmpl/bootstrap/css/src/opac.scss b/koha-tmpl/opac-tmpl/bootstrap/css/src/opac.scss index e48225df2e..b34581fe90 100644 --- a/koha-tmpl/opac-tmpl/bootstrap/css/src/opac.scss +++ b/koha-tmpl/opac-tmpl/bootstrap/css/src/opac.scss @@ -2724,6 +2724,10 @@ $star-selected: #EDB867; } } +#geo_search_map { + height: 400px; +} + @media print { .br-theme-fontawesome-stars { .br-widget { diff --git a/koha-tmpl/opac-tmpl/bootstrap/en/modules/opac-results.tt b/koha-tmpl/opac-tmpl/bootstrap/en/modules/opac-results.tt index 875bf71e2b..f42519feea 100644 --- a/koha-tmpl/opac-tmpl/bootstrap/en/modules/opac-results.tt +++ b/koha-tmpl/opac-tmpl/bootstrap/en/modules/opac-results.tt @@ -5,6 +5,7 @@ [% USE To %] [% SET TagsShowEnabled = ( ( Koha.Preference( 'TagsEnabled' ) == 1 ) && TagsShowOnList ) %] [% SET TagsInputEnabled = ( ( Koha.Preference( 'opacuserlogin' ) == 1 ) && ( Koha.Preference( 'TagsEnabled' ) == 1 ) && TagsInputOnList ) %] +[% SET GeoSearchEnabled = ( Koha.Preference( 'GeoSearchEnabled' ) == 1 ) %] [% SET CoverImagePlugins = KohaPlugins.get_plugins_opac_cover_images %] [% IF firstPage %] @@ -34,6 +35,7 @@ [% INCLUDE 'masthead.inc' %]
+ [% IF GeoSearchEnabled %]
[% END %]