[Mapbender-commits] r9869 - in trunk/mapbender/http: classes php
svn_mapbender at osgeo.org
svn_mapbender at osgeo.org
Mon Feb 19 07:12:19 PST 2018
Author: armin11
Date: 2018-02-19 07:12:19 -0800 (Mon, 19 Feb 2018)
New Revision: 9869
Modified:
trunk/mapbender/http/classes/class_syncCkan.php
trunk/mapbender/http/php/mod_showOpenDataOrganizations.php
Log:
Fix for exporting opendata information to ckan instances
Modified: trunk/mapbender/http/classes/class_syncCkan.php
===================================================================
--- trunk/mapbender/http/classes/class_syncCkan.php 2018-02-16 11:57:11 UTC (rev 9868)
+++ trunk/mapbender/http/classes/class_syncCkan.php 2018-02-19 15:12:19 UTC (rev 9869)
@@ -751,13 +751,20 @@
//only use metadata for which real licenses are defined !!!!!! - what should be done with the other metadata?- DO a left join!!!
//TODO: test what wents wrong, if fkey_mb_group_id is set in mapbender
if ($organization['is_primary_group']) {
- $sql = "SELECT *, f_get_coupled_resources(metadata_id) from mb_metadata LEFT OUTER JOIN md_termsofuse ON mb_metadata.metadata_id = md_termsofuse.fkey_metadata_id WHERE fkey_mb_user_id = $1 AND (fkey_mb_group_id is null OR fkey_mb_group_id = 0) AND export2csw IS true";
- $v = array($this->mapbenderUserId);
- $t = array('i');
+ $sql = "SELECT metadata_id as ressource_id, 'metadata' as ressource_type, uuid::varchar, title, lastchanged, fkey_termsofuse_id, f_get_coupled_resources(metadata_id) from mb_metadata LEFT OUTER JOIN md_termsofuse ON mb_metadata.metadata_id = md_termsofuse.fkey_metadata_id WHERE fkey_mb_user_id = $1 AND (fkey_mb_group_id is null OR fkey_mb_group_id = 0) AND export2csw IS true AND md_termsofuse.fkey_termsofuse_id in (select termsofuse_id from termsofuse where isopen = 1) ";
+ $sql .= " UNION SELECT layer_metadata.ressource_id, layer_metadata.ressource_type, layer_metadata.uuid::varchar, layer_metadata.title, to_timestamp(layer_metadata.lastchanged), wms_termsofuse.fkey_termsofuse_id, '{\"coupledResources\":{\"layerIds\":[' || layer_metadata.ressource_id || '],\"featuretypeIds\":[]}}' as f_get_coupled_resources FROM ";
+ $sql .= "(SELECT layer_id as ressource_id, 'layer' as ressource_type, layer.uuid::varchar, layer_title as title, wms.wms_timestamp as lastchanged, layer.fkey_wms_id FROM layer INNER JOIN wms on layer.fkey_wms_id = wms.wms_id WHERE wms_owner = $2 AND (fkey_mb_group_id is null OR fkey_mb_group_id = 0)";
+ $sql .= " AND layer.export2csw IS true AND layer.layer_searchable = 1 AND layer_id NOT IN (SELECT DISTINCT fkey_layer_id FROM ows_relation_metadata)) AS layer_metadata INNER JOIN wms_termsofuse ON layer_metadata.fkey_wms_id = wms_termsofuse.fkey_wms_id AND fkey_termsofuse_id IS NOT NULL AND wms_termsofuse.fkey_termsofuse_id IN (SELECT termsofuse_id FROM termsofuse WHERE isopen = 1)";
+ //$e = new mb_exception("class_syncCkan.php: sql: ".$sql);
+ $v = array($this->mapbenderUserId, $this->mapbenderUserId);
+ $t = array('i','i');
} else {
- $sql = "SELECT *, f_get_coupled_resources(metadata_id) from mb_metadata LEFT OUTER JOIN md_termsofuse ON mb_metadata.metadata_id = md_termsofuse.fkey_metadata_id WHERE fkey_mb_group_id = $1 AND export2csw IS true";
- $v = array($organization['id']);
- $t = array('i');
+ $sql = "SELECT metadata_id as ressource_id, 'metadata' as ressource_type, uuid::varchar, title, lastchanged, fkey_termsofuse_id, f_get_coupled_resources(metadata_id) from mb_metadata LEFT OUTER JOIN md_termsofuse ON mb_metadata.metadata_id = md_termsofuse.fkey_metadata_id WHERE fkey_mb_group_id = $1 AND export2csw IS true AND md_termsofuse.fkey_termsofuse_id in (select termsofuse_id from termsofuse where isopen = 1)";
+ $sql .= " UNION SELECT layer_metadata.ressource_id, layer_metadata.ressource_type, layer_metadata.uuid::varchar, layer_metadata.title, to_timestamp(layer_metadata.lastchanged), wms_termsofuse.fkey_termsofuse_id, '{\"coupledResources\":{\"layerIds\":[' || layer_metadata.ressource_id || '],\"featuretypeIds\":[]}}' ";
+ $sql = "as f_get_coupled_resources FROM (SELECT layer_id as ressource_id, 'layer' as ressource_type, layer.uuid::varchar, layer_title as title, wms.wms_timestamp as lastchanged, layer.fkey_wms_id FROM layer INNER JOIN wms on layer.fkey_wms_id = wms.wms_id WHERE fkey_mb_group_id = $2 AND layer.export2csw IS true AND layer.layer_searchable = 1 AND";
+ $sql = " layer_id NOT IN (SELECT DISTINCT fkey_layer_id FROM ows_relation_metadata)) AS layer_metadata INNER JOIN wms_termsofuse ON layer_metadata.fkey_wms_id = wms_termsofuse.fkey_wms_id AND fkey_termsofuse_id IS NOT NULL AND wms_termsofuse.fkey_termsofuse_id IN (SELECT termsofuse_id FROM termsofuse WHERE isopen = 1)";
+ $v = array($organization['id'], $organization['id']);
+ $t = array('i', 'i');
}
$res = db_prep_query($sql, $v, $t);
$countMetadataArray = 0;
@@ -766,12 +773,13 @@
//echo "List of datasets in geoportal instance:"."<br>";
while($row = db_fetch_array($res)){
$metadataArray[$countMetadataArray]["hasResource"] = false;
- $metadataArray[$countMetadataArray]["id"] = $row["metadata_id"];
+ $metadataArray[$countMetadataArray]["id"] = $row["ressource_id"];
$metadataArray[$countMetadataArray]["uuid"] = $row["uuid"];
$metadataArray[$countMetadataArray]["title"] = $row["title"];
$metadataArray[$countMetadataArray]["changedate"] = $row["lastchanged"];
$metadataArray[$countMetadataArray]["license_id"] = $row["fkey_termsofuse_id"];
$metadataArray[$countMetadataArray]["resources"] = $row["f_get_coupled_resources"];
+ $metadataArray[$countMetadataArray]["resource_type"] = $row["ressource_type"];
foreach (json_decode($metadataArray[$countMetadataArray]["resources"])->coupledResources->layerIds as $layerId) {
$layerArray[] = $layerId;
$metadataArray[$countMetadataArray]["hasResource"] = true;
@@ -799,6 +807,7 @@
if ($listAllMetadataInJson == true) {
$syncListResult->geoportal_organization[$numberGeoportalOrga]->datasource_metadata[$numberGeoportalMetadata]->id = $geoportalMetadata['uuid'];
$syncListResult->geoportal_organization[$numberGeoportalOrga]->datasource_metadata[$numberGeoportalMetadata]->date_time = $geoportalMetadata['changedate'];
+ $syncListResult->geoportal_organization[$numberGeoportalOrga]->datasource_metadata[$numberGeoportalMetadata]->resource_type = $geoportalMetadata['resource_type'];
$syncListResult->geoportal_organization[$numberGeoportalOrga]->datasource_metadata[$numberGeoportalMetadata]->resources = json_decode($geoportalMetadata['resources']);
}
$geoportalUuids[] = $geoportalMetadata['uuid'];
@@ -945,7 +954,7 @@
//first try to read from datasource
switch ($dataSourceType) {
case "mapbender":
- $resultCkanRepresentation = $this->getCkanRepresentation($datasetMetadata->id, $layerArrayMetadata, $featuretypeArrayMetadata, $syncList->ckan_orga_ident, $syncList->title, $syncList->email, $this->topicDataThemeCategoryMap);
+ $resultCkanRepresentation = $this->getCkanRepresentation($datasetMetadata->id, $layerArrayMetadata, $featuretypeArrayMetadata, $syncList->ckan_orga_ident, $syncList->title, $syncList->email, $this->topicDataThemeCategoryMap, $datasetMetadata->resource_type);
break;
case "portalucsw":
$resultCkanRepresentation = $this->getCkanRepresentationFromCsw($syncList->id, $datasetMetadata->id, $syncList->ckan_orga_ident, $syncList->name, $syncList->email, $this->topicDataThemeCategoryMap, $syncList->ckan_filter);
@@ -956,7 +965,7 @@
$resultCkanRepresentation = $this->getCkanRepresentationFromCkan($syncList->ckan_api_url, $syncList->ckan_api_version, $datasetMetadata->id, $syncList->central_filter, $syncList->ckan_orga_ident);
//$resultCkanRepresentation = false;
- //$resultCkanRepresentation = $this->getCkanRepresentation($datasetMetadata->id, $layerArrayMetadata, $featuretypeArrayMetadata, $syncList->ckan_orga_ident, $syncList->title, $syncList->email, $this->topicDataThemeCategoryMap);
+ //$resultCkanRepresentation = $this->getCkanRepresentation($datasetMetadata->id, $layerArrayMetadata, $featuretypeArrayMetadata, $syncList->ckan_orga_ident, $syncList->title, $syncList->email, $this->topicDataThemeCategoryMap, $datasetMetadata->resource_type);
break;
}
//if reading was successful
@@ -980,7 +989,7 @@
//first read from external source
switch ($dataSourceType) {
case "mapbender":
- $resultCkanRepresentation = $this->getCkanRepresentation($datasetMetadata->id, $layerArrayMetadata, $featuretypeArrayMetadata, $syncList->ckan_orga_ident, $syncList->title, $syncList->email, $this->topicDataThemeCategoryMap);
+ $resultCkanRepresentation = $this->getCkanRepresentation($datasetMetadata->id, $layerArrayMetadata, $featuretypeArrayMetadata, $syncList->ckan_orga_ident, $syncList->title, $syncList->email, $this->topicDataThemeCategoryMap, $datasetMetadata->resource_type);
break;
case "portalucsw":
$resultCkanRepresentation = $this->getCkanRepresentationFromCsw($syncList->id, $datasetMetadata->id, $syncList->ckan_orga_ident, $syncList->title, $syncList->email, $this->topicDataThemeCategoryMap, $syncList->ckan_filter);
@@ -990,7 +999,7 @@
//$e = new mb_exception("classes/class_syncCkan.php: try to pull json object from remote ckan - id: ".$datasetMetadata->id);
$resultCkanRepresentation = $this->getCkanRepresentationFromCkan($syncList->ckan_api_url, $syncList->ckan_api_version, $datasetMetadata->id, $syncList->central_filter, $syncList->ckan_orga_ident);
//$resultCkanRepresentation = false;
- //$resultCkanRepresentation = $this->getCkanRepresentation($datasetMetadata->id, $layerArrayMetadata, $featuretypeArrayMetadata, $syncList->ckan_orga_ident, $syncList->title, $syncList->email, $this->topicDataThemeCategoryMap);
+ //$resultCkanRepresentation = $this->getCkanRepresentation($datasetMetadata->id, $layerArrayMetadata, $featuretypeArrayMetadata, $syncList->ckan_orga_ident, $syncList->title, $syncList->email, $this->topicDataThemeCategoryMap, $datasetMetadata->resource_type);
break;
}
//if read from source was successful
@@ -1146,7 +1155,7 @@
}
//function to get ckan representation from mapbender metadata
- private function getCkanRepresentation($uuid, $layerArray, $featuretypeArray, $orgaId, $orgaTitle, $orgaEmail, $topicCkanCategoryMap) {
+ private function getCkanRepresentation($uuid, $layerArray, $featuretypeArray, $orgaId, $orgaTitle, $orgaEmail, $topicCkanCategoryMap, $resourceType = 'metadata') {
if (defined("MAPBENDER_PATH") && MAPBENDER_PATH != '') {
$mapbenderUrl = MAPBENDER_PATH;
} else {
@@ -1157,7 +1166,18 @@
//all or only those which have standardized licenses?
//$sql = "SELECT *, f_get_coupled_resources(metadata_id) from mb_metadata LEFT JOIN md_termsofuse ON mb_metadata.metadata_id = md_termsofuse.fkey_metadata_id WHERE mb_metadata.uuid = $1";
//$sql = "SELECT * , st_asgeojson(the_geom) as geojson from mb_metadata JOIN md_termsofuse ON mb_metadata.metadata_id = md_termsofuse.fkey_metadata_id JOIN termsofuse ON md_termsofuse.fkey_termsofuse_id = termsofuse.termsofuse_id WHERE mb_metadata.uuid = $1 AND export2csw IS true";
- $sql = "SELECT * , st_asgeojson(the_geom) as geojson, f_get_responsible_organization_for_ressource(metadata_id, 'metadata') as resp_party_id from mb_metadata LEFT OUTER JOIN md_termsofuse ON mb_metadata.metadata_id = md_termsofuse.fkey_metadata_id LEFT OUTER JOIN termsofuse ON md_termsofuse.fkey_termsofuse_id = termsofuse.termsofuse_id WHERE mb_metadata.uuid = $1 AND export2csw IS true";#7aeacc93-6b0d-da7f-1f86-80eea9e188b2
+ switch ($resourceType) {
+ case "layer":
+ $sql = <<<SQL
+
+SELECT layer.layer_id as metadata_id, layer.layer_title as title, layer.layer_abstract as abstract , f_get_responsible_organization_for_ressource(layer.fkey_wms_id, 'wms') as resp_party_id, termsofuse.* FROM layer LEFT OUTER JOIN wms_termsofuse ON layer.fkey_wms_id = wms_termsofuse.fkey_wms_id LEFT OUTER JOIN termsofuse ON wms_termsofuse.fkey_termsofuse_id = termsofuse.termsofuse_id WHERE layer.uuid = $1 AND layer.export2csw IS true AND layer.layer_searchable = 1
+
+SQL;
+ break;
+ case "metadata":
+ $sql = "SELECT * , st_asgeojson(the_geom) as geojson, f_get_responsible_organization_for_ressource(metadata_id, 'metadata') as resp_party_id from mb_metadata LEFT OUTER JOIN md_termsofuse ON mb_metadata.metadata_id = md_termsofuse.fkey_metadata_id LEFT OUTER JOIN termsofuse ON md_termsofuse.fkey_termsofuse_id = termsofuse.termsofuse_id WHERE mb_metadata.uuid = $1 AND export2csw IS true";
+ break;
+ }
$v = array($uuid);
$t = array('s');
$res = db_prep_query($sql, $v, $t);
@@ -1164,7 +1184,7 @@
if ($res) {
$row = db_fetch_assoc($res);
} else {
- $e = new mb_exception("classes/class_syncCkan.php: No metadata found for uuid: ".$uuid);
+ $e = new mb_exception("classes/class_syncCkan.php: No metadata/layer found for uuid: ".$uuid);
return false;
}
//get responsible organisation information
@@ -1220,7 +1240,9 @@
$ckanPackage->license_id = 'notspecified';
$ckanPackage->license_title = "Keine definierte Lizenz";
}
- $ckanPackage->spatial = $row['geojson'];
+ if ($resourceType == 'metadata') {
+ $ckanPackage->spatial = $row['geojson'];
+ }
//$ckanPackage->url = "";
//special categories
//$ckanPackage->govdata_categories = [];
@@ -1228,10 +1250,11 @@
$ckanPackage->registerobject_type = "Par_7_1_9";
//$e = new mb_exception("update_frequency from db: ".$row['update_frequency']." - frequency for dcat: ".$this->frequencyMap[$row['update_frequency']]);
-
-if (array_key_exists($row['update_frequency'],$this->frequencyMap)) {
- $ckanPackage->frequency = $this->frequencyMap[$row['update_frequency']];
-}
+ if ($resourceType == 'metadata') {
+ if (array_key_exists($row['update_frequency'],$this->frequencyMap)) {
+ $ckanPackage->frequency = $this->frequencyMap[$row['update_frequency']];
+ }
+ }
$ckanPackage->type = "geodata";
//build resources:
$resourcesArray = array();
@@ -1243,7 +1266,16 @@
$resourcesArray[$indexResourceArray]->name = "Originäre Metadaten";// für ".$row['layer_title'];
$resourcesArray[$indexResourceArray]->id = $metadataUuid."_iso19139";
$resourcesArray[$indexResourceArray]->description = $ckanPackage->title." - Anzeige der originären Metadaten";
- $resourcesArray[$indexResourceArray]->url = $mapbenderUrl."/php/mod_exportIso19139.php?url=http%3A%2F%2Fwww.geoportal.rlp.de%2Fmapbender%2Fphp%2Fmod_dataISOMetadata.php%3FoutputFormat%3Diso19139%26id%3D".$metadataUuid;
+
+ switch ($resourceType) {
+ case "layer":
+ $resourcesArray[$indexResourceArray]->url = $mapbenderUrl."/php/mod_showMetadata.php?resource=layer&layout=tabs&redirectToMetadataUrl=1&id=".$metadataId;
+ break;
+ case "metadata":
+ $resourcesArray[$indexResourceArray]->url = $mapbenderUrl."/php/mod_exportIso19139.php?url=http%3A%2F%2Fwww.geoportal.rlp.de%2Fmapbender%2Fphp%2Fmod_dataISOMetadata.php%3FoutputFormat%3Diso19139%26id%3D".$metadataUuid;
+ break;
+ }
+
$resourcesArray[$indexResourceArray]->format = "HTML";
$indexResourceArray++;
//views to generate
Modified: trunk/mapbender/http/php/mod_showOpenDataOrganizations.php
===================================================================
--- trunk/mapbender/http/php/mod_showOpenDataOrganizations.php 2018-02-16 11:57:11 UTC (rev 9868)
+++ trunk/mapbender/http/php/mod_showOpenDataOrganizations.php 2018-02-19 15:12:19 UTC (rev 9869)
@@ -1,8 +1,23 @@
<?php
require_once(dirname(__FILE__)."/../../core/globalSettings.php");
-
+$showOnlyDatasetMetadata = "false";
//params: opendata licenses, only metadata, ....
-
+if (isset($_REQUEST["showOnlyDatasetMetadata"]) & $_REQUEST["showOnlyDatasetMetadata"] != "") {
+ $testMatch = $_REQUEST["showOnlyDatasetMetadata"];
+ if (!($testMatch == 'true' or $testMatch == 'false')){
+ echo 'Parameter <b>showOnlyDatasetMetadata</b> is not valid (true,false (default to false)).<br/>';
+ die();
+ }
+ switch ($testMatch) {
+ case "true":
+ $showOnlyDatasetMetadata = "true";
+ break;
+ case "false":
+ $showOnlyDatasetMetadata = "false";
+ break;
+ }
+ $testMatch = NULL;
+}
$sql = <<<SQL
select opendata.*, mb_group.uuid, mb_group_id, mb_group_title, mb_group_description, mb_group_homepage, mb_group_logo_path, mb_group_address, mb_group_postcode, mb_group_city, mb_group_email, timestamp from mb_group, (select (sum(count_wms_layer)+sum(count_metadata)) as number_of_opendata_ressources, group_id,mb_group_name from (
@@ -18,6 +33,27 @@
SQL;
+
+$sql2 = <<<SQL
+
+select opendata.*, mb_group.uuid, mb_group_id, mb_group_title, mb_group_description, mb_group_homepage, mb_group_logo_path, mb_group_address, mb_group_postcode, mb_group_city, mb_group_email, timestamp from mb_group, (select (sum(count_wms_layer)+sum(count_metadata)) as number_of_opendata_ressources, group_id,mb_group_name from (
+
+select count_wms_layer, metadata_group.count_metadata, group_id,mb_group_name from mb_group, (select count(metadata_id) as count_metadata, 0 as count_wms_layer , f_get_responsible_organization_for_ressource(metadata_id, 'metadata') as group_id from mb_metadata where searchable is true and export2csw is true and metadata_id in (select fkey_metadata_id from md_termsofuse where fkey_termsofuse_id in (select termsofuse_id from termsofuse where isopen = 1)) group by group_id) as metadata_group where mb_group.mb_group_id = metadata_group.group_id
+
+) as opendata_ressources group by group_id, mb_group_name) as opendata
+where opendata.group_id = mb_group.mb_group_id
+
+SQL;
+
+switch ($showOnlyDatasetMetadata) {
+ case "true":
+ $result = db_query($sql2);
+ break;
+ case "false":
+ $result = db_query($sql);
+ break;
+}
+
$result = db_query($sql);
$returnObject = array();
More information about the Mapbender_commits
mailing list