[Mapbender-commits] r5499 -
trunk/mapbender/resources/db/pgsql/UTF-8/update
svn_mapbender at osgeo.org
svn_mapbender at osgeo.org
Thu Feb 11 06:35:02 EST 2010
Author: armin11
Date: 2010-02-11 06:35:02 -0500 (Thu, 11 Feb 2010)
New Revision: 5499
Modified:
trunk/mapbender/resources/db/pgsql/UTF-8/update/metadata_pgsql_UTF-8.sql
Log:
bugfixing metadata sqls
Modified: trunk/mapbender/resources/db/pgsql/UTF-8/update/metadata_pgsql_UTF-8.sql
===================================================================
--- trunk/mapbender/resources/db/pgsql/UTF-8/update/metadata_pgsql_UTF-8.sql 2010-02-11 11:29:50 UTC (rev 5498)
+++ trunk/mapbender/resources/db/pgsql/UTF-8/update/metadata_pgsql_UTF-8.sql 2010-02-11 11:35:02 UTC (rev 5499)
@@ -430,7 +430,7 @@
CONSTRAINT wfs_featuretype_md_topic_category_fkey_featuretype_id_fkey FOREIGN KEY (fkey_featuretype_id)
REFERENCES wfs_featuretype (featuretype_id) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE CASCADE,
- CONSTRAINT wfs_featuretype_md_topic_category_fkey_md_topic_category_id_fkey FOREIGN KEY (fkey_md_topic_category_id)
+ CONSTRAINT wfs_featuretype_md_topic_category_fkey_md_topic_cat_id_fkey FOREIGN KEY (fkey_md_topic_category_id)
REFERENCES md_topic_category (md_topic_category_id) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE CASCADE
)
@@ -614,7 +614,7 @@
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE STRICT;
-ALTER FUNCTION f_collect_searchtext(integer, integer) OWNER TO "geoportal-admin";
+ALTER FUNCTION f_collect_searchtext(integer, integer) OWNER TO "postgres";
-- Function: f_collect_searchtext_wfs(integer, integer)
@@ -668,68 +668,27 @@
LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION f_getwfsmodultype(integer) OWNER TO postgres;
+-- View: search_wms_view
+-- DROP VIEW search_wms_view;
-CREATE OR REPLACE VIEW search_wms_view AS SELECT distinct on (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.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.user_mb_group_name,f_collect_custom_cat_layer(layer_id) as md_custom_cats, f_collect_inspire_cat_layer(layer_id) as md_inspire_cats,f_collect_topic_cat_layer(layer_id) as md_topic_cats, 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||','||layer_epsg.miny::text||','||layer_epsg.maxx::text||','||layer_epsg.maxy::text as bbox 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.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.user_mb_group_name 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.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.user_mb_group_name FROM ( SELECT wms.wms_id, wms.wms_title, wms.wms_abstract, wms.stateorprovince, wms.country,mb_wms_availability.availability as availability,mb_wms_availability.last_status as status ,wms.accessconstraints, f_getwms_tou(wms.wms_id) AS termsofuse, wms.wms_timestamp, wms.wms_owner, 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.fkey_mb_group_id AS user_mb_group_name FROM registrating_groups 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;
+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.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.user_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, 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
+ 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.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.user_mb_group_name, wms_uncat.wms_proxylog, wms_uncat.wms_network_access, wms_uncat.wms_pricevolume
+ 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.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.user_mb_group_name, wms_dep.wms_proxylog, wms_dep.wms_network_access, wms_dep.wms_pricevolume
+ 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, 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.fkey_mb_group_id AS user_mb_group_name
+ FROM registrating_groups 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;
---SELECT distinct on (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.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.user_mb_group_name,f_collect_custom_cat_layer(layer_id) as md_custom_cats, f_collect_inspire_cat_layer(layer_id) as md_inspire_cats,f_collect_topic_cat_layer(layer_id) as md_topic_cats, 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||','||layer_epsg.miny::text||','||layer_epsg.maxx::text||','||layer_epsg.maxy::text as bbox INTO wms_search_table_tmp6 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.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.user_mb_group_name 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.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.user_mb_group_name FROM ( SELECT wms.wms_id, wms.wms_title, wms.wms_abstract, wms.stateorprovince, wms.country,mb_wms_availability.availability as availability,mb_wms_availability.last_status as status ,wms.accessconstraints, f_getwms_tou(wms.wms_id) AS termsofuse, wms.wms_timestamp, wms.wms_owner, 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.fkey_mb_group_id AS user_mb_group_name FROM registrating_groups 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; --now 5.5 seconds
---ORDER BY fkey_md_topic_category_id, load_count DESC, wms_id, layer_pos;
---DROP TABLE wms_search_table_test;
-
---DROP INDEX idx_wst_layer_id ;
---DROP INDEX idx_wst_load_count ;
--- DROP INDEX idx_wst_searchtext ;
--- DROP INDEX idx_wst_department ;
--- DROP INDEX idx_wst_fkey_md_topic_category_id;
-
--- DROP INDEX idx_wst_wms_timestamp;
--- DROP INDEX gist_wst_the_geom;
-
-
-
-
-
-ALTER TABLE wms_search_table_tmp2 RENAME TO wms_search_table_test;
-
---CREATE INDEX idx_wst_wms_id ON wms_search_table (wms_id);
-CREATE INDEX idx_wst_layer_id ON wms_search_table_test (layer_id);
-CREATE INDEX idx_wst_load_count ON wms_search_table_test (load_count);
-CREATE INDEX idx_wst_searchtext ON wms_search_table_test (searchtext);
-CREATE INDEX idx_wst_department ON wms_search_table_test (department);
-
-
---CREATE INDEX idx_wst_fkey_md_topic_category_id ON wms_search_table_test (fkey_md_topic_category_id);
-CREATE INDEX idx_wst_wms_timestamp ON wms_search_table_test (wms_timestamp);
-
-CREATE INDEX gist_wst_the_geom ON wms_search_table_test USING GIST (the_geom GIST_GEOMETRY_OPS);
-VACUUM ANALYZE wms_search_table_test;
-
---tests
---select md_topic_category_id from md_topic_category
---select count(*) from layer_md_topic_category where fkey_md_topic_category_id=2
---select count(wms_id) from wms_search_table_test,layer_md_topic_category where wms_search_table_test.layer_id=layer_md_topic_category.fkey_layer_id and layer_md_topic_category.fkey_md_topic_category_id=2
---select layer.layer_id,layer_md_topic_category.fkey_md_topic_category_id from layer left join layer_md_topic_category on layer.layer_id=layer_md_topic_category.fkey_layer_id order by layer.layer_id desc
---wms_uncat LEFT JOIN layer_md_topic_category ON wms_uncat.layer_id =layer_md_topic_category.fkey_layer_id);
-
---CREATE INDEX idx_md_topic_category_id ON md_ (layer_id);
---insert into layer_inspire_category (fkey_layer_id,fkey_inspire_category_id) values (20656,11);
---insert into layer_custom_category (fkey_layer_id,fkey_custom_category_id) values (20656,1);
---select fkey_md_topic_category_id, count(*) from wms_search_table_test where status=1 group by fkey_md_topic_category_id;
-
-
---SELECT md_topic_category.md_topic_category_id, COUNT(*) FROM wms_search_table_test INNER JOIN layer_md_topic_category ON (layer_md_topic_category.fkey_layer_id=wms_search_table_test.layer_id) INNER JOIN md_topic_category ON (md_topic_category.md_topic_category_id=layer_md_topic_category.fkey_md_topic_category_id) WHERE searchtext LIKE '%WALD%' GROUP BY md_topic_category.md_topic_category_id
-
---SELECT md_topic_category.md_topic_category_id, md_topic_category.md_topic_category_code_de, COUNT(*) FROM wms_search_table_test INNER JOIN layer_md_topic_category ON (layer_md_topic_category.fkey_layer_id=wms_search_table_test.layer_id) INNER JOIN md_topic_category ON (md_topic_category.md_topic_category_id=layer_md_topic_category.fkey_md_topic_category_id) GROUP BY md_topic_category.md_topic_category_id, md_topic_category.md_topic_category_code_de ORDER BY count DESC
-
-
---SELECT md_topic_category.md_topic_category_id, md_topic_category.md_topic_category_code_de, COUNT(*) FROM wms_search_table_test INNER JOIN layer_md_topic_category ON (layer_md_topic_category.fkey_layer_id=wms_search_table_test.layer_id) INNER JOIN md_topic_category ON (md_topic_category.md_topic_category_id=layer_md_topic_category.fkey_md_topic_category_id) GROUP BY md_topic_category.md_topic_category_id, md_topic_category.md_topic_category_code_de ORDER BY md_topic_category_id
-
-
-- View: search_wfs_view
-- DROP VIEW search_wfs_view;
More information about the Mapbender_commits
mailing list