Skip to content

Commit 88d794e

Browse files
SimonGoringSimonGoring
committed
Pulling all new records into the database.
1 parent 02669d1 commit 88d794e

33 files changed

+829
-3
lines changed

‎.gitignore‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1,2 +1,3 @@
11
connect_remote.json
22
.DS_Store
3+
ignore/*

‎function/ap/explorersearch3.sql‎

Lines changed: 339 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,339 @@
1+
CREATE OR REPLACE FUNCTION ap.explorersearch3(_taxonids da.int_list_type, _elemtypeids da.int_list_type, _taphtypeids da.int_list_type, _depenvids da.int_list_type, _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)
2+
RETURNS SETOF record
3+
LANGUAGE plpgsql
4+
AS $function$
5+
DECLARE sql varchar;
6+
paramlist varchar;
7+
doabund boolean := '0';
8+
doelem boolean := '0';
9+
dotaph boolean := '0';
10+
dodepenv boolean := '0';
11+
notaxa boolean := '0';
12+
sumgroupid int := null;
13+
poly geography := null;
14+
ctebase varchar;
15+
ctebaseselect varchar;
16+
ctebasefrom varchar;
17+
ctebasewhere varchar;
18+
cteages varchar;
19+
cteagesselect varchar;
20+
cteagesfrom varchar;
21+
cteageswhere varchar;
22+
cteds varchar;
23+
ctedsselect varchar;
24+
ctedsfrom varchar;
25+
ctedswhere varchar;
26+
27+
BEGIN
28+
IF (SELECT count(*) from _depenvids) > 0 THEN
29+
dodepenv := '1';
30+
END IF;
31+
IF (SELECT count(*) from _taxonids) < 1 THEN
32+
notaxa := '1';
33+
--some kind of GOTO dataset here--
34+
35+
IF notaxa = 1 THEN
36+
ctebase := '';
37+
cteages := '';
38+
cteds := ' WITH ';
39+
ELSE
40+
cteds := ',
41+
';
42+
cteds := cteds + 'ds AS (';
43+
ctedsselect := '
44+
SELECT
45+
ds.datasetid,
46+
ds.datasettypeid,
47+
ds.isembargo,
48+
ds.recdatecreated,
49+
cu.colltypeid,
50+
cu.depenvtid,
51+
cu.handle,
52+
cu.collunitname,
53+
s.siteid,
54+
s.sitename,
55+
s.sitedescription,
56+
s.notes,
57+
s.altitude,
58+
s.geog,
59+
s.latitudenorth,
60+
s.latitudesouth,
61+
s.longitudeeast,
62+
s.longitudewest,
63+
ages.ageoldest,
64+
ages.ageyoungest,
65+
ages.maxage,
66+
ages.minage';
67+
cteDsFrom := '
68+
JOIN ndb.collectionunits cu ON ds.collectionunitid = cu.collectionunitid
69+
JOIN ndb.sites s ON cu.siteid = s.siteid';
70+
END IF;
71+
IF notaxa = 1 THEN
72+
ctedsfrom := '
73+
FROM
74+
ndb.datasets ds' + ctedsfrom;
75+
IF NOT (ageold IS NULL AND ageyoung IS NULL) THEN
76+
ctedsfrom := ctedsfrom + '
77+
JOIN da.vbestdatasetages ages ON ds.datasetid = ages.datasetid';
78+
ELSE
79+
ctedsfrom := ctedsfrom + '
80+
LEFT JOIN da.vbestdatasetages ages ON ds.datasetid = ages.datasetid';
81+
END IF;
82+
ELSE
83+
ctedsfrom := '
84+
FROM
85+
ages
86+
JOIN ndb.datasets ds ON ages.datasetid = ds.datasetid' + ctedsfrom;
87+
END IF;
88+
89+
ctedswhere := '
90+
WHERE
91+
1=1';
92+
93+
94+
IF siteName IS NOT NULL THEN
95+
ctedswhere := ctedswhere + '
96+
AND s.sitename LIKE ''%'' + sitename + ''%''';
97+
END IF;
98+
IF subdate IS NOT NULL THEN
99+
ctedswhere := ctedswhere + '
100+
AND ds.recdatecreated >= subdate';
101+
END IF;
102+
IF gpid IS NOT NULL THEN
103+
ctedswhere := ctedswhere + '
104+
AND EXISTS (SELECT *
105+
FROM ndb.sitegeopolitical gp
106+
WHERE gp.siteid = s.siteid
107+
AND gp.geopoliticalid = gpid)';
108+
END IF;
109+
IF dodepenv = 1 THEN
110+
ctedswhere := ctedswhere + '
111+
AND cu.depenvtid IN (SELECT n FROM depenvids)';
112+
END IF;
113+
IF coltypeid IS NOT NULL THEN
114+
ctedswhere := ctedswhere + '
115+
AND cu.colltypeid = coltypeid';
116+
END IF;
117+
IF altmin IS NOT NULL THEN
118+
ctedswhere := ctedswhere + '
119+
AND s.altitude >= altmin';
120+
END IF;
121+
IF altMax IS NOT NULL THEN
122+
ctedswhere := ctedswhere + '
123+
AND s.altitude <= altmax';
124+
END IF;
125+
IF dbid IS NOT NULL THEN
126+
ctedswhere := ctedswhere + '
127+
AND EXISTS (SELECT *
128+
FROM ndb.datasetdatabases db
129+
WHERE db.datasetid = ds.datasetid
130+
AND db.databaseid = dbid)';
131+
END IF;
132+
IF contactid IS NOT NULL THEN
133+
ctedswhere := ctedswhere + '
134+
AND EXISTS (SELECT *
135+
FROM ap.datasetpisauthors p
136+
WHERE p.datasetid = ds.datasetid
137+
AND p.contactid = contactid)';
138+
END IF;
139+
IF keywordid IS NOT NULL AND notaxa = 1 THEN
140+
ctedswhere := ctedswhere + '
141+
AND EXISTS (SELECT k.datasetid, k.keywordid
142+
FROM ap.datasetkeywords k
143+
WHERE k.datasetid = ds.datasetid
144+
AND k.keywordid = keywordid)';
145+
END IF;
146+
IF datasettypeid IS NOT NULL THEN
147+
ctedswhere := ctedswhere + '
148+
AND ds.datasettypeid = datasettypeid';
149+
END IF;
150+
IF noTaxa = 1 AND NOT (_ageold IS NULL AND _ageyoung IS NULL) THEN
151+
IF _ageold IS NULL THEN
152+
_ageold := 10000000;
153+
END IF;
154+
IF _ageyoung IS NULL THEN
155+
_ageyoung := -250;
156+
END IF;
157+
IF _agedocontain = 1 THEN
158+
ctedswhere := ctedswhere + '
159+
AND (
160+
(ageyoung <= ages.ageyoungest AND _ageold >= ages.ageoldest) OR
161+
(ageyoung <= ages.minage AND _ageold >= ages.maxage)
162+
)';
163+
ELSE
164+
ctedswhere = ctedswhere + '
165+
AND (
166+
NOT (ages.ageyoungest < _ageold OR ages.ageoldest > _ageyoung ) OR
167+
NOT (ages.agemin < _ageold OR ages.agemax > _ageyoung )
168+
)';
169+
END IF;
170+
END IF;
171+
IF coords IS NOT NULL THEN
172+
/* SET poly = STPolyFromText(_coords, 4326);
173+
-- re-orient poly of ring defined clockwise
174+
IF poly.EnvelopeAngle() >= 90 THEN
175+
SET poly = poly.ReorientObject();
176+
END IF */
177+
ctedswhere := ctedswhere + '
178+
AND s.geog.STIntersects(poly) = 1';
179+
END IF;
180+
181+
cteds := cteds + ctedsselect + ctedsfrom + ctedswhere + '
182+
)';
183+
184+
RAISE NOTICE 'cteds = %', cteds;
185+
186+
--end of dataset block--
187+
END IF;
188+
189+
IF (SELECT count(*) from _elemTypeIds) > 0 THEN
190+
doElem := '1';
191+
END IF;
192+
193+
IF (SELECT count(*) from _taphTypeIds) > 0 THEN
194+
doTaph := '1';
195+
END IF;
196+
197+
IF _abundPct IS NOT NULL THEN
198+
BEGIN
199+
-- get SumGroupID of the first (or only) taxon id
200+
sumGroupId := (SELECT sg.sumgroupid FROM ndb.ecolgroups eg
201+
JOIN ap.pollensumgroups sg ON eg.ecolgroupid = sg.ecolgroupid
202+
WHERE
203+
eg.taxonid IN (SELECT n FROM _taxonIds LIMIT 1));
204+
IF sumGroupId > 0 THEN
205+
doAbund := '1';
206+
END IF;
207+
END;
208+
END IF;
209+
210+
-- START building standard base CTE
211+
IF _ageDirectDate = '0' THEN
212+
BEGIN
213+
cteBase := 'WITH base AS (';
214+
cteBaseSelect := '
215+
SELECT s.sampleid, s.datasetid';
216+
cteBaseFrom := '
217+
FROM ndb.samples s
218+
JOIN ndb.data d ON s.sampleid = d.sampleid
219+
JOIN ndb.variables v ON d.variableid = v.variableid';
220+
cteBaseWhere := '
221+
WHERE';
222+
223+
IF doAbund = '1' THEN
224+
BEGIN
225+
cteBaseSelect := cteBaseSelect || ',
226+
v.taxonid,
227+
CAST(d.value / SUM(d.value) OVER(PARTITION BY s.sampleid) * 100 AS DECIMAL(5,2)) AS abundance';
228+
cteBaseFrom := cteBaseFrom || '
229+
JOIN ndb.taxa t ON v.taxonid = t.taxonid
230+
JOIN ndb.ecolgroups e ON t.taxonid = e.taxonid
231+
JOIN ap.pollensumgroups sg ON e.ecolgroupid = sg.ecolgroupid';
232+
cteBaseWhere := cteBaseWhere || '
233+
sg.sumgroupid = ' || sumGroupId;
234+
END;
235+
ELSE
236+
BEGIN
237+
cteBaseWhere := cteBaseWhere || '
238+
v.taxonid IN (SELECT n FROM _taxonIds)';
239+
IF doElem = '1' THEN
240+
BEGIN
241+
cteBaseFrom := cteBaseFrom || '
242+
JOIN ndb.variableelements ve ON v.variableelementid = ve.variableelementid';
243+
cteBaseWhere = cteBaseWhere || '
244+
AND ve.elementtypeid IN (SELECT n FROM _elemTypeIds)';
245+
END;
246+
END IF;
247+
IF doTaph = '1' THEN
248+
BEGIN
249+
cteBaseFrom := cteBaseFrom || '
250+
JOIN ndb.summarydatataphonomy ta ON ta.dataid = d.dataid';
251+
cteBaseWhere := cteBaseWhere || '
252+
AND ta.taphonomictypeid IN (SELECT n FROM _taphTypeIds)';
253+
END;
254+
END IF;
255+
END;
256+
END IF;
257+
IF _keywordId IS NOT NULL THEN
258+
BEGIN
259+
cteBaseFrom := cteBaseFrom || '
260+
JOIN ndb.samplekeywords k on s.sampleid = k.sampleid';
261+
cteBaseWhere := cteBaseWhere || '
262+
AND k.KeywordID = _keywordId';
263+
END;
264+
END IF;
265+
266+
-- cteBase := cteBase || cteBaseSelect || cteBaseFrom || cteBaseWhere || '
267+
-- )';
268+
269+
cteBase := cteBaseSelect || cteBaseFrom || cteBaseWhere;
270+
END;
271+
END IF;
272+
-- END building base CTE
273+
274+
-- START building standard ages CTE
275+
IF _ageDirectDate = '0' THEN
276+
BEGIN
277+
cteAges := ',
278+
ages AS (';
279+
cteAgesSelect := '
280+
SELECT
281+
base.datasetid,
282+
MIN(sa.age) AS MinAge,
283+
MAX(sa.age) AS MaxAge,
284+
MIN(sa.ageyounger) AS AgeYoungest,
285+
MAX(sa.ageolder) AS AgeOldest';
286+
cteAgesFrom := '
287+
FROM
288+
base';
289+
cteAgesWhere := '
290+
WHERE
291+
1=1';
292+
293+
IF doAbund THEN
294+
cteAgesWhere := cteAgesWhere || '
295+
AND base.abundance > ' || _abundPct ||
296+
' AND base.taxonid IN (SELECT n FROM _taxonIds))';
297+
END IF;
298+
299+
IF NOT (_ageOld IS NULL AND _ageYoung IS NULL) THEN
300+
BEGIN
301+
cteAgesFrom := cteAgesFrom || '
302+
JOIN da.vsampagesstd sa ON base.sampleid = sa.sampleid';
303+
IF _ageOld IS NULL THEN
304+
_ageOld := 10000000;
305+
END IF;
306+
IF _ageYoung IS NULL THEN
307+
_ageYoung := -250;
308+
END IF;
309+
IF _ageDoContain = '1' THEN
310+
cteAgesWhere := cteAgesWhere || '
311+
AND (
312+
(' || _AgeYoung || '<= sa.age AND sa.age <= ' || _AgeOld || ') OR
313+
(' || _AgeYoung || '<= sa.ageyounger AND sa.ageolder <= ' || _AgeOld || ')
314+
)';
315+
ELSE
316+
cteAgesWhere := cteAgesWhere || '
317+
AND (
318+
(' || _AgeYoung || ' <= sa.age AND sa.age <= ' || _AgeOld || ') OR
319+
NOT (sa.ageolder < ' || _ageYoung || ' OR ' || _ageOld || '< sa.ageyounger)
320+
)';
321+
END IF;
322+
END;
323+
ELSE
324+
cteAgesFrom := cteAgesFrom + '
325+
LEFT JOIN da.vsampagesstd sa ON base.sampleid = sa.sampleid';
326+
END IF;
327+
328+
cteAges := cteAges || cteAgesSelect || cteAgesFrom || cteAgesWhere || ' GROUP BY base.datasetid)';
329+
END;
330+
END IF;
331+
-- END building ages CTE --
332+
333+
RAISE NOTICE 'cteAges = %', cteAges;
334+
335+
-- block for alternative combined base/ages CTE for directly dated specimens will go here --
336+
337+
338+
END;
339+
$function$

0 commit comments

Comments
 (0)