Developed in a REST API with CakePHP a script to convert a $lat and $lng coordinate into formatted addresses. The script checks if the coordinate is saved in the database, if it does not google search and already saved in the database.
The script has worked normally, follow 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 you put it in one of the functions of another Controller, it leaves the script very slow, generating an Error 502 Bad Gateway on the page. The controller is as follows:
A function of one of the controllers that uses 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 the line $dt = $GEO->geocode($latlng); the script works perfectly.
At all events, for reasons of debugging, I started commenting on the Geocodee Controller and starting it in stages. And I checked that I was conflicting with the insert of the tables because of the unique key, which I had solved with a Insert Select with a WHERE clause to check if it existed, but it is not working. After that I verified that the error did not occur with the same coordinate or address twice. So far I have no solution.