[Mapbender-commits] r5488 -
trunk/mapbender/resources/db/pgsql/UTF-8/update
svn_mapbender at osgeo.org
svn_mapbender at osgeo.org
Tue Feb 9 04:12:55 EST 2010
Author: armin11
Date: 2010-02-09 04:12:53 -0500 (Tue, 09 Feb 2010)
New Revision: 5488
Modified:
trunk/mapbender/resources/db/pgsql/UTF-8/update/update_2.6.1_to_2.7rc1_pgsql_UTF-8.sql
Log:
merging database extensions for searching and classification of services from geoportal.rlp
Modified: trunk/mapbender/resources/db/pgsql/UTF-8/update/update_2.6.1_to_2.7rc1_pgsql_UTF-8.sql
===================================================================
--- trunk/mapbender/resources/db/pgsql/UTF-8/update/update_2.6.1_to_2.7rc1_pgsql_UTF-8.sql 2010-02-05 14:30:59 UTC (rev 5487)
+++ trunk/mapbender/resources/db/pgsql/UTF-8/update/update_2.6.1_to_2.7rc1_pgsql_UTF-8.sql 2010-02-09 09:12:53 UTC (rev 5488)
@@ -1176,4 +1176,811 @@
INSERT INTO gui_element_vars(fkey_gui_id, fkey_e_id, var_name, var_value, context, var_type) VALUES ('toolbox_jquery', 'jq_ui_effects', 'slide', '0', '1 = effect active', 'php_var');
INSERT INTO gui_element_vars(fkey_gui_id, fkey_e_id, var_name, var_value, context, var_type) VALUES ('toolbox_jquery', 'jq_ui_effects', 'transfer', '1', '1 = effect active', 'php_var');
-INSERT INTO gui_mb_user (fkey_gui_id, fkey_mb_user_id, mb_user_type) VALUES ('toolbox_jquery', 1, 'owner');
\ No newline at end of file
+INSERT INTO gui_mb_user (fkey_gui_id, fkey_mb_user_id, mb_user_type) VALUES ('toolbox_jquery', 1, 'owner');
+--Things to be done for merging with geoportal.rlp***
+--Adoption of the group table to allow contact information for groups
+alter table mb_group add column mb_group_title character varying(255) NOT NULL DEFAULT ''::character varying;
+alter table mb_group add column mb_group_ext_id bigint;
+alter table mb_group add column mb_group_address character varying(255) NOT NULL DEFAULT ''::character varying;
+alter table mb_group add column mb_group_postcode character varying(255) NOT NULL DEFAULT ''::character varying;
+alter table mb_group add column mb_group_city character varying(255) NOT NULL DEFAULT ''::character varying;
+alter table mb_group add column mb_group_stateorprovince character varying(255) NOT NULL DEFAULT ''::character varying;
+alter table mb_group add column mb_group_country character varying(255) NOT NULL DEFAULT ''::character varying;
+alter table mb_group add column mb_group_voicetelephone character varying(255) NOT NULL DEFAULT ''::character varying;
+alter table mb_group add column mb_group_facsimiletelephone character varying(255) NOT NULL DEFAULT ''::character varying;
+alter table mb_group add column mb_group_email character varying(255) NOT NULL DEFAULT ''::character varying;
+alter table mb_group add column mb_group_logo_path text NOT NULL DEFAULT ''::character varying;
+alter table mb_user_mb_group add column mb_user_mb_group_type integer;
+--Adoption for adminsitration of conformities in the mapbender database***
+--searchable
+ALTER TABLE layer ADD COLUMN layer_searchable integer;
+ALTER TABLE layer ALTER COLUMN layer_searchable SET STORAGE PLAIN;
+ALTER TABLE layer ALTER COLUMN layer_searchable SET DEFAULT 1;
+-- Column: featuretype_searchable
+
+-- ALTER TABLE wfs_featuretype DROP COLUMN featuretype_searchable;
+
+--ALTER TABLE wfs_featuretype ADD COLUMN featuretype_searchable integer;
+--ALTER TABLE wfs_featuretype ALTER COLUMN featuretype_searchable SET STORAGE PLAIN;
+--ALTER TABLE wfs_featuretype ALTER COLUMN featuretype_searchable SET DEFAULT 1;
+
+
+ALTER TABLE wfs_featuretype ADD COLUMN featuretype_searchable integer;
+
+
+-- Column: wms_timestamp_create
+
+-- ALTER TABLE wms DROP COLUMN wms_timestamp_create;
+ALTER TABLE wms ADD COLUMN wms_timestamp_create integer;
+ALTER TABLE wms ALTER COLUMN wms_timestamp_create SET STORAGE PLAIN;
+
+--network accessibility
+ALTER TABLE wms ADD COLUMN wms_network_access integer;
+ALTER TABLE wms ALTER COLUMN wms_network_access SET STORAGE PLAIN;
+
+
+-- Column: wfs_timestamp_create
+
+-- ALTER TABLE wfs DROP COLUMN wfs_timestamp_create;
+ALTER TABLE wfs ADD COLUMN wfs_timestamp_create integer;
+ALTER TABLE wfs ALTER COLUMN wfs_timestamp_create SET STORAGE PLAIN;
+
+--network accessibility
+ALTER TABLE wfs ADD COLUMN wfs_network_access integer;
+ALTER TABLE wfs ALTER COLUMN wfs_network_access SET STORAGE PLAIN;
+
+
+--table for inspire metadata add on fields - for layer and featuretypes
+-- Table: inspire_md_data
+
+-- DROP TABLE inspire_md_data;
+
+-- Table: inspire_md_data
+
+-- DROP TABLE inspire_md_data;
+
+CREATE TABLE inspire_md_data
+(
+ data_id serial NOT NULL,
+ data_time_begin integer, --timestamp
+ data_time_end integer, --timestamp
+ data_lineage text,
+ data_spatial_res_value varchar(255), --
+ data_spatial_res_type integer, --look up types like 1:equivalentScale, 2:Distance - see guidance paper for metadata
+ CONSTRAINT data_id_pkey PRIMARY KEY (data_id)
+)
+WITH OIDS;
+--ALTER TABLE inspire_md_data OWNER TO "postgres";
+
+
+--ok
+--Conformity is a metadata information which is defined for spatial data services and spatial data sets - the information about the conformity should be generated automatically by the service registry
+--mainly the available metadata information should be tested. This can be done when resitrating the service and when edit the service metadata.
+-- There should be a list of available specifications (ir, or regulations) wherefor the services can be tested. This list have to be stored in the database.
+
+
+-- Table: conformity
+
+-- DROP TABLE conformity;
+
+-- Table: conformity
+
+-- DROP TABLE conformity;
+
+CREATE TABLE conformity
+(
+ conformity_id serial NOT NULL,
+ conformity_key varchar(255),
+ fkey_spec_class_key character varying(255),
+ conformity_code_en character varying(255),
+ conformity_code_fr character varying(255),
+ conformity_code_de character varying(255),
+ conformity_symbol character varying(255),
+ conformity_description_de text,
+ CONSTRAINT conformity_pkey PRIMARY KEY (conformity_id)
+)
+WITH OIDS;
+
+--ALTER TABLE conformity OWNER TO "postgres";
+
+INSERT INTO conformity (fkey_spec_class_key, conformity_key, conformity_code_en, conformity_code_de, conformity_code_fr, conformity_symbol, conformity_description_de) VALUES ('inspire','1','conformant','Konform','','','Die Ressource stimmt mit der angegebenen Spezifikation in vollem Umfang überein.');
+
+INSERT INTO conformity (fkey_spec_class_key, conformity_key, conformity_code_en, conformity_code_de, conformity_code_fr, conformity_symbol, conformity_description_de) VALUES ('inspire','2','notConformant','Nicht konform','','','Die Ressource stimmt mit der angegebenen Spezifikation nicht überein.');
+
+INSERT INTO conformity (fkey_spec_class_key, conformity_key, conformity_code_en, conformity_code_de, conformity_code_fr, conformity_symbol, conformity_description_de) VALUES ('inspire','3','notEvaluated','Nicht überprüft','','','Die Übereinstimmung ist nicht überprüft worden.');
+
+--ok
+
+-- spec_classification table:
+-- DROP TABLE spec_classification;
+
+-- Table spec_classification:
+
+-- DROP TABLE spec_classification;
+
+CREATE TABLE spec_classification
+(
+ spec_class_id serial NOT NULL,
+ -- spec_key varchar(5) NOT NULL,
+ spec_class_key character varying(255) UNIQUE,
+ spec_class_code_de character varying(255),
+ spec_class_code_en character varying(255),
+ spec_class_code_fr character varying(255),
+ spec_class_description_en text,
+ spec_class_description_de text,
+ spec_class_description_fr text,
+ spec_class_timestamp integer,
+ CONSTRAINT spec_class_id_pkey PRIMARY KEY (spec_class_id)
+
+)
+WITH OIDS;
+--ALTER TABLE spec_class OWNER TO "postgres";
+INSERT INTO spec_classification (spec_class_key, spec_class_code_de, spec_class_description_de) VALUES ('inspire','INSPIRE','Klasse der Inspire Spezifikationen/Regulations');
+
+-- specification table:
+-- DROP TABLE spec;
+
+-- Table spec:
+
+-- DROP TABLE spec;
+
+CREATE TABLE spec
+(
+ spec_id serial NOT NULL,
+ spec_key varchar(50) NOT NULL,
+ spec_code_en character varying,
+ spec_code_de character varying,
+ spec_code_fr character varying,
+ spec_link_en character varying,
+ spec_link_de character varying,
+ spec_link_fr character varying,
+ spec_description_en text,
+ spec_description_de text,
+ spec_description_fr text,
+ fkey_spec_class_key character varying(255),
+ spec_timestamp integer,
+ CONSTRAINT spec_id_pkey PRIMARY KEY (spec_id),
+ CONSTRAINT spec_spec_class_fkey FOREIGN KEY (fkey_spec_class_key)
+ REFERENCES spec_classification (spec_class_key) MATCH SIMPLE
+ ON UPDATE CASCADE ON DELETE CASCADE
+
+)
+WITH OIDS;
+--ALTER TABLE spec OWNER TO "postgres";
+
+INSERT INTO spec (spec_key, spec_code_en, spec_link_en, spec_description_en, fkey_spec_class_key, spec_timestamp) VALUES ('ir_interop','INSPIRE Implementing rules laying down technical arrangements','http://www.geoportal.rlp.de/','INSPIRE Implementing rules laying down technical arrangements for the interoperability and harmonisation of orthoimagery','inspire',extract(epoch FROM (to_timestamp('2011-05-15','YYYY-MM-DD'))));
+
+--conformity relation table:
+-- DROP TABLE conformity_relation;
+
+-- Table: conformity_relation
+
+-- DROP TABLE conformity_relation;
+
+CREATE TABLE conformity_relation
+(
+ relation_id serial NOT NULL,
+ fkey_wms_id integer,
+ fkey_wfs_id integer,
+ fkey_inspire_md_id integer,
+ fkey_conformity_id integer,
+ fkey_spec_id integer,
+ CONSTRAINT relation_id_pkey PRIMARY KEY (relation_id),
+ CONSTRAINT conformity_relation_wms_id_fkey FOREIGN KEY (fkey_wms_id)
+ REFERENCES wms (wms_id) MATCH SIMPLE
+ ON UPDATE CASCADE ON DELETE CASCADE,
+ CONSTRAINT conformity_relation_wfs_id_fkey FOREIGN KEY (fkey_wfs_id)
+ REFERENCES wfs (wfs_id) MATCH SIMPLE
+ ON UPDATE CASCADE ON DELETE CASCADE,
+ CONSTRAINT conformity_relation_inspire_md_fkey FOREIGN KEY (fkey_inspire_md_id)
+ REFERENCES inspire_md_data (data_id) MATCH SIMPLE
+ ON UPDATE CASCADE ON DELETE CASCADE,
+ CONSTRAINT conformity_relation_conformity_fkey FOREIGN KEY (fkey_conformity_id)
+ REFERENCES conformity (conformity_id) MATCH SIMPLE
+ ON UPDATE CASCADE ON DELETE CASCADE,
+ CONSTRAINT conformity_relation_spec_fkey FOREIGN KEY (fkey_spec_id)
+ REFERENCES spec (spec_id) MATCH SIMPLE
+ ON UPDATE CASCADE ON DELETE CASCADE
+)
+WITH OIDS;
+--ALTER TABLE conformity_relation OWNER TO "postgres";
+--delete all of the ABOVE:
+--alter table wms drop column wms_timestamp_create;
+--alter table wms drop column wms_network_access;
+--alter table wfs drop column wfs_timestamp_create;
+--alter table wfs drop column wfs_network_access;
+--DROP TABLE conformity_relation CASCADE;
+--DROP TABLE spec CASCADE;
+--DROP TABLE spec_classification CASCADE;
+--DROP TABLE conformity CASCADE;
+--DROP TABLE inspire_md_data CASCADE;
+--***
+--
+-- Table: custom_category
+
+-- DROP TABLE custom_category;
+
+-- Table: custom_category
+
+-- DROP TABLE custom_category;
+
+CREATE TABLE custom_category
+(
+ custom_category_id serial NOT NULL,
+ custom_category_key varchar(5) NOT NULL,
+ custom_category_code_en character varying(255),
+ custom_category_code_de character varying(255),
+ custom_category_code_fr character varying(255),
+ custom_category_symbol character varying(255),
+ custom_category_description_de text,
+ CONSTRAINT custom_category_pkey PRIMARY KEY (custom_category_id)
+)
+WITH OIDS;
+--ALTER TABLE custom_category OWNER TO "postgres";
+
+INSERT INTO custom_category (custom_category_key, custom_category_code_en, custom_category_code_de, custom_category_code_fr, custom_category_symbol, custom_category_description_de) VALUES ('dc1','dummy category','Dummy Kategorie','','','Demo Kategorie zur Klassifizierung von Mapbender Registry Inhalten');
+
+
+
+
+
+-- Table: layer_custom_category
+
+-- DROP TABLE layer_custom_category;
+
+CREATE TABLE layer_custom_category
+(
+ fkey_layer_id integer NOT NULL,
+ fkey_custom_category_id integer NOT NULL,
+ CONSTRAINT layer_custom_category_fkey_layer_id_fkey FOREIGN KEY (fkey_layer_id)
+ REFERENCES layer (layer_id) MATCH SIMPLE
+ ON UPDATE CASCADE ON DELETE CASCADE,
+ CONSTRAINT layer_custom_category_fkey_custom_category_id_fkey FOREIGN KEY (fkey_custom_category_id)
+ REFERENCES custom_category (custom_category_id) MATCH SIMPLE
+ ON UPDATE CASCADE ON DELETE CASCADE
+);
+--WITH (OIDS=FALSE);
+ALTER TABLE layer_custom_category OWNER TO postgres;
+
+
+
+-- Table: wfs_featuretype_custom_category
+
+-- DROP TABLE wfs_featuretype_custom_category;
+
+CREATE TABLE wfs_featuretype_custom_category
+(
+ fkey_featuretype_id integer NOT NULL,
+ fkey_custom_category_id integer NOT NULL,
+ CONSTRAINT wfs_featuretype_custom_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_custom_category_fkey_custom_category_id_fkey FOREIGN KEY (fkey_custom_category_id)
+ REFERENCES custom_category (custom_category_id) MATCH SIMPLE
+ ON UPDATE CASCADE ON DELETE CASCADE
+);
+--WITH (OIDS=FALSE);
+ALTER TABLE wfs_featuretype_custom_category OWNER TO postgres;
+
+
+--functions to collect the categories into strings
+
+CREATE OR REPLACE FUNCTION f_collect_custom_cat_layer(integer)
+ RETURNS text AS
+ $BODY$DECLARE
+ i_layer_id ALIAS FOR $1;
+ custom_cat_string TEXT;
+ custom_cat_record RECORD;
+
+BEGIN
+custom_cat_string := '';
+
+FOR custom_cat_record IN SELECT layer_custom_category.fkey_custom_category_id from layer_custom_category WHERE layer_custom_category.fkey_layer_id=$1 LOOP
+custom_cat_string := custom_cat_string || '{' ||custom_cat_record.fkey_custom_category_id || '}';
+END LOOP ;
+
+RETURN custom_cat_string;
+
+END;
+$BODY$
+ LANGUAGE 'plpgsql' VOLATILE STRICT;
+ALTER FUNCTION f_collect_custom_cat_layer(integer) OWNER TO postgres;
+
+
+
+CREATE OR REPLACE FUNCTION f_collect_custom_cat_wfs_featuretype(integer)
+ RETURNS text AS
+ $BODY$DECLARE
+ i_featuretype_id ALIAS FOR $1;
+ custom_cat_string TEXT;
+ custom_cat_record RECORD;
+
+BEGIN
+custom_cat_string := '';
+
+FOR custom_cat_record IN SELECT wfs_featuretype_custom_category.fkey_custom_category_id from wfs_featuretype_custom_category WHERE wfs_featuretype_custom_category.fkey_featuretype_id=$1 LOOP
+custom_cat_string := custom_cat_string || '{' ||custom_cat_record.fkey_custom_category_id || '}';
+END LOOP ;
+
+RETURN custom_cat_string;
+
+END;
+$BODY$
+ LANGUAGE 'plpgsql' VOLATILE STRICT;
+ALTER FUNCTION f_collect_custom_cat_wfs_featuretype(integer) OWNER TO postgres;
+
+-- Table: inspire_category
+
+-- DROP TABLE inspire_category;
+
+-- Table: inspire_category
+
+-- DROP TABLE inspire_category;
+
+CREATE TABLE inspire_category
+(
+ inspire_category_id serial NOT NULL,
+ inspire_category_key varchar(5) NOT NULL,
+ inspire_category_code_en character varying(255),
+ inspire_category_code_de character varying(255),
+ inspire_category_code_fr character varying(255),
+ inspire_category_symbol character varying(255),
+ inspire_category_description_de text,
+ CONSTRAINT inspire_category_pkey PRIMARY KEY (inspire_category_id)
+)
+WITH OIDS;
+--ALTER TABLE inspire_category OWNER TO "postgres";
+
+INSERT INTO inspire_category (inspire_category_key, inspire_category_code_en, inspire_category_code_de, inspire_category_code_fr, inspire_category_symbol, inspire_category_description_de) VALUES ('1.1','Coordinate reference systems','Koordinatenreferenzsysteme','','','Systeme zur eindeutigen räumlichen Referenzierung von Geodaten anhand eines Koordinatensatzes (x, y, z) und/oder Angaben zu Breite, Länge und Höhe auf der Grundlage eines geodätischen horizontalen und vertikalen Datums.');
+INSERT INTO inspire_category (inspire_category_key, inspire_category_code_en, inspire_category_code_de, inspire_category_code_fr, inspire_category_symbol, inspire_category_description_de) VALUES ('1.2','Geographical grid systems','Geografische Gittersysteme','','','Harmonisiertes Gittersystem mit Mehrfachauflösung, gemeinsamem Ursprungspunkt und standardisierter Lokalisierung und Größe der Gitterzellen.');
+INSERT INTO inspire_category (inspire_category_key, inspire_category_code_en, inspire_category_code_de, inspire_category_code_fr, inspire_category_symbol, inspire_category_description_de) VALUES ('1.3','Geographical names','Geografische Bezeichnungen','','','Namen von Gebieten, Regionen, Orten, Großstädten, Vororten, Städten oder Siedlungen sowie jedes geografische oder topografische Merkmal von öffentlichem oder historischem Interesse.');
+INSERT INTO inspire_category (inspire_category_key, inspire_category_code_en, inspire_category_code_de, inspire_category_code_fr, inspire_category_symbol, inspire_category_description_de) VALUES ('1.4','Administrative units','Verwaltungseinheiten','','','Lokale, regionale und nationale Verwaltungseinheiten, die die Gebiete abgrenzen, in denen die Mitgliedstaaten Hoheitsbefugnisse haben und/oder ausüben und die durch Verwaltungsgrenzen voneinander getrennt sind.');
+INSERT INTO inspire_category (inspire_category_key, inspire_category_code_en, inspire_category_code_de, inspire_category_code_fr, inspire_category_symbol, inspire_category_description_de) VALUES ('1.5','Addresses','Adressen','','','Lokalisierung von Grundstücken anhand von Adressdaten, in der Regel Straßenname, Hausnummer und Postleitzahl.');
+INSERT INTO inspire_category (inspire_category_key, inspire_category_code_en, inspire_category_code_de, inspire_category_code_fr, inspire_category_symbol, inspire_category_description_de) VALUES ('1.6','Cadastral parcels','Flurstücke/Grundstücke (Katasterparzellen)','','','Gebiete, die anhand des Grundbuchs oder gleichwertiger Verzeichnisse bestimmt werden. ');
+INSERT INTO inspire_category (inspire_category_key, inspire_category_code_en, inspire_category_code_de, inspire_category_code_fr, inspire_category_symbol, inspire_category_description_de) VALUES ('1.7','Transport networks','Verkehrsnetze','','','Verkehrsnetze und zugehörige Infrastruktureinrichtungen für Straßen-, Schienen- und Luftverkehr sowie Schifffahrt. Umfasst auch die Verbindungen zwischen den verschiedenen Netzen. Umfasst auch das transeuropäische Verkehrsnetz im Sinne der Entscheidung Nr. 1692/96/EG des Europäischen Parlaments und des Rates vom 23. Juli 1996 über gemeinschaftliche Leitlinien für den Aufbau eines transeuropäischen Verkehrsnetzes und künftiger Überarbeitungen dieser Entscheidung. ');
+INSERT INTO inspire_category (inspire_category_key, inspire_category_code_en, inspire_category_code_de, inspire_category_code_fr, inspire_category_symbol, inspire_category_description_de) VALUES ('1.8','Hydrography','Gewässernetz','','','Elemente des Gewässernetzes, einschließlich Meeresgebieten und allen sonstigen Wasserkörpern und hiermit verbundenen Teilsystemen, darunter Einzugsgebiete und Teileinzugsgebiete. Gegebenenfalls gemäß den Definitionen der Richtlinie 2000/60/EG des Europäischen Parlaments und des Rates vom 23. Oktober 2000 zur Schaffung eines Ordnungsrahmens für Maßnahmen der Gemeinschaft im Bereich der Wasserpolitik und in Form von Netzen.');
+INSERT INTO inspire_category (inspire_category_key, inspire_category_code_en, inspire_category_code_de, inspire_category_code_fr, inspire_category_symbol, inspire_category_description_de) VALUES ('1.9','Protected sites','Schutzgebiete','','','Gebiete, die im Rahmen des internationalen und des gemeinschaftlichen Rechts sowie des Rechts der Mitgliedstaaten ausgewiesen sind oder verwaltet werden, um spezifische Erhaltungsziele zu erreichen.');
+INSERT INTO inspire_category (inspire_category_key, inspire_category_code_en, inspire_category_code_de, inspire_category_code_fr, inspire_category_symbol, inspire_category_description_de) VALUES ('2.1','Elevation','Höhe','','','Digitale Höhenmodelle für Land-, Eis- und Meeresflächen. Dazu gehören Geländemodell, Tiefenmessung und Küstenlinie.');
+INSERT INTO inspire_category (inspire_category_key, inspire_category_code_en, inspire_category_code_de, inspire_category_code_fr, inspire_category_symbol, inspire_category_description_de) VALUES ('2.2','Land cover','Bodenbedeckung','','','Physische und biologische Bedeckung der Erdoberfläche, einschließlich künstlicher Flächen, landwirtschaftlicher Flächen, Wäldern, natürlicher (naturnaher) Gebiete, Feuchtgebieten und Wasserkörpern.');
+INSERT INTO inspire_category (inspire_category_key, inspire_category_code_en, inspire_category_code_de, inspire_category_code_fr, inspire_category_symbol, inspire_category_description_de) VALUES ('2.3','Orthoimagery','Orthofotografie','','','Georeferenzierte Bilddaten der Erdoberfläche von satelliten- oder luftfahrzeuggestützten Sensoren.');
+INSERT INTO inspire_category (inspire_category_key, inspire_category_code_en, inspire_category_code_de, inspire_category_code_fr, inspire_category_symbol, inspire_category_description_de) VALUES ('2.4','Geology','Geologie','','','Geologische Beschreibung anhand von Zusammensetzung und Struktur. Dies umfasst auch Grundgestein, Grundwasserleiter und Geomorphologie.');
+INSERT INTO inspire_category (inspire_category_key, inspire_category_code_en, inspire_category_code_de, inspire_category_code_fr, inspire_category_symbol, inspire_category_description_de) VALUES ('3.1','Statistical units','Statistische Einheiten','','','Einheiten für die Verbreitung oder Verwendung statistischer Daten.');
+INSERT INTO inspire_category (inspire_category_key, inspire_category_code_en, inspire_category_code_de, inspire_category_code_fr, inspire_category_symbol, inspire_category_description_de) VALUES ('3.2','Buildings','Gebäude','','','Geografischer Standort von Gebäuden.');
+INSERT INTO inspire_category (inspire_category_key, inspire_category_code_en, inspire_category_code_de, inspire_category_code_fr, inspire_category_symbol, inspire_category_description_de) VALUES ('3.3','Soil','Boden','','','Beschreibung von Boden und Unterboden anhand von Tiefe, Textur, Struktur und Gehalt an Teilchen sowie organischem Material, Steinigkeit, Erosion, gegebenenfalls durchschnittliches Gefälle und erwartete Wasserspeicherkapazität.');
+INSERT INTO inspire_category (inspire_category_key, inspire_category_code_en, inspire_category_code_de, inspire_category_code_fr, inspire_category_symbol, inspire_category_description_de) VALUES ('3.4','Land use','Bodennutzung','','','Beschreibung von Gebieten anhand ihrer derzeitigen und geplanten künftigen Funktion oder ihres sozioökonomischen Zwecks (z. B. Wohn-, Industrie- oder Gewerbegebiete, land- oder forstwirtschaftliche Flächen, Freizeitgebiete).');
+INSERT INTO inspire_category (inspire_category_key, inspire_category_code_en, inspire_category_code_de, inspire_category_code_fr, inspire_category_symbol, inspire_category_description_de) VALUES ('3.5','Human health and safety','Gesundheit und Sicherheit','','','Geografische Verteilung verstärkt auftretender pathologischer Befunde (Allergien, Krebserkrankungen, Erkrankungen der Atemwege usw.), Informationen über Auswirkungen auf die Gesundheit (Biomarker, Rückgang der Fruchtbarkeit, Epidemien) oder auf das Wohlbefinden (Ermüdung, Stress usw.) der Menschen in unmittelbarem Zusammenhang mit der Umweltqualität (Luftverschmutzung, Chemikalien, Abbau der Ozonschicht, Lärm usw.) oder in mittelbarem Zusammenhang mit der Umweltqualität (Nahrung, genetisch veränderte Organismen usw.).');
+INSERT INTO inspire_category (inspire_category_key, inspire_category_code_en, inspire_category_code_de, inspire_category_code_fr, inspire_category_symbol, inspire_category_description_de) VALUES ('3.6','Utility and governmental services','Versorgungswirtschaft und staatliche Dienste','','','Versorgungseinrichtungen wie Abwasser- und Abfallentsorgung, Energieversorgung und Wasserversorgung; staatliche Verwaltungs- und Sozialdienste wie öffentliche Verwaltung, Katastrophenschutz, Schulen und Krankenhäuser.');
+INSERT INTO inspire_category (inspire_category_key, inspire_category_code_en, inspire_category_code_de, inspire_category_code_fr, inspire_category_symbol, inspire_category_description_de) VALUES ('3.7','Environmental monitoring facilities','Umweltüberwachung','','','Standort und Betrieb von Umweltüberwachungseinrichtungen einschließlich Beobachtung und Messung von Schadstoffen, des Zustands von Umweltmedien und anderen Parametern des Ökosystems (Artenvielfalt, ökologischer Zustand der Vegetation usw.) durch oder im Auftrag von öffentlichen Behörden.');
+INSERT INTO inspire_category (inspire_category_key, inspire_category_code_en, inspire_category_code_de, inspire_category_code_fr, inspire_category_symbol, inspire_category_description_de) VALUES ('3.8','Production and industrial facilities','Produktions- und Industrieanlagen','','','Standorte für industrielle Produktion, einschließlich durch die Richtlinie 96/61/EG des Rates vom 24. September 1996 über die integrierte Vermeidung und Verminderung der Umweltverschmutzung erfasste Anlagen und Einrichtungen zur Wasserentnahme sowie Bergbau- und Lagerstandorte.');
+INSERT INTO inspire_category (inspire_category_key, inspire_category_code_en, inspire_category_code_de, inspire_category_code_fr, inspire_category_symbol, inspire_category_description_de) VALUES ('3.9','Agricultural and aquaculture facilities','Landwirtschaftliche Anlagen und Aquakulturanlagen','','','Landwirtschaftliche Anlagen und Produktionsstätten (einschließlich Bewässerungssystemen, Gewächshäusern und Ställen).');
+INSERT INTO inspire_category (inspire_category_key, inspire_category_code_en, inspire_category_code_de, inspire_category_code_fr, inspire_category_symbol, inspire_category_description_de) VALUES ('3.10','Population distribution — demography','Verteilung der Bevölkerung — Demografie','','','Geografische Verteilung der Bevölkerung, einschließlich Bevölkerungsmerkmalen und Tätigkeitsebenen, zusammengefasst nach Gitter, Region, Verwaltungseinheit oder sonstigen analytischen Einheiten.');
+INSERT INTO inspire_category (inspire_category_key, inspire_category_code_en, inspire_category_code_de, inspire_category_code_fr, inspire_category_symbol, inspire_category_description_de) VALUES ('3.11','Area management/restriction/regulation zones and reporting units','Bewirtschaftungsgebiete/Schutzgebiete/geregelte Gebiete und Berichterstattungseinheiten','','','Auf internationaler, europäischer, nationaler, regionaler und lokaler Ebene bewirtschaftete, geregelte oder zu Zwecken der Berichterstattung herangezogene Gebiete. Dazu zählen Deponien, Trinkwasserschutzgebiete, nitratempfindliche Gebiete, geregelte Fahrwasser auf See oder auf großen Binnengewässern, Gebiete für die Abfallverklappung, Lärmschutzgebiete, für Exploration und Bergbau ausgewiesene Gebiete, Flussgebietseinheiten, entsprechende Berichterstattungseinheiten und Gebiete des Küstenzonenmanagements.');
+INSERT INTO inspire_category (inspire_category_key, inspire_category_code_en, inspire_category_code_de, inspire_category_code_fr, inspire_category_symbol, inspire_category_description_de) VALUES ('3.12','Natural risk zones','Gebiete mit naturbedingten Risiken','','','Gefährdete Gebiete, eingestuft nach naturbedingten Risiken (sämtliche atmosphärischen, hydrologischen, seismischen, vulkanischen Phänomene sowie Naturfeuer, die aufgrund ihres örtlichen Auftretens sowie ihrer Schwere und Häufigkeit signifikante Auswirkungen auf die Gesellschaft haben können), z. B. Überschwemmungen, Erdrutsche und Bodensenkungen, Lawinen, Waldbrände, Erdbeben oder Vulkanausbrüche.');
+INSERT INTO inspire_category (inspire_category_key, inspire_category_code_en, inspire_category_code_de, inspire_category_code_fr, inspire_category_symbol, inspire_category_description_de) VALUES ('3.13','Atmospheric conditions','Atmosphärische Bedingungen','','','Physikalische Bedingungen in der Atmosphäre. Dazu zählen Geodaten auf der Grundlage von Messungen, Modellen oder einer Kombination aus beiden sowie Angabe der Messstandorte.');
+INSERT INTO inspire_category (inspire_category_key, inspire_category_code_en, inspire_category_code_de, inspire_category_code_fr, inspire_category_symbol, inspire_category_description_de) VALUES ('3.14','Meteorological geographical features','Meteorologisch-geografische Kennwerte','','','Witterungsbedingungen und deren Messung; Niederschlag, Temperatur, Gesamtverdunstung (Evapotranspiration), Windgeschwindigkeit und Windrichtung.');
+INSERT INTO inspire_category (inspire_category_key, inspire_category_code_en, inspire_category_code_de, inspire_category_code_fr, inspire_category_symbol, inspire_category_description_de) VALUES ('3.15','Oceanographic geographical features','Ozeanografisch-geografische Kennwerte','','','Physikalische Bedingungen der Ozeane (Strömungsverhältnisse, Salinität, Wellenhöhe usw.).');
+INSERT INTO inspire_category (inspire_category_key, inspire_category_code_en, inspire_category_code_de, inspire_category_code_fr, inspire_category_symbol, inspire_category_description_de) VALUES ('3.16','Sea regions','Meeresregionen','','','Physikalische Bedingungen von Meeren und salzhaltigen Gewässern, aufgeteilt nach Regionen und Teilregionen mit gemeinsamen Merkmalen.');
+INSERT INTO inspire_category (inspire_category_key, inspire_category_code_en, inspire_category_code_de, inspire_category_code_fr, inspire_category_symbol, inspire_category_description_de) VALUES ('3.17','Bio-geographical regions','Biogeografische Regionen','','','Gebiete mit relativ homogenen ökologischen Bedingungen und gemeinsamen Merkmalen.');
+INSERT INTO inspire_category (inspire_category_key, inspire_category_code_en, inspire_category_code_de, inspire_category_code_fr, inspire_category_symbol, inspire_category_description_de) VALUES ('3.18','Habitats and biotopes','Lebensräume und Biotope','','','Geografische Gebiete mit spezifischen ökologischen Bedingungen, Prozessen, Strukturen und (lebensunterstützenden) Funktionen als physische Grundlage für dort lebende Organismen. Dies umfasst auch durch geografische, abiotische und biotische Merkmale gekennzeichnete natürliche oder naturnahe terrestrische und aquatische Gebiete.');
+INSERT INTO inspire_category (inspire_category_key, inspire_category_code_en, inspire_category_code_de, inspire_category_code_fr, inspire_category_symbol, inspire_category_description_de) VALUES ('3.19','Species distribution','Verteilung der Arten','','','Geografische Verteilung des Auftretens von Tier- und Pflanzenarten, zusammengefasst in Gittern, Region, Verwaltungseinheit oder sonstigen analytischen Einheiten.');
+INSERT INTO inspire_category (inspire_category_key, inspire_category_code_en, inspire_category_code_de, inspire_category_code_fr, inspire_category_symbol, inspire_category_description_de) VALUES ('3.20','Energy resources','Energiequellen','','','Energiequellen wie Kohlenwasserstoffe, Wasserkraft, Bioenergie, Sonnen- und Windenergie usw., gegebenenfalls mit Tiefen- bzw. Höhenangaben zur Ausdehnung der Energiequelle.');
+INSERT INTO inspire_category (inspire_category_key, inspire_category_code_en, inspire_category_code_de, inspire_category_code_fr, inspire_category_symbol, inspire_category_description_de) VALUES ('3.21','Mineral resources','Mineralische Bodenschätze','','','Mineralische Bodenschätze wie Metallerze, Industrieminerale usw., gegebenenfalls mit Tiefen- bzw. Höhenangaben zur Ausdehnung der Bodenschätze.');
+
+
+-- Table: layer_inspire_category
+
+-- DROP TABLE layer_inspire_category;
+
+CREATE TABLE layer_inspire_category
+(
+ fkey_layer_id integer NOT NULL,
+ fkey_inspire_category_id integer NOT NULL,
+ CONSTRAINT layer_inspire_category_fkey_layer_id_fkey FOREIGN KEY (fkey_layer_id)
+ REFERENCES layer (layer_id) MATCH SIMPLE
+ ON UPDATE CASCADE ON DELETE CASCADE,
+ CONSTRAINT layer_inspire_category_fkey_inspire_category_id_fkey FOREIGN KEY (fkey_inspire_category_id)
+ REFERENCES inspire_category (inspire_category_id) MATCH SIMPLE
+ ON UPDATE CASCADE ON DELETE CASCADE
+);
+--WITH (OIDS=FALSE);
+ALTER TABLE layer_inspire_category OWNER TO postgres;
+
+
+
+-- Table: wfs_featuretype_inspire_category
+
+-- DROP TABLE wfs_featuretype_inspire_category;
+
+CREATE TABLE wfs_featuretype_inspire_category
+(
+ fkey_featuretype_id integer NOT NULL,
+ fkey_inspire_category_id integer NOT NULL,
+ CONSTRAINT wfs_featuretype_inspire_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_inspire_category_fkey_inspire_category_id_fkey FOREIGN KEY (fkey_inspire_category_id)
+ REFERENCES inspire_category (inspire_category_id) MATCH SIMPLE
+ ON UPDATE CASCADE ON DELETE CASCADE
+);
+--WITH (OIDS=FALSE);
+ALTER TABLE wfs_featuretype_inspire_category OWNER TO postgres;
+
+--functions to collect the categories into strings
+
+CREATE OR REPLACE FUNCTION f_collect_inspire_cat_layer(integer)
+ RETURNS text AS
+ $BODY$DECLARE
+ i_layer_id ALIAS FOR $1;
+ inspire_cat_string TEXT;
+ inspire_cat_record RECORD;
+
+BEGIN
+inspire_cat_string := '';
+
+FOR inspire_cat_record IN SELECT layer_inspire_category.fkey_inspire_category_id from layer_inspire_category WHERE layer_inspire_category.fkey_layer_id=$1 LOOP
+inspire_cat_string := inspire_cat_string || '{' ||inspire_cat_record.fkey_inspire_category_id || '}';
+END LOOP ;
+
+RETURN inspire_cat_string;
+
+END;
+$BODY$
+ LANGUAGE 'plpgsql' VOLATILE STRICT;
+ALTER FUNCTION f_collect_inspire_cat_layer(integer) OWNER TO postgres;
+
+
+
+CREATE OR REPLACE FUNCTION f_collect_inspire_cat_wfs_featuretype(integer)
+ RETURNS text AS
+ $BODY$DECLARE
+ i_featuretype_id ALIAS FOR $1;
+ inspire_cat_string TEXT;
+ inspire_cat_record RECORD;
+
+BEGIN
+inspire_cat_string := '';
+
+FOR inspire_cat_record IN SELECT wfs_featuretype_inspire_category.fkey_inspire_category_id from wfs_featuretype_inspire_category WHERE wfs_featuretype_inspire_category.fkey_featuretype_id=$1 LOOP
+inspire_cat_string := inspire_cat_string || '{' ||inspire_cat_record.fkey_inspire_category_id || '}';
+END LOOP ;
+
+RETURN inspire_cat_string;
+
+END;
+$BODY$
+ LANGUAGE 'plpgsql' VOLATILE STRICT;
+ALTER FUNCTION f_collect_inspire_cat_wfs_featuretype(integer) OWNER TO postgres;
+
+-- Table: wfs_featuretype_md_topic_category
+
+-- DROP TABLE wfs_featuretype_md_topic_category;
+
+CREATE TABLE wfs_featuretype_md_topic_category
+(
+ fkey_featuretype_id integer NOT NULL,
+ fkey_md_topic_category_id integer NOT NULL,
+ 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)
+ REFERENCES md_topic_category (md_topic_category_id) MATCH SIMPLE
+ ON UPDATE CASCADE ON DELETE CASCADE
+)
+WITHOUT OIDS;
+ALTER TABLE wfs_featuretype_md_topic_category OWNER TO "postgres";
+
+
+CREATE OR REPLACE FUNCTION f_collect_topic_cat_layer(integer)
+ RETURNS text AS
+ $BODY$DECLARE
+ i_layer_id ALIAS FOR $1;
+ topic_cat_string TEXT;
+ topic_cat_record RECORD;
+
+BEGIN
+topic_cat_string := '';
+
+FOR topic_cat_record IN SELECT layer_md_topic_category.fkey_md_topic_category_id from layer_md_topic_category WHERE layer_md_topic_category.fkey_layer_id=$1 LOOP
+topic_cat_string := topic_cat_string || '{' ||topic_cat_record.fkey_md_topic_category_id || '}';
+END LOOP ;
+
+RETURN topic_cat_string;
+
+END;
+$BODY$
+ LANGUAGE 'plpgsql' VOLATILE STRICT;
+ALTER FUNCTION f_collect_topic_cat_layer(integer) OWNER TO postgres;
+
+
+
+CREATE OR REPLACE FUNCTION f_collect_topic_cat_wfs_featuretype(integer)
+ RETURNS text AS
+ $BODY$DECLARE
+ i_featuretype_id ALIAS FOR $1;
+ topic_cat_string TEXT;
+ topic_cat_record RECORD;
+
+BEGIN
+topic_cat_string := '';
+
+FOR topic_cat_record IN SELECT wfs_featuretype_md_topic_category.fkey_md_topic_category_id from wfs_featuretype_md_topic_category WHERE wfs_featuretype_md_topic_category.fkey_featuretype_id=$1 LOOP
+topic_cat_string := topic_cat_string || '{' ||topic_cat_record.fkey_md_topic_category_id || '}';
+END LOOP ;
+
+RETURN topic_cat_string;
+
+END;
+$BODY$
+ LANGUAGE 'plpgsql' VOLATILE STRICT;
+ALTER FUNCTION f_collect_topic_cat_wfs_featuretype(integer) OWNER TO postgres;
+
+--generate the group for the decentral registrating institutions
+
+-- View: registrating_groups
+
+-- DROP VIEW registrating_groups;
+
+CREATE OR REPLACE VIEW registrating_groups AS
+ SELECT f.fkey_mb_group_id, f.fkey_mb_user_id
+ FROM mb_user_mb_group f, mb_user_mb_group s
+ WHERE f.mb_user_mb_group_type = 1 AND s.fkey_mb_group_id = 36 AND f.fkey_mb_user_id = s.fkey_mb_user_id
+ ORDER BY f.fkey_mb_group_id, f.fkey_mb_user_id;
+
+ALTER TABLE registrating_groups OWNER TO postgres;
+
+-- Function: f_getwfs_tou(integer)
+
+-- DROP FUNCTION f_getwfs_tou(integer);
+
+CREATE OR REPLACE FUNCTION f_getwfs_tou(integer)
+ RETURNS integer AS
+$BODY$
+DECLARE
+ wfs_tou int4;
+BEGIN
+wfs_tou := fkey_termsofuse_id from wfs_termsofuse where wfs_termsofuse.fkey_wfs_id=$1;
+RETURN wfs_tou;
+
+END;
+$BODY$
+ LANGUAGE 'plpgsql' VOLATILE;
+ALTER FUNCTION f_getwfs_tou(integer) OWNER TO postgres;
+
+
+-- Function: f_getwms_tou(integer)
+
+-- DROP FUNCTION f_getwms_tou(integer);
+
+CREATE OR REPLACE FUNCTION f_getwms_tou(integer)
+ RETURNS integer AS
+$BODY$
+DECLARE
+ wms_tou int4;
+BEGIN
+wms_tou := fkey_termsofuse_id from wms_termsofuse where wms_termsofuse.fkey_wms_id=$1;
+RETURN wms_tou;
+
+END;
+$BODY$
+ LANGUAGE 'plpgsql' VOLATILE;
+ALTER FUNCTION f_getwms_tou(integer) OWNER TO postgres;
+
+-- Function: f_collect_epsg(integer)
+
+-- DROP FUNCTION f_collect_epsg(integer);
+
+CREATE OR REPLACE FUNCTION f_collect_epsg(integer)
+ RETURNS text AS
+$BODY$DECLARE
+ i_layer_id ALIAS FOR $1;
+ epsg_string TEXT;
+ epsg_record RECORD;
+
+BEGIN
+epsg_string := '';
+
+FOR epsg_record IN SELECT layer_epsg.epsg from layer_epsg WHERE layer_epsg.fkey_layer_id=$1 LOOP
+epsg_string := epsg_string || epsg_record.epsg || ';';
+END LOOP ;
+
+RETURN epsg_string;
+
+ --CASE
+ --WHEN LEN(epsg) > 0 THEN LEFT(epsg, LEN(epsg) - 1)
+ -- ELSE epsg
+
+
+END;
+$BODY$
+ LANGUAGE 'plpgsql' VOLATILE STRICT;
+ALTER FUNCTION f_collect_epsg(integer) OWNER TO postgres;
+
+-- Function: f_layer_load_count(integer)
+
+-- DROP FUNCTION f_layer_load_count(integer);
+
+CREATE OR REPLACE FUNCTION f_layer_load_count(integer)
+ RETURNS integer AS
+$BODY$
+DECLARE
+ layer_rel int4;
+BEGIN
+layer_rel := load_count from layer_load_count where layer_load_count.fkey_layer_id=$1;
+RETURN layer_rel;
+
+END;
+$BODY$
+ LANGUAGE 'plpgsql' VOLATILE;
+ALTER FUNCTION f_layer_load_count(integer) OWNER TO postgres;
+
+-- Function: f_collect_searchtext(integer, integer)
+
+-- DROP FUNCTION f_collect_searchtext(integer, integer);
+
+CREATE OR REPLACE FUNCTION f_collect_searchtext(integer, integer)
+ RETURNS text AS
+$BODY$DECLARE
+ p_wms_id ALIAS FOR $1;
+ p_layer_id ALIAS FOR $2;
+
+ r_keywords RECORD;
+ l_result TEXT;
+BEGIN
+ l_result := '';
+ l_result := l_result || (SELECT COALESCE(wms_title, '') || ' ' || COALESCE(wms_abstract, '') FROM wms WHERE wms_id = p_wms_id);
+ l_result := l_result || (SELECT COALESCE(layer_name, '')|| ' ' || COALESCE(layer_title, '') || ' ' || COALESCE(layer_abstract, '') FROM layer WHERE layer_id = p_layer_id);
+ FOR r_keywords IN SELECT DISTINCT keyword FROM
+ (SELECT keyword FROM layer_keyword L JOIN keyword K ON (K.keyword_id = L.fkey_keyword_id AND L.fkey_layer_id = p_layer_id)
+ ) AS __keywords__ LOOP
+ l_result := l_result || ' ' || COALESCE(r_keywords.keyword, '');
+ END LOOP;
+ FOR r_keywords IN SELECT DISTINCT md_topic_category_code_de FROM
+ (SELECT md_topic_category_code_de FROM md_topic_category T JOIN layer_md_topic_category C ON (C.fkey_md_topic_category_id = T.md_topic_category_id AND C.fkey_layer_id = p_layer_id)
+ ) AS __keywords__ LOOP
+ l_result := l_result || ' ' || COALESCE(r_keywords.md_topic_category_code_de, '');
+ END LOOP;
+ l_result := UPPER(l_result);
+ l_result := replace(replace(replace(replace(replace(replace(replace(l_result,'Ä','AE'),'ß','SS'),'Ö','OE'),'Ü','UE'),'ä','AE'),'ü','UE'),'ö','OE');
+
+ RETURN l_result;
+END;
+$BODY$
+ LANGUAGE 'plpgsql' VOLATILE STRICT;
+ALTER FUNCTION f_collect_searchtext(integer, integer) OWNER TO "geoportal-admin";
+
+-- Function: f_collect_searchtext_wfs(integer, integer)
+
+-- DROP FUNCTION f_collect_searchtext_wfs(integer, integer);
+
+CREATE OR REPLACE FUNCTION f_collect_searchtext_wfs(integer, integer)
+ RETURNS text AS
+$BODY$
+DECLARE
+ p_wfs_id ALIAS FOR $1;
+ p_featuretype_id ALIAS FOR $2;
+ r_keywords RECORD;
+ l_result TEXT;
+BEGIN
+ l_result := '';
+ l_result := l_result || (SELECT COALESCE(wfs_title, '') || ' ' || COALESCE(wfs_abstract, '') FROM wfs WHERE wfs_id = p_wfs_id);
+ l_result := l_result || (SELECT COALESCE(featuretype_name, '')|| ' ' || COALESCE(featuretype_title, '') || ' ' || COALESCE(featuretype_abstract, '') FROM wfs_featuretype WHERE featuretype_id = p_featuretype_id);
+ FOR r_keywords IN SELECT DISTINCT keyword FROM
+ (SELECT keyword FROM wfs_featuretype_keyword L JOIN keyword K ON (K.keyword_id = L.fkey_keyword_id AND L.fkey_featuretype_id = p_featuretype_id)
+ ) AS __keywords__ LOOP
+ l_result := l_result || ' ' || COALESCE(r_keywords.keyword, '');
+ END LOOP;
+ l_result := UPPER(l_result);
+ l_result := replace(replace(replace(replace(replace(replace(replace(l_result,'Ä','AE'),'ß','SS'),'Ö','OE'),'Ü','UE'),'ü','UE'),'ö','OE'),'ä','AE');
+
+ RETURN l_result;
+END;
+$BODY$
+ LANGUAGE 'plpgsql' VOLATILE;
+ALTER FUNCTION f_collect_searchtext_wfs(integer, integer) OWNER TO postgres;
+
+-- Function: f_getwfsmodultype(integer)
+
+-- DROP FUNCTION f_getwfsmodultype(integer);
+
+CREATE OR REPLACE FUNCTION f_getwfsmodultype(integer)
+ RETURNS integer AS
+$BODY$
+DECLARE
+ i_search INT4;
+BEGIN
+i_search := count(*) from wfs_conf, wfs_conf_element where wfs_conf.wfs_conf_id=$1 and wfs_conf.wfs_conf_id=wfs_conf_element.fkey_wfs_conf_id and f_search=1;
+IF i_search > 0 THEN
+RETURN 1;
+else
+RETURN 0;
+END IF;
+
+END;
+$BODY$
+ LANGUAGE 'plpgsql' VOLATILE;
+ALTER FUNCTION f_getwfsmodultype(integer) OWNER TO postgres;
+
+
+
+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;
+
+
+
+
+
+
+--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;
+
+CREATE OR REPLACE VIEW search_wfs_view AS
+ 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.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
+ 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, wfs.wfs_timestamp, wfs.wfs_owner, user_dep.mb_group_id AS department, user_dep.mb_group_name
+ FROM ( SELECT registrating_groups.fkey_mb_user_id AS mb_user_id, mb_group.mb_group_id, mb_group.mb_group_name
+ 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;
+
+ALTER TABLE search_wfs_view OWNER TO postgres;
+
+
+
+
+
+
+
More information about the Mapbender_commits
mailing list