lp1863252 toward geosort
[Evergreen.git] / Open-ILS / src / sql / Pg / upgrade / XXXX.geosort.sql
1 BEGIN;
2
3 -- check whether patch can be applied
4 SELECT evergreen.upgrade_deps_block_check('XXXX', :eg_version);
5
6 CREATE EXTENSION earthdistance CASCADE;
7
8 -- 005.schema.actors.sql
9
10 -- CREATE TABLE actor.org_address (
11 --     ...
12 --     latitude    FLOAT,
13 --     longitude   FLOAT
14 -- );
15
16 ALTER TABLE actor.org_address ADD COLUMN latitude FLOAT;
17 ALTER TABLE actor.org_address ADD COLUMN longitude FLOAT;
18
19 -- 002.schema.config.sql
20
21 CREATE TABLE config.geolocation_service (
22     id           SERIAL PRIMARY KEY,
23     active       BOOLEAN,
24     owner        INT NOT NULL, -- REFERENCES actor.org_unit (id)
25     name         TEXT,
26     service_code TEXT,
27     api_key      TEXT
28 );
29
30 -- 800.fkeys.sql
31
32 ALTER TABLE config.geolocation_service ADD CONSTRAINT cgs_owner_fkey
33     FOREIGN KEY (owner) REFERENCES  actor.org_unit(id) DEFERRABLE INITIALLY DEFERRED;
34
35 -- 950.data.seed-values.sql
36
37 INSERT INTO config.global_flag (name, value, enabled, label)
38 VALUES (
39     'opac.use_geolocation',
40     NULL,
41     FALSE,
42     oils_i18n_gettext(
43         'opac.use_geolocation',
44         'Offer use of geographic location services in the public catalog',
45         'cgf', 'label'
46     )
47 );
48
49 INSERT INTO config.org_unit_setting_type (name, label, grp, description, datatype)
50 VALUES (
51     'opac.holdings_sort_by_geographic_proximity',
52     oils_i18n_gettext('opac.holdings_sort_by_geographic_proximity',
53         'Enable Holdings Sort by Geographic Proximity',
54         'coust', 'label'),
55     'opac',
56     oils_i18n_gettext('opac.holdings_sort_by_geographic_proximity',
57         'When set to TRUE, will cause the record details page to display the controls for sorting holdings by geographic proximity. This also depends on the global flag opac.use_geolocation being enabled.',
58         'coust', 'description'),
59     'bool'
60 );
61
62 INSERT INTO config.org_unit_setting_type (name, label, grp, description, datatype)
63 VALUES (
64     'opac.geographic_proximity_in_miles',
65     oils_i18n_gettext('opac.geographic_proximity_in_miles',
66         'Show Geographic Proximity in Miles',
67         'coust', 'label'),
68     'opac',
69     oils_i18n_gettext('opac.geographic_proximity_in_miles',
70         'When set to TRUE, will cause the record details page to show distances for geographic proximity in miles instead of kilometers.',
71         'coust', 'description'),
72     'bool'
73 );
74
75 INSERT INTO config.org_unit_setting_type (name, label, grp, description, datatype, fm_class)
76 VALUES (
77     'opac.geographic_location_service_for_address',
78     oils_i18n_gettext('opac.geographic_location_service_for_address',
79         'Geographic Location Service to use for Addresses',
80         'coust', 'label'),
81     'opac',
82     oils_i18n_gettext('opac.geographic_location_service_for_address',
83         'Specifies which geographic location service to use for converting address input to geographic coordinates.',
84         'coust', 'description'),
85     'link', 'cgs'
86 );
87
88 INSERT INTO permission.perm_list ( id, code, description ) VALUES
89  ( 630, 'VIEW_GEOLOCATION_SERVICES', oils_i18n_gettext(630,
90     'View geographic location services', 'ppl', 'description')),
91  ( 631, 'ADMIN_GEOLOCATION_SERVICES', oils_i18n_gettext(631,
92     'Administer geographic location services', 'ppl', 'description'))
93 ;
94
95 -- geolocation-aware variant
96 CREATE OR REPLACE FUNCTION evergreen.rank_ou(lib INT, search_lib INT, pref_lib INT, plat FLOAT, plon FLOAT)
97 RETURNS INTEGER AS $$
98     SELECT COALESCE(
99
100         -- lib matches search_lib
101         (SELECT CASE WHEN $1 = $2 THEN -20000 END),
102
103         -- lib matches pref_lib
104         (SELECT CASE WHEN $1 = $3 THEN -10000 END),
105
106
107         -- pref_lib is a child of search_lib and lib is a child of pref lib.
108         -- For example, searching CONS, pref lib is SYS1,
109         -- copies at BR1 and BR2 sort to the front.
110         (SELECT distance - 5000
111             FROM actor.org_unit_descendants_distance($3)
112             WHERE id = $1 AND $3 IN (
113                 SELECT id FROM actor.org_unit_descendants($2))),
114
115         -- lib is a child of search_lib
116         (SELECT distance FROM actor.org_unit_descendants_distance($2) WHERE id = $1),
117
118         -- all others pay cash
119         1000
120     ) + ((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
121             ll_to_earth(
122                 COALESCE(addr.latitude,plat), -- if the org has no coords, we just
123                 COALESCE(addr.longitude,plon) -- force 0 distance and let the above tie-break
124             ),ll_to_earth(plat,plon)
125         ) / 1000)::INT ) -- earth_distance is in meters, convert to kilometers and subtract from largest distance
126     FROM actor.org_unit org
127          LEFT JOIN actor.org_address addr ON (org.billing_address = addr.id)
128     WHERE org.id = $1;
129 $$ LANGUAGE SQL STABLE;
130
131 COMMIT;