3 -- check whether patch can be applied
4 SELECT evergreen.upgrade_deps_block_check('XXXX', :eg_version);
6 CREATE EXTENSION earthdistance CASCADE;
8 -- 005.schema.actors.sql
10 -- CREATE TABLE actor.org_address (
16 ALTER TABLE actor.org_address ADD COLUMN latitude FLOAT;
17 ALTER TABLE actor.org_address ADD COLUMN longitude FLOAT;
19 -- 002.schema.config.sql
21 CREATE TABLE config.geolocation_service (
22 id SERIAL PRIMARY KEY,
24 owner INT NOT NULL, -- REFERENCES actor.org_unit (id)
32 ALTER TABLE config.geolocation_service ADD CONSTRAINT cgs_owner_fkey
33 FOREIGN KEY (owner) REFERENCES actor.org_unit(id) DEFERRABLE INITIALLY DEFERRED;
35 -- 950.data.seed-values.sql
37 INSERT INTO config.global_flag (name, value, enabled, label)
39 'opac.use_geolocation',
43 'opac.use_geolocation',
44 'Offer use of geographic location services in the public catalog',
49 INSERT INTO config.org_unit_setting_type (name, label, grp, description, datatype)
51 'opac.holdings_sort_by_geographic_proximity',
52 oils_i18n_gettext('opac.holdings_sort_by_geographic_proximity',
53 'Enable Holdings Sort by Geographic Proximity',
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'),
62 INSERT INTO config.org_unit_setting_type (name, label, grp, description, datatype)
64 'opac.geographic_proximity_in_miles',
65 oils_i18n_gettext('opac.geographic_proximity_in_miles',
66 'Show Geographic Proximity in Miles',
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'),
75 INSERT INTO config.org_unit_setting_type (name, label, grp, description, datatype, fm_class)
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',
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'),
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'))
95 -- geolocation-aware variant
96 CREATE OR REPLACE FUNCTION evergreen.rank_ou(lib INT, search_lib INT, pref_lib INT, plat FLOAT, plon FLOAT)
100 -- lib matches search_lib
101 (SELECT CASE WHEN $1 = $2 THEN -20000 END),
103 -- lib matches pref_lib
104 (SELECT CASE WHEN $1 = $3 THEN -10000 END),
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))),
115 -- lib is a child of search_lib
116 (SELECT distance FROM actor.org_unit_descendants_distance($2) WHERE id = $1),
118 -- all others pay cash
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
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)
129 $$ LANGUAGE SQL STABLE;