-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathexec.checklocation.php
176 lines (142 loc) · 6.75 KB
/
exec.checklocation.php
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
<?php
// session_start();
// error_reporting(E_ALL);
// ini_set('display_errors','1');
// require_once('classes/conexao.class.php');
// $conexao = new Conexao;
// $conn = $conexao->Conectar();
// $data = $_POST['data'];
// $data = explode('//',$data);
// //output headers so that the file is downloaded rather than displayed
// header('Content-Type: text/csv; charset=utf-8');
// header('Content-Disposition: attachment; filename=file.csv');
// // create a file pointer connected to the output stream
// $output = fopen('php://output', 'w');
// // output the column headings
// fputs($output, implode(array('latitude', 'longitude', 'status'), ';')."\n");
// // loop over the rows, outputting them
// foreach($data as $occurrence){
// $value = '';
// $fields = explode(',',$occurrence);
// $sql = "select CASE WHEN contains(GeomFromEWKT(base_geografica.\"shp_limite_brasil_250MIL\".geom),GeomFromEWKT('SRID=4326;POINT(' || " . $fields[1] . " || ' ' || " . $fields[0]. " || ')'))
// THEN CAST(1 AS BIT)
// ELSE CAST(0 AS BIT) END
// from base_geografica.\"shp_limite_brasil_250MIL\"";
// $res = pg_exec($conn,$sql);
// while ($row = pg_fetch_array($res))
// {
// if($row[0] == 1){
// fputs($output, implode(array(trim($fields[0],"\n"), trim($fields[1],"\n"), 'OK'), ';')."\n");
// } else {
// fputs($output, implode(array(trim($fields[0],"\n"), trim($fields[1],"\n"), 'outside Brazil'), ';')."\n");
// }
// }
// }
session_start();
error_reporting(E_ALL);
ini_set('display_errors','1');
require_once('classes/conexao.class.php');
$conexao = new Conexao;
$conn = $conexao->Conectar();
$data = $_POST['data'];
$data = explode('//',$data);
//output headers so that the file is downloaded rather than displayed
header('Content-Type: text/csv; charset=utf-8');
header('Content-Disposition: attachment; filename=file.csv');
// create a file pointer connected to the output stream
$output = fopen('php://output', 'w');
// output the column headings
fputs($output, implode(array('taxon','latitude', 'longitude', 'município','status'), ';')."\n");
// loop over the rows, outputting them
foreach($data as $occurrence){
$value = '';
$fields = explode(';',$occurrence);
//lat = 0 ou lon = 0
//teste coordenada zero
if($fields[1] == 0 || $fields[2] == 0){
$value = 'Coordenada zerada';
}
if($value == ''){
//teste fora do Brasil
$sql = "select CASE WHEN contains(GeomFromEWKT(base_geografica.\"shp_limite_brasil_250MIL\".geom),GeomFromEWKT('SRID=4326;POINT(' || " . $fields[2] . " || ' ' || " . $fields[1]. " || ')'))
THEN CAST(1 AS BIT)
ELSE CAST(0 AS BIT) END
from base_geografica.\"shp_limite_brasil_250MIL\"";
$res = pg_exec($conn,$sql);
while ($row = pg_fetch_array($res))
{
if($row[0] == 1){
$value = 'Dentro do Brasil';
} else {
$value = 'Fora do Brasil';
}
}
//teste fora do municipio
$sql = "select nm_mun from base_geografica.\"municipios_2014\" where contains(GeomFromEWKT(base_geografica.\"municipios_2014\".geom),GeomFromEWKT('SRID=4326;POINT(' || " . $fields[2] . " || ' ' || " . $fields[1]. " || ')'))";
$res = pg_exec($conn,$sql);
while ($row = pg_fetch_array($res))
{
// echo trim($row[0]);
// echo preg_replace('/\s+/', ' ',trim($row[0]));
// echo strtolower(preg_replace('/\s+/', ' ',trim($fields[3])));
if(strtolower(preg_replace('/\s+/', ' ',trim($row[0]))) == strtolower(preg_replace('/\s+/', ' ',trim($fields[3])))){
$value = 'Dentro do Município';
} else {
$value = 'Fora do Município';
}
}
//teste inverter coordenada
if($value == '' || $value == 'Fora do Brasil' || $value == 'Fora do Município'){
$sql = "select nm_mun from base_geografica.\"municipios_2014\" where contains(GeomFromEWKT(base_geografica.\"municipios_2014\".geom),GeomFromEWKT('SRID=4326;POINT(' || " . $fields[1] . " || ' ' || " . $fields[2]. " || ')'))";
$res = pg_exec($conn,$sql);
while ($row = pg_fetch_array($res))
{
if(trim($row[0]) == trim($fields[3])){
$value = 'Coordenada Invertida';
$tmp = $fields[1];
$fields[1] = $fields[2];
$fields[2] = $tmp;
}
}
}
//teste trocar sinal latitude
if($value == '' || $value == 'Fora do Brasil' || $value == 'Fora do Município'){
$sql = "select nm_mun from base_geografica.\"municipios_2014\" where contains(GeomFromEWKT(base_geografica.\"municipios_2014\".geom),GeomFromEWKT('SRID=4326;POINT(' || " . $fields[2] . " || ' ' || -1 *" . $fields[1]. " || ')'))";
$res = pg_exec($conn,$sql);
while ($row = pg_fetch_array($res))
{
if(trim($row[0]) == trim($fields[3])){
$value = 'Sinal Latitude Invertido';
$fields[1] = -1 * $fields[1];
}
}
}
//teste trocar sinal longitude
if($value == '' || $value == 'Fora do Brasil' || $value == 'Fora do Município'){
$sql = "select nm_mun from base_geografica.\"municipios_2014\" where contains(GeomFromEWKT(base_geografica.\"municipios_2014\".geom),GeomFromEWKT('SRID=4326;POINT(' || -1 *" . $fields[2] . " || ' ' || " . $fields[1]. " || ')'))";
$res = pg_exec($conn,$sql);
while ($row = pg_fetch_array($res))
{
if(trim($row[0]) == trim($fields[3])){
$value = 'Sinal Longitude Invertido';
$fields[2] = -1 * $fields[2];
}
}
}
//teste trocar sinal latitude e longitude
if($value == '' || $value == 'Fora do Brasil' || $value == 'Fora do Município'){
$sql = "select nm_mun from base_geografica.\"municipios_2014\" where contains(GeomFromEWKT(base_geografica.\"municipios_2014\".geom),GeomFromEWKT('SRID=4326;POINT(' || -1 *" . $fields[2] . " || ' ' || -1 *" . $fields[1]. " || ')'))";
$res = pg_exec($conn,$sql);
while ($row = pg_fetch_array($res))
{
if(trim($row[0]) == trim($fields[3])){
$value = 'Sinal Latitude e Longitude Invertidos';
$fields[1] = -1 * $fields[1];
$fields[2] = -1 * $fields[2];
}
}
}
}
fputs($output, implode(array(trim($fields[0]),trim($fields[1]), trim($fields[2]), trim($fields[3]),$value), ';')."\n");
}
?>