[Mapbender-commits] r8755 - in trunk/mapbender: conf http/geoportal
svn_mapbender at osgeo.org
svn_mapbender at osgeo.org
Wed Dec 18 03:43:33 PST 2013
Author: armin11
Date: 2013-12-18 03:43:33 -0800 (Wed, 18 Dec 2013)
New Revision: 8755
Modified:
trunk/mapbender/conf/mapbender.conf-dist
trunk/mapbender/http/geoportal/mod_pullInspireMonitoring.php
trunk/mapbender/http/geoportal/mod_showInspireMonitoring.php
Log:
Alter INSPIRE table to allow paging via SQL. More than 2500 entries are too much ;-) .
Modified: trunk/mapbender/conf/mapbender.conf-dist
===================================================================
--- trunk/mapbender/conf/mapbender.conf-dist 2013-12-11 19:38:57 UTC (rev 8754)
+++ trunk/mapbender/conf/mapbender.conf-dist 2013-12-18 11:43:33 UTC (rev 8755)
@@ -213,6 +213,10 @@
#---------------------------------------------
define("INSPIRE_VALIDATOR_URL", "http://inspire-geoportal.ec.europa.eu/GeoportalProxyWebServices/resources/INSPIREResourceTester");
+# --------------------------------------------
+#INSPIRE Category, this is an ID of a mapbender custom_category which is used to identify inspire datasets - done so in germany
+# --------------------------------------------
+define("INSPIRE_CUSTOM_CAT_ID", "11");
# --------------------------------------------
# UPLOAD
Modified: trunk/mapbender/http/geoportal/mod_pullInspireMonitoring.php
===================================================================
--- trunk/mapbender/http/geoportal/mod_pullInspireMonitoring.php 2013-12-11 19:38:57 UTC (rev 8754)
+++ trunk/mapbender/http/geoportal/mod_pullInspireMonitoring.php 2013-12-18 11:43:33 UTC (rev 8755)
@@ -18,7 +18,12 @@
require_once(dirname(__FILE__)."/../../core/globalSettings.php");
require_once dirname(__FILE__)."/../classes/class_connector.php";
require_once dirname(__FILE__) . "/../classes/class_Uuid.php";
-$inspireCatId = 11;
+
+if (defined("INSPIRE_CUSTOM_CAT_ID") && INSPIRE_CUSTOM_CAT_ID != "") {
+ $inspireCatId = INSPIRE_CUSTOM_CAT_ID;
+} else {
+ $inspireCatId = 11;
+}
$outputFormat = 'json';
$lang = 'de';
$registratingDepartments = null;
@@ -67,6 +72,141 @@
$lang = $testMatch;
$testMatch = NULL;
}
+
+//database tables GET interface for server side processing options
+/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
+* Easy set variables
+*/
+
+/* Array of database columns which should be read and sent back to DataTables. Use a space where
+* you want to insert a non-database field (for example a counter or static image)
+*/
+$aColumns = array( 'title');
+
+/* Indexed column (used for fast and accurate table cardinality) */
+$sIndexColumn = "id";
+
+/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
+* If you just want to use the basic configuration for DataTables with PHP server-side, there is
+* no need to edit below this line
+*/
+
+/*
+* Paging
+*/
+if (isset( $_REQUEST['iDisplayLength']) && $_REQUEST['iDisplayLength'] != '-1') {
+ $jsonLimit = (integer)pg_escape_string( $_REQUEST['iDisplayLength'] );
+
+} else {
+ $jsonLimit = 2000;
+}
+
+
+$sLimit = "";
+if ( isset( $_REQUEST['iDisplayStart'] ) && $_REQUEST['iDisplayLength'] != '-1' )
+{
+ $sLimit = "OFFSET ".pg_escape_string( $_REQUEST['iDisplayStart'] )." LIMIT ".
+ pg_escape_string( $_REQUEST['iDisplayLength'] * 3);
+}
+
+/*
+* Ordering
+*/
+/*if ( isset( $_REQUEST['iSortCol_0'] ) )
+{
+ $sOrder = "ORDER BY ";
+ for ( $i=0 ; $i<intval( $_REQUEST['iSortingCols'] ) ; $i++ )
+ {
+ if ( $_REQUEST[ 'bSortable_'.intval($_REQUEST['iSortCol_'.$i]) ] == "true" )
+ {
+ $sOrder .= $aColumns[ intval( $_REQUEST['iSortCol_'.$i] ) ]."
+ ".pg_escape_string( $_REQUEST['sSortDir_'.$i] ) .", ";
+ }
+ }
+
+ $sOrder = substr_replace( $sOrder, "", -2 );
+ if ( $sOrder == "ORDER BY" )
+ {
+ $sOrder = "";
+ }
+}*/
+
+
+/*
+* Filtering
+* NOTE this does not match the built-in DataTables filtering which does it
+* word by word on any field. It's possible to do here, but concerned about efficiency
+* on very large tables, and MySQL's regex functionality is very limited
+ */
+$sWhere = "";
+if ( $_REQUEST['sSearch'] != "" )
+{
+ //$e = new mb_exception($_REQUEST['sSearch']);
+ $sWhere = "WHERE (";
+ for ( $i=0 ; $i<count($aColumns) ; $i++ )
+ {
+ $sWhere .= $aColumns[$i]." LIKE '%".pg_escape_string( $_REQUEST['sSearch'] )."%' OR ";
+ }
+ $sWhere = substr_replace( $sWhere, "", -3 );
+ $sWhere .= ')';
+}
+
+/* Individual column filtering */
+for ( $i=0 ; $i<count($aColumns) ; $i++ )
+{
+ if ( $_REQUEST['bSearchable_'.$i] == "true" && $_REQUEST['sSearch_'.$i] != '' )
+ {
+ if ( $sWhere == "" )
+ {
+ $sWhere = "WHERE ";
+ }
+ else
+ {
+ $sWhere .= " AND ";
+ }
+ $sWhere .= $aColumns[$i]." LIKE '%".pg_escape_string($_REQUEST['sSearch_'.$i])."%' ";
+ }
+}
+
+
+/*
+* SQL queries
+* Get data to display
+*/
+
+ /*$sWhere
+ $sOrder
+ $sLimit*/
+
+/* Data set length after filtering */
+/*$sQuery = "
+ SELECT FOUND_ROWS()
+";
+$rResultFilterTotal = mysql_query( $sQuery, $gaSql['link'] ) or die(mysql_error());
+$aResultFilterTotal = mysql_fetch_array($rResultFilterTotal);
+$iFilteredTotal = $aResultFilterTotal[0];*/
+
+/* Total data set length */
+/*$sQuery = "
+ SELECT COUNT(".$sIndexColumn.")
+ FROM $sTable
+";
+$rResultTotal = mysql_query( $sQuery, $gaSql['link'] ) or die(mysql_error());
+$aResultTotal = mysql_fetch_array($rResultTotal);
+$iTotal = $aResultTotal[0];*/
+
+
+/*
+* Output
+*/
+/*$output = array(
+ "sEcho" => intval($_REQUEST['sEcho']),
+ "iTotalRecords" => $iTotal,
+ "iTotalDisplayRecords" => $iFilteredTotal,
+ "aaData" => array()
+);*/
+
+//*******************************************************************
//get inspire category information from db
$sql = <<<SQL
select inspire_category_id, inspire_category_key, inspire_category_code_$lang from inspire_category
@@ -77,20 +217,53 @@
$inspireCategories['key'][$row['inspire_category_id']] = $row['inspire_category_key'];
$inspireCategories['title'][$row['inspire_category_id']] = $row['inspire_category_code_'.$lang];
}
+//define sql to do a count of inspire relevant data (metadata)
+$sqlCount = <<<SQL
+select count(metadata_id) as count from (select metadata_id, title from mb_metadata where metadata_id in (select distinct (metadata_id) from (select fkey_metadata_id as metadata_id from ows_relation_metadata inner join (select fkey_layer_id from layer_custom_category where fkey_custom_category_id = $inspireCatId ) as foo on foo.fkey_layer_id = ows_relation_metadata.fkey_layer_id
+union
+select fkey_metadata_id as metadata_id from ows_relation_metadata inner join (select fkey_featuretype_id from wfs_featuretype_custom_category where fkey_custom_category_id = $inspireCatId ) as foo on foo.fkey_featuretype_id = ows_relation_metadata.fkey_featuretype_id
+union
+select fkey_metadata_id as metadata_id from mb_metadata_custom_category where fkey_custom_category_id = $inspireCatId) as foo ) as foo2) as foo3
+SQL;
+
+$sqlCount2 = <<<SQL
+
+select count(metadata_id) from (select metadata_id, title, uuid from mb_metadata where metadata_id in (select distinct (metadata_id) from (select fkey_metadata_id as metadata_id from ows_relation_metadata inner join (select fkey_layer_id from layer_custom_category where fkey_custom_category_id = $inspireCatId ) as foo on foo.fkey_layer_id = ows_relation_metadata.fkey_layer_id
+union
+select fkey_metadata_id as metadata_id from ows_relation_metadata inner join (select fkey_featuretype_id from wfs_featuretype_custom_category where fkey_custom_category_id = $inspireCatId ) as foo on foo.fkey_featuretype_id = ows_relation_metadata.fkey_featuretype_id
+union
+select fkey_metadata_id as metadata_id from mb_metadata_custom_category where fkey_custom_category_id = $inspireCatId ) as foo )) as foo2 $sWhere
+SQL;
+
+$resultCount = db_query($sqlCount2);
+$rowCount = db_fetch_array($resultCount);
+$resultCount = $rowCount['count'];
+
+$iTotal = $resultCount;
+
+//$e = new mb_exception($rowCount['count']);
+//all inspire relevant data
+
+
//Define sql to select relevant information out of the registry.
//The direction is from the classified service layer/featuretype information to the coupled metadata.
//The classifications of the layers are used to decide if the resource is in the outgoing table.
- $sql = <<<SQL
+$sql = <<<SQL
+select distinct (uuid), * from (
+select metadata_layer.title, wms.uuid as service_uuid, wms_title || ':' || layer_title as inspire_service_title, metadata_layer.uuid, metadata_layer.datasetid, metadata_layer.datasetid_codespace, resource_id, resource_uuid, resource_type, service_id, inspire_download, wms_owner as service_owner, fkey_mb_group_id as service_group, wms_title as service_title, f_collect_inspire_cat_layer(resource_id) as inspire_cat from (select title, uuid, datasetid, datasetid_codespace, layer_id as resource_id, 'layer' as resource_type, fkey_wms_id as service_id, inspire_download, layer_title, resource_uuid from (select layer.layer_id, layer.layer_title, layer.fkey_wms_id, layer.uuid as resource_uuid, layer.inspire_download, layer_custom_category.fkey_custom_category_id from layer inner join layer_custom_category on layer.layer_id = layer_custom_category.fkey_layer_id where layer_custom_category.fkey_custom_category_id = $inspireCatId AND layer_searchable = 1 ORDER BY layer_id) as layer_inspi
re inner join (select metadata_id, datasetid, datasetid_codespace, uuid, title, fkey_layer_id from mb_metadata inner join ows_relation_metadata on ows_relation_metadata.fkey_metadata_id = mb_metadata.metadata_id) as metadata_relation on metadata_relation.fkey_layer_id = layer_inspire.layer_id) as metadata_layer INNER JOIN wms ON metadata_layer.service_id = wms_id
-select metadata_layer.title, wms.uuid as service_uuid, wms_title || ':' || layer_title as inspire_service_title, metadata_layer.uuid, metadata_layer.datasetid, metadata_layer.datasetid_codespace, resource_id, resource_uuid, resource_type, service_id, inspire_download, wms_owner as service_owner, fkey_mb_group_id as service_group, wms_title as service_title, f_collect_inspire_cat_layer(resource_id) as inspire_cat from (select title, uuid, datasetid, datasetid_codespace, layer_id as resource_id, 'layer' as resource_type, fkey_wms_id as service_id, inspire_download, layer_title, resource_uuid from (select layer.layer_id, layer.layer_title, layer.fkey_wms_id, layer.uuid as resource_uuid, layer.inspire_download, layer_custom_category.fkey_custom_category_id from layer inner join layer_custom_category on layer.layer_id = layer_custom_category.fkey_layer_id where layer_custom_category.fkey_custom_category_id = $inspireCatId AND layer_searchable = 1 ORDER BY layer_id) as layer_inspi
re inner join (select metadata_id, datasetid, datasetid_codespace, uuid, title, fkey_layer_id from mb_metadata inner join ows_relation_metadata on ows_relation_metadata.fkey_metadata_id = mb_metadata.metadata_id) as metadata_relation on metadata_relation.fkey_layer_id = layer_inspire.layer_id) as metadata_layer INNER JOIN wms ON metadata_layer.service_id = wms_id
-
union
-select metadata_featuretype.title, wfs.uuid as service_uuid, wfs_title || ':' || featuretype_title as inspire_service_title, metadata_featuretype.uuid, metadata_featuretype.datasetid, metadata_featuretype.datasetid_codespace, resource_id, resource_uuid, resource_type, service_id, inspire_download, wfs_owner as service_owner, fkey_mb_group_id as service_group, wfs_title as service_title, f_collect_inspire_cat_wfs_featuretype(resource_id) as inspire_cat from (select title, uuid, datasetid, datasetid_codespace, featuretype_id as resource_id, 'wfs_featuretype' as resource_type, resource_uuid, fkey_wfs_id as service_id, inspire_download, featuretype_title from (select wfs_featuretype.featuretype_id , wfs_featuretype.featuretype_title, wfs_featuretype.fkey_wfs_id, wfs_featuretype.inspire_download, wfs_featuretype.uuid as resource_uuid from wfs_featuretype) as featuretype_inspire inner join (select metadata_id, datasetid, datasetid_codespace, uuid, title, fkey_featuretype_id from
mb_metadata inner join ows_relation_metadata on ows_relation_metadata.fkey_metadata_id = mb_metadata.metadata_id) as metadata_relation on metadata_relation.fkey_featuretype_id = featuretype_inspire.featuretype_id) as metadata_featuretype INNER JOIN wfs ON metadata_featuretype.service_id = wfs_id
-order by uuid
+select metadata_featuretype.title, wfs.uuid as service_uuid, wfs_title || ':' || featuretype_title as inspire_service_title, metadata_featuretype.uuid, metadata_featuretype.datasetid, metadata_featuretype.datasetid_codespace, resource_id, resource_uuid, resource_type, service_id, inspire_download, wfs_owner as service_owner, fkey_mb_group_id as service_group, wfs_title as service_title, f_collect_inspire_cat_wfs_featuretype(resource_id) as inspire_cat from (select title, uuid, datasetid, datasetid_codespace, featuretype_id as resource_id, 'wfs_featuretype' as resource_type, resource_uuid, fkey_wfs_id as service_id, inspire_download, featuretype_title from (select wfs_featuretype.featuretype_id , wfs_featuretype.featuretype_title, wfs_featuretype.fkey_wfs_id, wfs_featuretype.inspire_download, wfs_featuretype.uuid as resource_uuid from wfs_featuretype inner join wfs_featuretype_custom_category on wfs_featuretype.featuretype_id = wfs_featuretype_custom_category.fkey_featuretyp
e_id where wfs_featuretype_custom_category.fkey_custom_category_id = $inspireCatId AND featuretype_searchable = 1 ORDER BY featuretype_id) as featuretype_inspire inner join (select metadata_id, datasetid, datasetid_codespace, uuid, title, fkey_featuretype_id from mb_metadata inner join ows_relation_metadata on ows_relation_metadata.fkey_metadata_id = mb_metadata.metadata_id) as metadata_relation on metadata_relation.fkey_featuretype_id = featuretype_inspire.featuretype_id) as metadata_featuretype INNER JOIN wfs ON metadata_featuretype.service_id = wfs_id
+union
+
+select title, null as service_uuid, null as inspire_service_title, uuid, datasetid, datasetid_codespace, metadata_id as resource_id, null as resource_uuid, null as resource_type, null as service_id, null as inspire_download, fkey_mb_user_id as service_owner, null as service_group, null as service_title, f_collect_inspire_cat_dataset(metadata_id) as inspire_cat from mb_metadata inner join mb_metadata_custom_category on mb_metadata.metadata_id = mb_metadata_custom_category.fkey_metadata_id where mb_metadata_custom_category.fkey_custom_category_id = $inspireCatId
+
+
+) as foo $sWhere $sOrder $sLimit
+
SQL;
-
$startTime = microtime();
//get all service / owner / fkey_group information for the list of services
$result = db_query($sql);
@@ -543,9 +716,25 @@
//normal output as json
$metadataIndex = -1;
$currentUuid = "";
- $output = array(
+ if (isset($iTotal)) {
+ //$e = new mb_exception("iTotal= ".$iTotal);
+ $output = array(
+ "sEcho" => intval($_REQUEST['sEcho']),
+ "iTotalRecords" => $iTotal,
+ "iTotalDisplayRecords" => $iTotal,
+ "aaData" => array()
+ );
+ } else {
+ $output = array(
+ "sEcho" => intval($_REQUEST['sEcho']),
+ "iTotalRecords" => $iTotal,
+ "iTotalDisplayRecords" => $iTotal,
+ "aaData" => array()
+ );
+ }
+ /*$output = array(
"aaData" => array()
- );
+ );*/
for ($i=0; $i < count($sqlTable['uuid']); $i++){
//filter for orga_id
//generate entry only if orga_id is the same as expected
@@ -555,10 +744,10 @@
$currentUuid = $sqlTable['uuid'][$i];
$metadataIndex++;
$output['aaData'][$metadataIndex]->detailImage = "<img id=\"expander\" src=\"../img/gnome/stock_zoom-in.png\">";
- $output['aaData'][$metadataIndex]->title = $sqlTable['title'][$i];
- //TODO: following has problems with more than 1300 entries!
- //$output['aaData'][$metadataIndex]->title = "<a href=\"../php/mod_iso19139ToHtml.php?url=".urlencode($mapbenderUrl."/php/mod_dataISOMetadata.php?outputFormat=iso19139&id=".$sqlTable['uuid'][$i])."\">".$sqlTable['title'][$i]."</a>";
- //$output['aaData'][$metadataIndex]->uuid = "<p id=\"uuidref\">".$sqlTable['uuid'][$i]."</p>";
+ //$output['aaData'][$metadataIndex]->title = $sqlTable['title'][$i];
+
+ $output['aaData'][$metadataIndex]->title = "<a href=\"../php/mod_iso19139ToHtml.php?url=".urlencode($mapbenderUrl."/php/mod_dataISOMetadata.php?outputFormat=iso19139&id=".$sqlTable['uuid'][$i])."\">".$sqlTable['title'][$i]."</a>";
+
$output['aaData'][$metadataIndex]->uuid = $sqlTable['uuid'][$i];
$output['aaData'][$metadataIndex]->organization = $sqlTable['organization'][$i];
$insCat = '';
@@ -596,6 +785,9 @@
$output['aaData'][$metadataIndex]->inspireCategories = implode(',',array_unique(explode(',',(rtrim($insCat,',')))));
}
}
+ if (isset($jsonLimit) && $metadataIndex >= $jsonLimit) {
+ break;
+ }
}
header('Content-Type: application/json; charset='.CHARSET);
echo json_encode($output, JSON_NUMERIC_CHECK);
Modified: trunk/mapbender/http/geoportal/mod_showInspireMonitoring.php
===================================================================
--- trunk/mapbender/http/geoportal/mod_showInspireMonitoring.php 2013-12-11 19:38:57 UTC (rev 8754)
+++ trunk/mapbender/http/geoportal/mod_showInspireMonitoring.php 2013-12-18 11:43:33 UTC (rev 8755)
@@ -200,7 +200,7 @@
//$html .= "{ \"bSortable\": false, \"aTargets\": [ 0 ] }";
//$html .= "],";
$html .= "\"bProcessing\": true,";
-//$html .= "\"bServerSide\": true,";
+$html .= "\"bServerSide\": true,";
//$html .= "\"iSortCol_0\": 1,";
//internationalization
$html .= "\"oLanguage\": {";
@@ -237,15 +237,15 @@
$html .= "\"aoColumns\": [";
//$html .= "\"<img src='../img/add.png'>\",";
$html .= "{ \"mData\": \"detailImage\", \"bSortable\": false },";
-$html .= "{ \"mData\": \"title\" },";
-$html .= "{ \"mData\": \"uuid\" },";
-$html .= "{ \"mData\": \"organization\"},";
+$html .= "{ \"mData\": \"title\", \"bSortable\": false },";
+$html .= "{ \"mData\": \"uuid\" , \"bSortable\": false },";
+$html .= "{ \"mData\": \"organization\", \"bSortable\": false },";
if ($withCounts) {
- $html .= "{ \"mData\": \"inspireCategories\"},";
- $html .= "{ \"mData\": \"numberViewServices\"},";
- $html .= "{ \"mData\": \"numberDownloadServices\"}";
+ $html .= "{ \"mData\": \"inspireCategories\", \"bSortable\": false },";
+ $html .= "{ \"mData\": \"numberViewServices\", \"bSortable\": false },";
+ $html .= "{ \"mData\": \"numberDownloadServices\", \"bSortable\": false }";
} else {
- $html .= "{ \"mData\": \"inspireCategories\"}";
+ $html .= "{ \"mData\": \"inspireCategories\", \"bSortable\": false }";
}
$html .= "]";
$html .= "});";
More information about the Mapbender_commits
mailing list