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

[svn_mapbender at osgeo.org]

Author: armin11
Date: 2017-02-20 07:43:49 -0800 (Mon, 20 Feb 2017)
New Revision: 9689

Modified:
   trunk/mapbender/resources/db/pgsql/UTF-8/update/update_2.7.4_to_2.8_pgsql_UTF-8.sql
Log:
New view for wfs metadata search - to be tested

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	2017-02-17 11:25:28 UTC (rev 9688)
+++ trunk/mapbender/resources/db/pgsql/UTF-8/update/update_2.7.4_to_2.8_pgsql_UTF-8.sql	2017-02-20 15:43:49 UTC (rev 9689)
@@ -1626,5 +1626,89 @@
 
 ALTER TABLE search_wfs_view
   OWNER TO postgres;
+--f_get_responsible_organization(integer, integer)
 
+-- DROP FUNCTION f_get_responsible_organization(integer, integer);
 
+CREATE OR REPLACE FUNCTION f_get_responsible_organization(i_user_id integer, i_group_id integer)
+  RETURNS integer AS
+$BODY$DECLARE
+  --i_user_id ALIAS FOR $1;
+  --i_group_id ALIAS FOR $2;
+  -- give i_group_id in form: coalesce(i_group_id,0), because otherwise NULL values will not be interpreted!
+  -- select f_get_responsible_organization(1,coalesce(21,0));
+  i_resp_orga_id INTEGER;
+
+BEGIN
+
+RAISE NOTICE 'group_id = %', i_group_id;
+
+IF i_group_id IS NULL OR i_group_id <= 0 THEN
+    i_resp_orga_id := fkey_mb_group_id FROM mb_user_mb_group WHERE mb_user_mb_group_type = 2 AND fkey_mb_user_id = i_user_id LIMIT 1;
+ELSE
+    i_resp_orga_id = i_group_id;
+END IF;
+
+  
+RETURN i_resp_orga_id;
+
+END;
+$BODY$
+  LANGUAGE plpgsql VOLATILE STRICT
+  COST 100;
+ALTER FUNCTION f_get_responsible_organization(integer, integer)
+  OWNER TO postgres;
+
+-- Function: f_get_geometry_type(integer)
+
+-- DROP FUNCTION f_get_geometry_type(integer);
+
+CREATE OR REPLACE FUNCTION f_get_geometry_type(integer)
+  RETURNS text AS
+$BODY$DECLARE
+  i_featuretype_id ALIAS FOR $1;
+  geometry_type TEXT;
+
+BEGIN
+
+geometry_type := element_type FROM wfs_element WHERE fkey_featuretype_id = i_featuretype_id AND element_type in ('GeometryPropertyType','MultiPolygonPropertyType','GeometryAssociationType','PointPropertyType','gml:MultiSurfacePropertyType','MultiSurfacePropertyType','MultiCurvePropertyType','MultiPointPropertyType','MultiLineStringPropertyType','LineStringPropertyType')  LIMIT 1;
+
+RETURN geometry_type;
+
+END;
+
+$BODY$
+  LANGUAGE plpgsql VOLATILE STRICT
+  COST 100;
+ALTER FUNCTION f_get_geometry_type(integer)
+  OWNER TO postgres;
+
+-- new view for wfs search
+
+-- View: search_wfs_view_2
+
+-- DROP VIEW search_wfs_view_2;
+
+CREATE OR REPLACE VIEW search_wfs_view_2 AS 
+
+SELECT wfs_new.*, isopen FROM (SELECT wfs_table.*, wfs_termsofuse.fkey_termsofuse_id FROM
+(SELECT wfs_info.*, mb_group.mb_group_id as department, mb_group.mb_group_name, mb_group_logo_path  FROM (SELECT featuretype.*, geometryfromtext(((((((((((((((((((('POLYGON(('::text || featuretype.featuretype_latlon_array[1]) || ' '::text) || featuretype.featuretype_latlon_array[2]) || ','::text) || featuretype.featuretype_latlon_array[1]) || ' '::text) || featuretype.featuretype_latlon_array[4]) || ','::text) || featuretype.featuretype_latlon_array[3]) || ' '::text) || featuretype.featuretype_latlon_array[4]) || ','::text) || featuretype.featuretype_latlon_array[3]) || ' '::text) || featuretype.featuretype_latlon_array[2]) || ','::text) || featuretype.featuretype_latlon_array[1]) || ' '::text) || featuretype.featuretype_latlon_array[2]) || '))'::text, 4326) AS the_geom, (((((featuretype.featuretype_latlon_array[1] || ','::text) || featuretype.featuretype_latlon_array[2]) || ','::text) || featuretype.featuretype_latlon_array[3]) || ','::text) || featuretype.featuretype_latlon_array[
 4] AS bbox, f_collect_searchtext_wfs(wfs_id, featuretype.featuretype_id) AS searchtext, wfs_conf_id, wfs_conf_abstract, wfs_conf_description, wfs_conf_type as modultype FROM 
+
+(SELECT DISTINCT featuretype_id, featuretype_srs, featuretype_title, featuretype_abstract,  featuretype_latlon_bbox, f_get_geometry_type(featuretype_id) AS element_type, 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, wfs.*  FROM 
+
+(SELECT DISTINCT wfs_id, wfs_title, wfs_abstract, wfs_timestamp_create, wfs_network_access, wfs_pricevolume, wfs_proxylog, wfs_owner, country, administrativearea, accessconstraints, fkey_mb_group_id, f_get_responsible_organization(wfs_owner, COALESCE(fkey_mb_group_id, 0)) AS orga_id
+  
+FROM wfs WHERE wfs_owner IN (SELECT fkey_mb_user_id FROM users_for_publishing) ORDER BY wfs_id) AS wfs 
+
+LEFT JOIN wfs_featuretype ON wfs.wfs_id = wfs_featuretype.fkey_wfs_id WHERE wfs_featuretype.featuretype_searchable = 1) AS featuretype
+
+LEFT JOIN wfs_conf ON featuretype.featuretype_id = wfs_conf.fkey_featuretype_id) AS wfs_info LEFT JOIN mb_group ON wfs_info.orga_id = mb_group.mb_group_id) AS wfs_table LEFT JOIN wfs_termsofuse ON wfs_table.wfs_id = wfs_termsofuse.fkey_wfs_id) AS wfs_new LEFT JOIN termsofuse ON wfs_new.fkey_termsofuse_id = termsofuse.termsofuse_id 
+
+ WHERE element_type IS NOT NULL;
+
+ALTER TABLE search_wfs_view_2
+  OWNER TO postgres;