0
I developed a script to convert a coordinate in a REST API with Cakephp $lat and $lng in formatted addresses. The script checks if the coordinate is saved in the bank, if negative it searches google and already saved in the bank.
The script has worked normally, follows the script below:
GEOCODES CONTROLLER
// Function to serialize array $response to the format requested
public function beforeRender() {
$this->set('_serialize', 'response');
}
public function index(){
//$functions = new GeocodeFunctions();
//$latlng = $functions->verificaLatLng($this->request->query['latlng']);
$response = $this->requestAction(
array('controller' => 'geocodes', 'action' => 'geocode'),
array('pass' => array($this->request->query['latlng']))
);
$this->set('response',$response);
}
public function geocode($latlng = null) {
$db = ConnectionManager::getDataSource("geocode");
$functions = new GeocodeFunctions();
//$latlng = $functions->verificaLatLng($latlng);
if($latlng != null){
$geocode = $db->fetchAll(
'Select enderecos.endereco as endereco from geocodes inner join enderecos on geocodes.id_endereco = enderecos.id where geocodes.latlng like ? ;'
,array($latlng)
);
if(empty($geocode)){
$google = $this->google($latlng, $db);
if(array_key_exists('results',$google) && $google->results != array()){
$response = array('code'=>2, 'endereco'=>$google->results[0]->formatted_address);
}else if(array_key_exists('error_message',$google)){
$response = array('code'=>3, 'endereco'=>'O seguinte erro ocorreu "'.$google->error_message.'", por favor informe ao suporte do ocorrido.');
}else{
$response = array('code'=>3, 'endereco'=>'Novo erro detectado! Por favor, entre em contato com o suporte informando dia e hora do ocorrido.');
CakeLog::write('geocodes', json_encode($google));
}
}
else{
$response= array('code'=>1, 'endereco'=>$geocode[0]['enderecos']['endereco']);
}
}
else{
$response = array('code'=>0, 'endereco'=>'latitude e longitudade vazia!');
}
return $response;
}
public function google($latlng = null, $db){
$data = array();
if($latlng != null){
$HttpSocket = new HttpSocket();
$results = $HttpSocket->get(
'https://maps.googleapis.com/maps/api/geocode/json',
array(
'latlng' => $latlng,
'key' => 'minha-chave-secreta'
)
);
if(array_key_exists('body', $results)) $data = json_decode($results['body']);
if(array_key_exists('results',$data) && count($data->results)>0){
$id = $db->fetchAll(
'Select id from enderecos where endereco like ? ;'
,array($data->results[0]->formatted_address)
);
if(empty($id)){
$db->fetchAll(
'
INSERT INTO `enderecos`(`endereco`) select ? from dual
WHERE NOT EXISTS (SELECT endereco FROM enderecos WHERE endereco = ?);
INSERT INTO `geocodes`(`latlng`, `id_endereco`) SELECT ? , (SELECT LAST_INSERT_ID()) from dual
WHERE NOT EXISTS (SELECT latlng FROM geocodes WHERE latlng = ?);
'
,array($data->results[0]->formatted_address, $data->results[0]->formatted_address, $latlng,$latlng)
);
}else{
$db->fetchAll(
'INSERT INTO `geocodes`(`latlng`, `id_endereco`) select ? , ? from dual
WHERE NOT EXISTS (SELECT latlng FROM geocodes WHERE latlng = ?)'
,array( $latlng, $id[0]['enderecos']['id'], $latlng )
);
}
}
return $data;//->results[0]->formatted_address
}
else{
return null;
}
}
public function save(){
}
}
?>
However, when placing it in one of the functions of another Controller, it makes the script very slow generating an Error 502 Bad Gateway on the page. The controller is the following:
A function of one of the controllers using Geocodecontroller
[...]
public function getRelatorioRotas(){
// Declarando classes
$fun = new Functions();
$GEO = new GeocodesController;
// obtendo dados da requisição
$data = $this->request->data;
// contador de pontos
if($data['dtFim'] == null){
$count = $this->Record->query("
Select Count(*) as total FROM `records`
WHERE `hour` >= ".$data['dtInicio']."
AND `vehicle_id` = ".$data['veiculo']."
ORDER BY `records`.`hour` asc
");
}else{
$count = $this->Record->query("
Select Count(*) as total FROM `records`
WHERE `hour` >= ".$data['dtInicio']."
AND `hour` <= ".$data['dtFim']."
AND `vehicle_id` = ".$data['veiculo']."
ORDER BY `records`.`hour` asc
");
}
$count = $count[0][0]['total'];
if($count>0){
// Instancia de variaveis
// variavel paginas e tempo
$i=$inicial=$distancia=$chega=0;
$final=20;
// Ponto Final e Endereço
$PontoFinal=$PontoInicial=$LastPonto=$dt=null;
// variavel de retorno dos pontos
$rotas = $rota = array();
$velMed = $velMax = $contadorRotas = 0;
$menor=true;
if($data['dtFim'] == null){
$query = $this->Record->query(
"SELECT
lat, lng, `ignition`, `hour`, `speed`
FROM `records`
WHERE `hour` >= ".$data['dtInicio']."
AND `vehicle_id` = ".$data['veiculo']."
ORDER BY `records`.`hour` asc
"
);
}else{
$query = $this->Record->query(
"SELECT
lat, lng, `ignition`, `hour`, `speed`
FROM `records`
WHERE `hour` >= ".$data['dtInicio']."
AND `hour` <= ".$data['dtFim']."
AND `vehicle_id` = ".$data['veiculo']."
ORDER BY `records`.`hour` asc
"
);
}
$count = count($query)-1;
foreach ($query as $key => $record) {
$latlng = $record['records']['lat'].",".$record['records']['lng'];
if( $record['records']['speed'] > $velMax){
$velMax = $record['records']['speed'];
}
$velMed+=$record['records']['speed'];
if( count($rota) <= 0 || $PontoInicial == null ) {
$dt = $GEO->geocode($latlng);
$PontoInicial = array(
'endereco' => $dt['endereco'],
'hour' => $record['records']['hour']
);
if($PontoFinal!=null && ( ($record['records']['hour'] - $PontoFinal['ponto']['hour'])<= (3*60*60))){
array_push($rota, array(
'latlng' => $PontoFinal['ponto']['latlng'],
'ignition' => $PontoFinal['ponto']['ignition'],
'speed' => $PontoFinal['ponto']['speed'],
'hour' => $record['records']['hour']
));
}
$obj = array(
'latlng' => $latlng,
'ignition' => $record['records']['ignition'],
'speed' => $record['records']['speed'],
'hour' => $record['records']['hour']
);
array_push($rota, $obj);
$distancia = 0;
}
else if(
$record['records']['ignition']==true
&& ($record['records']['hour'] - $LastPonto['hour']) < 600
) {
$ola = $fun->calcDistancia($LastPonto['lat'], $LastPonto['lng'], $record['records']['lat'], $record['records']['lng']);
$distancia += $ola;
if($ola > 0.01 && end($rota)['hour'] <= $record['records']['hour']){
$obj = array(
'latlng' => $latlng,
'ignition' => $record['records']['ignition'],
'speed' => $record['records']['speed'],
'hour' => $record['records']['hour'],
'distancia_do_ultimo_ponto' => $ola
);
array_push($rota, $obj);
}
}else {
if($key >= $count && end($rota)['hour'] <= $record['records']['hour']){
$ola = $fun->calcDistancia($LastPonto['lat'], $LastPonto['lng'], $record['records']['lat'], $record['records']['lng']);
$distancia += $ola;
$obj = array(
'latlng' => $latlng,
'ignition' => $record['records']['ignition'],
'speed' => $record['records']['speed'],
'hour' => $record['records']['hour'],
'distancia_do_ultimo_ponto' => $ola
);
$ultimoPonto = $obj;
}else{
$ultimoPonto = end($rota);
}
//$dt = $GEO->geocode($ultimoPonto['latlng']);
$PontoFinal = array(
'endereco' => $dt['endereco'],
'hour' => $ultimoPonto['hour']
);
$velMed /= (count($rota));
if($PontoFinal['hour'] != $PontoInicial['hour']){
$dur = $PontoFinal['hour'] - $PontoInicial['hour'];
if($dur > 60 && $distancia >0 && count($rota) >=4){
if(count($rotas)>0){
$ultima = end($rotas);
$ultima = end($ultima['Pontos']);
$oux = $rota[0];
$diferenca = (int)$rota[0]['hour'] - (int)$ultima['hour'];
if($diferenca < (600)){
$nova=false;
$contadorRotas--;
$ultima=array_pop($rotas);
if($ultima['velocidadeMaxima']>$velMax){
$velMax = $ultima['velocidadeMaxima'];
}
foreach ($rota as $value) {
array_push($ultima['Pontos'], $value);
}
array_push($rotas, array(
'id'=> $contadorRotas,
'pontoInicial'=>$ultima['pontoInicial'],
'Pontos' => $ultima['Pontos'],
'pontoFinal'=> $PontoFinal,
'distancia'=>$ultima['distancia']+$distancia,
'duracao'=> $ultima['duracao']+$dur,
'velocidadeMaxima'=> $velMax,
'velocidadeMedia'=> ($ultima['velocidadeMedia']+$velMed)/2
));
}else{
$nova = true;
}
}else{
$nova = true;
}
if($nova){
array_push($rotas, array(
'id'=> $contadorRotas,
'pontoInicial'=>$PontoInicial,
'Pontos' => $rota,
'pontoFinal'=> $PontoFinal,
'distancia'=>$distancia,
'duracao'=> $dur,
'velocidadeMaxima'=> $velMax,
'velocidadeMedia'=> $velMed
));
}
$contadorRotas++;
}
}
$PontoFinal['ponto'] = $rota[count($rota)-1];
$distancia = $velMed = $velMax = 0;
$PontoInicial=null;
$rota=array();
}
$LastPonto = $record['records'];
}
if(count($rotas)>0){
$this->set('response', array('code'=>1,'rotas'=>$rotas, 'dataInicial'=> $data['dtInicio']));
}else{
$this->set('response', array('code'=>0, 'rotas'=>$rotas, 'dataInicial'=> $data['dtInicio']));
}
}
else{
$this->set('response', array('code'=>0));
}
}
[...]
If I comment on the line $dt = $GEO->geocode($latlng); the script works perfectly.
Every way, for debug reasons I started commenting on the Controller of Geocodee and printing by steps. And I found that I was giving conflict in the tables Insert because of the key Unique, thing I had solved with a Insert Select with WHERE clause to check if it existed, but it is not working. After that I found that the error did not occur with the same coordinate or address twice. So far I am unsolved.
Write in the answer field the answer you found =] so this can help other people. It is also not considered well seen to put "solved" in the title, the ideal would be to mark the answer that actually solved your problem
– Jefferson Quesado
Place the answer in the following answer field: https://answall.com/revisions/248697/3
– Guilherme Nascimento
Thanks, I do not know much of the normalizations of stackoverflow.
– Hugo Barbato