// Connecting, selecting database
$dbconn = pg_connect("host=localhost  port=5432 dbname=routing user=postgres password=p8dx2780")
    or die('Could not connect: ' . pg_last_error());

// Get Parameters
$x = $_GET["x"];
$y = $_GET["y"];

// Execute SQL to find closet vertex to map click
$sql = 'SELECT v.id, ST_AsGeoJSON(v.the_geom)
FROM (SELECT ST_SetSRID(ST_Point('.$x.','.$y.'),
900913) As the_geom) As b LEFT JOIN vertices_tmp As v
ON ST_DWithin(v.the_geom, b.the_geom, 1000)
ORDER BY ST_Distance(v.the_geom, b.the_geom)
LIMIT 1;';

$result = pg_query($sql) or die('Query failed: ' . pg_last_error());

$arr = pg_fetch_array($result, 0, PGSQL_NUM);
//echo $arr[1];

// Set source variable
$sourceID = $arr[0];

// Free resultset
pg_free_result($result);

// Execute SQL to find all fire stations within the buffer
$sqlFireStations = 'SELECT f.rt_vert FROM (SELECT ST_SetSRID(ST_Point('.$x.','.$y.'),
900913) As the_geom) As b LEFT JOIN fire_stations_close As f
ON ST_DWithin(f.the_geom, b.the_geom, 5000)
ORDER BY ST_Distance(f.the_geom, b.the_geom)
LIMIT 5;';

$resultFireStations = pg_query($sqlFireStations) or die('Query failed: ' . pg_last_error());

// Execute SQL to calculate cost for all stations within the buffer
while($arrFireStations = pg_fetch_array($resultFireStations)) {
$sqlUpdateCost = 'UPDATE fire_stations_close
SET rtcost = (SELECT SUM(cost) FROM shortest_path_astar($$
            SELECT gid as id,
                   source::integer,
                   target::integer,
                   length::double precision as cost,
                   x1, y1, x2, y2
                  FROM streets_wgs84$$,
            '.$sourceID.', '.$arrFireStations[0].', false, false))
WHERE rt_vert = '.$arrFireStations[0].';';

$resultUpdateCost = pg_query($sqlUpdateCost) or die('Query failed: ' . pg_last_error());
};

// Free resultset
pg_free_result($resultFireStations);

// Execute SQL to select the closet fire station based on cost
$sqlFireByCost = 'SELECT rt_vert
FROM (SELECT ST_SetSRID(ST_Point('.$x.','.$y.'),
900913) As the_geom) As b LEFT JOIN fire_stations_close As f
ON ST_DWithin(f.the_geom, b.the_geom, 5000)
ORDER BY rtcost
LIMIT 1;';

$resultFireByCost = pg_query($sqlFireByCost) or die('Query Failed: ' . pg_last_error());
$arrFireByCost = pg_fetch_array($resultFireByCost, 0, PGSQL_NUM);
$destID = $arrFireByCost[0];
pg_free_result($resultFireByCost);

// Execute SQL to create route and export as GeoJSON
$sqlRoute = 'SELECT st_asgeojson(st_union(the_geom)) FROM astar_sp_delta($$streets_wgs84$$,'.$sourceID.','.$destID.',1000)';
$resultRoute = pg_query($sqlRoute) or die('Query failed: ' . pg_last_error());
$arrRoute = pg_fetch_array($resultRoute, 0, PGSQL_NUM);

// Display the GeoJSON
echo $arrRoute[0];

// Free resultset
pg_free_result($resultRoute);

// Closing connection
pg_close($dbconn);