[Mapbender-commits] r10287 - in trunk/mapbender: http/classes http/php resources/db/pgsql/UTF-8/update
svn_mapbender at osgeo.org
svn_mapbender at osgeo.org
Fri Oct 18 03:31:58 PDT 2019
Author: armin11
Date: 2019-10-18 03:31:58 -0700 (Fri, 18 Oct 2019)
New Revision: 10287
Modified:
trunk/mapbender/http/classes/class_metadata.php
trunk/mapbender/http/php/mod_callMetadata.php
trunk/mapbender/resources/db/pgsql/UTF-8/update/update_2.8_pgsql_UTF-8.sql
Log:
New option to search for application metadata
Modified: trunk/mapbender/http/classes/class_metadata.php
===================================================================
--- trunk/mapbender/http/classes/class_metadata.php 2019-10-17 06:41:44 UTC (rev 10286)
+++ trunk/mapbender/http/classes/class_metadata.php 2019-10-18 10:31:58 UTC (rev 10287)
@@ -136,6 +136,7 @@
$this->resourceClassifications[0]['relation_wfs'] = 'wfs_featuretype_md_topic_category';
$this->resourceClassifications[0]['relation_wmc'] = 'wmc_md_topic_category';
$this->resourceClassifications[0]['relation_dataset'] = 'mb_metadata_md_topic_category';
+$this->resourceClassifications[0]['relation_application'] = 'mb_metadata_md_topic_category';
//TODO: define this in mapbender
$this->resourceClassifications[1]['title'] = "INSPIRE"; //TODO: define the translations somewhere? - This is done in call_metadata.php before. Maybe we can get them from there? - It will be shown in the rightside categories table
@@ -149,6 +150,7 @@
$this->resourceClassifications[1]['relation_wfs'] = 'wfs_featuretype_inspire_category';
$this->resourceClassifications[1]['relation_wmc'] = 'wmc_inspire_category';
$this->resourceClassifications[1]['relation_dataset'] = 'mb_metadata_inspire_category';
+$this->resourceClassifications[1]['relation_application'] = 'mb_metadata_inspire_category';
//TODO: define this in mapbender
switch($this->languageCode){
case "de":
@@ -174,6 +176,7 @@
$this->resourceClassifications[2]['relation_wfs'] = 'wfs_featuretype_custom_category';
$this->resourceClassifications[2]['relation_wmc'] = 'wmc_custom_category';
$this->resourceClassifications[2]['relation_dataset'] = 'mb_metadata_custom_category';
+$this->resourceClassifications[2]['relation_application'] = 'mb_metadata_custom_category';
//TODO: define this in mapbender
switch ($this->languageCode) {
case "de":
@@ -195,6 +198,8 @@
$this->resourceCategories[2]['name'] = 'WMC';
$this->resourceCategories[3]['name'] = 'DAD';
$this->resourceCategories[4]['name'] = 'DATASET';
+$this->resourceCategories[4]['name'] = 'APPLICATION';
+
switch($this->languageCode){
case 'de':
$this->resourceCategories[0]['name2show'] = 'Kartenebenen';
@@ -202,6 +207,7 @@
$this->resourceCategories[2]['name2show'] = 'Kartenzusammenstellungen';
$this->resourceCategories[3]['name2show'] = 'KML/Newsfeeds';
$this->resourceCategories[4]['name2show'] = 'Datensätze';
+$this->resourceCategories[5]['name2show'] = 'Anwendungen';
$this->keywordTitle = 'Schlagwortliste';
break;
case 'en':
@@ -210,6 +216,7 @@
$this->resourceCategories[2]['name2show'] = 'Combined Maps';
$this->resourceCategories[3]['name2show'] = 'KML/Newsfeeds';
$this->resourceCategories[4]['name2show'] = 'Datasets';
+$this->resourceCategories[5]['name2show'] = 'Applications';
$this->keywordTitle = 'Keywordlist';
break;
case 'fr':
@@ -218,6 +225,7 @@
$this->resourceCategories[2]['name2show'] = 'Cartes composées';
$this->resourceCategories[3]['name2show'] = 'KML/Newsfeeds';
$this->resourceCategories[4]['name2show'] = 'Datasets';
+$this->resourceCategories[5]['name2show'] = 'Applications';
$this->keywordTitle = 'Keywordlist';
break;
default:
@@ -226,6 +234,7 @@
$this->resourceCategories[2]['name2show'] = 'Kartenzusammenstellungen';
$this->resourceCategories[3]['name2show'] = 'KML/Newsfeeds';
$this->resourceCategories[4]['name2show'] = 'Datensätze';
+$this->resourceCategories[5]['name2show'] = 'Anwendungen';
$this->keywordTitle = 'Schlagwortliste';
}
//not needed til now - maybe usefull for georss output
@@ -249,6 +258,11 @@
$this->datasetDoc = new DOMDocument('1.0');
$this->generateDatasetMetadata($this->datasetDoc);
}
+if (isset($this->searchResources) & strtolower($this->searchResources) === "application") {
+ $this->applicationDoc = new DOMDocument('1.0');
+ $this->generateApplicationMetadata($this->applicationDoc);
+}
+
}
if ($this->outputFormat === "json") {
@@ -364,6 +378,33 @@
$this->resourceClasses = array(0,1,2);
$this->generateDatasetMetadata($this->datasetDoc);
}
+ if (isset($this->searchResources) & strtolower($this->searchResources) === "application") {
+ $this->databaseIdColumnName = 'metadata_id'; //not metadata_id as in original table!
+ $this->databaseTableName = 'mb_metadata';
+ $this->searchView = 'search_application_view';
+ $this->whereStrCatExtension = " AND custom_category.custom_category_hidden = 0";
+ switch ($this->orderBy) {
+ case "rank":
+ $this->orderBy = " ORDER BY load_count DESC";
+ break;
+ case "id":
+ $this->orderBy = " ORDER BY metadata_id ASC";
+ break;
+ case "title":
+ $this->orderBy = " ORDER BY title DESC";
+ break;
+ case "date":
+ $this->orderBy = " ORDER BY dataset_timestamp DESC ";
+ break;
+ default:
+ //$this->orderBy = " ORDER BY load_count DESC";
+ $this->orderBy = " ORDER BY title DESC";
+ }
+
+ $this->resourceClasses = array(0,1,2);
+ $this->generateApplicationMetadata($this->applicationDoc);
+ }
+
}
$this->e = new mb_notice("orderBy new: ".$this->orderBy);
}
@@ -774,6 +815,71 @@
}
+ private function generateApplicationMetadataJSON($res, $n) {
+ //initialize object
+ $this->applicationJSON = new stdClass;
+ $this->applicationJSON->application = (object) array(
+ 'md' => (object) array(
+ 'nresults' => $n,
+ 'p' => $this->searchPages,
+ 'rpp' => $this->maxResults
+ ),
+ 'srv' => array()
+ );
+
+ //read out records
+ $serverCount = 0;
+ $applicationMatrix = db_fetch_all($res);
+ //sort result for accessing the right services
+ $applicationMatrix = $this->flipDiagonally($applicationMatrix);
+ //TODO check if order by db or order by php is faster!
+ #array_multisort($wfsMatrix['wfs_id'], SORT_ASC,$wfsMatrix['featuretype_id'], SORT_ASC,$wfsMatrix['wfs_conf_id'], SORT_ASC); //have some problems - the database version is more stable
+ #print_r($wfsMatrix);
+ $applicationMatrix = $this->flipDiagonally($applicationMatrix);
+ //read out first server entry - maybe this a little bit timeconsuming TODO
+ for($i=0; $i<count($applicationMatrix);$i++){
+ $this->applicationJSON->application->srv[$i]->id = $applicationMatrix[$i]['dataset_id'];
+ $this->applicationJSON->application->srv[$i]->title = $applicationMatrix[$i]['title'];
+ $this->applicationJSON->application->srv[$i]->uuid = $applicationMatrix[$i]['fileidentifier'];
+ $this->applicationJSON->application->srv[$i]->abstract = $applicationMatrix[$i]['dataset_abstract'];
+ $this->applicationJSON->application->srv[$i]->date = date("Y-m-d",strtotime($applicationMatrix[$i]['dataset_timestamp']));
+ $this->applicationJSON->application->srv[$i]->loadCount = $applicationMatrix[$i]['load_count'];
+ $this->applicationJSON->application->srv[$i]->respOrg = $applicationMatrix[$i]['mb_group_name'];
+ $this->applicationJSON->application->srv[$i]->logoUrl = $applicationMatrix[$i]['mb_group_logo_path'];
+ list($hasConstraints, $symbolLink) = $this->hasConstraints("dataset", $applicationMatrix[$i]['dataset_id']);
+ $this->applicationJSON->application->srv[$i]->hasConstraints = $hasConstraints;
+ $this->applicationJSON->application->srv[$i]->isopen = $applicationMatrix[$i]['isopen'];
+ $this->applicationJSON->application->srv[$i]->symbolLink = $symbolLink;
+ //TODO: other url - to metadata uuid!
+ $this->applicationJSON->application->srv[$i]->mdLink = $this->protocol."://".$this->hostName."/mapbender/php/mod_iso19139ToHtml.php?url=".urlencode($this->protocol."://".$this->hostName."/mapbender/php/mod_dataISOMetadata.php?outputFormat=iso19139&id=").$applicationMatrix[$i]['fileidentifier'];
+ //TODO: preview?
+ //TODO: preview and access url should be generated by class_administration.php
+ $admin = new administration();
+ $notNullElements = array('fkey_gui_id', 'fkey_mapviewer_id', 'fkey_wmc_serial_id');
+ foreach($notNullElements as $notNullElement) {
+ if ($applicationMatrix[$i][$notNullElement] == '' || $applicationMatrix[$i][$notNullElement] == null) {
+ $applicationMatrix[$i][$notNullElement] = false;
+ }
+ }
+$accessUrl = $admin->getMapviewerInvokeUrl($applicationMatrix[$i]['fkey_mapviewer_id'], $applicationMatrix[$i]['fkey_gui_id'], $applicationMatrix[$i]['fkey_wmc_serial_id']);
+$previewUrl = $admin->getMetadataPreviewUrl($applicationMatrix[$i]['metadata_id']);
+$this->applicationJSON->application->srv[$i]->accessURL = $accessUrl;
+ $this->applicationJSON->application->srv[$i]->previewURL = $previewUrl;
+ $spatialSource = "";
+ $stateOrProvince = $applicationMatrix[$i]['mb_group_stateorprovince'];
+ if ($stateOrProvince == "NULL" || $stateOrProvince == "") {
+ $spatialSource = $applicationMatrix[$i]['mb_group_country'];
+ } else {
+ $spatialSource = $applicationMatrix[$i]['mb_group_stateorprovince'];
+ }
+ $this->applicationJSON->application->srv[$i]->iso3166 = $spatialSource;
+
+ $this->applicationJSON->application->srv[$i]->bbox = array($applicationMatrix[$i]['bbox']); //TODO: read out bbox from wmc $applicationMatrix[$i][''];
+ $this->applicationJSON->application->srv[$i]->timeBegin = date("Y-m-d",strtotime($applicationMatrix[$i]['timebegin']));
+ $this->applicationJSON->application->srv[$i]->timeEnd = date("Y-m-d",strtotime($applicationMatrix[$i]['timeend']));
+ }
+ }
+
private function generateWMSMetadataJSON($res, $n) {
//initialize object
$this->wmsJSON = new stdClass;
@@ -998,6 +1104,7 @@
}*/
}
if ($this->resultTarget == 'web' or $this->resultTarget == 'debug') {
+ header('Content-Type: application/json');
echo $this->wmsJSON;
}
if ($this->resultTarget == 'internal') {
@@ -1004,6 +1111,7 @@
$this->internalResult = $this->wmsJSON;
}
if ($this->resultTarget == 'categories') {
+header('Content-Type: application/json');
echo $this->catJSON;
}
if ($this->resultTarget == 'webclient') {
@@ -1029,6 +1137,7 @@
//if (defined("CORS_WHITELIST") && CORS_WHITELIST != "") {
// header('Access-Control-Allow-Origin: '.CORS_WHITELIST);
//}
+header('Content-Type: application/json');
echo $this->json->encode($this->allJSON);
//echo "test";
}
@@ -1064,9 +1173,11 @@
}*/
}
if ($this->resultTarget == 'web'or $this->resultTarget == 'debug') {
+header('Content-Type: application/json');
echo $this->wfsJSON;
}
if ($this->resultTarget == 'categories') {
+header('Content-Type: application/json');
echo $this->catJSON;
}
if ($this->resultTarget == 'internal') {
@@ -1095,6 +1206,7 @@
//if (defined("CORS_WHITELIST") && CORS_WHITELIST != "") {
// header('Access-Control-Allow-Origin: '.CORS_WHITELIST);
//}
+header('Content-Type: application/json');
echo $this->json->encode($this->allJSON);
//echo "test";
}
@@ -1125,9 +1237,11 @@
}*/
}
if ($this->resultTarget == 'web' or $this->resultTarget == 'debug') {
+header('Content-Type: application/json');
echo $this->wmcJSON;
}
if ($this->resultTarget == 'categories') {
+header('Content-Type: application/json');
echo $this->catJSON;
}
if ($this->resultTarget == 'webclient') {
@@ -1185,9 +1299,11 @@
}*/
}
if ($this->resultTarget == 'web' or $this->resultTarget == 'debug') {
+header('Content-Type: application/json');
echo $this->datasetJSON;
}
if ($this->resultTarget == 'categories') {
+header('Content-Type: application/json');
echo $this->catJSON;
}
if ($this->resultTarget == 'webclient') {
@@ -1213,6 +1329,7 @@
//if (defined("CORS_WHITELIST") && CORS_WHITELIST != "") {
// header('Access-Control-Allow-Origin: '.CORS_WHITELIST);
//}
+header('Content-Type: application/json');
echo $this->json->encode($this->allJSON);
//echo "test";
}
@@ -1223,6 +1340,65 @@
$e = new mb_notice("Wrote the MD_Dataset-File");
}
+ private function generateApplicationMetadata($xmlDoc) {
+ $starttime = $this->microtime_float();
+ list($sql, $v, $t, $n) = $this->generateSearchSQL();
+ //call database search in limits
+ $res = db_prep_query($sql, $v, $t);
+ if ($this->outputFormat == 'json'){
+ //generate json
+ $this->generateApplicationMetadataJSON($res,$n);
+ $usedTime = $this->microtime_float() - $starttime;
+ //put in the time to generate the data
+ $this->applicationJSON->application->md->genTime = $usedTime;
+ $this->applicationJSON = $this->json->encode($this->applicationJSON);
+ if ($this->resultTarget == 'file') {
+ $filename = $this->tempFolder."/".$this->searchId."_".$this->searchResources."_".$this->searchPages.".json";
+ $admin = new administration();
+ $admin->putToStorage($filename, $this->applicationJSON, TMP_SEARCH_RESULT_STORAGE, TMP_SEARCH_RESULT_MAX_AGE);
+ }
+ if ($this->resultTarget == 'web' or $this->resultTarget == 'debug') {
+header('Content-Type: application/json');
+ echo $this->applicationJSON;
+ }
+ if ($this->resultTarget == 'categories') {
+header('Content-Type: application/json');
+ echo $this->catJSON;
+ }
+ if ($this->resultTarget == 'webclient') {
+ $this->allJSON = new stdClass;
+ $this->allJSON->categories = $this->json->decode($this->catJSON);
+ $this->allJSON->keywords = $this->json->decode($this->keyJSON);
+ //load filter from file getFromStorage
+ $admin = new administration();
+ $filename = $this->tempFolder."/".$this->searchId."_filter.json";
+ $fileExists = $admin->getFromStorage($filename,TMP_SEARCH_RESULT_STORAGE);
+ if ($fileExists == false) {
+ //$e = new mb_exception("class_metadata.php: No filter json exists!");
+ } else {
+ $filterJSON = $fileExists;
+ $filterJSON = $this->json->decode($filterJSON);
+ $this->allJSON->filter = $filterJSON;
+ }
+ $this->allJSON->application = $this->json->decode($this->applicationJSON);
+ //$e = new mb_exception("originFromHeader: ".$this->originFromHeader);
+ if ($this->originFromHeader != false) {
+ header('Access-Control-Allow-Origin: '.$this->originFromHeader);
+ }
+ //if (defined("CORS_WHITELIST") && CORS_WHITELIST != "") {
+ // header('Access-Control-Allow-Origin: '.CORS_WHITELIST);
+ //}
+header('Content-Type: application/json');
+ echo $this->json->encode($this->allJSON);
+ //echo "test";
+ }
+ }
+ $usedTime2 = $this->microtime_float() - $starttime;
+ //echo "<br>used time: ".$usedTime."<br>";
+ $e = new mb_notice("Time to generate Application-Metadata: ".$usedTime2);
+ $e = new mb_notice("Wrote the MD_Application-File");
+ }
+
private function replaceChars_all($text){
$search = array( "ä", "ö", "ü", "Ä", "Ö", "Ü", "ß");
$repWith = array("ae", "oe", "ue", "AE", "OE", "UE", "ss");
@@ -1268,7 +1444,7 @@
//check for postgis version
//sql for get version string
//get version number
- if ((strtolower($this->searchResources) === "wms" or strtolower($this->searchResources) === "wmc" or strtolower($this->searchResources) === "dataset" or strtolower($this->searchResources) === "wfs" ) & $this->searchBbox != NULL) {
+ if ((strtolower($this->searchResources) === "wms" or strtolower($this->searchResources) === "wmc" or strtolower($this->searchResources) === "dataset" or strtolower($this->searchResources) === "wfs" ) or strtolower($this->searchResources) === "application" & $this->searchBbox != NULL) {
//decide which type of search should be done
//check for postgis version cause postgis versions < 1.4 have problems when doing disjoint and inside
//
@@ -1410,7 +1586,7 @@
}
//filter for data actuality (only for datasets)
- if (strtolower($this->searchResources) === "dataset") {
+ if (strtolower($this->searchResources) === "dataset" || strtolower($this->searchResources) === "application") {
if ($this->timeBegin != NULL && $this->timeEnd != NULL){
$time ="((to_timestamp('".$this->timeBegin."','YYYY-MM-DD'),to_timestamp('".$this->timeEnd."','YYYY-MM-DD')) OVERLAPS (timebegin,timeend))";
array_push($whereCondArray, $time);
@@ -1484,7 +1660,11 @@
private function writeCategories($whereStr, $v, $t) {
//generate count sql
//generate count of all entries
- $sqlN = "SELECT count(".$this->searchResources."_id) from ".$this->searchView." ";
+ if ($this->searchResources != 'application') {
+ $sqlN = "SELECT count(".$this->searchResources."_id) from ".$this->searchView." ";
+ } else{
+ $sqlN = "SELECT count(metadata_id) from ".$this->searchView." ";
+ }
if ($whereStr != '') {
$sqlN .= $whereStr;
}
@@ -2128,6 +2308,13 @@
$sql .= " md_termsofuse ON (mb_metadata.metadata_id = md_termsofuse.fkey_metadata_id) LEFT OUTER JOIN termsofuse ON";
$sql .= " (md_termsofuse.fkey_termsofuse_id=termsofuse.termsofuse_id) where mb_metadata.metadata_id = $1";
}
+ if ($type == "application") {
+ $sql = "SELECT constraints as accessconstraints, fees, termsofuse.name,";
+ $sql .= " termsofuse.termsofuse_id ,termsofuse.symbollink, termsofuse.description,termsofuse.descriptionlink from mb_metadata LEFT OUTER JOIN";
+ $sql .= " md_termsofuse ON (mb_metadata.metadata_id = md_termsofuse.fkey_metadata_id) LEFT OUTER JOIN termsofuse ON";
+ $sql .= " (md_termsofuse.fkey_termsofuse_id=termsofuse.termsofuse_id) where mb_metadata.metadata_id = $1";
+}
+
$v = array();
$t = array();
array_push($t, "i");
Modified: trunk/mapbender/http/php/mod_callMetadata.php
===================================================================
--- trunk/mapbender/http/php/mod_callMetadata.php 2019-10-17 06:41:44 UTC (rev 10286)
+++ trunk/mapbender/http/php/mod_callMetadata.php 2019-10-18 10:31:58 UTC (rev 10287)
@@ -384,9 +384,9 @@
} else {
$testArray = explode(',',$testMatch);
for($i=0; $i<count($testArray);$i++){
- if (!($testArray[$i] == 'wms' or $testArray[$i] == 'wfs' or $testArray[$i] == 'wmc' or $testArray[$i] == 'dataset')) {
+ if (!($testArray[$i] == 'wms' or $testArray[$i] == 'wfs' or $testArray[$i] == 'wmc' or $testArray[$i] == 'dataset' or $testArray[$i] == 'application')) {
//echo 'searchResources: <b>'.$testMatch.'</b>at least one of them does not exists!<br/>';
- echo 'Parameter <b>searchResources</b>at least one of them does not exists! (wms,wfs,wmc,dataset)<br/>';
+ echo 'Parameter <b>searchResources</b>at least one of them does not exists! (wms,wfs,wmc,dataset,application)<br/>';
die();
}
}
@@ -691,6 +691,7 @@
$resourceCategories['wmc'] = 'Kartenzusammenstellungen';
$resourceCategories['dataset'] = 'Datensätze';
$resourceCategories['georss'] = 'KML/Newsfeeds';
+ $resourceCategories['application'] = 'Anwendungen';
$orderByTitle['header'] = 'Sortierung nach:';
$orderByTitle['id'] = 'Identifizierungsnummer';
@@ -721,6 +722,7 @@
$resourceCategories['wmc'] = 'Combined Maps';
$resourceCategories['dataset'] = 'Datasets';
$resourceCategories['georss'] = 'KML/Newsfeeds';
+ $resourceCategories['application'] = 'Applications';
$orderByTitle['header'] = 'Sort by:';
$orderByTitle['id'] = 'identification number';
@@ -749,7 +751,8 @@
$resourceCategories['wfs'] = 'Services de recherche et de téléchargement';
$resourceCategories['wmc'] = 'Cartes composées';
$resourceCategories['dataset'] = 'Datasets';
- $resourceCategories['georss'] = 'KML/Newsfeeds';
+ $resourceCategories['georss'] = 'KML/Newsfeeds';
+ $resourceCategories['application'] = 'Applications';
$orderByTitle['header'] = 'classé selon:';
$orderByTitle['id'] = 'numéro d\'identification';
@@ -779,6 +782,7 @@
$resourceCategories['wmc'] = 'Kartenzusammenstellungen';
$resourceCategories['dataset'] = 'Datasets';
$resourceCategories['georss'] = 'KML/Newsfeeds';
+ $resourceCategories['application'] = 'Applications';
$orderByTitle['header'] = 'Sortierung nach:';
$orderByTitle['id'] = 'ID';
@@ -1133,7 +1137,7 @@
#echo "class initiated<br>";
}
else {
- echo "Result for web can only requested for one type of resource (wms, wfs, wmc, georss)!";
+ echo "Result for web can only requested for one type of resource (wms, wfs, wmc, georss, application)!";
}
}
/*
Modified: trunk/mapbender/resources/db/pgsql/UTF-8/update/update_2.8_pgsql_UTF-8.sql
===================================================================
--- trunk/mapbender/resources/db/pgsql/UTF-8/update/update_2.8_pgsql_UTF-8.sql 2019-10-17 06:41:44 UTC (rev 10286)
+++ trunk/mapbender/resources/db/pgsql/UTF-8/update/update_2.8_pgsql_UTF-8.sql 2019-10-18 10:31:58 UTC (rev 10287)
@@ -152,3 +152,517 @@
REFERENCES gui (gui_id) MATCH SIMPLE
ON UPDATE CASCADE;
--todo sql body element e_width=null ... e_styles: overflow:scroll; ....
+
+-- new for search for applications - materialized view:
+
+-- needs postgres 9.3+!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
+-- View: search_dataset_view
+
+DROP MATERIALIZED VIEW search_application_view;
+
+CREATE MATERIALIZED VIEW search_application_view AS
+ SELECT DISTINCT ON (datasets.metadata_id) datasets.user_id,
+ datasets.dataset_id,
+ datasets.metadata_id,
+ datasets.dataset_srs,
+ datasets.title,
+ datasets.dataset_abstract,
+ datasets.accessconstraints,
+ datasets.isopen,
+ datasets.termsofuse,
+ datasets.searchtext,
+ datasets.dataset_timestamp,
+ datasets.department,
+ datasets.mb_group_name,
+ datasets.mb_group_title,
+ datasets.mb_group_country,
+ datasets.load_count,
+ datasets.mb_group_stateorprovince,
+ datasets.md_inspire_cats,
+ datasets.md_custom_cats,
+ datasets.md_topic_cats,
+ datasets.the_geom,
+ datasets.bbox,
+ datasets.preview_url,
+ datasets.fileidentifier,
+ datasets.coupled_resources,
+ datasets.mb_group_logo_path,
+ datasets.timebegin,
+ datasets.timeend,
+--new for application metadata
+ datasets.link,
+ datasets.fkey_gui_id,
+ datasets.fkey_wmc_serial_id,
+ datasets.fkey_mapviewer_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 dataset_dep.load_count IS NULL THEN 0::bigint
+ ELSE dataset_dep.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::date AS timebegin,
+dataset_dep.link AS link,
+dataset_dep.fkey_gui_id AS fkey_gui_id,
+dataset_dep.fkey_wmc_serial_id AS fkey_wmc_serial_id,
+dataset_dep.fkey_mapviewer_id AS fkey_mapviewer_id,
+
+ CASE
+ WHEN dataset_dep.update_frequency::text = 'continual'::text THEN now()::date
+ WHEN dataset_dep.update_frequency::text = 'daily'::text THEN now()::date
+ WHEN dataset_dep.update_frequency::text = 'weekly'::text THEN (now() - '7 days'::interval)::date
+ WHEN dataset_dep.update_frequency::text = 'fortnightly'::text THEN (now() - '14 days'::interval)::date
+ WHEN dataset_dep.update_frequency::text = 'monthly'::text THEN (now() - '1 mon'::interval)::date
+ WHEN dataset_dep.update_frequency::text = 'quarterly'::text THEN (now() - '3 mons'::interval)::date
+ WHEN dataset_dep.update_frequency::text = 'biannually'::text THEN (now() - '6 mons'::interval)::date
+ WHEN dataset_dep.update_frequency::text = 'annually'::text THEN (now() - '1 year'::interval)::date
+ ELSE dataset_dep.timeend::date
+ END AS 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,
+ mb_metadata.fkey_mb_user_id,
+ mb_metadata.constraints AS accessconstraints,
+ mb_metadata.update_frequency,
+ f_getmd_tou(mb_metadata.metadata_id) AS termsofuse,
+ f_tou_isopen(f_getmd_tou(mb_metadata.metadata_id)) AS isopen,
+ mb_metadata.mb_group_id AS department,
+ mb_metadata.mb_group_name,
+ mb_metadata.mb_group_title,
+ mb_metadata.mb_group_country,
+ mb_metadata.mb_group_stateorprovince,
+ mb_metadata.mb_group_logo_path,
+mb_metadata.link,
+mb_metadata.fkey_gui_id,
+mb_metadata.fkey_wmc_serial_id,
+mb_metadata.fkey_mapviewer_id
+
+ FROM ( SELECT mb_metadata_1.metadata_id,
+ mb_metadata_1.uuid,
+ mb_metadata_1.origin,
+ mb_metadata_1.includeincaps,
+ mb_metadata_1.fkey_mb_group_id,
+ mb_metadata_1.schema,
+ mb_metadata_1.createdate,
+ mb_metadata_1.changedate,
+ mb_metadata_1.lastchanged,
+ mb_metadata_1.link,
+ mb_metadata_1.linktype,
+ mb_metadata_1.md_format,
+ mb_metadata_1.title,
+ mb_metadata_1.abstract,
+ mb_metadata_1.searchtext,
+ mb_metadata_1.status,
+ mb_metadata_1.type,
+ mb_metadata_1.harvestresult,
+ mb_metadata_1.harvestexception,
+ mb_metadata_1.export2csw,
+ mb_metadata_1.tmp_reference_1,
+ mb_metadata_1.tmp_reference_2,
+ mb_metadata_1.spatial_res_type,
+ mb_metadata_1.spatial_res_value,
+ mb_metadata_1.ref_system,
+ mb_metadata_1.format,
+ mb_metadata_1.inspire_charset,
+ mb_metadata_1.inspire_top_consistence,
+ mb_metadata_1.fkey_mb_user_id,
+ mb_metadata_1.responsible_party,
+ mb_metadata_1.individual_name,
+ mb_metadata_1.visibility,
+ mb_metadata_1.locked,
+ mb_metadata_1.copyof,
+ mb_metadata_1.constraints,
+ mb_metadata_1.fees,
+ mb_metadata_1.classification,
+ mb_metadata_1.browse_graphic,
+ mb_metadata_1.inspire_conformance,
+ mb_metadata_1.preview_image,
+ mb_metadata_1.the_geom,
+ mb_metadata_1.lineage,
+ mb_metadata_1.datasetid,
+ mb_metadata_1.randomid,
+ mb_metadata_1.update_frequency,
+ mb_metadata_1.datasetid_codespace,
+ mb_metadata_1.bounding_geom,
+ mb_metadata_1.inspire_whole_area,
+ mb_metadata_1.inspire_actual_coverage,
+ mb_metadata_1.datalinks,
+ mb_metadata_1.inspire_download,
+ mb_metadata_1.transfer_size,
+ mb_metadata_1.md_license_source_note,
+ mb_metadata_1.responsible_party_name,
+ mb_metadata_1.responsible_party_email,
+ mb_metadata_1.searchable,
+ mb_metadata_1.load_count,
+
+mb_metadata_1.fkey_gui_id,
+mb_metadata_1.fkey_wmc_serial_id,
+mb_metadata_1.fkey_mapviewer_id,
+
+ user_dep.fkey_mb_group_id,
+ user_dep.mb_group_id,
+ 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,
+ user_dep.fkey_mb_user_id_from_users
+ FROM ( SELECT mb_metadata_2.metadata_id,
+ mb_metadata_2.uuid,
+ mb_metadata_2.origin,
+ mb_metadata_2.includeincaps,
+ mb_metadata_2.fkey_mb_group_id,
+ mb_metadata_2.schema,
+ mb_metadata_2.createdate,
+ mb_metadata_2.changedate,
+ mb_metadata_2.lastchanged,
+ mb_metadata_2.link,
+ mb_metadata_2.linktype,
+ mb_metadata_2.md_format,
+ mb_metadata_2.title,
+ mb_metadata_2.abstract,
+ mb_metadata_2.searchtext,
+ mb_metadata_2.status,
+ mb_metadata_2.type,
+ mb_metadata_2.harvestresult,
+ mb_metadata_2.harvestexception,
+ mb_metadata_2.export2csw,
+ mb_metadata_2.tmp_reference_1,
+ mb_metadata_2.tmp_reference_2,
+ mb_metadata_2.spatial_res_type,
+ mb_metadata_2.spatial_res_value,
+ mb_metadata_2.ref_system,
+ mb_metadata_2.format,
+ mb_metadata_2.inspire_charset,
+ mb_metadata_2.inspire_top_consistence,
+ mb_metadata_2.fkey_mb_user_id,
+ mb_metadata_2.responsible_party,
+ mb_metadata_2.individual_name,
+ mb_metadata_2.visibility,
+ mb_metadata_2.locked,
+ mb_metadata_2.copyof,
+ mb_metadata_2.constraints,
+ mb_metadata_2.fees,
+ mb_metadata_2.classification,
+ mb_metadata_2.browse_graphic,
+ mb_metadata_2.inspire_conformance,
+ mb_metadata_2.preview_image,
+ mb_metadata_2.the_geom,
+ mb_metadata_2.lineage,
+ mb_metadata_2.datasetid,
+ mb_metadata_2.randomid,
+ mb_metadata_2.update_frequency,
+ mb_metadata_2.datasetid_codespace,
+ mb_metadata_2.bounding_geom,
+ mb_metadata_2.inspire_whole_area,
+ mb_metadata_2.inspire_actual_coverage,
+ mb_metadata_2.datalinks,
+ mb_metadata_2.inspire_download,
+ mb_metadata_2.transfer_size,
+ mb_metadata_2.md_license_source_note,
+ mb_metadata_2.responsible_party_name,
+ mb_metadata_2.responsible_party_email,
+ mb_metadata_2.searchable,
+
+mb_metadata_2.fkey_gui_id,
+mb_metadata_2.fkey_wmc_serial_id,
+mb_metadata_2.fkey_mapviewer_id,
+
+ metadata_load_count.load_count
+ FROM mb_metadata mb_metadata_2
+ LEFT JOIN metadata_load_count ON mb_metadata_2.metadata_id = metadata_load_count.fkey_metadata_id) mb_metadata_1,
+ ( SELECT groups_for_publishing.fkey_mb_group_id,
+ groups_for_publishing.mb_group_id,
+ groups_for_publishing.mb_group_name,
+ groups_for_publishing.mb_group_title,
+ groups_for_publishing.mb_group_country,
+ groups_for_publishing.mb_group_stateorprovince,
+ groups_for_publishing.mb_group_logo_path,
+ 0 AS fkey_mb_user_id_from_users
+ FROM groups_for_publishing) user_dep
+ WHERE mb_metadata_1.fkey_mb_group_id = user_dep.mb_group_id AND mb_metadata_1.the_geom IS NOT NULL AND mb_metadata_1.searchable IS TRUE AND mb_metadata_1.type = 'application'
+ UNION ALL
+ SELECT mb_metadata_1.metadata_id,
+ mb_metadata_1.uuid,
+ mb_metadata_1.origin,
+ mb_metadata_1.includeincaps,
+ mb_metadata_1.fkey_mb_group_id,
+ mb_metadata_1.schema,
+ mb_metadata_1.createdate,
+ mb_metadata_1.changedate,
+ mb_metadata_1.lastchanged,
+ mb_metadata_1.link,
+ mb_metadata_1.linktype,
+ mb_metadata_1.md_format,
+ mb_metadata_1.title,
+ mb_metadata_1.abstract,
+ mb_metadata_1.searchtext,
+ mb_metadata_1.status,
+ mb_metadata_1.type,
+ mb_metadata_1.harvestresult,
+ mb_metadata_1.harvestexception,
+ mb_metadata_1.export2csw,
+ mb_metadata_1.tmp_reference_1,
+ mb_metadata_1.tmp_reference_2,
+ mb_metadata_1.spatial_res_type,
+ mb_metadata_1.spatial_res_value,
+ mb_metadata_1.ref_system,
+ mb_metadata_1.format,
+ mb_metadata_1.inspire_charset,
+ mb_metadata_1.inspire_top_consistence,
+ mb_metadata_1.fkey_mb_user_id,
+ mb_metadata_1.responsible_party,
+ mb_metadata_1.individual_name,
+ mb_metadata_1.visibility,
+ mb_metadata_1.locked,
+ mb_metadata_1.copyof,
+ mb_metadata_1.constraints,
+ mb_metadata_1.fees,
+ mb_metadata_1.classification,
+ mb_metadata_1.browse_graphic,
+ mb_metadata_1.inspire_conformance,
+ mb_metadata_1.preview_image,
+ mb_metadata_1.the_geom,
+ mb_metadata_1.lineage,
+ mb_metadata_1.datasetid,
+ mb_metadata_1.randomid,
+ mb_metadata_1.update_frequency,
+ mb_metadata_1.datasetid_codespace,
+ mb_metadata_1.bounding_geom,
+ mb_metadata_1.inspire_whole_area,
+ mb_metadata_1.inspire_actual_coverage,
+ mb_metadata_1.datalinks,
+ mb_metadata_1.inspire_download,
+ mb_metadata_1.transfer_size,
+ mb_metadata_1.md_license_source_note,
+ mb_metadata_1.responsible_party_name,
+ mb_metadata_1.responsible_party_email,
+ mb_metadata_1.searchable,
+ mb_metadata_1.load_count,
+
+mb_metadata_1.fkey_gui_id,
+mb_metadata_1.fkey_wmc_serial_id,
+mb_metadata_1.fkey_mapviewer_id,
+
+ user_dep.fkey_mb_group_id,
+ user_dep.mb_group_id,
+ 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,
+ user_dep.fkey_mb_user_id_from_users
+ FROM ( SELECT mb_metadata_2.metadata_id,
+ mb_metadata_2.uuid,
+ mb_metadata_2.origin,
+ mb_metadata_2.includeincaps,
+ mb_metadata_2.fkey_mb_group_id,
+ mb_metadata_2.schema,
+ mb_metadata_2.createdate,
+ mb_metadata_2.changedate,
+ mb_metadata_2.lastchanged,
+ mb_metadata_2.link,
+ mb_metadata_2.linktype,
+ mb_metadata_2.md_format,
+ mb_metadata_2.title,
+ mb_metadata_2.abstract,
+ mb_metadata_2.searchtext,
+ mb_metadata_2.status,
+ mb_metadata_2.type,
+ mb_metadata_2.harvestresult,
+ mb_metadata_2.harvestexception,
+ mb_metadata_2.export2csw,
+ mb_metadata_2.tmp_reference_1,
+ mb_metadata_2.tmp_reference_2,
+ mb_metadata_2.spatial_res_type,
+ mb_metadata_2.spatial_res_value,
+ mb_metadata_2.ref_system,
+ mb_metadata_2.format,
+ mb_metadata_2.inspire_charset,
+ mb_metadata_2.inspire_top_consistence,
+ mb_metadata_2.fkey_mb_user_id,
+ mb_metadata_2.responsible_party,
+ mb_metadata_2.individual_name,
+ mb_metadata_2.visibility,
+ mb_metadata_2.locked,
+ mb_metadata_2.copyof,
+ mb_metadata_2.constraints,
+ mb_metadata_2.fees,
+ mb_metadata_2.classification,
+ mb_metadata_2.browse_graphic,
+ mb_metadata_2.inspire_conformance,
+ mb_metadata_2.preview_image,
+ mb_metadata_2.the_geom,
+ mb_metadata_2.lineage,
+ mb_metadata_2.datasetid,
+ mb_metadata_2.randomid,
+ mb_metadata_2.update_frequency,
+ mb_metadata_2.datasetid_codespace,
+ mb_metadata_2.bounding_geom,
+ mb_metadata_2.inspire_whole_area,
+ mb_metadata_2.inspire_actual_coverage,
+ mb_metadata_2.datalinks,
+ mb_metadata_2.inspire_download,
+ mb_metadata_2.transfer_size,
+ mb_metadata_2.md_license_source_note,
+ mb_metadata_2.responsible_party_name,
+ mb_metadata_2.responsible_party_email,
+ mb_metadata_2.searchable,
+
+mb_metadata_2.fkey_gui_id,
+mb_metadata_2.fkey_wmc_serial_id,
+mb_metadata_2.fkey_mapviewer_id,
+
+ metadata_load_count.load_count
+ FROM mb_metadata mb_metadata_2
+ LEFT JOIN metadata_load_count ON mb_metadata_2.metadata_id = metadata_load_count.fkey_metadata_id) mb_metadata_1,
+ ( SELECT publishing_registrating_authorities.fkey_mb_group_id,
+ publishing_registrating_authorities.mb_group_id,
+ publishing_registrating_authorities.mb_group_name,
+ publishing_registrating_authorities.mb_group_title,
+ publishing_registrating_authorities.mb_group_country,
+ publishing_registrating_authorities.mb_group_stateorprovince,
+ publishing_registrating_authorities.mb_group_logo_path,
+ users_for_publishing.fkey_mb_user_id AS fkey_mb_user_id_from_users
+ FROM groups_for_publishing publishing_registrating_authorities,
+ users_for_publishing
+ WHERE users_for_publishing.primary_group_id = publishing_registrating_authorities.fkey_mb_group_id) user_dep
+ WHERE (mb_metadata_1.fkey_mb_group_id IS NULL OR mb_metadata_1.fkey_mb_group_id = 0) AND mb_metadata_1.fkey_mb_user_id = user_dep.fkey_mb_user_id_from_users AND mb_metadata_1.the_geom IS NOT NULL AND mb_metadata_1.searchable IS TRUE AND mb_metadata_1.type = 'application') mb_metadata(metadata_id, uuid, origin, includeincaps, fkey_mb_group_id, schema, createdate, changedate, lastchanged, link, linktype, md_format, title, abstract, searchtext, status, type, harvestresult, harvestexception, export2csw, tmp_reference_1, tmp_reference_2, spatial_res_type, spatial_res_value, ref_system, format, inspire_charset, inspire_top_consistence, fkey_mb_user_id, responsible_party, individual_name, visibility, locked, copyof, constraints, fees, classification, browse_graphic, inspire_conformance, preview_image, the_geom, lineage, datasetid, randomid, update_frequency, datasetid_codespace, bounding_geom, inspire_whole_area, inspire_actual_coverage, datalinks, inspire_downloa
d, transfer_size, md_license_source_note, responsible_party_name, responsible_party_email, searchable, load_count, fkey_gui_id, fkey_wmc_serial_id, fkey_mapviewer_id, fkey_mb_group_id_1, mb_group_id, mb_group_name, mb_group_title, mb_group_country, mb_group_stateorprovince, mb_group_logo_path, fkey_mb_user_id_from_users)) dataset_dep
+ ORDER BY dataset_dep.dataset_id) datasets;
+
+ALTER TABLE search_application_view
+ OWNER TO postgres;
+GRANT ALL ON TABLE search_application_view TO postgres;
+GRANT ALL ON TABLE search_application_view TO mapbenderdbuser;
+
+-- create indices:
+--UPDATE search_application_view SET load_count=0 WHERE load_count is NULL;
+
+-- Index: gist_wst_application_the_geom
+
+-- DROP INDEX gist_wst_application_the_geom;
+
+CREATE INDEX gist_wst_application_the_geom
+ ON search_application_view
+ USING gist
+ (the_geom);
+
+-- Index: idx_wst_application_searchtext
+
+-- DROP INDEX idx_wst_application_searchtext;
+
+CREATE INDEX idx_wst_application_searchtext
+ ON search_application_view
+ USING btree
+ (searchtext);
+
+-- Index: idx_wst_application_department
+
+-- DROP INDEX idx_wst_application_department;
+
+CREATE INDEX idx_wst_application_department
+ ON search_application_view
+ USING btree
+ (department);
+-- Index: idx_wst_application_md_topic_cats
+
+-- DROP INDEX idx_wst_application_md_topic_cats;
+
+CREATE INDEX idx_wst_application_md_topic_cats
+ ON search_application_view
+ USING btree
+ (md_topic_cats);
+-- Index: idx_wst_application_dataset_id
+
+-- DROP INDEX idx_wst_application_metadata_id;
+
+CREATE INDEX idx_wst_application_metadata_id
+ ON search_application_view
+ USING btree
+ (metadata_id);
+
+-- DROP INDEX idx_wst_application_metadata_id;
+
+CREATE INDEX idx_wst_application_dataset_id
+ ON search_application_view
+ USING btree
+ (dataset_id);
+-- Index: idx_wst_application_md_inspire_cats
+
+-- DROP INDEX idx_wst_application_md_inspire_cats;
+
+CREATE INDEX idx_wst_application_md_inspire_cats
+ ON search_application_view
+ USING btree
+ (md_inspire_cats);
+
+-- Index: idx_wst_application_md_custom_cats
+
+-- DROP INDEX idx_wst_application_md_custom_cats;
+
+CREATE INDEX idx_wst_application_md_custom_cats
+ ON search_application_view
+ USING btree
+ (md_custom_cats);
+
+-- Index: idx_wst_application_timebegin
+
+-- DROP INDEX idx_wst_application_timebegin;
+
+CREATE INDEX idx_wst_application_timebegin
+ ON search_application_view
+ USING btree
+ (timebegin);
+
+-- Index: idx_wst_application_timeend
+
+-- DROP INDEX idx_wst_application_timeend;
+
+CREATE INDEX idx_wst_application_timeend
+ ON search_application_view
+ USING btree
+ (timeend);
+
+-- Index: idx_wst_application_department
+
+-- DROP INDEX idx_wst_application_department;
+
+CREATE INDEX idx_wst_application_timestamp
+ ON search_application_view
+ USING btree
+ (dataset_timestamp);
+
+
+
+
More information about the Mapbender_commits
mailing list