/**
* @Route("/stops")
* @Method({"GET"})
*/
public function allStopsAction(Request $request)
{
$callbackFunction = $request->query->get('jsonp');
$srid = $this->container->getParameter('realtimebus.map.srid');
$stopsFinder = new StopsFinder($db = $this->get('doctrine')->getConnection(), $srid);
$linesStr = $request->query->get('lines');
if (!is_null($linesStr)) {
$stopsFinder->setLines(LinesUtils::getLinesFromQuery($linesStr));
}
return ControllerUtils::jResponse($stopsFinder->getStops(), $callbackFunction);
}
/**
* @Route("/geocode")
* @Method({"GET"})
*/
public function geocodeAction(Request $request)
{
$callbackFunction = $request->query->get('jsonp');
$linesStr = $request->query->get('lines');
$geocode = new Geocoder($this->get('doctrine')->getConnection());
if ($linesStr) {
$geocode->setLines(LinesUtils::getLinesFromQuery($linesStr));
}
$results = $geocode->find($request);
return ControllerUtils::jResponse($results, $callbackFunction);
}
public function find(Request $request)
{
$results = array();
$query = $request->get('query');
if (stripos($query, 'bz') === false) {
$address = $query . ' BZ';
} else {
$address = $query;
}
$distance = $request->get('distance');
if (empty($distance)) {
$distance = 1000;
}
$whereLines = '';
if (!is_null($this->lines)) {
// $lines was set explicitely, otherwise everthing is accepted
if (count($this->lines) == 0) {
// 0 lines set
return array();
}
$whereLines = " AND (" . LinesUtils::whereLines('lid_verlauf.li_nr', 'lid_verlauf.str_li_var', $this->lines) . ")";
}
$url = 'http://maps.googleapis.com/maps/api/geocode/json?sensor=false®ion=it&address=' . urlencode($address);
$ch = curl_init();
curl_setopt($ch, CURLOPT_URL, $url);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
$jsonString = curl_exec($ch);
curl_close($ch);
if ($jsonString) {
try {
$json = json_decode($jsonString, true);
if ($json['status'] == 'OK') {
foreach ($json['results'] as $result) {
array_push($results, array('type' => 'location', 'name' => $result['formatted_address'], 'srid' => 'EPSG:4326', 'lat' => $result['geometry']['location']['lat'], 'lon' => $result['geometry']['location']['lng']));
}
}
} catch (Exception $e) {
}
$validResults = array();
foreach ($results as &$result) {
if ($result['lat'] > 48 || $result['lat'] < 46) {
continue;
}
if ($result['lon'] > 13 || $result['lon'] < 10) {
continue;
}
$sql = 'select
rec_ort.ort_nr, rec_ort.onr_typ_nr, ort_name as name, st_x(rec_ort.the_geom) as lon, st_y(rec_ort.the_geom) as lat
from vdv.rec_ort
INNER JOIN vdv.lid_verlauf
ON lid_verlauf.ort_nr=rec_ort.ort_nr
AND lid_verlauf.onr_typ_nr=rec_ort.onr_typ_nr
where st_intersects(rec_ort.the_geom, st_buffer(st_transform(st_geomfromtext(:geom, 4326), 25832), :distance))
' . $whereLines . '
group by rec_ort.onr_typ_nr, rec_ort.ort_nr
order by st_distance(rec_ort.the_geom, st_transform(st_geomfromtext(:geom, 4326), 25832))
limit 3';
try {
$stmt = $this->connection->prepare($sql);
$stmt->execute(array('geom' => 'POINT(' . $result['lon'] . ' ' . $result['lat'] . ')', 'distance' => $distance));
} catch (Exception $e) {
continue;
}
$result['stops'] = array();
while ($row = $stmt->fetch(\PDO::FETCH_ASSOC)) {
array_push($result['stops'], $row);
}
if (!empty($result['stops'])) {
array_push($validResults, $result);
}
}
unset($result);
$results = $validResults;
}
return $results;
}
public function getStops()
{
$selectStops = <<<EOQ
SELECT rec_ort.onr_typ_nr,
rec_ort.ort_nr,
rec_ort.ort_name,
rec_ort.ort_ref_ort_name,
ST_AsGeoJSON(rec_ort.the_geom) as json_geom
FROM vdv.rec_ort
EOQ;
$featureList = new FeatureList();
if (!is_null($this->lines)) {
// $lines was set explicitely, otherwise everthing is accepted
if (count($this->lines) == 0) {
return $featureList;
} else {
// some lines where selected, otherwise return empty FeatureCollection
$selectStops .= <<<EOQ
INNER JOIN vdv.lid_verlauf
ON lid_verlauf.ort_nr=rec_ort.ort_nr
AND lid_verlauf.onr_typ_nr=rec_ort.onr_typ_nr
WHERE
EOQ;
$selectStops .= LinesUtils::whereLines('lid_verlauf.li_nr', 'lid_verlauf.str_li_var', $this->lines);
}
}
$res = $this->connection->query($selectStops);
while ($row = $res->fetch(\PDO::FETCH_ASSOC)) {
$geometry = json_decode($row['json_geom'], true);
unset($row['json_geom']);
$featureList->add($row, $geometry);
}
return $featureList->getFeatureCollection();
}
public function positions()
{
$whereLines = '';
if (!is_null($this->lines)) {
// $lines was set explicitely, otherwise everthing is accepted
if (count($this->lines) == 0) {
// 0 lines set
return array();
}
$whereLines = " AND (" . LinesUtils::whereLines('rec_frt.li_nr', 'rec_frt.str_li_var', $this->lines) . ")";
}
$selectActPositions = <<<EOQ
SELECT
rec_frt.frt_fid,
gps_date,
delay_sec,
rec_frt.li_nr,
rec_frt.str_li_var,
lidname,
insert_date,
li_r,
li_g,
li_b,
next_rec_ort.ort_nr AS ort_nr,
next_rec_ort.onr_typ_nr AS onr_typ_nr,
next_rec_ort.ort_name AS ort_name,
next_rec_ort.ort_ref_ort_name AS ort_ref_ort_name,
ST_AsGeoJSON(ST_Transform(vehicle_position_act.the_geom, {$this->srid})) AS json_geom,
ST_AsGeoJSON(ST_Transform(vehicle_position_act.extrapolation_geom, {$this->srid})) AS json_extrapolation_geom
FROM vdv.vehicle_position_act
INNER JOIN vdv.rec_frt
ON vehicle_position_act.frt_fid=rec_frt.teq_nummer
INNER JOIN vdv.rec_lid
ON rec_frt.li_nr=rec_lid.li_nr
AND rec_frt.str_li_var=rec_lid.str_li_var
LEFT JOIN vdv.lid_verlauf lid_verlauf_next
ON rec_frt.li_nr=lid_verlauf_next.li_nr
AND rec_frt.str_li_var=lid_verlauf_next.str_li_var
AND vehicle_position_act.li_lfd_nr + 1 = lid_verlauf_next.li_lfd_nr
LEFT JOIN vdv.rec_ort next_rec_ort
ON lid_verlauf_next.onr_typ_nr=next_rec_ort.onr_typ_nr
AND lid_verlauf_next.ort_nr=next_rec_ort.ort_nr
LEFT JOIN vdv.line_attributes
ON rec_frt.li_nr=line_attributes.li_nr
WHERE gps_date > NOW() - interval '10 minute'
-- AND vehicle_position_act.status='r'
{$whereLines}
EOQ;
$res = $this->connection->query($selectActPositions);
$featureList = new FeatureList();
while ($row = $res->fetch(\PDO::FETCH_ASSOC)) {
if (!is_null($row['json_extrapolation_geom'])) {
$geometry = json_decode($row['json_extrapolation_geom'], true);
} else {
$geometry = json_decode($row['json_geom'], true);
}
unset($row['json_geom']);
unset($row['json_extrapolation_geom']);
$hex = str_pad(dechex($row['li_r']), 2, "0", STR_PAD_LEFT);
$hex .= str_pad(dechex($row['li_g']), 2, "0", STR_PAD_LEFT);
$hex .= str_pad(dechex($row['li_b']), 2, "0", STR_PAD_LEFT);
$row['hexcolor'] = '#' . $hex;
$row['hexcolor2'] = strtoupper($hex);
$featureList->add($row, $geometry);
}
return $featureList->getFeatureCollection();
}