/** * @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(); }