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

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

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

fixes an error on parsing datatime module

Line 
1-- Patch for new releases
2--Returns a POLYGON representing the given BoundingBox
3CREATE OR REPLACE FUNCTION create_SRID_frombbox(west numeric, south numeric, east numeric, north numeric) 
4        RETURNS geometry AS $$
5BEGIN
6        RETURN ST_SetSRID(ST_MakeBox2D(ST_Point(west, south), ST_Point(east, north)), 4326);
7END;
8$$ LANGUAGE plpgsql;
9
10CREATE OR REPLACE FUNCTION patch_ceda_moles() RETURNS void AS $$
11DECLARE
12    col_name varchar;       
13BEGIN
14        --Checks if tm_instant.column exists
15        SELECT attname INTO col_name FROM pg_attribute
16                WHERE attrelid = (SELECT oid FROM pg_class WHERE relname = 'tm_instant') 
17                AND attname = 'ceda_timestamp';
18
19        --Alters the tm_instant table is necessary
20        IF col_name is NULL THEN
21                alter table tm_instant add column ceda_timestamp timestamp;
22                RAISE INFO 'Creates tm_instant.ceda_timestamp';
23        ELSE
24                RAISE INFO 'tm_instant.ceda_timestamp already exists';
25        END IF;
26
27        --Synchronize the DB with the tm_instant.ceda_timestamp
28        UPDATE tm_instant
29        SET ceda_timestamp = to_timestamp("year" || '-0' || "month" || '-' || "day", 'YYYY-MM-DD HH24:MI:SS')
30        FROM tm_instant AS tm_inst
31                JOIN tm_position
32                        ON tm_position.tm_instant_position_id = tm_inst.id
33                JOIN date
34                        ON date.tm_position_date8601_id = tm_position.id
35        WHERE
36        tm_instant.id = tm_inst.id;
37        RAISE INFO 'updated tm_instant.ceda_timestamp column';
38
39        --Drops the period_view if exists
40        DROP VIEW IF EXISTS period_view;
41        RAISE INFO 'Dropped VIEW period_view';
42        --Creates period_view   
43        CREATE VIEW period_view AS
44                SELECT tm_period.id
45                        AS tm_period_id,
46                start_instant.id
47                        AS start_instant_id,
48                end_instant.id
49                        AS end_instant_id,
50                start_instant.ceda_timestamp
51                        AS start_time,
52                end_instant.ceda_timestamp
53                        AS end_time,
54                tm_period.mo_observationcollection__phenomenontime_tm_period
55                        AS observation_collection_id,
56                tm_period.om_observation__phenomenontime_tm_period_id
57                        AS observation_id
58        FROM tm_period
59        JOIN tm_instant AS start_instant
60                ON tm_period.id = start_instant.tm_period_begin_id
61        JOIN tm_instant AS end_instant
62                ON tm_period.id = end_instant.tm_period_end_id;
63        ALTER TABLE period_view OWNER TO badc;         
64        RAISE INFO 'Created VIEW period_view - owner BADC';             
65
66        --Drops the instant_view if exists
67        DROP VIEW IF EXISTS instant_view;
68        RAISE INFO 'Dropped VIEW instant_view';
69        --Creates instant_view 
70        CREATE VIEW instant_view AS
71                SELECT tm_instant.id
72                                AS tm_instant_id,
73                        tm_instant.ceda_timestamp
74                                AS start_time,
75                        tm_instant.mo_observationcollection__phenomenontime_tm_instant
76                                AS observation_collection_id,
77                        tm_instant.om_observation__phenomenontime_tm_instant_id
78                                AS observation_id
79                FROM tm_instant
80                WHERE tm_instant.tm_period_begin_id IS NULL 
81                        AND tm_instant.tm_period_end_id IS NULL 
82                        AND (tm_instant.mo_observationcollection__phenomenontime_tm_instant IS NOT NULL
83                                OR tm_instant.om_observation__phenomenontime_tm_instant_id IS NOT NULL);
84        ALTER TABLE instant_view OWNER TO badc;                         
85        RAISE INFO 'Created VIEW instant_view - owner BADC';   
86
87        --Drops the mo_observationcollection_ge_view if exists
88        DROP VIEW IF EXISTS mo_observationcollection_ge_view;
89        RAISE INFO 'Dropped VIEW mo_observationcollection_ge_view';     
90        --Creates mo_observationcollection_ge_view     
91        CREATE VIEW  mo_observationcollection_ge_view AS
92                SELECT mo_observationcollection.id, 
93                        create_SRID_frombbox("eastBoundLongitude", 
94                                                                "westBoundLongitude", 
95                                                                "northBoundLatitude", 
96                                                                "southBoundLatitude")
97                FROM mo_observationcollection
98                        join ex_geographicboundingbox
99                                ON mo_observationcollection.id = ex_geographicboundingbox.mo_observationcollection__geographicextent_ex_geographicboundin;
100        ALTER TABLE mo_observationcollection_ge_view OWNER TO badc;                             
101        RAISE INFO 'Created VIEW mo_observationcollection_ge_view - owner BADC';                                       
102
103        --Drops the mo_observation_ge_view if exists
104        DROP VIEW IF EXISTS mo_observation_ge_view;     
105        RAISE INFO 'Dropped VIEW mo_observation_ge_view';
106        --Creates mo_observation_ge_view       
107        CREATE VIEW  mo_observation_ge_view AS
108        SELECT mo_observation.id, 
109                        create_SRID_frombbox("eastBoundLongitude", 
110                                                                "westBoundLongitude", 
111                                                                "northBoundLatitude", 
112                                                                "southBoundLatitude")
113                FROM mo_observation
114                        join ex_geographicboundingbox
115                                ON mo_observation.id = ex_geographicboundingbox.mo_observation__geographicextent_ex_geographicboundingbox;     
116        ALTER TABLE mo_observation_ge_view OWNER TO badc;                               
117        RAISE INFO 'Created VIEW mo_observation_ge_view - owner BADC'; 
118                               
119                               
120        --Drops the time_record_type if exists
121        DROP TYPE IF EXISTS time_record_type CASCADE;
122        RAISE INFO 'Dropped TYPE time_record_type';     
123        --Creates time_record_type (used by merge_period_instant_views() function)
124        CREATE TYPE time_record_type as (       
125                start_timestamp                 timestamp without time zone,
126                end_timestamp                   timestamp without time zone,
127                mo_observationcollection_id     integer,
128                om_observation_id               integer
129        );
130        RAISE INFO 'Created TYPE time_record_type';
131END;
132$$ LANGUAGE plpgsql;
133
134--Executes the patch
135SELECT * from  patch_ceda_moles();
136
137--Returns a table with the following tables
138-- ObservationCollection        AS observation_collection_id
139-- Observation                          AS  observation_id
140-- start_timestamp                      AS timestamp
141-- end_timestamp                        AS timestamp (NULL if does not exist)
142CREATE OR REPLACE FUNCTION merge_period_instant_views() RETURNS SETOF time_record_type AS $$
143DECLARE
144    item_record RECORD; 
145    time_record time_record_type;   
146BEGIN
147    FOR item_record IN SELECT * FROM instant_view
148        LOOP
149                time_record.start_timestamp := item_record.start_time;
150                time_record.end_timestamp := NULL;
151                time_record.mo_observationcollection_id := item_record.observation_collection_id;
152                time_record.om_observation_id := item_record.observation_id;
153                RETURN NEXT time_record;
154        END LOOP;
155
156    FOR item_record IN SELECT * FROM period_view
157        LOOP
158                time_record.start_timestamp := item_record.start_time;
159                time_record.end_timestamp := item_record.end_time;
160                time_record.mo_observationcollection_id := item_record.observation_collection_id;
161                time_record.om_observation_id := item_record.observation_id;
162                RETURN NEXT time_record;
163        END LOOP;
164RETURN;
165END;
166$$ LANGUAGE plpgsql;
167
168SELECT time_record.*, md_identifier.code_search_vector FROM merge_period_instant_views() as time_record, md_identifier
169WHERE
170 time_record.mo_observationcollection_id = md_identifier.mo_observationcollection_identifier
171 OR time_record.om_observation_id = md_identifier.mo_observation_identifier;
172
173               
174--SELECT ceda_observationcollection.id,
175--ST_AsText(ST_Intersection(mo_observationcollection_ge_view.geometry, create_SRID_frombbox(1, -1, 1, -1))),
176--ph_time.end_timestamp,
177--ph_time.start_timestamp
178--from ceda_observationcollection
179--      join mo_observationcollection_ge_view()
180--              on ceda_observationcollection.id = mo_observationcollection_ge_view.id
181--      join merge_period_instant_views() as ph_time
182--              on ceda_observationcollection.id = ph_time.mo_observationcollection_id
183--WHERE ST_AsText(ST_Intersection(mo_observationcollection_ge_view.geometry, create_SRID_frombbox(1, -1, 1, -1))) = 'GEOMETRYCOLLECTION EMPTY'
Note: See TracBrowser for help on using the repository browser.