source: mauRepo/MolesManager/trunk/cedaMoles/MolesManager/db/ceda_customization.sql @ 8558

Subversion URL: http://proj.badc.rl.ac.uk/svn/ndg/mauRepo/MolesManager/trunk/cedaMoles/MolesManager/db/ceda_customization.sql@8558
Revision 8558, 3.4 KB checked in by mnagni, 7 years ago (diff)

Incomplete - # 22551: List resources / display single file
 http://team.ceda.ac.uk/trac/ceda/ticket/22551

Line 
1CREATE VIEW period_view AS
2SELECT tm_period.id as tm_period_id,
3        start_instant.id as start_instant_id,
4        end_instant.id as end_instant_id,
5        start_instant.ceda_timestamp as start_time,
6        end_instant.ceda_timestamp as end_time,
7        tm_period.mo_observationcollection__phenomenontime_tm_period as observation_collection_id,
8        tm_period.om_observation__phenomenontime_tm_period_id as observation_id
9FROM tm_period
10JOIN tm_instant as start_instant
11        ON tm_period.id = start_instant.tm_period_begin_id
12JOIN tm_instant as end_instant
13        ON tm_period.id = end_instant.tm_period_end_id;
14
15CREATE VIEW instant_view AS
16SELECT tm_instant.id as tm_instant_id,
17        tm_instant.ceda_timestamp as start_time,
18        tm_instant.mo_observationcollection__phenomenontime_tm_instant as observation_collection_id,
19        tm_instant.om_observation__phenomenontime_tm_instant_id as observation_id
20FROM tm_instant
21WHERE tm_instant.tm_period_begin_id IS NULL 
22        AND tm_instant.tm_period_end_id IS NULL 
23        AND (tm_instant.mo_observationcollection__phenomenontime_tm_instant IS NOT NULL
24                OR tm_instant.om_observation__phenomenontime_tm_instant_id IS NOT NULL);
25               
26CREATE TYPE time_record_type as (       
27        start_timestamp                 timestamp without time zone,
28        end_timestamp                   timestamp without time zone,
29        mo_observationcollection_id     integer,
30        om_observation_id               integer
31        );             
32       
33CREATE OR REPLACE FUNCTION merge_period_instant_views() RETURNS SETOF time_record_type AS $$
34DECLARE
35    item_record RECORD; 
36    time_record time_record_type;   
37BEGIN
38    FOR item_record IN SELECT * FROM instant_view
39        LOOP
40                time_record.start_timestamp := item_record.start_time;
41                time_record.end_timestamp := NULL;
42                time_record.mo_observationcollection_id := item_record.observation_collection_id;
43                time_record.om_observation_id := item_record.observation_id;
44                RETURN NEXT time_record;
45        END LOOP;
46
47    FOR item_record IN SELECT * FROM period_view
48        LOOP
49                time_record.start_timestamp := item_record.start_time;
50                time_record.end_timestamp := item_record.end_time;
51                time_record.mo_observationcollection_id := item_record.observation_collection_id;
52                time_record.om_observation_id := item_record.observation_id;
53                RETURN NEXT time_record;
54        END LOOP;
55RETURN;
56END;
57$$ LANGUAGE plpgsql;
58
59SELECT time_record.*, md_identifier.code_search_vector FROM merge_period_instant_views() as time_record, md_identifier
60WHERE
61 time_record.mo_observationcollection_id = md_identifier.mo_observationcollection_identifier
62 OR time_record.om_observation_id = md_identifier.mo_observation_identifier
63
64--Returns a POLYGON representing the given BoundingBox
65CREATE OR REPLACE FUNCTION create_SRID_frombbox(west numeric, south numeric, east numeric, north numeric) 
66        RETURNS geometry AS $$
67BEGIN
68        RETURN ST_SetSRID(ST_MakeBox2D(ST_Point(west, south), ST_Point(east, north)), 4326);
69END;
70$$ LANGUAGE plpgsql;
71
72CREATE VIEW  mo_observationcollection_ge
73SELECT mo_observationcollection.id, create_SRID_frombbox("eastBoundLongitude", "westBoundLongitude", "northBoundLatitude", "southBoundLatitude")
74FROM mo_observationcollection
75        join ex_geographicboundingbox
76                ON mo_observationcollection.id = ex_geographicboundingbox.mo_observationcollection__geographicextent_ex_geographicboundin
77
78CREATE VIEW  mo_observation_ge
79SELECT mo_observation.id, create_SRID_frombbox("eastBoundLongitude", "westBoundLongitude", "northBoundLatitude", "southBoundLatitude")
80FROM mo_observation
81        join ex_geographicboundingbox
82                ON mo_observation.id = ex_geographicboundingbox.mo_observation__geographicextent_ex_geographicboundingbox
83
Note: See TracBrowser for help on using the repository browser.