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_modifyQuery($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 (isset($visualizationSettings['labelColumn'])
  79             && $visualizationSettings['labelColumn'] != ''
  80         ) {
  81             $modified_query .= '`' . $visualizationSettings['labelColumn'] .'`, ';
  82         }
  83
  84         // Wrap the spatial column with 'ASTEXT()' function and add it
  85         $modified_query .= 'ASTEXT(`' . $visualizationSettings['spatialColumn']
  86             . '`) AS `' . $visualizationSettings['spatialColumn'] . '`, ';
  87
  88         // Get the SRID
  89         $modified_query .= 'SRID(`' . $visualizationSettings['spatialColumn']
  90             . '`) AS `srid` ';
  91     }
  92
  93     // Append the rest of the query
  94     $from_pos = stripos($sql_query, 'FROM');
  95     $modified_query .= substr($sql_query, $from_pos);
  96     return $modified_query;
  97 }
  98
  99 // Local function to sanitize the expression taken
 100 // from the results of PMA_SQP_analyze function.
 101 function sanitize($select)
 102 {
 103     $table_col = $select['table_name'] . "." . $select['column'];
 104     $db_table_col = $select['db'] . "." . $select['table_name']
 105         . "." . $select['column'];
 106
 107     if ($select['expr'] == $select['column']) {
 108         return "`" . $select['column'] . "`";
 109     } elseif ($select['expr'] == $table_col) {
 110         return "`" . $select['table_name'] . "`.`" . $select['column'] . "`";
 111     } elseif ($select['expr'] == $db_table_col) {
 112         return "`" . $select['db'] . "`.`" . $select['table_name']
 113             . "`.`" . $select['column'] . "`";
 114     }
 115     return $select['expr'];
 116 }
 117
 118 /**
 119  * Formats a visualization for the GIS query results.
 120  *
 121  * @param array  $data                   Data for the status chart
 122  * @param array  &$visualizationSettings Settings used to generate the chart
 123  * @param string $format                 Format of the visulaization
 124  *
 125  * @return string HTML and JS code for the GIS visualization
 126  */
 127 function PMA_GIS_visualizationResults($data, &$visualizationSettings, $format)
 128 {
 129     include_once './libraries/gis/pma_gis_visualization.php';
 130     include_once './libraries/gis/pma_gis_factory.php';
 131
 132     if (! isset($data[0])) {
 133         // empty data
 134         return __('No data found for GIS visualization.');
 135     } else {
 136         $visualization = new PMA_GIS_Visualization($data, $visualizationSettings);
 137         if ($visualizationSettings != null) {
 138             foreach ($visualization->getSettings() as $setting => $val) {
 139                 if (! isset($visualizationSettings[$setting])) {
 140                     $visualizationSettings[$setting] = $val;
 141                 }
 142             }
 143         }
 144         if ($format == 'svg') {
 145             return $visualization->asSvg();
 146         } elseif ($format == 'png') {
 147             return $visualization->asPng();
 148         } elseif ($format == 'ol') {
 149             return $visualization->asOl();
 150         }
 151     }
 152 }
 153
 154 /**
 155  * Generate visualization for the GIS query results and save it to a file.
 156  *
 157  * @param array  $data                  data for the status chart
 158  * @param array  $visualizationSettings settings used to generate the chart
 159  * @param string $format                format of the visulaization
 160  * @param string $fileName              file name
 161  *
 162  * @return file File containing the visualization
 163  */
 164 function PMA_GIS_saveToFile($data, $visualizationSettings, $format, $fileName)
 165 {
 166     include_once './libraries/gis/pma_gis_visualization.php';
 167     include_once './libraries/gis/pma_gis_factory.php';
 168
 169     if (isset($data[0])) {
 170         $visualization = new PMA_GIS_Visualization($data, $visualizationSettings);
 171
 172         if ($format == 'svg') {
 173             $visualization->toFileAsSvg($fileName);
 174         } elseif ($format == 'png') {
 175             $visualization->toFileAsPng($fileName);
 176         } elseif ($format == 'pdf') {
 177             $visualization->toFileAsPdf($fileName);
 178         }
 179     }
 180 }
 181 ?>