<?php





   // obter ponto de ínicio
   $start = split(' ',$_REQUEST['startpoint']);
   $startPoint = array($start[0], $start[1]);

   // obter ponto de destino
   $end = split(' ',$_REQUEST['finalpoint']);
   $endPoint = array($end[0], $end[1]);




   // encontrar vertice mais próximo
   $startpoint = findNearestpoint($startPoint);
   $endpoint   = findNearestpoint($endPoint);



   // função findNearestpoint
   function findNearestpoint($lonlat) {



    // Connectar à base

    $con = pg_connect("host=localhost dbname=****** port =5432 user=***** password=****");



     // query para encontar o ponto mais próximo
     //Uma vez que o mapa esta no EPSG 900913 e a base no EPSG27492 foi necessário converter as coordenadas

    $sql = "SELECT id, the_geom,
    distance(the_geom, GeometryFromText(ST_Transform(ST_SetSRID(ST_Point(".$lonlat[0].", ".$lonlat[1]."),900913),27492),27492)) As dist
    FROM vertices_tmp
    ORDER BY dist LIMIT 1";








      $query = pg_query($con,$sql);

      $edge['id']      = pg_fetch_result($query, 0, 0);
      $edge['the_geom'] = pg_fetch_result($query, 0, 1);



      // fechar conexão à base de dados
      pg_close($con);

      return $edge;
   }





   // Selecionar o algoritmo para calcular o percurso


   switch($_REQUEST['method']) {

    case 'SP1' :


            $sql = "SELECT arcos.n_passeio, ST_AsGeoJSON(ST_Transform(arcos.the_geom,900913)) AS geojson, length(arcos.the_geom) AS length, arcos.n_passeio
                    FROM arcos
                    JOIN
                    (SELECT * FROM shortest_path('SELECT id,source::int4 AS source,target::int4 AS target,length::double precision AS cost FROM arcos',

                ".$startpoint['id'].",".$endpoint['id'].",false,false)) AS rota
                    ON
                    arcos.n_passeio = rota.edge_id;";



            break;




    case 'SP2' :

            $sql = "SELECT arcos.n_passeio, ST_AsGeoJSON(ST_Transform(arcos.the_geom,900913)) AS geojson, length(arcos.the_geom) AS length, arcos.n_passeio
                    FROM arcos
                    JOIN
                    (SELECT * FROM shortest_path('SELECT id,source::int4 AS source,target::int4 AS target,length::double precision AS cost FROM arcos',

                ".$startpoint['id'].",".$endpoint['id'].",false,false)) AS rota
                    ON
                    arcos.n_passeio = rota.edge_id;";



            break;





    case 'SP3' :

            $sql = "SELECT arcos.n_passeio, ST_AsGeoJSON(ST_Transform(arcos.the_geom,900913)) AS geojson, length(arcos.the_geom) AS length, arcos.n_passeio
                    FROM arcos
                    JOIN
                    (SELECT * FROM shortest_path('SELECT id,source::int4 AS source,target::int4 AS target,length::double precision AS cost FROM arcos',

                ".$startpoint['id'].",".$endpoint['id'].",false,false)) AS rota
                    ON
                    arcos.n_passeio = rota.edge_id;";



            break;



   } // fechar switch




   // Conectar à base
   $dbcon = pg_connect("host=localhost dbname=****** port =5432 user=******* password=******");



   // Realizar a query
   $query = pg_query($dbcon,$sql);







   // Retornar a rota como GeoJSON
   $geojson = array(
      'type'      => 'FeatureCollection',
      'features'  => array()
   );





   // Adicionar as linhas do percurso ao array geojson
   while($edge=pg_fetch_assoc($query)) {

      $feature = array(
         'type' => 'Feature',
         'geometry' => json_decode($edge['geojson'], true),

        'crs' => array(
            'type' => 'EPSG',
            'properties' => array('code' => '900913')
         ),



         'properties' => array(
            'id' => $edge['id'],
            'length' => $edge['length']
         )
      );





      //adicionar o array dos percursos ao array collection
     array_push($geojson['features'], $feature);
   }





   // fechar a conexão à base de dados
   pg_close($dbcon);



   // Retornar o resultado
   header('Content-type: application/json',true);
   echo json_encode($geojson);









   //points




   $sql_pontos = "SELECT pontos.n_passeio, ST_AsGeoJSON(ST_Transform(pontos.the_geom,900913)) AS geojson_pontos
                    FROM pontos
                    JOIN
                    (SELECT * FROM shortest_path('SELECT id,source::int4 AS source,target::int4 AS target,length::double precision AS cost FROM arcos',
                    ".$startpoint['id'].",".$endpoint['id'].",false,false)) AS rota
                    ON
                    pontos.n_passeio = rota.edge_id";




   // Conectar à base para os pontos
   $dbcon = pg_connect("host=localhost dbname=****** port =5432 user=***** password=*****");



    //realizar a query dos pontos

    $query_pontos = pg_query($dbcon,$sql_pontos);



    // Retornar os pontos como geojson

   $geojson_pontos = array(
      'type'      => 'FeatureCollection',
      'features'  => array()
   );





     // Adicionar os pontos dos pontos ao array geojson

   while($point=pg_fetch_assoc($query_pontos)) {

      $feature_pontos = array(
         'type' => 'Feature',
         'geometry' => json_decode($point['geojson_pontos'], true),

        'crs' => array(
            'type' => 'EPSG',
            'properties' => array('code' => '900913')
         ),



         'properties' => array(
            'id' => $point['id']
         )
      );


    //pontos
     array_push($geojson_pontos['features'], $feature_pontos);
   }




     // fechar a conexão à base de dados
    pg_close($dbcon);




      //pontos
   header('Content-type: application/json',true);
   echo json_encode($geojson_pontos);