Skip to content

Commit 42123e1

Browse files
SimonGoringSimonGoring
committed
Rewriting the sql structure for downloads.
Trying to standardize the querying & retrn of site/dataset & download objects.
1 parent c856209 commit 42123e1

File tree

4 files changed

+665
-0
lines changed

4 files changed

+665
-0
lines changed

‎function/ap/siteobject.sql‎

Lines changed: 53 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,53 @@
1+
CREATE OR REPLACE FUNCTION ap.siteobject(_siteid integer)
2+
RETURNS TABLE(siteid integer,
3+
sitename character varying,
4+
sitedescription character varying,
5+
geography text,
6+
area double precision,
7+
altitude double precision,
8+
notes text,
9+
geopolitical character varying[])
10+
LANGUAGE sql
11+
AS $function$
12+
SELECT sts.siteid,
13+
sts.sitename as sitename,
14+
sts.sitedescription AS sitedescription,
15+
ST_AsGeoJSON(sts.geog,5,2) as geography,
16+
sts.area AS area,
17+
sts.altitude AS altitude,
18+
sts.notes AS notes,
19+
gpn.names AS geopolitical
20+
FROM
21+
ndb.sites AS sts
22+
LEFT JOIN ap.geopolnames AS gpn ON gpn.siteid = sts.siteid
23+
WHERE sts.siteid = _siteid
24+
GROUP BY sts.siteid, sts.sitename, sts.sitedescription, ST_AsGeoJSON(sts.geog,5,2),
25+
sts.area, sts.altitude, sts.notes, gpn.names;
26+
$function$;
27+
28+
CREATE OR REPLACE FUNCTION ap.siteobject(_siteid integer[])
29+
RETURNS TABLE(siteid integer,
30+
sitename character varying,
31+
sitedescription character varying,
32+
geography text,
33+
area double precision,
34+
altitude double precision,
35+
notes text,
36+
geopolitical character varying[])
37+
LANGUAGE sql
38+
AS $function$
39+
SELECT sts.siteid,
40+
sts.sitename as sitename,
41+
sts.sitedescription AS sitedescription,
42+
ST_AsGeoJSON(sts.geog,5,2) as geography,
43+
sts.area AS area,
44+
sts.altitude AS altitude,
45+
sts.notes AS notes,
46+
gpn.names AS geopolitical
47+
FROM
48+
ndb.sites AS sts
49+
LEFT JOIN ap.geopolnames AS gpn ON gpn.siteid = sts.siteid
50+
WHERE sts.siteid = ANY(_siteid)
51+
GROUP BY sts.siteid, sts.sitename, sts.sitedescription, ST_AsGeoJSON(sts.geog,5,2),
52+
sts.area, sts.altitude, sts.notes, gpn.names;
53+
$function$;

‎helpers/bigQuerytable.sql‎

Lines changed: 94 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,94 @@
1+
DROP MATERIALIZED VIEW ap.querytable CASCADE;
2+
CREATE MATERIALIZED VIEW ap.querytable AS (
3+
WITH sgp AS (
4+
SELECT st.siteid,
5+
array_append(gp.geoout, gp.geoin::int) AS geopol,
6+
rank() OVER (partition by st.siteid order by st.siteid, array_length(gp.geoout,1) ASC) AS rank
7+
FROM ndb.sites AS st
8+
INNER JOIN ndb.sitegeopolitical AS sgp ON st.siteid = sgp.siteid
9+
INNER JOIN ndb.geopoliticalunits AS gpu ON gpu.geopoliticalid = sgp.geopoliticalid
10+
INNER JOIN ndb.geopaths AS gp ON gp.geoin = sgp.geopoliticalid
11+
)
12+
SELECT st.siteid,
13+
st.sitename,
14+
ds.datasetid,
15+
st.altitude,
16+
dst.datasettype,
17+
dsdb.databaseid,
18+
cu.collectionunitid,
19+
cut.colltype,
20+
dvt.depenvt,
21+
st.geog,
22+
arg.older,
23+
arg.younger,
24+
agetypes.agetype,
25+
array_agg(DISTINCT var.taxonid) AS taxa,
26+
array_agg(DISTINCT smpkw.keywordid) AS keywords,
27+
array_agg(DISTINCT dpi.contactid) AS contacts,
28+
sgp.geopol
29+
FROM ndb.sites AS st
30+
INNER JOIN ndb.collectionunits AS cu ON cu.siteid = st.siteid
31+
INNER JOIN ndb.collectiontypes AS cut ON cut.colltypeid = cu.colltypeid
32+
INNER JOIN ndb.datasets AS ds ON ds.collectionunitid = cu.collectionunitid
33+
INNER JOIN ndb.depenvttypes AS dvt ON dvt.depenvtid = cu.depenvtid
34+
INNER JOIN ndb.datasetpis AS dpi ON dpi.datasetid = ds.datasetid
35+
INNER JOIN ndb.datasettypes AS dst ON dst.datasettypeid = ds.datasettypeid
36+
INNER JOIN ndb.datasetdatabases AS dsdb ON ds.datasetid = dsdb.datasetid
37+
LEFT OUTER JOIN ndb.dsageranges AS arg ON ds.datasetid = arg.datasetid
38+
LEFT OUTER JOIN ndb.agetypes AS agetypes ON agetypes.agetypeid = arg.agetypeid
39+
INNER JOIN ndb.samples AS smp ON smp.datasetid = ds.datasetid
40+
LEFT OUTER JOIN ndb.samplekeywords AS smpkw ON smpkw.sampleid = smp.sampleid
41+
INNER JOIN ndb.data AS dt ON dt.sampleid = smp.sampleid
42+
INNER JOIN ndb.variables AS var ON var.variableid = dt.variableid
43+
INNER JOIN sgp AS sgp ON st.siteid = sgp.siteid
44+
WHERE sgp.rank = 1
45+
GROUP BY st.siteid,
46+
cu.collectionunitid,
47+
st.sitename,
48+
ds.datasetid,
49+
cut.colltype,
50+
dsdb.databaseid,
51+
st.altitude,
52+
dst.datasettype,
53+
st.geog,
54+
arg.older,
55+
arg.younger,
56+
agetypes.agetype,
57+
sgp.geopol,
58+
dvt.depenvt
59+
);
60+
61+
CREATE INDEX spatialgeom ON ap.querytable USING GIST(geog);
62+
CREATE INDEX dstindex ON ap.querytable(datasettype);
63+
CREATE INDEX depenvindex ON ap.querytable(depenvt);
64+
CREATE INDEX atyindex ON ap.querytable(agetype);
65+
CREATE INDEX siteidindex ON ap.querytable(siteid);
66+
CREATE INDEX sitename ON ap.querytable USING GIST (sitename gist_trgm_ops);
67+
CREATE INDEX datasetidindex ON ap.querytable(datasetid);
68+
CREATE INDEX altitudeidx ON ap.querytable(altitude);
69+
CREATE INDEX taxonidindex ON ap.querytable USING GIN(taxa gin__int_ops);
70+
CREATE INDEX contactidindex ON ap.querytable USING GIN(contacts gin__int_ops);
71+
CREATE INDEX keywordidindex ON ap.querytable USING GIN(keywords gin__int_ops);
72+
CREATE INDEX geoidindex ON ap.querytable USING GIN(geopol gin__int_ops);
73+
CREATE INDEX olderidx ON ap.querytable(older);
74+
CREATE INDEX youngeridx ON ap.querytable(younger);
75+
76+
CREATE OR REPLACE FUNCTION ap.updatequery()
77+
RETURNS TRIGGER LANGUAGE plpgsql
78+
AS $$
79+
BEGIN
80+
REFRESH MATERIALIZED VIEW CONCURRENTLY ap.querytable;
81+
RETURN NULL;
82+
END $$;
83+
84+
CREATE TRIGGER updatewidequeryst
85+
AFTER INSERT OR DELETE
86+
ON ndb.sites
87+
FOR EACH STATEMENT
88+
EXECUTE PROCEDURE ap.updategpsites();
89+
90+
CREATE TRIGGER updatewidequeryds
91+
AFTER INSERT OR DELETE
92+
ON ndb.datasets
93+
FOR EACH STATEMENT
94+
EXECUTE PROCEDURE ap.updategpsites();

‎helpers/geopolnames_withTrigger.sql‎

Lines changed: 27 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,27 @@
1+
DROP MATERIALIZED VIEW ap.geopolnames;
2+
CREATE MATERIALIZED VIEW ap.geopolnames AS (
3+
SELECT DISTINCT siteid, array_agg(geopoliticalname) AS names FROM
4+
(SELECT DISTINCT p.siteid, gpu.geopoliticalname, gpu.rank
5+
FROM ndb.geopoliticalunits AS gpu
6+
INNER JOIN
7+
(SELECT p.siteid,
8+
UNNEST(p.geopol)
9+
FROM ap.querytable AS p) AS p ON p.unnest = gpu.geopoliticalid
10+
ORDER BY p.siteid, gpu.rank ASC) AS sq
11+
GROUP BY sq.siteid);
12+
13+
CREATE INDEX gpsiteidindex ON ap.geopolnames(siteid);
14+
15+
CREATE OR REPLACE FUNCTION ap.updategpsites()
16+
RETURNS TRIGGER LANGUAGE plpgsql
17+
AS $$
18+
BEGIN
19+
REFRESH MATERIALIZED VIEW CONCURRENTLY ap.geopolnames;
20+
RETURN NULL;
21+
END $$;
22+
23+
CREATE TRIGGER updategeopol
24+
AFTER INSERT OR DELETE
25+
ON ndb.sites
26+
FOR EACH STATEMENT
27+
EXECUTE PROCEDURE ap.updategpsites();

0 commit comments

Comments
 (0)