Skip to content

Commit 4ad878f

Browse files
SimonGoringSimonGoring
committed
Adding the ap helper functions.
1 parent 76ec35e commit 4ad878f

File tree

5 files changed

+316
-7
lines changed

5 files changed

+316
-7
lines changed

‎README.md‎

Lines changed: 6 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -6,11 +6,12 @@ This is a repository to store the functions used within the Neotoma Database for
66

77
All individuals are welcome to contribute to this repository. Contributions are subject to the [Code of Conduct](https://github.com/neotomadb/Neotoma_SQL/blob/master/code_of_conduct.md) for this repository.
88

9-
* Steve Crawford - [Penn State](http://www.ems.psu.edu/node/147)
10-
* Simon Goring - [University of Wisconsin](http://goring.org)
11-
* Mike Stryker - [Penn State](http://www.ems.psu.edu/node/2892)
12-
* Anna George - University of Wisconsin
13-
* Jack Williams - University of Wisconsin
9+
* Steve Crawford - [Penn State](http://www.ems.psu.edu/node/147)
10+
* Simon Goring - [University of Wisconsin](http://goring.org)
11+
* Mike Stryker - [Penn State](http://www.ems.psu.edu/node/2892)
12+
* Jonathan Nelson - Penn State
13+
* Adrian George - University of Wisconsin
14+
* Jack Williams - University of Wisconsin
1415

1516
## Description
1617

‎function/ap/objectstructures/collectionunitobject.sql‎

Lines changed: 73 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,73 @@
1+
--
2+
DROP FUNCTION IF EXISTS ap.collectionunitobject(_collectionunitid integer);
3+
CREATE OR REPLACE FUNCTION ap.collectionunitobject(_collectionunitid integer)
4+
RETURNS TABLE(collectionunitid integer, collectionunit jsonb)
5+
LANGUAGE sql
6+
AS $function$
7+
SELECT clu.collectionunitid,
8+
jsonb_build_object('collectionunit', jsonb_build_object('collectionunitid', clu.collectionunitid,
9+
'depositionalenvironment', dvt.depenvt,
10+
'collectionunit', clu.collunitname,
11+
'handle', clu.handle,
12+
'collunittype', cts.colltype,
13+
'colldate', clu.colldate,
14+
'waterdepth', clu.waterdepth,
15+
'notes', clu.notes,
16+
'collectiondevice', clu.colldevice,
17+
'location', clu.location,
18+
'gpslocation', json_build_object('latitude', clu.gpslatitude,
19+
'longitude', clu.gpslongitude,
20+
'gpsaltitude', clu.gpsaltitude,
21+
'gpserror', clu.gpserror),
22+
'collectors', json_agg(DISTINCT jsonb_build_object('contactid', cnt.contactid,
23+
'contactname', cnt.contactname,
24+
'familyname', cnt.familyname,
25+
'firstname', cnt.givennames,
26+
'initials', cnt.leadinginitials)))) AS collectionunit
27+
FROM
28+
ndb.collectionunits AS clu
29+
LEFT JOIN ndb.depenvttypes AS dvt ON dvt.depenvtid = clu.depenvtid
30+
LEFT JOIN ndb.collectiontypes AS cts ON cts.colltypeid = clu.colltypeid
31+
LEFT OUTER JOIN ndb.collectors AS col ON col.collectionunitid = clu.collectionunitid
32+
LEFT JOIN ndb.contacts AS cnt ON cnt.contactid = col.contactid
33+
WHERE clu.collectionunitid = _collectionunitid
34+
GROUP BY clu.collectionunitid, dvt.depenvt, cts.colltype;
35+
$function$;
36+
37+
COMMENT ON FUNCTION ap.collectionunitobject(_collectionunitid integer) IS 'Create the JSON object used to return relevant collectionunit metadata from a single collection unit id.';
38+
39+
DROP FUNCTION ap.collectionunitobject(_collectionunitid integer[]);
40+
CREATE OR REPLACE FUNCTION ap.collectionunitobject(_collectionunitid integer[])
41+
RETURNS TABLE(collectionunitid integer, collectionunit jsonb)
42+
LANGUAGE sql
43+
AS $function$
44+
SELECT clu.collectionunitid,
45+
jsonb_build_object('collectionunit', jsonb_build_object('collectionunitid', clu.collectionunitid,
46+
'depositionalenvironment', dvt.depenvt,
47+
'collectionunit', clu.collunitname,
48+
'handle', clu.handle,
49+
'collunittype', cts.colltype,
50+
'colldate', clu.colldate,
51+
'waterdepth', clu.waterdepth,
52+
'notes', clu.notes,
53+
'collectiondevice', clu.colldevice,
54+
'location', clu.location,
55+
'gpslocation', json_build_object('latitude', clu.gpslatitude,
56+
'longitude', clu.gpslongitude,
57+
'gpsaltitude', clu.gpsaltitude,
58+
'gpserror', clu.gpserror),
59+
'collectors', json_agg(DISTINCT jsonb_build_object('contactid', cnt.contactid,
60+
'contactname', cnt.contactname,
61+
'familyname', cnt.familyname,
62+
'firstname', cnt.givennames,
63+
'initials', cnt.leadinginitials)))) AS collectionunit
64+
FROM
65+
ndb.collectionunits AS clu
66+
LEFT JOIN ndb.depenvttypes AS dvt ON dvt.depenvtid = clu.depenvtid
67+
LEFT JOIN ndb.collectiontypes AS cts ON cts.colltypeid = clu.colltypeid
68+
LEFT OUTER JOIN ndb.collectors AS col ON col.collectionunitid = clu.collectionunitid
69+
LEFT JOIN ndb.contacts AS cnt ON cnt.contactid = col.contactid
70+
WHERE clu.collectionunitid = ANY(_collectionunitid)
71+
GROUP BY clu.collectionunitid, dvt.depenvt, cts.colltype
72+
$function$;
73+
COMMENT ON FUNCTION ap.collectionunitobject(_collectionunitid integer[]) IS 'Create the JSON object used to return relevant collectionunit metadata from an array of collection unit ids.';

‎function/ap/objectstructures/datasetobject.sql‎

Lines changed: 107 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,107 @@
1+
--
2+
DROP FUNCTION IF EXISTS ap.datasetobject(_datasetid integer);
3+
CREATE OR REPLACE FUNCTION ap.datasetobject(_datasetid integer)
4+
RETURNS TABLE(datasetid integer, dataset jsonb)
5+
LANGUAGE sql
6+
AS $function$
7+
SELECT dts.datasetid,
8+
jsonb_build_object('datasetid', dts.datasetid,
9+
'datasettype', dst.datasettype,
10+
'datasetnotes', dts.notes,
11+
'database', cstdb.databasename,
12+
'doi', json_agg(DISTINCT doi.doi),
13+
'datasetpublications', json_agg(DISTINCT jsonb_build_object('publicationid', pub.publicationid,
14+
'citation', pub.citation)),
15+
'datasetpi', json_agg(DISTINCT jsonb_build_object('contactid', cnt.contactid,
16+
'contactname', cnt.contactname,
17+
'familyname', cnt.familyname,
18+
'firstname', cnt.givennames,
19+
'initials', cnt.leadinginitials)),
20+
'datasetprocessors', json_agg(DISTINCT jsonb_build_object('contactid', cntp.contactid,
21+
'contactname', cntp.contactname,
22+
'familyname', cntp.familyname,
23+
'firstname', cntp.givennames,
24+
'initials', cntp.leadinginitials)),
25+
'agerange', json_agg(DISTINCT jsonb_build_object('ageyoung', agerange.younger,
26+
'ageold', agerange.older,
27+
'units', agetypes.agetype)),
28+
'repository', json_agg(DISTINCT ri.repository))
29+
AS dataset
30+
FROM
31+
ndb.datasets AS dts
32+
LEFT OUTER JOIN ndb.datasettypes AS dst ON dst.datasettypeid = dts.datasettypeid
33+
LEFT OUTER JOIN ndb.datasetdoi AS doi ON dts.datasetid = doi.datasetid
34+
LEFT OUTER JOIN ndb.datasetdatabases AS dsdb ON dsdb.datasetid = dts.datasetid
35+
LEFT OUTER JOIN ndb.datasetpublications AS dtpub ON dtpub.datasetid = dts.datasetid
36+
LEFT OUTER JOIN ndb.publications AS pub ON pub.publicationid = dtpub.publicationid
37+
LEFT OUTER JOIN ndb.datasetpis AS dspi ON dspi.datasetid = dts.datasetid
38+
LEFT OUTER JOIN ndb.dataprocessors AS dspr ON dspr.datasetid = dts.datasetid
39+
LEFT OUTER JOIN ndb.contacts AS cntp ON cntp.contactid = dspi.contactid
40+
LEFT OUTER JOIN ndb.contacts AS cnt ON cnt.contactid = dspi.contactid
41+
LEFT OUTER JOIN ndb.dsageranges AS agerange ON dts.datasetid = agerange.datasetid
42+
LEFT OUTER JOIN ndb.agetypes AS agetypes ON agetypes.agetypeid = agerange.agetypeid
43+
LEFT OUTER JOIN ndb.constituentdatabases AS cstdb ON dsdb.databaseid = cstdb.databaseid
44+
LEFT OUTER JOIN ndb.repositoryspecimens AS rpspec ON rpspec.datasetid = dts.datasetid
45+
LEFT OUTER JOIN ndb.repositoryinstitutions AS ri ON ri.repositoryid = rpspec.repositoryid
46+
WHERE dts.datasetid = _datasetid
47+
GROUP BY
48+
dts.datasetid,
49+
dst.datasettype,
50+
dts.notes,
51+
cstdb.databasename
52+
$function$;
53+
54+
COMMENT ON FUNCTION ap.datasetobject(_datasetid integer) IS 'Create the JSON object used to return relevant dataset metadata from a single datasetid.';
55+
56+
DROP FUNCTION ap.datasetobject(_datasetid integer[]);
57+
CREATE OR REPLACE FUNCTION ap.datasetobject(_datasetid integer[])
58+
RETURNS TABLE(datasetid integer, dataset jsonb)
59+
LANGUAGE sql
60+
AS $function$
61+
SELECT dts.datasetid,
62+
jsonb_build_object('datasetid', dts.datasetid,
63+
'datasettype', dst.datasettype,
64+
'datasetnotes', dts.notes,
65+
'database', cstdb.databasename,
66+
'doi', json_agg(DISTINCT doi.doi),
67+
'datasetpublications', json_agg(DISTINCT jsonb_build_object('publicationid', pub.publicationid,
68+
'citation', pub.citation)),
69+
'datasetpi', json_agg(DISTINCT jsonb_build_object('contactid', cnt.contactid,
70+
'contactname', cnt.contactname,
71+
'familyname', cnt.familyname,
72+
'firstname', cnt.givennames,
73+
'initials', cnt.leadinginitials)),
74+
'datasetprocessors', json_agg(DISTINCT jsonb_build_object('contactid', cntp.contactid,
75+
'contactname', cntp.contactname,
76+
'familyname', cntp.familyname,
77+
'firstname', cntp.givennames,
78+
'initials', cntp.leadinginitials)),
79+
'agerange', json_agg(DISTINCT jsonb_build_object('ageyoung', agerange.younger,
80+
'ageold', agerange.older,
81+
'units', agetypes.agetype)),
82+
'repository', json_agg(DISTINCT ri.repository))
83+
AS dataset
84+
FROM
85+
ndb.datasets AS dts
86+
LEFT OUTER JOIN ndb.datasettypes AS dst ON dst.datasettypeid = dts.datasettypeid
87+
LEFT OUTER JOIN ndb.datasetdoi AS doi ON dts.datasetid = doi.datasetid
88+
LEFT OUTER JOIN ndb.datasetdatabases AS dsdb ON dsdb.datasetid = dts.datasetid
89+
LEFT OUTER JOIN ndb.datasetpublications AS dtpub ON dtpub.datasetid = dts.datasetid
90+
LEFT OUTER JOIN ndb.publications AS pub ON pub.publicationid = dtpub.publicationid
91+
LEFT OUTER JOIN ndb.datasetpis AS dspi ON dspi.datasetid = dts.datasetid
92+
LEFT OUTER JOIN ndb.dataprocessors AS dspr ON dspr.datasetid = dts.datasetid
93+
LEFT OUTER JOIN ndb.contacts AS cntp ON cntp.contactid = dspi.contactid
94+
LEFT OUTER JOIN ndb.contacts AS cnt ON cnt.contactid = dspi.contactid
95+
LEFT OUTER JOIN ndb.dsageranges AS agerange ON dts.datasetid = agerange.datasetid
96+
LEFT OUTER JOIN ndb.agetypes AS agetypes ON agetypes.agetypeid = agerange.agetypeid
97+
LEFT OUTER JOIN ndb.constituentdatabases AS cstdb ON dsdb.databaseid = cstdb.databaseid
98+
LEFT OUTER JOIN ndb.repositoryspecimens AS rpspec ON rpspec.datasetid = dts.datasetid
99+
LEFT OUTER JOIN ndb.repositoryinstitutions AS ri ON ri.repositoryid = rpspec.repositoryid
100+
WHERE dts.datasetid = ANY(_datasetid)
101+
GROUP BY
102+
dts.datasetid,
103+
dst.datasettype,
104+
dts.notes,
105+
cstdb.databasename
106+
$function$;
107+
COMMENT ON FUNCTION ap.siteobject(_siteid integer[]) IS 'Create the JSON object used to return relevant dataset metadata from an array of datasetids.';

‎function/ap/objectstructures/sampleobject.sql‎

Lines changed: 126 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,126 @@
1+
DROP FUNCTION IF EXISTS ap.sample;
2+
CREATE OR REPLACE FUNCTION ap.sample(_datasetid integer)
3+
RETURNS TABLE(datasetid integer, sample jsonb)
4+
LANGUAGE sql
5+
AS $function$
6+
SELECT
7+
ds.datasetid,
8+
jsonb_build_object('sampleid', dsd.sampleid,
9+
'keywords', array_agg(DISTINCT ky.keyword),
10+
'depth', anu.depth,
11+
'datum', jsonb_agg(DISTINCT jsonb_build_object('value', dt.value,
12+
'variablename', tx.taxonname,
13+
'taxonid', tx.taxonid,
14+
'taxongroup', txg.taxagroup,
15+
'ecologicalgroup', ecg.ecolgroupid,
16+
'element', ve.variableelement,
17+
'elementtype', vt.elementtype,
18+
'symmetry', vs.symmetry,
19+
'context', vc.variablecontext,
20+
'units', vru.variableunits)),
21+
'sampleanalyst', json_agg(DISTINCT jsonb_build_object('contactid', cnt.contactid,
22+
'contactname', cnt.contactname,
23+
'familyname', cnt.familyname,
24+
'firstname', cnt.givennames,
25+
'initials', cnt.leadinginitials)),
26+
'ages', jsonb_agg(
27+
DISTINCT jsonb_build_object('chronologyid', ch.chronologyid,
28+
'chronologyname', ch.chronologyname,
29+
'agetype', cht.agetype,
30+
'age', sma.age,
31+
'ageyounger', sma.ageyounger,
32+
'ageolder', sma.ageolder))) AS sampledata
33+
FROM
34+
ndb.datasets AS ds
35+
LEFT OUTER JOIN ndb.dsdatasample AS dsd ON dsd.datasetid = ds.datasetid
36+
LEFT OUTER JOIN ndb.data AS dt ON dt.dataid = dsd.dataid
37+
LEFT OUTER JOIN ndb.variables AS var ON var.variableid = dsd.variableid
38+
LEFT OUTER JOIN ndb.taxa AS tx ON tx.taxonid = var.taxonid
39+
LEFT OUTER JOIN ndb.taxagrouptypes AS txg ON txg.taxagroupid = tx.taxagroupid
40+
LEFT OUTER JOIN ndb.ecolgroups AS ecg ON ecg.taxonid = tx.taxonid
41+
LEFT OUTER JOIN ndb.variableunits AS vru ON vru.variableunitsid = var.variableunitsid
42+
LEFT OUTER JOIN ndb.samples AS smp ON smp.sampleid = dsd.sampleid
43+
LEFT OUTER JOIN ndb.sampleanalysts AS san ON san.sampleid = smp.sampleid
44+
LEFT JOIN ndb.contacts AS cnt ON cnt.contactid = san.contactid
45+
LEFT OUTER JOIN ndb.analysisunits AS anu ON anu.analysisunitid = smp.analysisunitid
46+
LEFT JOIN ndb.variableelements AS ve ON ve.variableelementid = var.variableelementid
47+
LEFT OUTER JOIN ndb.elementsymmetries AS vs ON vs.symmetryid = ve.symmetryid
48+
LEFT OUTER JOIN ndb.elementtypes AS vt ON vt.elementtypeid = ve.elementtypeid
49+
LEFT JOIN ndb.variablecontexts AS vc ON vc.variablecontextid = var.variablecontextid
50+
LEFT OUTER JOIN ndb.sampleages AS sma ON sma.sampleid = smp.sampleid
51+
LEFT OUTER JOIN ndb.chronologies AS ch ON sma.chronologyid = ch.chronologyid
52+
LEFT OUTER JOIN ndb.agetypes AS cht ON cht.agetypeid = ch.agetypeid
53+
LEFT OUTER JOIN ndb.samplekeywords AS smpky ON smpky.sampleid = smp.sampleid
54+
LEFT OUTER JOIN ndb.keywords AS ky ON ky.keywordid = smpky.keywordid
55+
WHERE
56+
ds.datasetid = _datasetid
57+
GROUP BY ds.datasetid,
58+
dsd.sampleid,
59+
anu.depth,
60+
anu.thickness
61+
ORDER BY anu.depth ASC;
62+
$function$;
63+
64+
DROP FUNCTION IF EXISTS ap.sample;
65+
CREATE OR REPLACE FUNCTION ap.sample(_datasetid integer[])
66+
RETURNS TABLE(datasetid integer, sample jsonb)
67+
LANGUAGE sql
68+
AS $function$
69+
SELECT
70+
ds.datasetid,
71+
jsonb_build_object( 'sampleid', dsd.sampleid,
72+
'keywords', array_agg(DISTINCT ky.keyword),
73+
'depth', anu.depth,
74+
'thickness', anu.thickness,
75+
'datum', jsonb_agg(DISTINCT jsonb_build_object('value', dt.value,
76+
'variablename', tx.taxonname,
77+
'taxonid', tx.taxonid,
78+
'taxongroup', txg.taxagroup,
79+
'ecologicalgroup', ecg.ecolgroupid,
80+
'element', ve.variableelement,
81+
'elementtype', vt.elementtype,
82+
'symmetry', vs.symmetry,
83+
'context', vc.variablecontext,
84+
'units', vru.variableunits)),
85+
'sampleanalyst', json_agg(DISTINCT jsonb_build_object('contactid', cnt.contactid,
86+
'contactname', cnt.contactname,
87+
'familyname', cnt.familyname,
88+
'firstname', cnt.givennames,
89+
'initials', cnt.leadinginitials)),
90+
'ages', jsonb_agg(
91+
DISTINCT jsonb_build_object('chronologyid', ch.chronologyid,
92+
'chronologyname', ch.chronologyname,
93+
'agetype', cht.agetype,
94+
'age', sma.age,
95+
'ageyounger', sma.ageyounger,
96+
'ageolder', sma.ageolder))) AS sampledata
97+
FROM
98+
ndb.datasets AS ds
99+
LEFT OUTER JOIN ndb.dsdatasample AS dsd ON dsd.datasetid = ds.datasetid
100+
LEFT OUTER JOIN ndb.data AS dt ON dt.dataid = dsd.dataid
101+
LEFT OUTER JOIN ndb.variables AS var ON var.variableid = dsd.variableid
102+
LEFT OUTER JOIN ndb.taxa AS tx ON tx.taxonid = var.taxonid
103+
LEFT OUTER JOIN ndb.taxagrouptypes AS txg ON txg.taxagroupid = tx.taxagroupid
104+
LEFT OUTER JOIN ndb.ecolgroups AS ecg ON ecg.taxonid = tx.taxonid
105+
LEFT OUTER JOIN ndb.variableunits AS vru ON vru.variableunitsid = var.variableunitsid
106+
LEFT OUTER JOIN ndb.samples AS smp ON smp.sampleid = dsd.sampleid
107+
LEFT OUTER JOIN ndb.sampleanalysts AS san ON san.sampleid = smp.sampleid
108+
LEFT JOIN ndb.contacts AS cnt ON cnt.contactid = san.contactid
109+
LEFT OUTER JOIN ndb.analysisunits AS anu ON anu.analysisunitid = smp.analysisunitid
110+
LEFT JOIN ndb.variableelements AS ve ON ve.variableelementid = var.variableelementid
111+
LEFT OUTER JOIN ndb.elementsymmetries AS vs ON vs.symmetryid = ve.symmetryid
112+
LEFT OUTER JOIN ndb.elementtypes AS vt ON vt.elementtypeid = ve.elementtypeid
113+
LEFT JOIN ndb.variablecontexts AS vc ON vc.variablecontextid = var.variablecontextid
114+
LEFT OUTER JOIN ndb.sampleages AS sma ON sma.sampleid = smp.sampleid
115+
LEFT OUTER JOIN ndb.chronologies AS ch ON sma.chronologyid = ch.chronologyid
116+
LEFT OUTER JOIN ndb.agetypes AS cht ON cht.agetypeid = ch.agetypeid
117+
LEFT OUTER JOIN ndb.samplekeywords AS smpky ON smpky.sampleid = smp.sampleid
118+
LEFT OUTER JOIN ndb.keywords AS ky ON ky.keywordid = smpky.keywordid
119+
WHERE
120+
ds.datasetid = ANY(_datasetid)
121+
GROUP BY ds.datasetid,
122+
dsd.sampleid,
123+
anu.depth,
124+
anu.thickness
125+
ORDER BY anu.depth ASC;
126+
$function$;

0 commit comments

Comments
 (0)