lp1863252 toward geosort

[Evergreen.git] / Open-ILS / src / sql / Pg / upgrade / XXXX.geosort.sql

diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.geosort.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.geosort.sql
index 9966492..aeb39b7 100644 (file)
--- 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;