Bug 31652 - Add geo-search
Summary: Add geo-search
Status: Passed QA
Alias: None
Product: Koha
Classification: Unclassified
Component: Searching - Elasticsearch (show other bugs)
Version: Main
Hardware: All All
: P5 - low new feature (vote)
Assignee: Mark Hofstetter
QA Contact:
URL:
Keywords: rel_24_05_candidate
Depends on: 36151
Blocks:
  Show dependency treegraph
 
Reported: 2022-09-29 11:38 UTC by Thomas Klausner
Modified: 2024-04-28 21:24 UTC (History)
10 users (show)

See Also:
Change sponsored?: Sponsored
Patch complexity: Medium patch
Documentation contact:
Documentation submission:
Text to go in the release notes:
Version(s) released in:


Attachments
Bug 31652: Add geo-search (16.34 KB, patch)
2022-10-06 11:39 UTC, Thomas Klausner
Details | Diff | Splinter Review
Bug 31652: Add geo-search (16.31 KB, patch)
2023-07-20 13:23 UTC, Thomas Klausner
Details | Diff | Splinter Review
Bug 31652: Add geo-search (26.92 KB, patch)
2023-08-11 19:17 UTC, Thomas Klausner
Details | Diff | Splinter Review
Bug 31652: Add geo-search (15.75 KB, patch)
2024-04-11 09:16 UTC, Mark Hofstetter
Details | Diff | Splinter Review
Bug 31652: Add geo-search (15.88 KB, patch)
2024-04-11 13:19 UTC, Nick Clemens
Details | Diff | Splinter Review
Bug 31652: (follow-up) Fix update and remove fixed fixme (2.72 KB, patch)
2024-04-11 13:19 UTC, Nick Clemens
Details | Diff | Splinter Review
Bug 31652: Add geo-search (14.22 KB, patch)
2024-04-27 16:49 UTC, Thomas Klausner
Details | Diff | Splinter Review
Bug 31652: Add geo-search (14.16 KB, patch)
2024-04-27 16:58 UTC, Thomas Klausner
Details | Diff | Splinter Review

Note You need to log in before you can comment on or make changes to this bug.
Description Thomas Klausner 2022-09-29 11:38:56 UTC
A customer wants to add a geo-search to Koha:

* Store lat/lng in biblio 034$s and 034$t
* Add lat/lng to ElasticSearch index as geo_point: https://www.elastic.co/guide/en/elasticsearch/reference/7.17/geo-point.html
* Add support for ElasticSearch geo-queries: https://www.elastic.co/guide/en/elasticsearch/reference/7.17/geo-queries.html
* Use Leaflet.js to render maps and pins

We (HKS3, koha-support.eu) have already implemented most of it. We will provide our changes as a patch (or a set of patches) and hope that they will be merged into the main Koha source code.

It will take a bit more time to actually provide the code, but I create this ticket now so we have an "anchor" if anybody wants to discuss this feature etc.

Greetings,
domm
Comment 1 Thomas Klausner 2022-10-06 11:39:40 UTC
Created attachment 141406 [details] [review]
Bug 31652: Add geo-search

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.
Comment 2 David Nind 2023-05-02 23:25:58 UTC
The patch no longer applies 8-(...

git bz apply 31652

Bug 31652 - Add geo-search

141406 - Bug 31652: Add geo-search

Apply? [(y)es, (n)o, (i)nteractive] y
Applying: Bug 31652: Add geo-search
.git/rebase-apply/patch:323: trailing whitespace.
# can be single or multiple parameters separated by comma: kw,right-Truncation 
warning: 1 line adds whitespace errors.
Using index info to reconstruct a base tree...
M       Koha/SearchEngine/Elasticsearch.pm
M       Koha/SearchEngine/Elasticsearch/QueryBuilder.pm
M       Koha/SearchEngine/Elasticsearch/Search.pm
M       admin/searchengine/elasticsearch/field_config.yaml
M       admin/searchengine/elasticsearch/mappings.yaml
M       installer/data/mysql/kohastructure.sql
M       koha-tmpl/intranet-tmpl/prog/en/modules/admin/searchengine/elasticsearch/mappings.tt
M       opac/opac-search.pl
.git/rebase-apply/patch:323: trailing whitespace.
# can be single or multiple parameters separated by comma: kw,right-Truncation 
warning: 1 line adds whitespace errors.
Falling back to patching base and 3-way merge...
Auto-merging opac/opac-search.pl
Auto-merging koha-tmpl/intranet-tmpl/prog/en/modules/admin/searchengine/elasticsearch/mappings.tt
CONFLICT (content): Merge conflict in koha-tmpl/intranet-tmpl/prog/en/modules/admin/searchengine/elasticsearch/mappings.tt
Auto-merging installer/data/mysql/kohastructure.sql
CONFLICT (content): Merge conflict in installer/data/mysql/kohastructure.sql
Auto-merging admin/searchengine/elasticsearch/mappings.yaml
Auto-merging admin/searchengine/elasticsearch/field_config.yaml
CONFLICT (content): Merge conflict in admin/searchengine/elasticsearch/field_config.yaml
Auto-merging Koha/SearchEngine/Elasticsearch/Search.pm
Auto-merging Koha/SearchEngine/Elasticsearch/QueryBuilder.pm
Auto-merging Koha/SearchEngine/Elasticsearch.pm
error: Failed to merge in the changes.
Patch failed at 0001 Bug 31652: Add geo-search
Comment 3 Thomas Klausner 2023-07-20 13:23:26 UTC
Created attachment 153720 [details] [review]
Bug 31652: Add geo-search

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.
Comment 4 Thomas Klausner 2023-07-20 13:25:18 UTC
I've updated the patch and it should apply now (again) on current master. Please try to signoff again, I'd love to have this in master before Perl Koha Conference (where I'll do a talk on this patch!)
Comment 5 David Cook 2023-07-20 23:47:01 UTC
Comment on attachment 153720 [details] [review]
Bug 31652: Add geo-search

Review of attachment 153720 [details] [review]:
-----------------------------------------------------------------

::: Koha/SearchEngine/Elasticsearch/Indexer.pm
@@ +117,4 @@
>      }
>  
>      my $documents = $self->marc_records_to_documents($records);
> +

It looks like this file might not be altered beyond whitespace? Looks like the whitespace change could be removed to simplify the overall patch.

::: Koha/SearchEngine/Elasticsearch/Search.pm
@@ +93,5 @@
>      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}};

Is this a pre-existing problem or introduced in this patch?
Comment 6 Mark Hofstetter 2023-08-08 07:39:45 UTC
This is/was a pre-existing problem
Comment 7 Thomas Klausner 2023-08-11 19:17:12 UTC
Created attachment 154388 [details] [review]
Bug 31652: Add geo-search

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 <mark@hofstetter.at>, Thomas Klausner <domm@plix.at>
Comment 8 Thomas Klausner 2023-08-11 19:40:30 UTC
The latest patch has some warnings/errors reported from koha-qa.pl. Plus I'm not totally happy with the way the search params are handled. So I guess it will need a bit more work.
Comment 9 Lucas Gass 2023-08-14 17:01:50 UTC
(In reply to Thomas Klausner from comment #8)
> The latest patch has some warnings/errors reported from koha-qa.pl. Plus I'm
> not totally happy with the way the search params are handled. So I guess it
> will need a bit more work.

Based on this I am setting this to FQA, to get it out of the 'Needs signoff' queue
Comment 10 Thomas Klausner 2023-08-21 14:36:34 UTC
Here's some old-ish work on improving ElasticSearch, which could be used/integrated here (as pointed out by Jonathan during PerlKohaCon)

https://github.com/fredericd/Koha/compare/master...newelastic
Comment 11 Nick Clemens 2023-08-22 15:21:50 UTC
(In reply to Thomas Klausner from comment #10)
> Here's some old-ish work on improving ElasticSearch, which could be
> used/integrated here (as pointed out by Jonathan during PerlKohaCon)
> 
> https://github.com/fredericd/Koha/compare/master...newelastic

This should go onto a new bug - if we want to change searching at large let's do that, then base this on top of that work
Comment 12 Fridolin Somers 2023-08-28 18:55:59 UTC
Is is necessary to use external server for static files ?

https://unpkg.com/leaflet@1.9.1/dist/leaflet.css
https://unpkg.com/leaflet@1.9.1/dist/leaflet.js

Can't we embed in sources ?
Comment 13 Mark Hofstetter 2023-08-31 07:36:30 UTC
I don't see a problem in embedding the files if that is "the prefered way of doing things"
Comment 14 Fridolin Somers 2023-08-31 22:21:48 UTC
Lets see what QA says
Comment 15 David Cook 2023-09-01 00:05:04 UTC
Comment on attachment 154388 [details] [review]
Bug 31652: Add geo-search

Review of attachment 154388 [details] [review]:
-----------------------------------------------------------------

::: koha-tmpl/opac-tmpl/bootstrap/en/modules/opac-results.tt
@@ +592,5 @@
>          [% Asset.js("js/openlibrary.js") | $raw %]
>      [% END %]
> +    [% IF ( GeoSearchEnabled ) %]
> +        <link rel="stylesheet" href="https://unpkg.com/leaflet@1.9.1/dist/leaflet.css" integrity="sha256-sA+zWATbFveLLNqWO2gtiw3HL/lh1giY/Inf1BJ0z14=" crossorigin=""/>
> +        <script src="https://unpkg.com/leaflet@1.9.1/dist/leaflet.js" integrity="sha256-NDI0K41gVbWqfkkaHj15IzU7PtMoelkzyKp8TOaFQ3s=" crossorigin=""></script>

As Frido suggested, we don't link out to externally hosted static files. Lots of reasons for that.

Koha already has leaflet.js and leaflet.css although a much older version: 1.0.3+ed36a04

I'd suggest opening a separate issue to upgrade Leaflet and then having that be a dependency of this issue.
Comment 16 Katrin Fischer 2023-09-10 22:55:44 UTC
I think we should definitely not link to external files but keep shipping our own. I believe updating the ones we ship would be a good first step here and hopefully not too hard. I think maybe they are used for Mana?
Comment 17 Mark Hofstetter 2024-04-09 11:02:54 UTC
git@github.com:HKS3/koha-branches.git

it's in the branch 

topic-es-2312

for those who want to see it, please send me your github id
Comment 18 Mark Hofstetter 2024-04-11 08:17:59 UTC
I now created a public git branch 

https://github.com/HKS3/koha-devel/tree/topic-es-2312

working on a cypress test
Comment 19 Mark Hofstetter 2024-04-11 09:16:25 UTC
Created attachment 164666 [details] [review]
Bug 31652: Add geo-search

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/
Comment 20 Nick Clemens 2024-04-11 13:19:00 UTC
Created attachment 164689 [details] [review]
Bug 31652: Add geo-search

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/
Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com>
Signed-off-by: Nick Clemens <nick@bywatersolutions.com>
Comment 21 Nick Clemens 2024-04-11 13:19:04 UTC
Created attachment 164690 [details] [review]
Bug 31652: (follow-up) Fix update and remove fixed fixme

Update test count as well

Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com>
Signed-off-by: Nick Clemens <nick@bywatersolutions.com>
Comment 22 Nick Clemens 2024-04-11 13:20:11 UTC
Worked with Mark and Martin to move this forward - this version simply adds the ability to do a geo-search - it would need work for an interface to make the searching exposed, but I think this will provide a useful base. Passing QA
Comment 23 Katrin Fischer 2024-04-26 16:23:06 UTC
Please check:

1) Are the changes to name / label in kohastructure intended?

   `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',

2) System preference GeoSearchEnabled

I believe this should be removed and due to rewrites? There is no other mention of this pref.

+++ b/koha-tmpl/intranet-tmpl/prog/en/modules/admin/preferences/searching.pref
@@ -99,6 +99,13 @@ Searching:
             - "List of search fields (separated by | or ,) that should not be autotruncated by Elasticsearch even if <a href='/cgi-bin/koha/admin/preferences.pl?op=search&searchfield=QueryAutoTruncate'>QueryAutoTruncate</a> is set to 'Yes':"
             - pref: ESPreventAutoTruncate
               class: long
+        -
+            - pref: GeoSearchEnabled
+              type: boolean
+              choices:
+                  1: Enable
+                  0: Disable
+            - 'GeoSearch via Elasticsearch'
     Search form:

3) Capitalization

Usually I just fix these sneakily, but so we don't forget with the other points:

+                                              <option value="geo_point" selected="selected">Geo Point</option>

We only capitalize the first word, so this should read: Geo Point (I know Call Number in the same spot is wrong as well, but no need to add more)


Please fix!
Comment 24 Thomas Klausner 2024-04-27 16:49:22 UTC
Created attachment 165707 [details] [review]
Bug 31652: Add geo-search

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/
Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com>
Signed-off-by: Nick Clemens <nick@bywatersolutions.com>

Additional finetuning:

- Fix update and remove fixed fixme
- Update test count as well
- fix last small issues raised in Comment 23
Comment 25 Thomas Klausner 2024-04-27 16:58:15 UTC
Created attachment 165708 [details] [review]
Bug 31652: Add geo-search

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
* 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/
Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com>
Signed-off-by: Nick Clemens <nick@bywatersolutions.com>

Additional finetuning:

- Fix update and remove fixed fixme
- Update test count as well
- fix last small issues raised in Comment 23
Comment 26 Katrin Fischer 2024-04-27 17:23:49 UTC
Please switch back to PQA when you are done!
Comment 27 Thomas Klausner 2024-04-27 20:00:11 UTC
I assume you mean status = "Passed QA"? I've now set it to this :-)
Comment 28 David Nind 2024-04-27 20:21:55 UTC
I thought I would have a go at testing, even though it has passed QA.

Unfortunately, the patches don't apply - sha1/fake ancestor error:

git bz apply 31652

Bug 31652 - Add geo-search

164690 - Bug 31652: (follow-up) Fix update and remove fixed fixme
165708 - Bug 31652: Add geo-search

Apply? [(y)es, (n)o, (i)nteractive] y
Applying: Bug 31652: (follow-up) Fix update and remove fixed fixme
error: sha1 information is lacking or useless (Koha/SearchEngine/Elasticsearch/Search.pm).
error: could not build fake ancestor
Patch failed at 0001 Bug 31652: (follow-up) Fix update and remove fixed fixme
Comment 29 Katrin Fischer 2024-04-28 08:51:09 UTC
I think the patch sequence is wrong now. I was going to do 2 git bz apply -i here to fix on applying.
Comment 30 David Nind 2024-04-28 09:52:36 UTC
(In reply to Katrin Fischer from comment #29)
> I think the patch sequence is wrong now. I was going to do 2 git bz apply -i
> here to fix on applying.

I tried that, but it didn't work for me 8-)
Comment 31 Thomas Klausner 2024-04-28 16:27:49 UTC
When creating the current patch, I did a rebase on the fresh main and squashed the three commits (original, "(follow-up)" and my fixes according to Katrins comment) into one commit.

So there should only be one patch:
  Bug 31652: Add geo-search (14.16 KB, patch)
  2024-04-27 16:58 UTC, Thomas Klausner 


Not sure how/if I can obsolte this one:
  Bug 31652: (follow-up) Fix update and remove fixed fixme (2.72 KB, patch)
  2024-04-11 13:19 UTC, Nick Clemens 

Or if it is possible (or how to do it) to tell git bz to only use my rebased/squashed patch
Comment 32 David Nind 2024-04-28 18:08:47 UTC
(In reply to Thomas Klausner from comment #31)
> When creating the current patch, I did a rebase on the fresh main and
> squashed the three commits (original, "(follow-up)" and my fixes according
> to Katrins comment) into one commit.
> 
> So there should only be one patch:
>   Bug 31652: Add geo-search (14.16 KB, patch)
>   2024-04-27 16:58 UTC, Thomas Klausner 
> 
> 
> Not sure how/if I can obsolte this one:
>   Bug 31652: (follow-up) Fix update and remove fixed fixme (2.72 KB, patch)
>   2024-04-11 13:19 UTC, Nick Clemens 
> 
> Or if it is possible (or how to do it) to tell git bz to only use my
> rebased/squashed patch

You can obsolete the patch from Bugzilla:
1. Under the attachments section, click the 'Details' link beside the patch to obsolete.
2. In the title/header for the patch, click 'Edit details' again.
3. Click the tick box beside 'obsolete'.
4. Add a comment.
5. Click 'Submit'.
Comment 33 Thomas Klausner 2024-04-28 19:38:37 UTC
I obsolted the old patch, now git bz works again:

  kohadev-koha@kohadevbox:koha(another_31652_try)$ git bz apply 31652

  Bug 31652 - Add geo-search

  165708 - Bug 31652: Add geo-search

  Apply? [(y)es, (n)o, (i)nteractive] y
  Applying: Bug 31652: Add geo-search


Please try again
Comment 34 David Nind 2024-04-28 21:24:30 UTC
Hi Thomas.

I can confirm the bug now applies!

I had a go at testing (not that this is required, as it has already passed QA). Things I noted:
- database update required after applying the patch
- also needed to reset the Elasticsearch mappings
- I didn't get any search results in the OPAC
- wasn't sure how to install the HKS3GeoSearch plugin 

Here are my testing notes (using KTD):
1. Start up KTD with Elasticsearch: ktd --es7 up
2. Apply the patch
3. Update the database: updatedatabase
4. Restart everything
5. Edit the books framework and make 034$s and 034$t visible in the editor
6. Add example coordinates for a couple of records (I added lat=48.216 and lon=16.395 to records 262 and 126)
7. Reset the Elasticsearch mappings (Administration > Catalog > Search engine configuration (Elasticsearch) > Reset mappings)
8. Rebuild the search index: koha-elasticsearch --rebuild -d -b -a kohadev
9. Check if indexing working: curl http://es:9200/koha_kohadev_biblios/_doc/123?pretty | grep -A5 geolocation
- The result I got:

curl http://es:9200/koha_kohadev_biblios/_doc/262?pretty | grep -A5 geolocation
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100 1078    "geolocation" : [ 0      0      0 --:--:-- --:--:-- --:--:--     0
3      {
         "lon" : "16.395",
         "lat" : "48.216"
1      }
0    ],
0 10783    0     0  2164k      0 --:--:-- --:--:-- --:--:-- 2632k

10. Query Elasticsearch directly:

- Result:
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}}}}}}'
{
....

A lot of information returned - basically the details for the two records that I added the coordinates to.
...
}

11. OPAC query:

http://127.0.0.1:8080/cgi-bin/koha/opac-search.pl?advsearch=1&idx=geolocation&q=lat:48.25+lng:18.35+distance:100km&do=Search
- Puts details in the search field: lat:48.25 lng:18.35 distance:100km
- Results displayed:

No results found!
No results found for that in catalog. 

12. HKS3GeoSearch Koha Plugin:
- No release available. Does this mean I have to clone the repository into the plugins folder? (I tried cloning the repository into /var/lib/koha/kohadev/plugins, but nothing appears in the list of plugins.)

David