TME_MySQL_DataConnector.php
5.21 KB
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
<?php
//
// SOFTWARE NAME: Thematic Mapping Engine
// SOFTWARE RELEASE: 1.6
// COPYRIGHT NOTICE: Copyright (C) 2008 Bjorn Sandvik,
// bjorn@thematicmapping.org
// SOFTWARE LICENSE: GNU General Public License version 3 (GPLv3)
// NOTICE: >
// This program is free software; you can redistribute it and/or
// modify it under the terms of version 3 of the GNU General
// Public License as published by the Free Software Foundation.
//
// This program is distributed in the hope that it will be useful,
// but WITHOUT ANY WARRANTY; without even the implied warranty of
// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
// GNU General Public License for more details.
// http://www.gnu.org/licenses/
//
//
// Set database access information as constants
// Should be stored outside of the Web directory
DEFINE ('DB_USER', 'myuser');
DEFINE ('DB_PASSWORD', 'mypass');
DEFINE ('DB_HOST', 'localhost');
DEFINE ('DB_NAME', 'tme');
class DataConnector
{
public $featureTable = 'country_simpl';
public $indicatorTable = 'indicator';
public $valuesTable = 'indicator_values';
private $dbc;
// Constructor
function __construct()
{
// Make the connection
$this->dbc = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME) or die ("Could not connect to MySQL: " . mysqli_connect_error());
}
// Fetch all indicators
function getIndicators(){
$sql = 'SELECT id, name, description, source FROM indicator ORDER BY name';
$result = $this->dbc->query($sql);
// Loop thorough all indicators
while($row = $result->fetch_row())
{
// Add values to array
$indicators[] = array('id' => $row[0],
'name' => $row[1],
'description' => $row[2],
'source' => $row[3]);
}
// Make JSON encoded array
$json = json_encode(array('indicators' => $indicators));
return $json;
}
// Fetch all avaialbe years for one indicator
function getIndicatorYears($indicatorID){
$sql = "SELECT DISTINCT year FROM indicator_values WHERE variable=$indicatorID ORDER BY year";
$result = $this->dbc->query($sql);
// Loop thorough all years
while($row = $result->fetch_row())
{
// Add values to array
$years[] = array('year' => $row[0]);
}
// Make JSON encoded array
$json = json_encode(array('years' => $years));
return $json;
}
// Make data store
function getDataStore($indicatorID, $year, $region){
$sqlregion = '';
if ($region) $sqlregion = "region = $region AND";
$sqlyear = '';
if ($year) $sqlyear = "AND year = $year";
// Add features - exclude Antarctica
$sql = "SELECT un AS featureID, name, lon, lat, AsText(geom) AS wkt
FROM $this->featureTable
WHERE $sqlregion un != 10
ORDER BY featureID";
$features = $this->dbc->query($sql);
while($row = $features->fetch_array(MYSQLI_ASSOC))
{
// First field should be feature id
$featureID = array_shift($row);
// Add feature to dataStore
$dataStore['features'][$featureID] = $row;
}
$indicatorYears = array();
// Select indicator metadata
$sql = "SELECT name, description, source, decimals,
(SELECT ROUND(MAX(value),decimals) FROM indicator_values, $this->featureTable WHERE variable=$indicatorID AND indicator_values.area=un $sqlyear) AS max,
(SELECT ROUND(MIN(value),decimals) FROM indicator_values, $this->featureTable WHERE variable=$indicatorID AND indicator_values.area=un $sqlyear) AS min
FROM $this->indicatorTable
WHERE id=$indicatorID";
$result = $this->dbc->query($sql);
$indicator = $result->fetch_assoc();
$precision = $indicator['decimals'];
// Add indicator to dataStore
$dataStore['indicators'][$indicatorID] = $indicator;
// Select indicator values (only values that have features)
$sql = "SELECT indvalues.area AS featureID, indvalues.year, indvalues.value
FROM $this->valuesTable AS indvalues, $this->featureTable
WHERE indvalues.variable=$indicatorID
AND indvalues.area=un
$sqlyear
ORDER BY indvalues.value"; // Needed for qunatiles calculation
$result = $this->dbc->query($sql);
// Add indicator values to dataStore
while($row = $result->fetch_row())
{
$dataStore['indicators'][$indicatorID]['values'][$row[1]][$row[0]] = number_format($row[2], $precision, '.', '');
// Find all years with values (could also be a separate sql for better performance)
$indicatorYears[$row[1]] = $row[1];
}
sort($indicatorYears);
$dataStore['indicators'][$indicatorID]['years'] = $indicatorYears;
return $dataStore;
}
}
?>