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

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

Incomplete - # 22558: Formatting of start/end times to conform to OpenSearch? time extension
 http://team.ceda.ac.uk/trac/ceda/ticket/22558

Now can search much faster the CEDA_ObservationCollection on all the given parameters (start, end, q, bbox).

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        -- which represents a human readable collection the stored periods
44        CREATE VIEW period_view AS
45                SELECT tm_period.id
46                        AS tm_period_id,
47                start_instant.id
48                        AS start_instant_id,
49                end_instant.id
50                        AS end_instant_id,
51                start_instant.ceda_timestamp
52                        AS start_time,
53                end_instant.ceda_timestamp
54                        AS end_time,
55                tm_period.mo_observationcollection__phenomenontime_tm_period
56                        AS observation_collection_id,
57                tm_period.om_observation__phenomenontime_tm_period_id
58                        AS observation_id
59        FROM tm_period
60        JOIN tm_instant AS start_instant
61                ON tm_period.id = start_instant.tm_period_begin_id
62        JOIN tm_instant AS end_instant
63                ON tm_period.id = end_instant.tm_period_end_id;
64        ALTER TABLE period_view OWNER TO badc;         
65        RAISE INFO 'Created VIEW period_view - owner BADC';             
66
67        --Drops the instant_view if exists
68        DROP VIEW IF EXISTS instant_view;
69        RAISE INFO 'Dropped VIEW instant_view';
70        -- Creates instant_view
71        -- which represents a human readable collection the stored start times 
72        CREATE VIEW instant_view AS
73                SELECT tm_instant.id
74                                AS tm_instant_id,
75                        tm_instant.ceda_timestamp
76                                AS start_time,
77                        tm_instant.mo_observationcollection__phenomenontime_tm_instant
78                                AS observation_collection_id,
79                        tm_instant.om_observation__phenomenontime_tm_instant_id
80                                AS observation_id
81                FROM tm_instant
82                WHERE tm_instant.tm_period_begin_id IS NULL 
83                        AND tm_instant.tm_period_end_id IS NULL 
84                        AND (tm_instant.mo_observationcollection__phenomenontime_tm_instant IS NOT NULL
85                                OR tm_instant.om_observation__phenomenontime_tm_instant_id IS NOT NULL);
86        ALTER TABLE instant_view OWNER TO badc;                         
87        RAISE INFO 'Created VIEW instant_view - owner BADC';   
88
89        --Drops the mo_observationcollection_ge_view if exists
90        DROP VIEW IF EXISTS mo_observationcollection_ge_view;
91        RAISE INFO 'Dropped VIEW mo_observationcollection_ge_view';     
92        -- Creates mo_observationcollection_ge_view     
93        -- which represents postgis encoding of the observationcollection's geometry
94        CREATE VIEW  mo_observationcollection_ge_view AS
95                SELECT mo_observationcollection.id, 
96                        create_SRID_frombbox("eastBoundLongitude", 
97                                                                "westBoundLongitude", 
98                                                                "northBoundLatitude", 
99                                                                "southBoundLatitude") as geometry
100                FROM mo_observationcollection
101                        join ex_geographicboundingbox
102                                ON mo_observationcollection.id = ex_geographicboundingbox.mo_observationcollection__geographicextent_ex_geographicboundin;
103        ALTER TABLE mo_observationcollection_ge_view OWNER TO badc;                             
104        RAISE INFO 'Created VIEW mo_observationcollection_ge_view - owner BADC';                                       
105
106        --Drops the mo_observation_ge_view if exists
107        DROP VIEW IF EXISTS mo_observation_ge_view;     
108        RAISE INFO 'Dropped VIEW mo_observation_ge_view';
109        -- Creates mo_observation_ge_view
110        -- which represents postgis encoding of the observation's geometry     
111        CREATE VIEW  mo_observation_ge_view AS
112        SELECT mo_observation.id, 
113                        create_SRID_frombbox("eastBoundLongitude", 
114                                                                "westBoundLongitude", 
115                                                                "northBoundLatitude", 
116                                                                "southBoundLatitude") as geometry
117                FROM mo_observation
118                        join ex_geographicboundingbox
119                                ON mo_observation.id = ex_geographicboundingbox.mo_observation__geographicextent_ex_geographicboundingbox;     
120        ALTER TABLE mo_observation_ge_view OWNER TO badc;                               
121        RAISE INFO 'Created VIEW mo_observation_ge_view - owner BADC'; 
122                               
123                               
124        --Drops the time_record_type if exists
125        DROP TYPE IF EXISTS time_record_type CASCADE;
126        RAISE INFO 'Dropped TYPE time_record_type';     
127        --Creates time_record_type (used by merge_period_instant_views() function)
128        CREATE TYPE time_record_type as (       
129                start_timestamp                 timestamp without time zone,
130                end_timestamp                   timestamp without time zone,
131                mo_observationcollection_id     integer,
132                om_observation_id               integer
133        );
134        RAISE INFO 'Created TYPE time_record_type';
135END;
136$$ LANGUAGE plpgsql;
137
138--Executes the patch on old DB instances
139SELECT * from  patch_ceda_moles();
140
141--Returns a table with the following tables
142-- ObservationCollection        AS observation_collection_id
143-- Observation                          AS  observation_id
144-- start_timestamp                      AS timestamp
145-- end_timestamp                        AS timestamp (NULL if does not exist)
146CREATE OR REPLACE FUNCTION merge_period_instant_views() RETURNS SETOF time_record_type AS $$
147DECLARE
148    item_record RECORD; 
149    time_record time_record_type;   
150BEGIN
151    FOR item_record IN SELECT * FROM instant_view
152        LOOP
153                time_record.start_timestamp := item_record.start_time;
154                time_record.end_timestamp := NULL;
155                time_record.mo_observationcollection_id := item_record.observation_collection_id;
156                time_record.om_observation_id := item_record.observation_id;
157                RETURN NEXT time_record;
158        END LOOP;
159
160    FOR item_record IN SELECT * FROM period_view
161        LOOP
162                time_record.start_timestamp := item_record.start_time;
163                time_record.end_timestamp := item_record.end_time;
164                time_record.mo_observationcollection_id := item_record.observation_collection_id;
165                time_record.om_observation_id := item_record.observation_id;
166                RETURN NEXT time_record;
167        END LOOP;
168RETURN;
169END;
170$$ LANGUAGE plpgsql;
171
172SELECT time_record.*, md_identifier.code_search_vector FROM merge_period_instant_views() as time_record, md_identifier
173WHERE
174 time_record.mo_observationcollection_id = md_identifier.mo_observationcollection_identifier
175 OR time_record.om_observation_id = md_identifier.mo_observation_identifier;
176
177               
178--SELECT ceda_observationcollection.id,
179--ST_AsText(ST_Intersection(mo_observationcollection_ge_view.geometry, create_SRID_frombbox(1, -1, 1, -1))),
180--ph_time.end_timestamp,
181--ph_time.start_timestamp
182--from ceda_observationcollection
183--      join mo_observationcollection_ge_view()
184--              on ceda_observationcollection.id = mo_observationcollection_ge_view.id
185--      join merge_period_instant_views() as ph_time
186--              on ceda_observationcollection.id = ph_time.mo_observationcollection_id
187--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.