[Mapbender-commits] r9454 - trunk/mapbender/resources/db/pgsql/UTF-8/update

[svn_mapbender at osgeo.org]

Author: armin11
Date: 2016-05-12 03:20:19 -0700 (Thu, 12 May 2016)
New Revision: 9454

Modified:
   trunk/mapbender/resources/db/pgsql/UTF-8/update/update_2.7.4_to_2.8_pgsql_UTF-8.sql
Log:
fix for featuretype search

Modified: trunk/mapbender/resources/db/pgsql/UTF-8/update/update_2.7.4_to_2.8_pgsql_UTF-8.sql
===================================================================
--- trunk/mapbender/resources/db/pgsql/UTF-8/update/update_2.7.4_to_2.8_pgsql_UTF-8.sql	2016-05-11 13:52:38 UTC (rev 9453)
+++ trunk/mapbender/resources/db/pgsql/UTF-8/update/update_2.7.4_to_2.8_pgsql_UTF-8.sql	2016-05-12 10:20:19 UTC (rev 9454)
@@ -941,31 +941,53 @@
 DROP VIEW search_dataset_view;
 
 CREATE OR REPLACE VIEW search_dataset_view AS 
- SELECT dataset_dep.fkey_mb_user_id AS user_id, dataset_dep.dataset_id, dataset_dep.dataset_id AS metadata_id, dataset_dep.srs AS dataset_srs, dataset_dep.title, dataset_dep.abstract AS dataset_abstract, dataset_dep.accessconstraints, dataset_dep.isopen, dataset_dep.termsofuse, f_collect_searchtext_dataset(dataset_dep.dataset_id) AS searchtext, dataset_dep.dataset_timestamp, dataset_dep.department, dataset_dep.mb_group_name, dataset_dep.mb_group_title, dataset_dep.mb_group_country, CASE WHEN load_count IS NULL THEN 0 ELSE load_count END as load_count, dataset_dep.mb_group_stateorprovince, f_collect_inspire_cat_dataset(dataset_dep.dataset_id) AS md_inspire_cats, f_collect_custom_cat_dataset(dataset_dep.dataset_id) AS md_custom_cats, f_collect_topic_cat_dataset(dataset_dep.dataset_id) AS md_topic_cats, dataset_dep.bbox AS the_geom, (((((st_xmin(dataset_dep.bbox::box3d)::text || ','::text) || st_ymin(dataset_dep.bbox::box3d)::text) || ','::text) || st_xmax(dataset_dep.bbox::box3d)::tex
 t) || ','::text) || st_ymax(dataset_dep.bbox::box3d)::text AS bbox, dataset_dep.preview_url, dataset_dep.fileidentifier, f_get_coupled_resources(dataset_dep.dataset_id) AS coupled_resources, dataset_dep.mb_group_logo_path, dataset_dep.timeBegin, dataset_dep.timeEnd 
+
+SELECT DISTINCT ON (metadata_id) * FROM ( SELECT dataset_dep.fkey_mb_user_id AS user_id, dataset_dep.dataset_id, dataset_dep.dataset_id AS metadata_id, dataset_dep.srs AS dataset_srs, dataset_dep.title, dataset_dep.abstract AS dataset_abstract, dataset_dep.accessconstraints, dataset_dep.isopen, dataset_dep.termsofuse, f_collect_searchtext_dataset(dataset_dep.dataset_id) AS searchtext, dataset_dep.dataset_timestamp, dataset_dep.department, dataset_dep.mb_group_name, dataset_dep.mb_group_title, dataset_dep.mb_group_country, CASE WHEN load_count IS NULL THEN 0 ELSE load_count END as load_count, dataset_dep.mb_group_stateorprovince, f_collect_inspire_cat_dataset(dataset_dep.dataset_id) AS md_inspire_cats, f_collect_custom_cat_dataset(dataset_dep.dataset_id) AS md_custom_cats, f_collect_topic_cat_dataset(dataset_dep.dataset_id) AS md_topic_cats, dataset_dep.bbox AS the_geom, (((((st_xmin(dataset_dep.bbox::box3d)::text || ','::text) || st_ymin(dataset_dep.bbox::box3d)::text) || ','::text)
  || st_xmax(dataset_dep.bbox::box3d)::text) || ','::text) || st_ymax(dataset_dep.bbox::box3d)::text AS bbox, dataset_dep.preview_url, dataset_dep.fileidentifier, f_get_coupled_resources(dataset_dep.dataset_id) AS coupled_resources, dataset_dep.mb_group_logo_path, dataset_dep.timeBegin, dataset_dep.timeEnd 
    FROM ( SELECT mb_metadata.the_geom AS bbox, mb_metadata.ref_system AS srs, mb_metadata.metadata_id AS dataset_id, mb_metadata.title, mb_metadata.abstract, mb_metadata.lastchanged AS dataset_timestamp, mb_metadata.tmp_reference_1 as timeBegin, mb_metadata.tmp_reference_2 as timeEnd, mb_metadata.uuid AS fileidentifier, mb_metadata.preview_image AS preview_url, mb_metadata.load_count as load_count, mb_metadata.fkey_mb_user_id, mb_metadata.constraints as accessconstraints, f_getmd_tou(mb_metadata.metadata_id) AS termsofuse, f_tou_isopen(f_getmd_tou(mb_metadata.metadata_id)) AS isopen, user_dep.mb_group_id AS department, user_dep.mb_group_name, user_dep.mb_group_title, user_dep.mb_group_country, user_dep.mb_group_stateorprovince, user_dep.mb_group_logo_path
            FROM ( SELECT registrating_groups.fkey_mb_user_id AS mb_user_id, mb_group.mb_group_id, mb_group.mb_group_name, mb_group.mb_group_title, mb_group.mb_group_country, mb_group.mb_group_stateorprovince, mb_group.mb_group_logo_path
                    FROM registrating_groups, mb_group
-                  WHERE registrating_groups.fkey_mb_group_id = mb_group.mb_group_id) user_dep, (SELECT mb_metadata.* ,metadata_load_count.load_count FROM mb_metadata LEFT JOIN metadata_load_count ON mb_metadata.metadata_id = metadata_load_count.fkey_metadata_id) as mb_metadata
+                  WHERE registrating_groups.fkey_mb_group_id = mb_group.mb_group_id) user_dep, (SELECT mb_metadata.* ,metadata_load_count.load_count FROM mb_metadata LEFT JOIN metadata_load_count ON mb_metadata.metadata_id = metadata_load_count.fkey_metadata_id) AS mb_metadata
           WHERE user_dep.mb_user_id = mb_metadata.fkey_mb_user_id AND mb_metadata.the_geom IS NOT NULL) dataset_dep
-  ORDER BY dataset_dep.dataset_id;
+  ORDER BY dataset_dep.dataset_id) AS datasets;
 
 ALTER TABLE search_dataset_view
   OWNER TO postgres;
 
 DROP VIEW search_wfs_view;
 CREATE OR REPLACE VIEW search_wfs_view AS 
-SELECT *, geometryfromtext(((((((((((((((((((('POLYGON(('::text || wfs_without_geom.featuretype_latlon_array[1]::text) || ' '::text) || wfs_without_geom.featuretype_latlon_array[2]::text) || ','::text) || wfs_without_geom.featuretype_latlon_array[1]::text) || ' '::text) || wfs_without_geom.featuretype_latlon_array[4]::text) || ','::text) || wfs_without_geom.featuretype_latlon_array[3]::text) || ' '::text) || wfs_without_geom.featuretype_latlon_array[4]::text) || ','::text) || wfs_without_geom.featuretype_latlon_array[3]::text) || ' '::text) || wfs_without_geom.featuretype_latlon_array[2]::text) || ','::text) || wfs_without_geom.featuretype_latlon_array[1]::text) || ' '::text) || wfs_without_geom.featuretype_latlon_array[2]::text) || '))'::text, 4326) AS the_geom, (((((wfs_without_geom.featuretype_latlon_array[1]::text || ','::text) || wfs_without_geom.featuretype_latlon_array[2]::text) || ','::text) || wfs_without_geom.featuretype_latlon_array[3]::text) || ','::text) || wfs_without_
 geom.featuretype_latlon_array[4]::text AS bbox FROM (SELECT wfs_dep.wfs_id, wfs_dep.wfs_title, wfs_dep.wfs_abstract, wfs_dep.administrativearea, wfs_dep.country, wfs_dep.accessconstraints, wfs_dep.termsofuse, wfs_dep.isopen, wfs_dep.wfs_owner, wfs_featuretype.featuretype_id, wfs_featuretype.featuretype_srs, wfs_featuretype.featuretype_title, wfs_featuretype.featuretype_abstract, f_collect_searchtext_wfs(wfs_dep.wfs_id, wfs_featuretype.featuretype_id) AS searchtext, wfs_element.element_type, wfs_conf.wfs_conf_id, wfs_conf.wfs_conf_abstract, wfs_conf.wfs_conf_description, f_getwfsmodultype(wfs_conf.wfs_conf_id) AS modultype, wfs_dep.wfs_timestamp, wfs_dep.department, wfs_dep.mb_group_name, wfs_dep.mb_group_logo_path, wfs_dep.wfs_network_access, wfs_dep.wfs_pricevolume, wfs_dep.wfs_proxylog, wfs_featuretype.featuretype_latlon_bbox, CASE WHEN wfs_featuretype.featuretype_latlon_bbox='' THEN string_to_array('-180,-90,180,90',',') WHEN wfs_featuretype.featuretype_latlon_bbox IS NULL THEN s
 tring_to_array('-180,-90,180,90',',') ELSE string_to_array(wfs_featuretype.featuretype_latlon_bbox,',') END AS featuretype_latlon_array 
-   FROM ( SELECT wfs.wfs_id, wfs.wfs_title, wfs.wfs_abstract, wfs.administrativearea, wfs.country, wfs.accessconstraints, f_getwfs_tou(wfs.wfs_id) AS termsofuse, f_tou_isopen(f_getwfs_tou(wfs.wfs_id)) AS isopen, wfs.wfs_timestamp, wfs.wfs_owner, wfs.wfs_proxylog, wfs.wfs_network_access, wfs.wfs_pricevolume,  user_dep.mb_group_id AS department, user_dep.mb_group_name, user_dep.mb_group_logo_path
-           FROM ( SELECT registrating_groups.fkey_mb_user_id AS mb_user_id, mb_group.mb_group_id, mb_group.mb_group_name, mb_group.mb_group_logo_path
-                   FROM registrating_groups, mb_group
-                  WHERE registrating_groups.fkey_mb_group_id = mb_group.mb_group_id) user_dep, wfs
-          WHERE user_dep.mb_user_id = wfs.wfs_owner) wfs_dep, wfs_featuretype, wfs_element, wfs_conf
-  WHERE wfs_featuretype.fkey_wfs_id = wfs_dep.wfs_id AND wfs_featuretype.featuretype_searchable = 1 AND wfs_element.element_type::text ~~ '%Type'::text AND wfs_featuretype.featuretype_id = wfs_element.fkey_featuretype_id AND wfs_featuretype.featuretype_id = wfs_conf.fkey_featuretype_id
-  ORDER BY wfs_featuretype.featuretype_id) AS wfs_without_geom;
 
+SELECT wfs_without_geom.wfs_id, wfs_without_geom.wfs_title, wfs_without_geom.wfs_abstract, wfs_without_geom.administrativearea, wfs_without_geom.country, wfs_without_geom.accessconstraints, wfs_without_geom.termsofuse, wfs_without_geom.isopen, wfs_without_geom.wfs_owner, wfs_without_geom.featuretype_id, wfs_without_geom.featuretype_srs, wfs_without_geom.featuretype_title, wfs_without_geom.featuretype_abstract, wfs_without_geom.searchtext, wfs_without_geom.element_type, wfs_without_geom.wfs_conf_id, wfs_without_geom.wfs_conf_abstract, wfs_without_geom.wfs_conf_description, wfs_without_geom.modultype, wfs_without_geom.wfs_timestamp, wfs_without_geom.department, wfs_without_geom.mb_group_name, wfs_without_geom.mb_group_logo_path, wfs_without_geom.wfs_network_access, wfs_without_geom.wfs_pricevolume, wfs_without_geom.wfs_proxylog, wfs_without_geom.featuretype_latlon_bbox, wfs_without_geom.featuretype_latlon_array, geometryfromtext(((((((((((((((((((('POLYGON(('::text || wfs_without_geom
 .featuretype_latlon_array[1]) || ' '::text) || wfs_without_geom.featuretype_latlon_array[2]) || ','::text) || wfs_without_geom.featuretype_latlon_array[1]) || ' '::text) || wfs_without_geom.featuretype_latlon_array[4]) || ','::text) || wfs_without_geom.featuretype_latlon_array[3]) || ' '::text) || wfs_without_geom.featuretype_latlon_array[4]) || ','::text) || wfs_without_geom.featuretype_latlon_array[3]) || ' '::text) || wfs_without_geom.featuretype_latlon_array[2]) || ','::text) || wfs_without_geom.featuretype_latlon_array[1]) || ' '::text) || wfs_without_geom.featuretype_latlon_array[2]) || '))'::text, 4326) AS the_geom, (((((wfs_without_geom.featuretype_latlon_array[1] || ','::text) || wfs_without_geom.featuretype_latlon_array[2]) || ','::text) || wfs_without_geom.featuretype_latlon_array[3]) || ','::text) || wfs_without_geom.featuretype_latlon_array[4] AS bbox
+   FROM ( 
+
+--new search wfs view with all featuretypes
+DROP VIEW search_wfs_view;
+CREATE OR REPLACE VIEW search_wfs_view AS 
+
+SELECT wfs_without_geom.wfs_id, wfs_without_geom.wfs_title, wfs_without_geom.wfs_abstract, wfs_without_geom.administrativearea, wfs_without_geom.country, wfs_without_geom.accessconstraints, wfs_without_geom.termsofuse, wfs_without_geom.isopen, wfs_without_geom.wfs_owner, wfs_without_geom.featuretype_id, wfs_without_geom.featuretype_srs, wfs_without_geom.featuretype_title, wfs_without_geom.featuretype_abstract, wfs_without_geom.searchtext, wfs_without_geom.element_type, wfs_without_geom.wfs_conf_id, wfs_without_geom.wfs_conf_abstract, wfs_without_geom.wfs_conf_description, wfs_without_geom.modultype, wfs_without_geom.wfs_timestamp, wfs_without_geom.department, wfs_without_geom.mb_group_name, wfs_without_geom.mb_group_logo_path, wfs_without_geom.wfs_network_access, wfs_without_geom.wfs_pricevolume, wfs_without_geom.wfs_proxylog, wfs_without_geom.featuretype_latlon_bbox, wfs_without_geom.featuretype_latlon_array, geometryfromtext(((((((((((((((((((('POLYGON(('::text || wfs_without_geom
 .featuretype_latlon_array[1]) || ' '::text) || wfs_without_geom.featuretype_latlon_array[2]) || ','::text) || wfs_without_geom.featuretype_latlon_array[1]) || ' '::text) || wfs_without_geom.featuretype_latlon_array[4]) || ','::text) || wfs_without_geom.featuretype_latlon_array[3]) || ' '::text) || wfs_without_geom.featuretype_latlon_array[4]) || ','::text) || wfs_without_geom.featuretype_latlon_array[3]) || ' '::text) || wfs_without_geom.featuretype_latlon_array[2]) || ','::text) || wfs_without_geom.featuretype_latlon_array[1]) || ' '::text) || wfs_without_geom.featuretype_latlon_array[2]) || '))'::text, 4326) AS the_geom, (((((wfs_without_geom.featuretype_latlon_array[1] || ','::text) || wfs_without_geom.featuretype_latlon_array[2]) || ','::text) || wfs_without_geom.featuretype_latlon_array[3]) || ','::text) || wfs_without_geom.featuretype_latlon_array[4] AS bbox
+   FROM ( 
+
+
+SELECT wfs_element.*, wfs_conf.wfs_conf_id, wfs_conf.wfs_conf_abstract, wfs_conf.wfs_conf_description, f_getwfsmodultype(wfs_conf.wfs_conf_id) AS modultype FROM (SELECT wfs_dep.wfs_id, wfs_dep.wfs_title, wfs_dep.wfs_abstract, wfs_dep.administrativearea, wfs_dep.country, wfs_dep.accessconstraints, wfs_dep.termsofuse, wfs_dep.isopen, wfs_dep.wfs_owner, wfs_featuretype.featuretype_id, wfs_featuretype.featuretype_srs, wfs_featuretype.featuretype_title, wfs_featuretype.featuretype_abstract, f_collect_searchtext_wfs(wfs_dep.wfs_id, wfs_featuretype.featuretype_id) AS searchtext, wfs_element.element_type, wfs_dep.wfs_timestamp, wfs_dep.department, wfs_dep.mb_group_name, wfs_dep.mb_group_logo_path, wfs_dep.wfs_network_access, wfs_dep.wfs_pricevolume, wfs_dep.wfs_proxylog, wfs_featuretype.featuretype_latlon_bbox, 
+                CASE
+                    WHEN wfs_featuretype.featuretype_latlon_bbox::text = ''::text THEN string_to_array('-180,-90,180,90'::text, ','::text)
+                    WHEN wfs_featuretype.featuretype_latlon_bbox IS NULL THEN string_to_array('-180,-90,180,90'::text, ','::text)
+                    ELSE string_to_array(wfs_featuretype.featuretype_latlon_bbox::text, ','::text)
+                END AS featuretype_latlon_array
+           FROM ( SELECT wfs.wfs_id, wfs.wfs_title, wfs.wfs_abstract, wfs.administrativearea, wfs.country, wfs.accessconstraints, f_getwfs_tou(wfs.wfs_id) AS termsofuse, f_tou_isopen(f_getwfs_tou(wfs.wfs_id)) AS isopen, wfs.wfs_timestamp, wfs.wfs_owner, wfs.wfs_proxylog, wfs.wfs_network_access, wfs.wfs_pricevolume, user_dep.mb_group_id AS department, user_dep.mb_group_name, user_dep.mb_group_logo_path
+                   FROM ( SELECT registrating_groups.fkey_mb_user_id AS mb_user_id, mb_group.mb_group_id, mb_group.mb_group_name, mb_group.mb_group_logo_path
+                           FROM registrating_groups, mb_group
+                          WHERE registrating_groups.fkey_mb_group_id = mb_group.mb_group_id) user_dep, wfs
+                  WHERE user_dep.mb_user_id = wfs.wfs_owner) wfs_dep, wfs_featuretype, wfs_element
+          WHERE wfs_featuretype.fkey_wfs_id = wfs_dep.wfs_id AND wfs_featuretype.featuretype_searchable = 1 AND wfs_element.element_type::text ~~ '%Type'::text AND wfs_featuretype.featuretype_id = wfs_element.fkey_featuretype_id
+          ORDER BY wfs_featuretype.featuretype_id) AS wfs_element LEFT OUTER JOIN wfs_conf ON wfs_element.featuretype_id = wfs_conf.fkey_featuretype_id
+
+) wfs_without_geom;
+
+
 ALTER TABLE search_wfs_view
   OWNER TO postgres;
 
+
 -- Table: metadata_load_count
 
 -- DROP TABLE metadata_load_count;