From 5655f25f5116a570a97bb63e6f2c458fcb1f0755 Mon Sep 17 00:00:00 2001 From: Mike Rylander Date: Thu, 10 Dec 2020 16:41:24 -0500 Subject: [PATCH 1/1] lp1863252 toward geosort * Add earthdistance-based org ranking * Accept user input and get best-guess coordinates from that Signed-off-by: Mike Rylander Signed-off-by: Terran McCanna --- .../lib/OpenILS/WWW/EGCatLoader/Record.pm | 18 ++++++++- Open-ILS/src/sql/Pg/990.schema.unapi.sql | 36 ++++++++++++++++++ .../src/sql/Pg/create_database_extensions.sql | 1 + Open-ILS/src/sql/Pg/upgrade/XXXX.geosort.sql | 38 +++++++++++++++++++ .../opac/parts/record/copy_table.tt2 | 12 ++++++ .../opac/parts/record/copy_table.tt2 | 12 ++++-- 6 files changed, 113 insertions(+), 4 deletions(-) diff --git a/Open-ILS/src/perlmods/lib/OpenILS/WWW/EGCatLoader/Record.pm b/Open-ILS/src/perlmods/lib/OpenILS/WWW/EGCatLoader/Record.pm index 9deb0bfc8a..ca5b34e1d1 100644 --- a/Open-ILS/src/perlmods/lib/OpenILS/WWW/EGCatLoader/Record.pm +++ b/Open-ILS/src/perlmods/lib/OpenILS/WWW/EGCatLoader/Record.pm @@ -341,10 +341,26 @@ sub mk_copy_query { }}; }; + my $ou_sort_param = [$org, $pref_ou ]; + my $gl = $self->cgi->param('geographic-location'); + if ($gl) { + my $geo = OpenSRF::AppSession->create("open-ils.geo"); + my $coords = $geo + ->request('open-ils.geo.retrieve_coordinates', $org, scalar $gl) + ->gather(1); + if ($coords + && ref($coords) + && $$coords{latitude} + && $$coords{longitude} + ) { + push(@$ou_sort_param, $$coords{latitude}, $$coords{longitude}); + } + } + # Unsure if we want these in the shared function, leaving here for now unshift(@{$query->{order_by}}, { class => "aou", field => 'id', - transform => 'evergreen.rank_ou', params => [$org, $pref_ou] + transform => 'evergreen.rank_ou', params => $ou_sort_param } ); push(@{$query->{order_by}}, diff --git a/Open-ILS/src/sql/Pg/990.schema.unapi.sql b/Open-ILS/src/sql/Pg/990.schema.unapi.sql index b761fbbb54..9c334c8acc 100644 --- a/Open-ILS/src/sql/Pg/990.schema.unapi.sql +++ b/Open-ILS/src/sql/Pg/990.schema.unapi.sql @@ -40,6 +40,42 @@ RETURNS INTEGER AS $$ ); $$ LANGUAGE SQL STABLE; +-- geolocation-aware variant +CREATE OR REPLACE FUNCTION evergreen.rank_ou(lib INT, search_lib INT, pref_lib INT, plat FLOAT, plon FLOAT) +RETURNS INTEGER AS $$ + SELECT COALESCE( + + -- lib matches search_lib + (SELECT CASE WHEN $1 = $2 THEN -20000 END), + + -- lib matches pref_lib + (SELECT CASE WHEN $1 = $3 THEN -10000 END), + + + -- pref_lib is a child of search_lib and lib is a child of pref lib. + -- For example, searching CONS, pref lib is SYS1, + -- copies at BR1 and BR2 sort to the front. + (SELECT distance - 5000 + FROM actor.org_unit_descendants_distance($3) + WHERE id = $1 AND $3 IN ( + SELECT id FROM actor.org_unit_descendants($2))), + + -- lib is a child of search_lib + (SELECT distance FROM actor.org_unit_descendants_distance($2) WHERE id = $1), + + -- all others pay cash + 1000 + ) + ((SELECT CASE WHEN addr.latitude IS NULL THEN 0 ELSE -20038 END) + (earth_distance( -- shortest GC distance is returned, only half the circumfrence is needed + ll_to_earth( + COALESCE(addr.latitude,plat), -- if the org has no coords, we just + COALESCE(addr.longitude,plon) -- force 0 distance and let the above tie-break + ),ll_to_earth(plat,plon) + ) / 1000)::INT ) -- earth_distance is in meters, convert to kilometers and subtract from largest distance + FROM actor.org_unit org + LEFT JOIN actor.org_address addr ON (org.billing_address = addr.id) + WHERE org.id = $1; +$$ LANGUAGE SQL STABLE; + -- this version exists mainly to accommodate JSON query transform limitations -- (the transform argument must be an IDL field, not an entire row/object) -- XXX is there another way? diff --git a/Open-ILS/src/sql/Pg/create_database_extensions.sql b/Open-ILS/src/sql/Pg/create_database_extensions.sql index 013032c78c..b37f79ed8d 100644 --- a/Open-ILS/src/sql/Pg/create_database_extensions.sql +++ b/Open-ILS/src/sql/Pg/create_database_extensions.sql @@ -21,3 +21,4 @@ CREATE EXTENSION hstore; CREATE EXTENSION intarray; CREATE EXTENSION pgcrypto; CREATE EXTENSION unaccent; +CREATE EXTENSION earthdistance CASCADE; diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.geosort.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.geosort.sql index 99664924e4..aeb39b74c2 100644 --- a/Open-ILS/src/sql/Pg/upgrade/XXXX.geosort.sql +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.geosort.sql @@ -3,6 +3,8 @@ BEGIN; -- check whether patch can be applied SELECT evergreen.upgrade_deps_block_check('XXXX', :eg_version); +CREATE EXTENSION earthdistance CASCADE; + -- 005.schema.actors.sql -- CREATE TABLE actor.org_address ( @@ -90,4 +92,40 @@ INSERT INTO permission.perm_list ( id, code, description ) VALUES 'Administer geographic location services', 'ppl', 'description')) ; +-- geolocation-aware variant +CREATE OR REPLACE FUNCTION evergreen.rank_ou(lib INT, search_lib INT, pref_lib INT, plat FLOAT, plon FLOAT) +RETURNS INTEGER AS $$ + SELECT COALESCE( + + -- lib matches search_lib + (SELECT CASE WHEN $1 = $2 THEN -20000 END), + + -- lib matches pref_lib + (SELECT CASE WHEN $1 = $3 THEN -10000 END), + + + -- pref_lib is a child of search_lib and lib is a child of pref lib. + -- For example, searching CONS, pref lib is SYS1, + -- copies at BR1 and BR2 sort to the front. + (SELECT distance - 5000 + FROM actor.org_unit_descendants_distance($3) + WHERE id = $1 AND $3 IN ( + SELECT id FROM actor.org_unit_descendants($2))), + + -- lib is a child of search_lib + (SELECT distance FROM actor.org_unit_descendants_distance($2) WHERE id = $1), + + -- all others pay cash + 1000 + ) + ((SELECT CASE WHEN addr.latitude IS NULL THEN 0 ELSE -20038 END) + (earth_distance( -- shortest GC distance is returned, only half the circumfrence is needed + ll_to_earth( + COALESCE(addr.latitude,plat), -- if the org has no coords, we just + COALESCE(addr.longitude,plon) -- force 0 distance and let the above tie-break + ),ll_to_earth(plat,plon) + ) / 1000)::INT ) -- earth_distance is in meters, convert to kilometers and subtract from largest distance + FROM actor.org_unit org + LEFT JOIN actor.org_address addr ON (org.billing_address = addr.id) + WHERE org.id = $1; +$$ LANGUAGE SQL STABLE; + COMMIT; diff --git a/Open-ILS/src/templates-bootstrap/opac/parts/record/copy_table.tt2 b/Open-ILS/src/templates-bootstrap/opac/parts/record/copy_table.tt2 index 034760c302..9bf4fd96cb 100755 --- a/Open-ILS/src/templates-bootstrap/opac/parts/record/copy_table.tt2 +++ b/Open-ILS/src/templates-bootstrap/opac/parts/record/copy_table.tt2 @@ -30,6 +30,18 @@ IF has_copies or ctx.foreign_copies; depth = CGI.param('copy_depth').defined ? CGI.param('copy_depth') : CGI.param('depth').defined ? CGI.param('depth') : ctx.copy_summary.last.depth; total_copies = ctx.copy_summary.$depth.count; %] +[% IF ctx.geo_sort %] +
+[% FOREACH p IN CGI.params.keys; NEXT IF p == 'geographic-location' %] + +[% END %] + + [% l("Sort by distance from:") %] + + + +
+[% END %] diff --git a/Open-ILS/src/templates/opac/parts/record/copy_table.tt2 b/Open-ILS/src/templates/opac/parts/record/copy_table.tt2 index 15f2dbabc8..f62e96ef7c 100644 --- a/Open-ILS/src/templates/opac/parts/record/copy_table.tt2 +++ b/Open-ILS/src/templates/opac/parts/record/copy_table.tt2 @@ -32,11 +32,17 @@ IF has_copies or ctx.foreign_copies; %] [% use_courses = (ctx.get_org_setting(ctx.aou_tree.id, 'circ.course_materials_opt_in') == 1) ? 1 : 0 %] [% IF ctx.geo_sort %] + +[% FOREACH p IN CGI.params.keys; NEXT IF p == 'geographic-location' %] + +[% END %] + + + + + [% END %]
[% l("Sort by distance from:") %] - - -
-- 2.43.2