Skip to content

Commit 503917b

Browse files
SimonGoringSimonGoring
committed
Updating the dataset in the download.
1 parent abc9486 commit 503917b

File tree

1 file changed

+46
-33
lines changed

1 file changed

+46
-33
lines changed

‎function/doi/01_datasetinfo.sql‎

Lines changed: 46 additions & 33 deletions
Original file line numberDiff line numberDiff line change
@@ -4,20 +4,29 @@ CREATE OR REPLACE FUNCTION doi.datasetinfo(dsid INT[])
44
AS $function$
55

66
SELECT dts.datasetid,
7+
sts.site || jsonb_build_object('collectionunit', json_build_object('collectionunitid', clu.collectionunitid,
8+
'depositionalenvironment', dvt.depenvt,
9+
'collectionunit', clu.collunitname,
10+
'handle', clu.handle,
11+
'collunittype', cts.colltype,
12+
'colldate', clu.colldate,
13+
'waterdepth', clu.waterdepth,
14+
'notes', clu.notes,
15+
'collectiondevice', clu.colldevice,
16+
'gpslocation', json_build_object('latitude', clu.gpslatitude,
17+
'longitude', clu.gpslongitude,
18+
'gpsaltitude', clu.gpsaltitude,
19+
'gpserror', clu.gpserror),
20+
'location', clu.location)
721
json_build_object('site', json_build_object('siteid', sts.siteid,
822
'sitename', sts.sitename,
9-
'depositionalenvironment', dvt.depenvt,
1023
'sitedescription', sts.sitedescription,
1124
'sitenotes', sts.notes,
12-
'geography', ST_AsGeoJSON(sts.geog,5,2),
25+
'geography', sts.geography,
26+
'area', sts.area,
1327
'altitude', sts.altitude,
14-
'collectionunit', json_build_object('collectionunitid', clu.collectionunitid,
15-
'collectionunit', clu.collunitname,
16-
'handle', clu.handle,
17-
'collunittype', cts.colltype,
18-
'colldate', clu.colldate,
19-
'notes', clu.notes,
20-
'location', clu.location,
28+
'geopolitical', sts.geopolitical,
29+
'collectionunit', ,
2130
'dataset', json_build_object( 'datasetid', dts.datasetid,
2231
'datasettype', dst.datasettype,
2332
'datasetnotes', dts.notes,
@@ -26,17 +35,18 @@ SELECT dts.datasetid,
2635
'datasetpi', dsau.authors,
2736
'agerange', agerange.ages))))
2837
FROM
29-
ndb.datasets AS dts LEFT OUTER JOIN
30-
ndb.collectionunits AS clu ON clu.collectionunitid = dts.collectionunitid LEFT OUTER JOIN
31-
ndb.depenvttypes AS dvt ON dvt.depenvtid = clu.depenvtid LEFT OUTER JOIN
32-
ndb.sites AS sts ON sts.siteid = clu.siteid LEFT OUTER JOIN
33-
ndb.datasettypes AS dst ON dst.datasettypeid = dts.datasettypeid LEFT OUTER JOIN
34-
ndb.collectiontypes AS cts ON clu.colltypeid = cts.colltypeid LEFT OUTER JOIN
35-
ndb.datasetdatabases AS dsdb ON dsdb.datasetid = dts.datasetid LEFT OUTER JOIN
36-
(SELECT * FROM doi.agerange(dsid)) AS agerange ON agerange.datasetid = dts.datasetid LEFT OUTER JOIN
37-
ndb.constituentdatabases AS cstdb ON dsdb.databaseid = cstdb.databaseid LEFT OUTER JOIN
38-
(SELECT * FROM doi.doireturn(dsid)) AS doi ON doi.datasetid = dts.datasetid LEFT OUTER JOIN
39-
(SELECT * FROM doi.datasetauthors(dsid)) AS dsau ON dsau.datasetid = dts.datasetid
38+
ndb.datasets AS dts
39+
LEFT OUTER JOIN ndb.collectionunits AS clu ON clu.collectionunitid = dts.collectionunitid
40+
LEFT OUTER JOIN ndb.depenvttypes AS dvt ON dvt.depenvtid = clu.depenvtid
41+
LEFT OUTER JOIN (SELECT * FROM ap.siteobject(clu.siteid)) AS sts ON sts.siteid = clu.siteid
42+
LEFT OUTER JOIN ndb.datasettypes AS dst ON dst.datasettypeid = dts.datasettypeid
43+
LEFT OUTER JOIN ndb.collectiontypes AS cts ON clu.colltypeid = cts.colltypeid
44+
LEFT OUTER JOIN ndb.datasetdatabases AS dsdb ON dsdb.datasetid = dts.datasetid
45+
LEFT OUTER JOIN ndb.collectors AS col ON col.collectionunitid = clu.collectionunitid
46+
LEFT OUTER JOIN (SELECT * FROM doi.agerange(dsid)) AS agerange ON agerange.datasetid = dts.datasetid
47+
LEFT OUTER JOIN ndb.constituentdatabases AS cstdb ON dsdb.databaseid = cstdb.databaseid
48+
LEFT OUTER JOIN (SELECT * FROM doi.doireturn(dsid)) AS doi ON doi.datasetid = dts.datasetid
49+
LEFT OUTER JOIN (SELECT * FROM doi.datasetauthors(dsid)) AS dsau ON dsau.datasetid = dts.datasetid
4050
WHERE dts.datasetid = ANY(dsid)
4151

4252
$function$;
@@ -48,29 +58,32 @@ CREATE OR REPLACE FUNCTION doi.datasetinfo(
4858
AS $function$
4959

5060
SELECT dts.datasetid,
51-
json_build_object('site', json_build_object('siteid', sts.siteid,
52-
'sitename', sts.sitename,
53-
'depositionalenvironment', dvt.depenvt,
54-
'sitedescription', sts.sitedescription,
55-
'sitenotes', sts.notes,
56-
'geography', ST_AsGeoJSON(sts.geog,5,2),
57-
'altitude', sts.altitude,
58-
'collectionunitid', clu.collectionunitid,
59-
'collectionunit', clu.collunitname,
60-
'handle', clu.handle,
61-
'unittype', cts.colltype),
61+
ap.siteobject(clu.siteid) ||
62+
json_build_object('collectionunit', json_build_object('collectionunitid', clu.collectionunitid,
63+
'depositionalenvironment', dvt.depenvt,
64+
'collectionunit', clu.collunitname,
65+
'handle', clu.handle,
66+
'collunittype', cts.colltype,
67+
'colldate', clu.colldate,
68+
'waterdepth', clu.waterdepth,
69+
'notes', clu.notes,
70+
'collectiondevice', clu.colldevice,
71+
'gpslocation', json_build_object('latitude', clu.gpslatitude,
72+
'longitude', clu.gpslongitude,
73+
'gpsaltitude', clu.gpsaltitude,
74+
'gpserror', clu.gpserror),
75+
'location', clu.location)
6276
'dataset', json_build_object( 'datasetid', dts.datasetid,
6377
'datasettype', dst.datasettype,
6478
'datasetnotes', dts.notes,
6579
'database', cstdb.databasename,
6680
'doi', doi.dois,
6781
'datasetpi', dsau.authors,
68-
'agerange', agerange.ages))
82+
'agerange', agerange.ages))
6983
FROM
7084
ndb.datasets AS dts LEFT OUTER JOIN
7185
ndb.collectionunits AS clu ON clu.collectionunitid = dts.collectionunitid LEFT OUTER JOIN
7286
ndb.depenvttypes AS dvt ON dvt.depenvtid = clu.depenvtid LEFT OUTER JOIN
73-
ndb.sites AS sts ON sts.siteid = clu.siteid LEFT OUTER JOIN
7487
ndb.datasettypes AS dst ON dst.datasettypeid = dts.datasettypeid LEFT OUTER JOIN
7588
ndb.collectiontypes as cts ON clu.colltypeid = cts.colltypeid LEFT OUTER JOIN
7689
ndb.datasetdatabases AS dsdb ON dsdb.datasetid = dts.datasetid LEFT OUTER JOIN

0 commit comments

Comments
 (0)