1<?php 2/** 3 * Handles visualization of GIS data 4 */ 5 6declare(strict_types=1); 7 8namespace PhpMyAdmin\Gis; 9 10use PhpMyAdmin\Core; 11use PhpMyAdmin\Sanitize; 12use PhpMyAdmin\Util; 13use TCPDF; 14use const PNG_ALL_FILTERS; 15use function array_merge; 16use function base64_encode; 17use function count; 18use function imagecolorallocate; 19use function imagecreatetruecolor; 20use function imagedestroy; 21use function imagefilledrectangle; 22use function imagepng; 23use function intval; 24use function is_numeric; 25use function mb_strlen; 26use function mb_strpos; 27use function mb_strtolower; 28use function mb_substr; 29use function ob_get_clean; 30use function ob_start; 31use function is_string; 32 33/** 34 * Handles visualization of GIS data 35 */ 36class GisVisualization 37{ 38 /** @var array Raw data for the visualization */ 39 private $data; 40 41 /** @var string */ 42 private $modifiedSql; 43 44 /** @var array Set of default settings values are here. */ 45 private $settings = [ 46 // Array of colors to be used for GIS visualizations. 47 'colors' => [ 48 '#B02EE0', 49 '#E0642E', 50 '#E0D62E', 51 '#2E97E0', 52 '#BCE02E', 53 '#E02E75', 54 '#5CE02E', 55 '#E0B02E', 56 '#0022E0', 57 '#726CB1', 58 '#481A36', 59 '#BAC658', 60 '#127224', 61 '#825119', 62 '#238C74', 63 '#4C489B', 64 '#87C9BF', 65 ], 66 67 68 // Hex values for abovementioned colours 69 'colors_hex' => [ 70 [176, 46, 224], 71 [224, 100, 46], 72 [224, 214, 46], 73 [46, 151, 224], 74 [188, 224, 46], 75 [224, 46, 117], 76 [92, 224, 46], 77 [224, 176, 46], 78 [0, 34, 224], 79 [114, 108, 177], 80 [72, 26, 54], 81 [186, 198, 88], 82 [18, 114, 36], 83 [130, 81, 25], 84 [35, 140, 116], 85 [76, 72, 155], 86 [135, 201, 191], 87 ], 88 89 // The width of the GIS visualization. 90 'width' => 600, 91 // The height of the GIS visualization. 92 'height' => 450, 93 ]; 94 95 /** @var array Options that the user has specified. */ 96 private $userSpecifiedSettings = null; 97 98 /** 99 * Returns the settings array 100 * 101 * @return array the settings array 102 * 103 * @access public 104 */ 105 public function getSettings() 106 { 107 return $this->settings; 108 } 109 110 /** 111 * Factory 112 * 113 * @param string $sql_query SQL to fetch raw data for visualization 114 * @param array $options Users specified options 115 * @param int $row number of rows 116 * @param int $pos start position 117 * 118 * @return GisVisualization 119 * 120 * @access public 121 */ 122 public static function get($sql_query, array $options, $row, $pos) 123 { 124 return new GisVisualization($sql_query, $options, $row, $pos); 125 } 126 127 /** 128 * Get visualization 129 * 130 * @param array $data Raw data, if set, parameters other than $options will be 131 * ignored 132 * @param array $options Users specified options 133 * 134 * @return GisVisualization 135 */ 136 public static function getByData(array $data, array $options) 137 { 138 return new GisVisualization(null, $options, null, null, $data); 139 } 140 141 /** 142 * Check if data has SRID 143 * 144 * @return bool 145 */ 146 public function hasSrid() 147 { 148 foreach ($this->data as $row) { 149 if ($row['srid'] != 0) { 150 return true; 151 } 152 } 153 154 return false; 155 } 156 157 /** 158 * Stores user specified options. 159 * 160 * @param string $sql_query SQL to fetch raw data for visualization 161 * @param array $options Users specified options 162 * @param int $row number of rows 163 * @param int $pos start position 164 * @param array|null $data raw data. If set, parameters other than $options 165 * will be ignored 166 * 167 * @access public 168 */ 169 private function __construct($sql_query, array $options, $row, $pos, $data = null) 170 { 171 $this->userSpecifiedSettings = $options; 172 if (isset($data)) { 173 $this->data = $data; 174 } else { 175 $this->modifiedSql = $this->modifySqlQuery($sql_query, $row, $pos); 176 $this->data = $this->fetchRawData(); 177 } 178 } 179 180 /** 181 * All the variable initialization, options handling has to be done here. 182 * 183 * @return void 184 * 185 * @access protected 186 */ 187 protected function init() 188 { 189 $this->handleOptions(); 190 } 191 192 /** 193 * Returns sql for fetching raw data 194 * 195 * @param string $sql_query The SQL to modify. 196 * @param int $rows Number of rows. 197 * @param int $pos Start position. 198 * 199 * @return string the modified sql query. 200 */ 201 private function modifySqlQuery($sql_query, $rows, $pos) 202 { 203 $isMariaDb = $this->userSpecifiedSettings['isMariaDB'] === true; 204 $modified_query = 'SELECT '; 205 $spatialAsText = 'ASTEXT'; 206 $spatialSrid = 'SRID'; 207 $axisOrder = ''; 208 209 if ($this->userSpecifiedSettings['mysqlVersion'] >= 50600) { 210 $spatialAsText = 'ST_ASTEXT'; 211 $spatialSrid = 'ST_SRID'; 212 } 213 214 // If MYSQL version >= 8.0.1 override default axis order 215 if ($this->userSpecifiedSettings['mysqlVersion'] >= 80001 && ! $isMariaDb) { 216 $axisOrder = ', \'axis-order=long-lat\''; 217 } 218 219 // If label column is chosen add it to the query 220 if (! empty($this->userSpecifiedSettings['labelColumn'])) { 221 $modified_query .= Util::backquote( 222 $this->userSpecifiedSettings['labelColumn'] 223 ) 224 . ', '; 225 } 226 // Wrap the spatial column with 'ST_ASTEXT()' function and add it 227 $modified_query .= $spatialAsText . '(' 228 . Util::backquote($this->userSpecifiedSettings['spatialColumn']) 229 . $axisOrder . ') AS ' . Util::backquote( 230 $this->userSpecifiedSettings['spatialColumn'] 231 ) 232 . ', '; 233 234 // Get the SRID 235 $modified_query .= $spatialSrid . '(' 236 . Util::backquote($this->userSpecifiedSettings['spatialColumn']) 237 . ') AS ' . Util::backquote('srid') . ' '; 238 239 // Append the original query as the inner query 240 $modified_query .= 'FROM (' . $sql_query . ') AS ' 241 . Util::backquote('temp_gis'); 242 243 // LIMIT clause 244 if (is_numeric($rows) && $rows > 0) { 245 $modified_query .= ' LIMIT '; 246 if (is_numeric($pos) && $pos >= 0) { 247 $modified_query .= $pos . ', ' . $rows; 248 } else { 249 $modified_query .= $rows; 250 } 251 } 252 253 return $modified_query; 254 } 255 256 /** 257 * Returns raw data for GIS visualization. 258 * 259 * @return array the raw data. 260 */ 261 private function fetchRawData() 262 { 263 global $dbi; 264 265 $modified_result = $dbi->tryQuery($this->modifiedSql); 266 267 if ($modified_result === false) { 268 return []; 269 } 270 271 $data = []; 272 while ($row = $dbi->fetchAssoc($modified_result)) { 273 $data[] = $row; 274 } 275 276 return $data; 277 } 278 279 /** 280 * A function which handles passed parameters. Useful if desired 281 * chart needs to be a little bit different from the default one. 282 * 283 * @return void 284 * 285 * @access private 286 */ 287 private function handleOptions() 288 { 289 if ($this->userSpecifiedSettings === null) { 290 return; 291 } 292 293 $this->settings = array_merge( 294 $this->settings, 295 $this->userSpecifiedSettings 296 ); 297 } 298 299 /** 300 * Sanitizes the file name. 301 * 302 * @param string $file_name file name 303 * @param string $ext extension of the file 304 * 305 * @return string the sanitized file name 306 * 307 * @access private 308 */ 309 private function sanitizeName($file_name, $ext) 310 { 311 $file_name = Sanitize::sanitizeFilename($file_name); 312 313 // Check if the user already added extension; 314 // get the substring where the extension would be if it was included 315 $extension_start_pos = mb_strlen($file_name) - mb_strlen($ext) - 1; 316 $user_extension 317 = mb_substr( 318 $file_name, 319 $extension_start_pos, 320 mb_strlen($file_name) 321 ); 322 $required_extension = '.' . $ext; 323 if (mb_strtolower($user_extension) != $required_extension) { 324 $file_name .= $required_extension; 325 } 326 327 return $file_name; 328 } 329 330 /** 331 * Handles common tasks of writing the visualization to file for various formats. 332 * 333 * @param string $file_name file name 334 * @param string $type mime type 335 * @param string $ext extension of the file 336 * 337 * @return void 338 * 339 * @access private 340 */ 341 private function writeToFile($file_name, $type, $ext) 342 { 343 $file_name = $this->sanitizeName($file_name, $ext); 344 Core::downloadHeader($file_name, $type); 345 } 346 347 /** 348 * Generate the visualization in SVG format. 349 * 350 * @return string the generated image resource 351 * 352 * @access private 353 */ 354 private function svg() 355 { 356 $this->init(); 357 358 $output = '<?xml version="1.0" encoding="UTF-8" standalone="no"?>' 359 . "\n" 360 . '<svg version="1.1" xmlns:svg="http://www.w3.org/2000/svg"' 361 . ' xmlns="http://www.w3.org/2000/svg"' 362 . ' width="' . intval($this->settings['width']) . '"' 363 . ' height="' . intval($this->settings['height']) . '">' 364 . '<g id="groupPanel">'; 365 366 $scale_data = $this->scaleDataSet($this->data); 367 $output .= $this->prepareDataSet($this->data, $scale_data, 'svg', ''); 368 369 $output .= '</g></svg>'; 370 371 return $output; 372 } 373 374 /** 375 * Get the visualization as a SVG. 376 * 377 * @return string the visualization as a SVG 378 * 379 * @access public 380 */ 381 public function asSVG() 382 { 383 return $this->svg(); 384 } 385 386 /** 387 * Saves as a SVG image to a file. 388 * 389 * @param string $file_name File name 390 * 391 * @return void 392 * 393 * @access public 394 */ 395 public function toFileAsSvg($file_name) 396 { 397 $img = $this->svg(); 398 $this->writeToFile($file_name, 'image/svg+xml', 'svg'); 399 echo $img; 400 } 401 402 /** 403 * Generate the visualization in PNG format. 404 * 405 * @return resource the generated image resource 406 * 407 * @access private 408 */ 409 private function png() 410 { 411 $this->init(); 412 413 // create image 414 $image = imagecreatetruecolor( 415 $this->settings['width'], 416 $this->settings['height'] 417 ); 418 419 // fill the background 420 $bg = imagecolorallocate($image, 229, 229, 229); 421 imagefilledrectangle( 422 $image, 423 0, 424 0, 425 $this->settings['width'] - 1, 426 $this->settings['height'] - 1, 427 $bg 428 ); 429 430 $scale_data = $this->scaleDataSet($this->data); 431 $image = $this->prepareDataSet($this->data, $scale_data, 'png', $image); 432 433 return $image; 434 } 435 436 /** 437 * Get the visualization as a PNG. 438 * 439 * @return string the visualization as a PNG 440 * 441 * @access public 442 */ 443 public function asPng() 444 { 445 $img = $this->png(); 446 447 // render and save it to variable 448 ob_start(); 449 imagepng($img, null, 9, PNG_ALL_FILTERS); 450 imagedestroy($img); 451 $output = ob_get_clean(); 452 453 // base64 encode 454 $encoded = base64_encode((string) $output); 455 456 return '<img src="data:image/png;base64,' . $encoded . '">'; 457 } 458 459 /** 460 * Saves as a PNG image to a file. 461 * 462 * @param string $file_name File name 463 * 464 * @return void 465 * 466 * @access public 467 */ 468 public function toFileAsPng($file_name) 469 { 470 $img = $this->png(); 471 $this->writeToFile($file_name, 'image/png', 'png'); 472 imagepng($img, null, 9, PNG_ALL_FILTERS); 473 imagedestroy($img); 474 } 475 476 /** 477 * Get the code for visualization with OpenLayers. 478 * 479 * @return string the code for visualization with OpenLayers 480 * 481 * @todo Should return JSON to avoid eval() in gis_data_editor.js 482 * @access public 483 */ 484 public function asOl() 485 { 486 $this->init(); 487 $scale_data = $this->scaleDataSet($this->data); 488 $output = 'function drawOpenLayers() {' 489 . 'if (typeof ol !== "undefined") {' 490 . 'var olCss = "js/vendor/openlayers/theme/ol.css";' 491 . '$(\'head\').append(\'<link rel="stylesheet" type="text/css" href=\'+olCss+\'>\');' 492 . 'var vectorLayer = new ol.source.Vector({});' 493 . 'var map = new ol.Map({' 494 . 'target: \'openlayersmap\',' 495 . 'layers: [' 496 . 'new ol.layer.Tile({' 497 . 'source: new ol.source.OSM()' 498 . '}),' 499 . 'new ol.layer.Vector({' 500 . 'source: vectorLayer' 501 . '})' 502 . '],' 503 . 'view: new ol.View({' 504 . 'center: ol.proj.fromLonLat([37.41, 8.82]),' 505 . 'zoom: 4' 506 . '}),' 507 . 'controls: [new ol.control.MousePosition({' 508 . 'coordinateFormat: ol.coordinate.createStringXY(4),' 509 . 'projection: \'EPSG:4326\'}),' 510 . 'new ol.control.Zoom,' 511 . 'new ol.control.Attribution]' 512 . '});'; 513 $output .= $this->prepareDataSet($this->data, $scale_data, 'ol', '') 514 . 'return map;' 515 . '}' 516 . 'return undefined;' 517 . '}'; 518 519 return $output; 520 } 521 522 /** 523 * Saves as a PDF to a file. 524 * 525 * @param string $file_name File name 526 * 527 * @return void 528 * 529 * @access public 530 */ 531 public function toFileAsPdf($file_name) 532 { 533 $this->init(); 534 535 // create pdf 536 $pdf = new TCPDF( 537 '', 538 'pt', 539 $GLOBALS['cfg']['PDFDefaultPageSize'], 540 true, 541 'UTF-8', 542 false 543 ); 544 545 // disable header and footer 546 $pdf->setPrintHeader(false); 547 $pdf->setPrintFooter(false); 548 549 //set auto page breaks 550 $pdf->SetAutoPageBreak(false); 551 552 // add a page 553 $pdf->AddPage(); 554 555 $scale_data = $this->scaleDataSet($this->data); 556 $pdf = $this->prepareDataSet($this->data, $scale_data, 'pdf', $pdf); 557 558 // sanitize file name 559 $file_name = $this->sanitizeName($file_name, 'pdf'); 560 $pdf->Output($file_name, 'D'); 561 } 562 563 /** 564 * Convert file to image 565 * 566 * @param string $format Output format 567 * 568 * @return string File 569 */ 570 public function toImage($format) 571 { 572 if ($format === 'svg') { 573 return $this->asSVG(); 574 } 575 576 if ($format === 'png') { 577 return $this->asPng(); 578 } 579 580 if ($format === 'ol') { 581 return $this->asOl(); 582 } 583 584 return ''; 585 } 586 587 /** 588 * Convert file to given format 589 * 590 * @param string $filename Filename 591 * @param string $format Output format 592 * 593 * @return void 594 */ 595 public function toFile($filename, $format) 596 { 597 if ($format === 'svg') { 598 $this->toFileAsSvg($filename); 599 } elseif ($format === 'png') { 600 $this->toFileAsPng($filename); 601 } elseif ($format === 'pdf') { 602 $this->toFileAsPdf($filename); 603 } 604 } 605 606 /** 607 * Calculates the scale, horizontal and vertical offset that should be used. 608 * 609 * @param array $data Row data 610 * 611 * @return array an array containing the scale, x and y offsets 612 * 613 * @access private 614 */ 615 private function scaleDataSet(array $data) 616 { 617 $min_max = [ 618 'maxX' => 0.0, 619 'maxY' => 0.0, 620 'minX' => 0.0, 621 'minY' => 0.0, 622 ]; 623 $border = 15; 624 // effective width and height of the plot 625 $plot_width = $this->settings['width'] - 2 * $border; 626 $plot_height = $this->settings['height'] - 2 * $border; 627 628 foreach ($data as $row) { 629 // Figure out the data type 630 $ref_data = $row[$this->settings['spatialColumn']]; 631 if (! is_string($ref_data)) { 632 continue; 633 } 634 $type_pos = mb_strpos($ref_data, '('); 635 if ($type_pos === false) { 636 continue; 637 } 638 $type = mb_substr($ref_data, 0, $type_pos); 639 640 $gis_obj = GisFactory::factory($type); 641 if (! $gis_obj) { 642 continue; 643 } 644 $scale_data = $gis_obj->scaleRow( 645 $row[$this->settings['spatialColumn']] 646 ); 647 648 // Update minimum/maximum values for x and y coordinates. 649 $c_maxX = (float) $scale_data['maxX']; 650 if ($min_max['maxX'] === 0.0 || $c_maxX > $min_max['maxX']) { 651 $min_max['maxX'] = $c_maxX; 652 } 653 654 $c_minX = (float) $scale_data['minX']; 655 if ($min_max['minX'] === 0.0 || $c_minX < $min_max['minX']) { 656 $min_max['minX'] = $c_minX; 657 } 658 659 $c_maxY = (float) $scale_data['maxY']; 660 if ($min_max['maxY'] === 0.0 || $c_maxY > $min_max['maxY']) { 661 $min_max['maxY'] = $c_maxY; 662 } 663 664 $c_minY = (float) $scale_data['minY']; 665 if ($min_max['minY'] !== 0.0 && $c_minY >= $min_max['minY']) { 666 continue; 667 } 668 669 $min_max['minY'] = $c_minY; 670 } 671 672 // scale the visualization 673 $x_ratio = ($min_max['maxX'] - $min_max['minX']) / $plot_width; 674 $y_ratio = ($min_max['maxY'] - $min_max['minY']) / $plot_height; 675 $ratio = $x_ratio > $y_ratio ? $x_ratio : $y_ratio; 676 677 $scale = $ratio != 0 ? 1 / $ratio : 1; 678 679 if ($x_ratio < $y_ratio) { 680 // center horizontally 681 $x = ($min_max['maxX'] + $min_max['minX'] - $plot_width / $scale) / 2; 682 // fit vertically 683 $y = $min_max['minY'] - ($border / $scale); 684 } else { 685 // fit horizontally 686 $x = $min_max['minX'] - ($border / $scale); 687 // center vertically 688 $y = ($min_max['maxY'] + $min_max['minY'] - $plot_height / $scale) / 2; 689 } 690 691 return [ 692 'scale' => $scale, 693 'x' => $x, 694 'y' => $y, 695 'minX' => $min_max['minX'], 696 'maxX' => $min_max['maxX'], 697 'minY' => $min_max['minY'], 698 'maxY' => $min_max['maxY'], 699 'height' => $this->settings['height'], 700 ]; 701 } 702 703 /** 704 * Prepares and return the dataset as needed by the visualization. 705 * 706 * @param array $data Raw data 707 * @param array $scale_data Data related to scaling 708 * @param string $format Format of the visualization 709 * @param resource|TCPDF|string|false $results Image object in the case of png 710 * TCPDF object in the case of pdf 711 * 712 * @return mixed the formatted array of data 713 * 714 * @access private 715 */ 716 private function prepareDataSet(array $data, array $scale_data, $format, $results) 717 { 718 $color_number = 0; 719 720 // loop through the rows 721 foreach ($data as $row) { 722 $index = $color_number % count($this->settings['colors']); 723 724 // Figure out the data type 725 $ref_data = $row[$this->settings['spatialColumn']]; 726 if (! is_string($ref_data)) { 727 continue; 728 } 729 $type_pos = mb_strpos($ref_data, '('); 730 if ($type_pos === false) { 731 continue; 732 } 733 $type = mb_substr($ref_data, 0, $type_pos); 734 735 $gis_obj = GisFactory::factory($type); 736 if (! $gis_obj) { 737 continue; 738 } 739 $label = ''; 740 if (isset($this->settings['labelColumn'], $row[$this->settings['labelColumn']])) { 741 $label = $row[$this->settings['labelColumn']]; 742 } 743 744 if ($format === 'svg') { 745 $results .= $gis_obj->prepareRowAsSvg( 746 $row[$this->settings['spatialColumn']], 747 $label, 748 $this->settings['colors'][$index], 749 $scale_data 750 ); 751 } elseif ($format === 'png') { 752 $results = $gis_obj->prepareRowAsPng( 753 $row[$this->settings['spatialColumn']], 754 $label, 755 $this->settings['colors'][$index], 756 $scale_data, 757 $results 758 ); 759 } elseif ($format === 'pdf' && $results instanceof TCPDF) { 760 $results = $gis_obj->prepareRowAsPdf( 761 $row[$this->settings['spatialColumn']], 762 $label, 763 $this->settings['colors'][$index], 764 $scale_data, 765 $results 766 ); 767 } elseif ($format === 'ol') { 768 $results .= $gis_obj->prepareRowAsOl( 769 $row[$this->settings['spatialColumn']], 770 $row['srid'], 771 $label, 772 $this->settings['colors_hex'][$index], 773 $scale_data 774 ); 775 } 776 $color_number++; 777 } 778 779 return $results; 780 } 781 782 /** 783 * Set user specified settings 784 * 785 * @param array $userSpecifiedSettings User specified settings 786 * 787 * @return void 788 */ 789 public function setUserSpecifiedSettings(array $userSpecifiedSettings) 790 { 791 $this->userSpecifiedSettings = $userSpecifiedSettings; 792 } 793} 794