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