libraries/gis_visualization.lib.php

   1 <?php
   2 /**
   3  * Functions used to generate GIS visualizations.
   4  *
   5  * @package phpMyAdmin
   6  */
   7
   8
   9
  10 /**
  11  * Returns a modified sql query with only the label column
  12  * and spatial column(wrapped with 'ASTEXT()' function).
  13  *
  14  * @param string $sql_query             original sql query
  15  * @param array  $visualizationSettings settings for the visualization
  16  *
  17  * @return the modified sql query.
  18  */
  19 function PMA_GIS_modify_query($sql_query, $visualizationSettings)
  20 {
  21     $modified_query = 'SELECT ';
  22
  23     $analyzed_query = PMA_SQP_analyze(PMA_SQP_parse($sql_query));
  24     // If select clause is not *
  25     if (trim($analyzed_query[0]['select_expr_clause']) != '*') {
  26         // If label column is chosen add it to the query
  27         if (isset($visualizationSettings['labelColumn'])
  28             && $visualizationSettings['labelColumn'] != ''
  29         ) {
  30             // Check to see whether an alias has been used on the label column
  31             $is_label_alias = false;
  32             foreach ($analyzed_query[0]['select_expr'] as $select) {
  33                 if ($select['alias'] == $visualizationSettings['labelColumn']) {
  34                     $modified_query .= sanitize($select) . ' AS `'
  35                     . $select['alias'] . '`, ';
  36                     $is_label_alias = true;
  37                     break;
  38                 }
  39             }
  40             // If no alias have been used on the label column
  41             if (! $is_label_alias) {
  42                 foreach ($analyzed_query[0]['select_expr'] as $select) {
  43                     if ($select['column'] == $visualizationSettings['labelColumn']) {
  44                         $modified_query .= sanitize($select) . ', ';
  45                     }
  46                 }
  47             }
  48         }
  49
  50         // Check to see whether an alias has been used on the spatial column
  51         $is_spatial_alias = false;
  52         foreach ($analyzed_query[0]['select_expr'] as $select) {
  53             if ($select['alias'] == $visualizationSettings['spatialColumn']) {
  54                 $sanitized = sanitize($select);
  55                 $modified_query .= 'ASTEXT(' . $sanitized . ') AS `'
  56                 . $select['alias'] . '`, ';
  57                 // Get the SRID
  58                 $modified_query .= 'SRID(' . $sanitized . ') AS `srid` ';
  59                 $is_spatial_alias = true;
  60                 break;
  61             }
  62         }
  63         // If no alias have been used on the spatial column
  64         if (! $is_spatial_alias) {
  65             foreach ($analyzed_query[0]['select_expr'] as $select) {
  66                 if ($select['column'] == $visualizationSettings['spatialColumn']) {
  67                     $sanitized = sanitize($select);
  68                     $modified_query .= 'ASTEXT(' . $sanitized
  69                         . ') AS `' . $select['column'] . '`, ';
  70                     // Get the SRID
  71                     $modified_query .= 'SRID(' . $sanitized . ') AS `srid` ';
  72                 }
  73             }
  74         }
  75         // If select cluase is *
  76     } else {
  77         // If label column is chosen add it to the query
  78         if ($visualizationSettings['labelColumn'] != '') {
  79             $modified_query .= '`' . $visualizationSettings['labelColumn'] .'`, ';
  80         }
  81
  82         // Wrap the spatial column with 'ASTEXT()' function and add it
  83         $modified_query .= 'ASTEXT(`' . $visualizationSettings['spatialColumn']
  84             . '`) AS `' . $visualizationSettings['spatialColumn'] . '`, ';
  85
  86         // Get the SRID
  87         $modified_query .= 'SRID(`' . $visualizationSettings['spatialColumn'] . '`) AS `srid` ';
  88     }
  89
  90     // Append the rest of the query
  91     $from_pos = stripos($sql_query, 'FROM');
  92     $modified_query .= substr($sql_query, $from_pos);
  93     return $modified_query;
  94 }
  95
  96 // Local function to sanitize the expression taken
  97 // from the results of PMA_SQP_analyze function.
  98 function sanitize($select)
  99 {
 100     $table_col = $select['table_name'] . "." . $select['column'];
 101     $db_table_col = $select['db'] . "." . $select['table_name'] . "." . $select['column'];
 102
 103     if ($select['expr'] == $select['column']) {
 104         return "`" . $select['column'] . "`";
 105     } elseif ($select['expr'] == $table_col) {
 106         return "`" . $select['table_name'] . "`.`" . $select['column'] . "`";
 107     } elseif ($select['expr'] == $db_table_col) {
 108         return "`" . $select['db'] . "`.`" . $select['table_name'] . "`.`" . $select['column'] . "`";
 109     }
 110     return $select['expr'];
 111 }
 112
 113 /**
 114  * Formats a visualization for the GIS query results.
 115  *
 116  * @param array  $data                   Data for the status chart
 117  * @param array  &$visualizationSettings Settings used to generate the chart
 118  * @param string $format                 Format of the visulaization
 119  *
 120  * @return string HTML and JS code for the GIS visualization
 121  */
 122 function PMA_GIS_visualization_results($data, &$visualizationSettings, $format)
 123 {
 124     include_once './libraries/gis/pma_gis_visualization.php';
 125     include_once './libraries/gis/pma_gis_factory.php';
 126
 127     if (! isset($data[0])) {
 128         // empty data
 129         return __('No data found for GIS visualization.');
 130     } else {
 131         $visualization = new PMA_GIS_Visualization($data, $visualizationSettings);
 132         if ($visualizationSettings != null) {
 133             foreach ($visualization->getSettings() as $setting => $val) {
 134                 if (! isset($visualizationSettings[$setting])) {
 135                     $visualizationSettings[$setting] = $val;
 136                 }
 137             }
 138         }
 139         if ($format == 'svg') {
 140             return $visualization->asSvg();
 141         } elseif ($format == 'png') {
 142             return $visualization->asPng();
 143         } elseif ($format == 'ol') {
 144             return $visualization->asOl();
 145         }
 146     }
 147 }
 148
 149 /**
 150  * Generate visualization for the GIS query results and save it to a file.
 151  *
 152  * @param array  $data                  data for the status chart
 153  * @param array  $visualizationSettings settings used to generate the chart
 154  * @param string $format                format of the visulaization
 155  * @param string $fileName              file name
 156  *
 157  * @return file File containing the visualization
 158  */
 159 function PMA_GIS_save_to_file($data, $visualizationSettings, $format, $fileName)
 160 {
 161     include_once './libraries/gis/pma_gis_visualization.php';
 162     include_once './libraries/gis/pma_gis_factory.php';
 163
 164     if (isset($data[0])) {
 165         $visualization = new PMA_GIS_Visualization($data, $visualizationSettings);
 166
 167         if ($format == 'svg') {
 168             $visualization->toFileAsSvg($fileName);
 169         } elseif ($format == 'png') {
 170             $visualization->toFileAsPng($fileName);
 171         } elseif ($format == 'pdf') {
 172             $visualization->toFileAsPdf($fileName);
 173         }
 174     }
 175 }
 176 ?>