[Mapbender-commits] r8863 - trunk/mapbender/resources/db/pgsql/UTF-8/update
svn_mapbender at osgeo.org
svn_mapbender at osgeo.org
Wed May 14 06:51:01 PDT 2014
Author: armin11
Date: 2014-05-14 06:51:01 -0700 (Wed, 14 May 2014)
New Revision: 8863
Modified:
trunk/mapbender/resources/db/pgsql/UTF-8/update/update_2.7.4_to_2.8_pgsql_UTF-8.sql
Log:
Possibility to give back sdi downloadoptions for a special layer in the searchinterface - all related metadata uuids, that have connected download options will be collected and in the search index.
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 2014-05-14 09:23:24 UTC (rev 8862)
+++ trunk/mapbender/resources/db/pgsql/UTF-8/update/update_2.7.4_to_2.8_pgsql_UTF-8.sql 2014-05-14 13:51:01 UTC (rev 8863)
@@ -369,5 +369,76 @@
ALTER TABLE mb_metadata ALTER COLUMN inspire_whole_area TYPE varchar;
ALTER TABLE mb_metadata ALTER COLUMN inspire_actual_coverage TYPE varchar;
+--
+--new function to get all related download options for a given layer_id
+--
+-- Function: f_get_download_options_for_layer(integer)
+-- DROP FUNCTION f_get_download_options_for_layer(integer);
+CREATE OR REPLACE FUNCTION f_get_download_options_for_layer(integer)
+ RETURNS text AS
+$BODY$DECLARE
+ i_layer_id ALIAS FOR $1;
+ md_uuids_string TEXT;
+ md_uuid_record RECORD;
+
+BEGIN
+md_uuids_string := '';
+
+FOR md_uuid_record IN SELECT DISTINCT
+
+uuid from mb_metadata ,
+
+(
+
+select layer.layer_id as resource_id, 'layer' as resource_type , fkey_metadata_id as md_id from layer inner join ows_relation_metadata on layer.layer_id = ows_relation_metadata.fkey_layer_id where ows_relation_metadata.fkey_metadata_id in
+(select mb_metadata.metadata_id from ows_relation_metadata inner join mb_metadata on ows_relation_metadata.fkey_metadata_id = mb_metadata.metadata_id where fkey_layer_id = $1)
+and layer.inspire_download = 1 and layer_searchable = 1
+
+union
+
+select wfs_featuretype.featuretype_id as resource_id, 'featuretype' as resource_type , fkey_metadata_id as md_id from wfs_featuretype inner join ows_relation_metadata on wfs_featuretype.featuretype_id = ows_relation_metadata.fkey_featuretype_id where ows_relation_metadata.fkey_metadata_id in
+(select mb_metadata.metadata_id from ows_relation_metadata inner join mb_metadata on ows_relation_metadata.fkey_metadata_id = mb_metadata.metadata_id where fkey_layer_id = $1) and wfs_featuretype.inspire_download = 1 and wfs_featuretype.featuretype_searchable = 1
+
+union
+
+select mb_metadata.metadata_id as resource_id, 'metadata' as resource_type , mb_metadata.metadata_id as md_id from mb_metadata where metadata_id in (select mb_metadata.metadata_id from ows_relation_metadata inner join mb_metadata on ows_relation_metadata.fkey_metadata_id = mb_metadata.metadata_id where fkey_layer_id = $1) and mb_metadata.inspire_download = 1
+
+) as download_options where mb_metadata.metadata_id = download_options.md_id
+
+LOOP
+md_uuids_string := md_uuids_string || '{' ||md_uuid_record.uuid || '}';
+END LOOP ;
+
+RETURN md_uuids_string;
+
+END;
+$BODY$
+ LANGUAGE plpgsql VOLATILE STRICT
+ COST 100;
+ALTER FUNCTION f_get_download_options_for_layer(integer)
+ OWNER TO postgres;
+
+-- alter view for list of metadata uuids that are connected to download services
+
+
+DROP VIEW search_wms_view CASCADE;
+
+CREATE OR REPLACE VIEW search_wms_view AS
+ SELECT DISTINCT ON (wms_unref.layer_id) wms_unref.wms_id, wms_unref.availability, wms_unref.status, wms_unref.wms_title, wms_unref.wms_abstract, wms_unref.stateorprovince, wms_unref.country, wms_unref.accessconstraints, wms_unref.termsofuse, wms_unref.isopen, wms_unref.wms_owner, wms_unref.layer_id, wms_unref.epsg, wms_unref.layer_title, wms_unref.layer_abstract, wms_unref.layer_name, wms_unref.layer_parent, wms_unref.layer_pos, wms_unref.layer_queryable, wms_unref.load_count, wms_unref.searchtext, wms_unref.wms_timestamp, wms_unref.department, wms_unref.mb_group_name, f_collect_custom_cat_layer(wms_unref.layer_id) AS md_custom_cats, f_collect_inspire_cat_layer(wms_unref.layer_id) AS md_inspire_cats, f_collect_topic_cat_layer(wms_unref.layer_id) AS md_topic_cats, f_get_download_options_for_layer(wms_unref.layer_id) as md_download_options, geometryfromtext(((((((((((((((((((('POLYGON(('::text || layer_epsg.minx::text) || ' '::text) || layer_epsg.miny::text) || ','::text) ||
layer_epsg.minx::text) || ' '::text) || layer_epsg.maxy::text) || ','::text) || layer_epsg.maxx::text) || ' '::text) || layer_epsg.maxy::text) || ','::text) || layer_epsg.maxx::text) || ' '::text) || layer_epsg.miny::text) || ','::text) || layer_epsg.minx::text) || ' '::text) || layer_epsg.miny::text) || '))'::text, 4326) AS the_geom, (((((layer_epsg.minx::text || ','::text) || layer_epsg.miny::text) || ','::text) || layer_epsg.maxx::text) || ','::text) || layer_epsg.maxy::text AS bbox, wms_unref.wms_proxylog, wms_unref.wms_network_access, wms_unref.wms_pricevolume, wms_unref.mb_group_logo_path
+ FROM ( SELECT wms_uncat.wms_id, wms_uncat.availability, wms_uncat.status, wms_uncat.wms_title, wms_uncat.wms_abstract, wms_uncat.stateorprovince, wms_uncat.country, wms_uncat.accessconstraints, wms_uncat.termsofuse, wms_uncat.isopen, wms_uncat.wms_owner, wms_uncat.layer_id, wms_uncat.epsg, wms_uncat.layer_title, wms_uncat.layer_abstract, wms_uncat.layer_name, wms_uncat.layer_parent, wms_uncat.layer_pos, wms_uncat.layer_queryable, wms_uncat.load_count, wms_uncat.searchtext, wms_uncat.wms_timestamp, wms_uncat.department, wms_uncat.mb_group_name, wms_uncat.wms_proxylog, wms_uncat.wms_network_access, wms_uncat.wms_pricevolume, wms_uncat.mb_group_logo_path
+ FROM ( SELECT wms_dep.wms_id, wms_dep.availability, wms_dep.status, wms_dep.wms_title, wms_dep.wms_abstract, wms_dep.stateorprovince, wms_dep.country, wms_dep.accessconstraints, wms_dep.termsofuse, wms_dep.isopen, wms_dep.wms_owner, layer.layer_id, f_collect_epsg(layer.layer_id) AS epsg, layer.layer_title, layer.layer_abstract, layer.layer_name, layer.layer_parent, layer.layer_pos, layer.layer_queryable, f_layer_load_count(layer.layer_id) AS load_count, f_collect_searchtext(wms_dep.wms_id, layer.layer_id) AS searchtext, wms_dep.wms_timestamp, wms_dep.department, wms_dep.mb_group_name, wms_dep.wms_proxylog, wms_dep.wms_network_access, wms_dep.wms_pricevolume, wms_dep.mb_group_logo_path
+ FROM ( SELECT wms.wms_id, wms.wms_title, wms.wms_abstract, wms.stateorprovince, wms.country, mb_wms_availability.availability, mb_wms_availability.last_status AS status, wms.accessconstraints, f_getwms_tou(wms.wms_id) AS termsofuse, f_tou_isopen(f_getwms_tou(wms.wms_id)) as isopen, wms.wms_timestamp, wms.wms_owner, wms.wms_proxylog, wms.wms_network_access, wms.wms_pricevolume, user_dep.fkey_mb_group_id AS department, user_dep.fkey_mb_group_id, user_dep.fkey_mb_group_id AS wms_department, user_dep.mb_group_name, user_dep.mb_group_logo_path
+ FROM ( SELECT registrating_groups.fkey_mb_user_id, mb_group.mb_group_id AS fkey_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, wms, mb_wms_availability
+ WHERE wms.wms_owner = user_dep.fkey_mb_user_id AND wms.wms_id = mb_wms_availability.fkey_wms_id) wms_dep, layer
+ WHERE layer.fkey_wms_id = wms_dep.wms_id AND layer.layer_searchable = 1) wms_uncat) wms_unref, layer_epsg
+ WHERE layer_epsg.epsg::text = 'EPSG:4326'::text AND wms_unref.layer_id = layer_epsg.fkey_layer_id
+ ORDER BY wms_unref.layer_id;
+
+ALTER TABLE search_wms_view
+ OWNER TO postgres;
+
+
More information about the Mapbender_commits
mailing list