libraries/tbl_gis_visualization.lib.php

   1 <?php
   2 /* vim: set expandtab sw=4 ts=4 sts=4: */
   3 /**
   4  * Functions used to generate GIS visualizations.
   5  *
   6  * @package PhpMyAdmin
   7  */
   8
   9
  10
  11 /**
  12  * Returns a modified sql query with only the label column
  13  * and spatial column(wrapped with 'ASTEXT()' function).
  14  *
  15  * @param string $sql_query             original sql query
  16  * @param array  $visualizationSettings settings for the visualization
  17  *
  18  * @return the modified sql query.
  19  */
  20 function PMA_GIS_modifyQuery($sql_query, $visualizationSettings)
  21 {
  22     $modified_query = 'SELECT ';
  23
  24     $analyzed_query = PMA_SQP_analyze(PMA_SQP_parse($sql_query));
  25     // If select clause is not *
  26     if (trim($analyzed_query[0]['select_expr_clause']) != '*') {
  27         // If label column is chosen add it to the query
  28         if (isset($visualizationSettings['labelColumn'])
  29             && $visualizationSettings['labelColumn'] != ''
  30         ) {
  31             // Check to see whether an alias has been used on the label column
  32             $is_label_alias = false;
  33             foreach ($analyzed_query[0]['select_expr'] as $select) {
  34                 if ($select['alias'] == $visualizationSettings['labelColumn']) {
  35                     $modified_query .= sanitize($select) . ' AS `'
  36                     . $select['alias'] . '`, ';
  37                     $is_label_alias = true;
  38                     break;
  39                 }
  40             }
  41             // If no alias have been used on the label column
  42             if (! $is_label_alias) {
  43                 foreach ($analyzed_query[0]['select_expr'] as $select) {
  44                     if ($select['column'] == $visualizationSettings['labelColumn']) {
  45                         $modified_query .= sanitize($select) . ', ';
  46                     }
  47                 }
  48             }
  49         }
  50
  51         // Check to see whether an alias has been used on the spatial column
  52         $is_spatial_alias = false;
  53         foreach ($analyzed_query[0]['select_expr'] as $select) {
  54             if ($select['alias'] == $visualizationSettings['spatialColumn']) {
  55                 $sanitized = sanitize($select);
  56                 $modified_query .= 'ASTEXT(' . $sanitized . ') AS `'
  57                 . $select['alias'] . '`, ';
  58                 // Get the SRID
  59                 $modified_query .= 'SRID(' . $sanitized . ') AS `srid` ';
  60                 $is_spatial_alias = true;
  61                 break;
  62             }
  63         }
  64         // If no alias have been used on the spatial column
  65         if (! $is_spatial_alias) {
  66             foreach ($analyzed_query[0]['select_expr'] as $select) {
  67                 if ($select['column'] == $visualizationSettings['spatialColumn']) {
  68                     $sanitized = sanitize($select);
  69                     $modified_query .= 'ASTEXT(' . $sanitized
  70                         . ') AS `' . $select['column'] . '`, ';
  71                     // Get the SRID
  72                     $modified_query .= 'SRID(' . $sanitized . ') AS `srid` ';
  73                 }
  74             }
  75         }
  76         // If select clause is *
  77     } else {
  78         // If label column is chosen add it to the query
  79         if (isset($visualizationSettings['labelColumn'])
  80             && $visualizationSettings['labelColumn'] != ''
  81         ) {
  82             $modified_query .= '`' . $visualizationSettings['labelColumn'] .'`, ';
  83         }
  84
  85         // Wrap the spatial column with 'ASTEXT()' function and add it
  86         $modified_query .= 'ASTEXT(`' . $visualizationSettings['spatialColumn']
  87             . '`) AS `' . $visualizationSettings['spatialColumn'] . '`, ';
  88
  89         // Get the SRID
  90         $modified_query .= 'SRID(`' . $visualizationSettings['spatialColumn']
  91             . '`) AS `srid` ';
  92     }
  93
  94     // Append the rest of the query
  95     $from_pos = stripos($sql_query, 'FROM');
  96     $modified_query .= substr($sql_query, $from_pos);
  97     return $modified_query;
  98 }
  99
 100 /**
 101  * Local function to sanitize the expression taken
 102  * from the results of PMA_SQP_analyze function.
 103  *
 104  * @param aray $select Select to sanitize.
 105  *
 106  * @return Sanitized string.
 107  */
 108 function sanitize($select)
 109 {
 110     $table_col = $select['table_name'] . "." . $select['column'];
 111     $db_table_col = $select['db'] . "." . $select['table_name']
 112         . "." . $select['column'];
 113
 114     if ($select['expr'] == $select['column']) {
 115         return "`" . $select['column'] . "`";
 116     } elseif ($select['expr'] == $table_col) {
 117         return "`" . $select['table_name'] . "`.`" . $select['column'] . "`";
 118     } elseif ($select['expr'] == $db_table_col) {
 119         return "`" . $select['db'] . "`.`" . $select['table_name']
 120             . "`.`" . $select['column'] . "`";
 121     }
 122     return $select['expr'];
 123 }
 124
 125 /**
 126  * Formats a visualization for the GIS query results.
 127  *
 128  * @param array  $data                   Data for the status chart
 129  * @param array  &$visualizationSettings Settings used to generate the chart
 130  * @param string $format                 Format of the visulaization
 131  *
 132  * @return string HTML and JS code for the GIS visualization
 133  */
 134 function PMA_GIS_visualizationResults($data, &$visualizationSettings, $format)
 135 {
 136     include_once './libraries/gis/pma_gis_visualization.php';
 137     include_once './libraries/gis/pma_gis_factory.php';
 138
 139     if (! isset($data[0])) {
 140         // empty data
 141         return __('No data found for GIS visualization.');
 142     } else {
 143         $visualization = new PMA_GIS_Visualization($data, $visualizationSettings);
 144         if ($visualizationSettings != null) {
 145             foreach ($visualization->getSettings() as $setting => $val) {
 146                 if (! isset($visualizationSettings[$setting])) {
 147                     $visualizationSettings[$setting] = $val;
 148                 }
 149             }
 150         }
 151         if ($format == 'svg') {
 152             return $visualization->asSvg();
 153         } elseif ($format == 'png') {
 154             return $visualization->asPng();
 155         } elseif ($format == 'ol') {
 156             return $visualization->asOl();
 157         }
 158     }
 159 }
 160
 161 /**
 162  * Generate visualization for the GIS query results and save it to a file.
 163  *
 164  * @param array  $data                  data for the status chart
 165  * @param array  $visualizationSettings settings used to generate the chart
 166  * @param string $format                format of the visulaization
 167  * @param string $fileName              file name
 168  *
 169  * @return file File containing the visualization
 170  */
 171 function PMA_GIS_saveToFile($data, $visualizationSettings, $format, $fileName)
 172 {
 173     include_once './libraries/gis/pma_gis_visualization.php';
 174     include_once './libraries/gis/pma_gis_factory.php';
 175
 176     if (isset($data[0])) {
 177         $visualization = new PMA_GIS_Visualization($data, $visualizationSettings);
 178
 179         if ($format == 'svg') {
 180             $visualization->toFileAsSvg($fileName);
 181         } elseif ($format == 'png') {
 182             $visualization->toFileAsPng($fileName);
 183         } elseif ($format == 'pdf') {
 184             $visualization->toFileAsPdf($fileName);
 185         }
 186     }
 187 }
 188
 189 /**
 190  * Function to get html for the options lists
 191  *
 192  * @param array  $options array of options
 193  * @param string $select  the item that shoul be selected by default
 194  *
 195  * @return string $html   the html for the options lists
 196  */
 197 function PMA_getHtmlForOptionsList($options, $select)
 198 {
 199     $html = '';
 200     foreach ($options as $option) {
 201         $html .= '<option value="' . htmlspecialchars($option) . '"';
 202         if ($option == $select) {
 203             $html .= ' selected="selected"';
 204         }
 205         $html .= '>' . htmlspecialchars($option) . '</option>';
 206     }
 207
 208     return $html;
 209 }
 210
 211 /**
 212  * Function to get html for the lebel column and spatial column
 213  *
 214  * @param string $column                the column type. i.e either "labelColumn"
 215  *                                      or "spatialColumn"
 216  * @param array  $columnCandidates      the list of select options
 217  * @param array  $visualizationSettings visualization settings
 218  *
 219  * @return string  $html
 220  */
 221 function PMA_getHtmlForColumn($column, $columnCandidates, $visualizationSettings)
 222 {
 223     $html = '<tr><td><label for="labelColumn">';
 224     $html .= ($column=="labelColumn") ? __("Label column") : __("Spatial column");
 225     $html .= '</label></td>';
 226
 227     $html .= '<td><select name="visualizationSettings[' . $column . ']" id="'
 228         . $column . '">';
 229
 230     if ($column == "labelColumn") {
 231         $html .= '<option value="">' . __("-- None --") . '</option>';
 232     }
 233
 234     $html .= PMA_getHtmlForOptionsList(
 235         $columnCandidates, $visualizationSettings[$column]
 236     );
 237
 238     $html .= '</select></td>';
 239     $html .= '</tr>';
 240
 241     return $html;
 242 }
 243
 244 /**
 245  * Function to get HTML for the option of using open street maps
 246  *
 247  * @param boolean $isSelected the default value
 248  *
 249  * @return string HTML string
 250  */
 251 function PMA_getHtmlForUseOpenStreetMaps($isSelected)
 252 {
 253     $html = '<tr><td class="choice" colspan="2">';
 254     $html .= '<input type="checkbox" name="visualizationSettings[choice]"'
 255         . 'id="choice" value="useBaseLayer"';
 256     if ($isSelected) {
 257         $html .= ' checked="checked"';
 258     }
 259     $html .= '/>';
 260     $html .= '<label for="choice">';
 261     $html .= __("Use OpenStreetMaps as Base Layer");
 262     $html .= '</label>';
 263     $html .= '</td></tr>';
 264
 265     return $html;
 266 }
 267
 268 /**
 269  * Function to generate HTML for the GIS visualization page
 270  *
 271  * @param array   $url_params            url parameters
 272  * @param array   $labelCandidates       list of candidates for the label
 273  * @param array   $spatialCandidates     list of candidates for the spatial column
 274  * @param array   $visualizationSettings visualization settings
 275  * @param String  $sql_query             the sql query
 276  * @param String  $visualization         HTML and js code for the visualization
 277  * @param boolean $svg_support           whether svg download format is supported
 278  * @param array   $data                  array of visualizing data
 279  *
 280  * @return string HTML code for the GIS visualization
 281  */
 282 function PMA_getHtmlForGisVisualization(
 283     $url_params, $labelCandidates, $spatialCandidates, $visualizationSettings,
 284     $sql_query, $visualization, $svg_support, $data
 285 ) {
 286     $html = '<div id="div_view_options">';
 287     $html .= '<fieldset>';
 288     $html .= '<legend>' . __('Display GIS Visualization') . '</legend>';
 289
 290     $html .= '<div style="width: 400px; float: left;">';
 291     $html .= '<form method="post" action="tbl_gis_visualization.php">';
 292     $html .= PMA_URL_getHiddenInputs($url_params);
 293     $html .= '<table class="gis_table">';
 294
 295     $html .= PMA_getHtmlForColumn(
 296         "labelColumn", $labelCandidates, $visualizationSettings
 297     );
 298
 299     $html .= PMA_getHtmlForColumn(
 300         "spatialColumn", $spatialCandidates, $visualizationSettings
 301     );
 302
 303     $html .= '<tr><td></td>';
 304     $html .= '<td class="button"><input type="submit"';
 305     $html .= ' name="displayVisualizationBtn" value="';
 306     $html .= __('Redraw');
 307     $html .= '" /></td></tr>';
 308
 309     if (! $GLOBALS['PMA_Config']->isHttps()) {
 310         $isSelected = isset($visualizationSettings['choice']) ? true : false;
 311         $html .= PMA_getHtmlForUseOpenStreetMaps($isSelected);
 312     }
 313
 314     $html .= '</table>';
 315     $html .= '<input type="hidden" name="displayVisualization" value="redraw">';
 316     $html .= '<input type="hidden" name="sql_query" value="';
 317     $html .= htmlspecialchars($sql_query) . '" />';
 318     $html .= '</form>';
 319     $html .= '</div>';
 320
 321     $html .= '<div  style="float:left;">';
 322     $html .= '<form method="post" class="disableAjax"';
 323     $html .= ' action="tbl_gis_visualization.php">';
 324     $html .= PMA_URL_getHiddenInputs($url_params);
 325     $html .= '<table class="gis_table">';
 326     $html .= '<tr><td><label for="fileName">';
 327     $html .= __("File name") . '</label></td>';
 328     $html .= '<td><input type="text" name="fileName" id="fileName" /></td></tr>';
 329
 330     $html .= '<tr><td><label for="fileFormat">';
 331     $html .= __("Format") . '</label></td>';
 332     $html .= '<td><select name="fileFormat" id="fileFormat">';
 333     $html .= '<option value="png">PNG</option>';
 334     $html .= '<option value="pdf">PDF</option>';
 335
 336     if ($svg_support) {
 337         $html .= '<option value="svg" selected="selected">SVG</option>';
 338     }
 339     $html .= '</select></td></tr>';
 340
 341     $html .= '<tr><td></td>';
 342     $html .= '<td class="button"><input type="submit" name="saveToFileBtn" value="';
 343     $html .= __('Download') . '" /></td></tr>';
 344     $html .= '</table>';
 345
 346     $html .= '<input type="hidden" name="saveToFile" value="download">';
 347     $html .= '<input type="hidden" name="sql_query" value="';
 348     $html .= htmlspecialchars($sql_query) . '" />';
 349     $html .= '</form>';
 350     $html .= '</div>';
 351
 352     $html .= '<div style="clear:both;">&nbsp;</div>';
 353
 354     $html .= '<div id="placeholder" style="width:';
 355     $html .= htmlspecialchars($visualizationSettings['width']) . 'px;height:';
 356     $html .= htmlspecialchars($visualizationSettings['height']) . 'px;">';
 357     $html .= $visualization;
 358     $html .= '</div>';
 359
 360     $html .= '<div id="openlayersmap"></div>';
 361     $html .= '<input type="hidden" id="pmaThemeImage" value="';
 362     $html .= $GLOBALS['pmaThemeImage'] . '" />';
 363     $html .= '<script language="javascript" type="text/javascript">';
 364     $html .= 'function drawOpenLayers()';
 365     $html .= '{';
 366
 367     if (! $GLOBALS['PMA_Config']->isHttps()) {
 368         $html .= PMA_GIS_visualizationResults($data, $visualizationSettings, 'ol');
 369     }
 370     $html .= '}';
 371     $html .= '</script>';
 372     $html .= '</fieldset>';
 373     $html .= '</div>';
 374
 375     return $html;
 376 }
 377 ?>