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