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;