Skip to content

Commit 0c537db

Browse files
SimonGoringSimonGoring
committed
Updating the data variables and re-formatting explorer search.
1 parent a1d29eb commit 0c537db

File tree

2 files changed

+68
-48
lines changed

2 files changed

+68
-48
lines changed

‎function/ap/explorersearch.sql‎

Lines changed: 66 additions & 46 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,24 @@
1-
CREATE OR REPLACE FUNCTION ap.explorersearch(_taxonids integer[] DEFAULT NULL::integer[], _elemtypeids integer[] DEFAULT NULL::integer[], _taphtypeids integer[] DEFAULT NULL::integer[], _depenvids integer[] DEFAULT NULL::integer[], _abundpct integer DEFAULT NULL::integer, _datasettypeid integer DEFAULT NULL::integer, _keywordid integer DEFAULT NULL::integer, _coords character varying DEFAULT NULL::character varying, _gpid integer DEFAULT NULL::integer, _altmin integer DEFAULT NULL::integer, _altmax integer DEFAULT NULL::integer, _coltypeid integer DEFAULT NULL::integer, _dbid integer DEFAULT NULL::integer, _sitename character varying DEFAULT NULL::character varying, _contactid integer DEFAULT NULL::integer, _ageold integer DEFAULT NULL::integer, _ageyoung integer DEFAULT NULL::integer, _agedocontain boolean DEFAULT true, _agedirectdate boolean DEFAULT false, _subdate date DEFAULT NULL::date, _debug boolean DEFAULT false)
1+
CREATE OR REPLACE FUNCTION ap.explorersearch(_taxonids integer[] DEFAULT NULL::integer[],
2+
_elemtypeids integer[] DEFAULT NULL::integer[],
3+
_taphtypeids integer[] DEFAULT NULL::integer[],
4+
_depenvids integer[] DEFAULT NULL::integer[],
5+
_abundpct integer DEFAULT NULL::integer,
6+
_datasettypeid integer DEFAULT NULL::integer,
7+
_keywordid integer DEFAULT NULL::integer,
8+
_coords character varying DEFAULT NULL::character varying,
9+
_gpid integer DEFAULT NULL::integer,
10+
_altmin integer DEFAULT NULL::integer,
11+
_altmax integer DEFAULT NULL::integer,
12+
_coltypeid integer DEFAULT NULL::integer,
13+
_dbid integer DEFAULT NULL::integer,
14+
_sitename character varying DEFAULT NULL::character varying,
15+
_contactid integer DEFAULT NULL::integer,
16+
_ageold integer DEFAULT NULL::integer,
17+
_ageyoung integer DEFAULT NULL::integer,
18+
_agedocontain boolean DEFAULT true,
19+
_agedirectdate boolean DEFAULT false,
20+
_subdate date DEFAULT NULL::date,
21+
_debug boolean DEFAULT false)
222
RETURNS TABLE(datasetid integer, datasettype character varying, databasename character varying, minage integer, maxage integer, ageyoungest integer, ageoldest integer, siteid integer, sitename character varying, sitedescription text, notes text, collunithandle character varying, collunitname character varying, latitudenorth double precision, latitudesouth double precision, longitudeeast double precision, longitudewest double precision)
323
LANGUAGE plpgsql
424
AS $function$
@@ -31,18 +51,18 @@ BEGIN
3151
doDepEnv := true;
3252
END IF;
3353

34-
54+
3555
debugLenTaxonids := array_length(_taxonids,1);
36-
56+
3757
RAISE NOTICE '_taxonids length is %', debugLenTaxonids;
3858

39-
IF array_length(_taxonids,1) < 1 OR _taxonids is null THEN
59+
IF array_length(_taxonids,1) < 1 OR _taxonids is null THEN
4060
noTaxa := true;
4161
RAISE NOTICE 'noTaxa is %', noTaxa;
42-
62+
4363
END IF;
4464

45-
65+
4666
IF ( (array_length(_elemtypeids,1) > 0 OR _elemtypeids IS NOT NULL) AND noTaxa = false ) THEN
4767
doElem := true;
4868
RAISE NOTICE 'doElem is %', doElem;
@@ -58,10 +78,10 @@ BEGIN
5878
BEGIN
5979
-- get SumGroupID of the first (or only) taxon id
6080
sumGroupId := (
61-
SELECT sg.sumgroupid
81+
SELECT sg.sumgroupid
6282
FROM ndb.ecolgroups eg
6383
JOIN ap.pollensumgroups sg ON eg.ecolgroupid = sg.ecolgroupid
64-
WHERE
84+
WHERE
6585
eg.taxonid IN (array_to_string( _taxonids[0],','))
6686
);
6787
IF sumGroupId > 0 THEN
@@ -83,15 +103,15 @@ BEGIN
83103
JOIN ndb.variables v ON d.variableid = v.variableid ';
84104
cteBaseWhere := '
85105
WHERE';
86-
106+
87107
IF doAbund = true THEN
88108
BEGIN
89109
cteBaseSelect := cteBaseSelect || ',
90110
v.taxonid,
91-
CAST(d.value / SUM(d.value) OVER(PARTITION BY s.sampleid) * 100 AS DECIMAL(5,2)) AS abundance';
92-
cteBaseFrom := cteBaseFrom || '
93-
JOIN ndb.taxa t ON v.taxonid = t.taxonid
94-
JOIN ndb.ecolgroups e ON t.taxonid = e.taxonid
111+
CAST(d.value / SUM(d.value) OVER(PARTITION BY s.sampleid) * 100 AS DECIMAL(5,2)) AS abundance';
112+
cteBaseFrom := cteBaseFrom || '
113+
JOIN ndb.taxa t ON v.taxonid = t.taxonid
114+
JOIN ndb.ecolgroups e ON t.taxonid = e.taxonid
95115
JOIN ap.pollensumgroups sg ON e.ecolgroupid = sg.ecolgroupid';
96116
cteBaseWhere := cteBaseWhere || '
97117
sg.sumgroupid = ' || sumGroupId;
@@ -128,11 +148,11 @@ BEGIN
128148
AND k.KeywordID = _keywordId';
129149
END;
130150
END IF;
131-
132-
cteBase := cteBase || cteBaseSelect || cteBaseFrom || cteBaseWhere || '
151+
152+
cteBase := cteBase || cteBaseSelect || cteBaseFrom || cteBaseWhere || '
133153
)';
134-
END;
135-
END IF;
154+
END;
155+
END IF;
136156
-- END building base CTE
137157

138158
-- START building standard ages CTE
@@ -159,7 +179,7 @@ BEGIN
159179
AND base.abundance > ' || _abundPct ||
160180
' AND base.taxonid IN (' || array_to_string( _taxonids ,',') || '))';
161181
END IF;
162-
182+
163183
IF NOT (_ageold IS NULL AND _ageyoung IS NULL AND noTaxa = false) THEN
164184
BEGIN
165185
cteAgesFrom := cteAgesFrom || '
@@ -174,7 +194,7 @@ BEGIN
174194
END IF;
175195

176196
IF _ageDoContain = true THEN
177-
cteAgesWhere := cteAgesWhere || '
197+
cteAgesWhere := cteAgesWhere || '
178198
AND (
179199
(' || _ageyoung || '<= sa.age AND sa.age <= ' || _ageold || ') OR
180200
(' || _ageyoung || '<= sa.ageyounger AND sa.ageolder <= ' || _ageold || ')
@@ -246,15 +266,15 @@ BEGIN
246266
-- END alternative ages CTE for directly dated specimens
247267

248268
-- START building ds (dataset) CTE
249-
269+
250270

251271
IF noTaxa = true THEN
252272
BEGIN
253273
cteBase := '';
254274
cteAges := '';
255275
cteDs := ' WITH ';
256276
END;
257-
ELSE
277+
ELSE
258278
cteDs := ',
259279
';
260280
END IF;
@@ -293,7 +313,7 @@ BEGIN
293313
BEGIN
294314
cteBase := '';
295315
cteAges := '';
296-
316+
297317
cteDsFrom := '
298318
FROM
299319
ndb.datasets ds' || cteDsFrom ;
@@ -319,70 +339,70 @@ BEGIN
319339
WHERE
320340
1=1 ';
321341

322-
IF _sitename IS NOT NULL THEN
323-
cteDsWhere := cteDsWhere || '
324-
AND s.sitename LIKE ''%' || _sitename || '%''' ;
342+
IF _sitename IS NOT NULL THEN
343+
cteDsWhere := cteDsWhere || '
344+
AND s.sitename ILIKE ''%' || _sitename || '%''' ;
325345
END IF;
326346

327347
IF _subdate IS NOT NULL THEN
328-
cteDsWhere := cteDsWhere || '
348+
cteDsWhere := cteDsWhere || '
329349
AND ds.recdatecreated >= ' || _subdate;
330350
END IF;
331351

332352
IF _gpid IS NOT NULL THEN
333-
cteDsWhere := cteDsWhere || '
353+
cteDsWhere := cteDsWhere || '
334354
AND EXISTS (SELECT *
335355
FROM ndb.sitegeopolitical gp
336356
WHERE gp.siteid = s.siteid
337357
AND gp.geopoliticalid = ' || _gpid || ' )';
338358
END IF;
339359

340360
IF doDepEnv = true THEN
341-
cteDsWhere := cteDsWhere || '
361+
cteDsWhere := cteDsWhere || '
342362
AND cu.depenvtid IN (array_to_string(' || depEnvIds || ','',''))';
343363
END IF;
344364

345365
IF _coltypeid IS NOT NULL THEN
346-
cteDsWhere := cteDsWhere || '
366+
cteDsWhere := cteDsWhere || '
347367
AND cu.CollTypeID = ' || _coltypeid;
348368
END IF;
349369

350370
IF _altmin IS NOT NULL THEN
351-
cteDsWhere := cteDsWhere || '
371+
cteDsWhere := cteDsWhere || '
352372
AND s.Altitude >= ' || _altmin;
353373
END IF;
354374

355375
IF _altmax IS NOT NULL THEN
356-
cteDsWhere := cteDsWhere || '
376+
cteDsWhere := cteDsWhere || '
357377
AND s.Altitude <= ' || _altmax;
358378
END IF;
359379

360380
IF _dbid IS NOT NULL THEN
361-
cteDsWhere := cteDsWhere || '
381+
cteDsWhere := cteDsWhere || '
362382
AND EXISTS (SELECT *
363383
FROM ndb.datasetdatabases db
364384
WHERE db.datasetid = ds.datasetid
365385
AND db.databaseid = ' || _dbid || ' )';
366386
END IF;
367387

368388
IF _contactid IS NOT NULL THEN
369-
cteDsWhere := cteDsWhere || '
389+
cteDsWhere := cteDsWhere || '
370390
AND EXISTS (SELECT *
371391
FROM ap.datasetpisauthors p
372392
WHERE p.datasetid = ds.datasetid
373393
AND p.contactid = ' || _contactid || ' )';
374394
END IF;
375395

376-
IF _keywordid IS NOT NULL AND noTaxa = true THEN
377-
cteDsWhere := cteDsWhere || '
396+
IF _keywordid IS NOT NULL AND noTaxa = true THEN
397+
cteDsWhere := cteDsWhere || '
378398
AND EXISTS (SELECT k.datasetid, k.keywordid
379399
FROM ap.datasetkeywords k
380400
WHERE k.datasetid = ds.datasetid
381-
AND k.keywordid = ' || _keywordid ' )';
401+
AND k.keywordid = ' || _keywordid ' )';
382402
END IF;
383403

384404
IF _datasettypeid IS NOT NULL THEN
385-
cteDsWhere := cteDsWhere || '
405+
cteDsWhere := cteDsWhere || '
386406
AND ds.DatasetTypeID = ' || _datasettypeid;
387407
END IF;
388408

@@ -395,9 +415,9 @@ BEGIN
395415
_ageyoung := -250;
396416
END IF;
397417
IF _agedocontain = true THEN
398-
cteDsWhere := cteDsWhere || '
418+
cteDsWhere := cteDsWhere || '
399419
AND (
400-
( ' || _ageyoung || ' <= ages.ageyoungest AND ' || _ageold || ' >= ages.ageoldest) OR
420+
( ' || _ageyoung || ' <= ages.ageyoungest AND ' || _ageold || ' >= ages.ageoldest) OR
401421
( ' || _ageyoung || ' <= ages.minage AND ' || _ageold || ' >= ages.maxage)
402422
)';
403423
ELSE
@@ -411,13 +431,13 @@ BEGIN
411431
END IF;
412432

413433
--todo review postgis syntax ---
414-
434+
415435
RAISE NOTICE '_coords value %', _coords;
416436
IF _coords IS NOT NULL THEN
417437
BEGIN
418438
poly := ST_GeogFromText(_coords)::geography;
419-
420-
cteDsWhere := format( '%s' || '
439+
440+
cteDsWhere := format( '%s' || '
421441
AND ST_Intersects(s.geog,' || '%L' || ') = true', cteDsWhere, poly);
422442
END;
423443
END IF;
@@ -427,9 +447,9 @@ BEGIN
427447
-- END building ds (dataset) CTE
428448
--call CTEs
429449

430-
450+
431451
thesql := format(
432-
'%s' || '%s' || '%s' ||
452+
'%s' || '%s' || '%s' ||
433453
' SELECT
434454
ds.datasetid,
435455
dt.datasettype,
@@ -456,10 +476,10 @@ BEGIN
456476
, cteBase, cteAges, cteDs);
457477

458478
RAISE NOTICE '%', thesql;
459-
479+
460480
RETURN QUERY EXECUTE thesql;
461481

462-
482+
463483

464484
END;
465485

‎function/ts/updatedatavariable.sql‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
CREATE OR REPLACE FUNCTION ts.updatedatavariable(_datasetid integer, _oldvariableid integer, _newvariableid integer, _contactid integer)
1+
CREATE OR REPLACE FUNCTION ts.updatedatavariable(_datasetid integer, _oldvariableid integer, _newvariableid integer)
22
RETURNS void
33
LANGUAGE sql
44
AS $function$
@@ -9,6 +9,6 @@ AS $function$
99
)
1010
UPDATE ndb.data
1111
SET variableid = _newvariableid
12-
WHERE dataid = (SELECT dataid FROM dataids);
12+
WHERE dataid IN (SELECT dataid FROM dataids);
1313

1414
$function$

0 commit comments

Comments
 (0)