Advertisement
Guest User

Untitled

a guest
Nov 11th, 2016
122
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 18.64 KB | None | 0 0
  1. <?php
  2. define('DB_HOST', 'localhost');
  3. define('DB_NAME', 'trains_geo');
  4. define('DB_USER', 'root');
  5. define('DB_PASSWORD', '');
  6. define('DB_ENCODING', 'utf8');
  7. define('DB_CHARSET', 'utf8');
  8.  
  9. class DbConnect {
  10.  
  11. private $db_host = DB_HOST;
  12. private $db_name = DB_NAME;
  13. private $db_user = DB_USER;
  14. private $db_password = DB_PASSWORD;
  15. private $db_encoding = DB_ENCODING;
  16. private $db_charset = DB_CHARSET;
  17.  
  18. /**
  19. * PDO instance
  20. * @var PDO
  21. */
  22. private $pdo = null;
  23.  
  24. /**
  25. * Open the database connection
  26. */
  27. public function __construct() {
  28. // open database connection
  29. $conStr = sprintf("mysql:host=%s;dbname=%s;encoding:%s;charset=%s;", $this->db_host, $this->db_name, $this->db_encoding, $this->db_charset);
  30.  
  31. try {
  32. $this->pdo = new PDO($conStr, $this->db_user, $this->db_password);
  33. $this->pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  34. } catch (PDOException $e) {
  35. echo $e->getMessage();
  36. }
  37. }
  38.  
  39. public function getPdo() {
  40. return $this->pdo;
  41. }
  42.  
  43. /**
  44. * close the database connection
  45. */
  46. public function __destruct() {
  47. // close the database connection
  48. $this->pdo = null;
  49. }
  50.  
  51. }
  52.  
  53. class DrawMapStructure {
  54.  
  55. private $pdo = null;
  56. private $spatialDataPolylines = array();
  57. private $spatialDataStationsPoints = array();
  58. private $spatialDataCurrentConnectionLine = array();
  59. private $spatialDataCurrentConnectionSpatialLine = array();
  60. private $stationSpiderLines = array();
  61. private $distances = array();
  62. private $simplePathStations = array();
  63. private $stationLineIntersects = array();
  64. private $lastCheckedLineIds = array();
  65. private $floatPrecision = 10;
  66. private $lineId = 0;
  67. private $checkSLI = true;
  68. public $dijkstrasRouteEngine = array();
  69. private $SRP = array();
  70. public $SRPStations;
  71. public $SRPSections;
  72. public $totalCost;
  73.  
  74. public function __construct() {
  75. $dbConnect = new DbConnect();
  76. $this->pdo = $dbConnect->getPdo();
  77. }
  78.  
  79. public function getRailwaysPolylines() {
  80. $sql = 'SELECT `id`, ST_AsGeoJSON(`line`, :floatPrecision) AS `line` FROM `connections`';
  81. $stmt = $this->pdo->prepare($sql);
  82. $stmt->bindParam(':floatPrecision', $this->floatPrecision, PDO::PARAM_INT);
  83. $stmt->execute();
  84.  
  85. while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
  86. $this->spatialDataPolylines[$row['id']] = json_decode($row['line'], true);
  87. }
  88.  
  89. return $this->spatialDataPolylines;
  90. }
  91.  
  92. public function getStationsPoints() {
  93. $sql = 'SELECT `id`, `name`, ST_AsGeoJSON(`point`, :floatPrecision) AS `point` FROM `stations`';
  94.  
  95. $stmt = $this->pdo->prepare($sql);
  96. $stmt->bindParam(':floatPrecision', $this->floatPrecision, PDO::PARAM_INT);
  97. $stmt->execute();
  98.  
  99. while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
  100. $this->spatialDataStationsPoints[$row['id']] = json_decode($row['point'], true);
  101. $this->spatialDataStationsPoints[$row['id']]['name'] = $row['name'];
  102. }
  103.  
  104. return $this->spatialDataStationsPoints;
  105. }
  106.  
  107. public function getStationPoint($stationId, $stationPointJSON = false) {
  108. if($stationPointJSON) {
  109. $sql = 'SELECT ST_AsGeoJSON(`point`, :floatPrecision) AS `pointJSON` FROM `stations` WHERE `id`=:id';
  110.  
  111. $stmt = $this->pdo->prepare($sql);
  112. $stmt->bindParam(':floatPrecision', $this->floatPrecision, PDO::PARAM_INT);
  113. $stmt->bindParam(':id', $stationId, PDO::PARAM_INT);
  114. $stmt->execute();
  115.  
  116. return json_decode($stmt->fetch(PDO::FETCH_ASSOC)['pointJSON'], true);
  117. } else {
  118. $sql = 'SELECT `point` FROM `stations` WHERE `id`=:id';
  119.  
  120. $stmt = $this->pdo->prepare($sql);
  121. $stmt->bindParam(':id', $stationId, PDO::PARAM_INT);
  122. $stmt->execute();
  123.  
  124. return $stmt->fetch(PDO::FETCH_ASSOC)['point'];
  125. }
  126. }
  127.  
  128. public function getCurrentConnectionLine($beginStationId, $endStationId) {
  129. $sql = 'SELECT s.`id` AS `station_id`, s.`name`, c.`id` AS `line_id`, ST_AsGeoJSON(c.`line`, :floatPrecision) AS `currentLineSpatialJSONData`
  130. FROM `connections` c
  131. JOIN `stations` s ON (ST_Intersects(s.`point`, c.`line`) = 1)
  132. WHERE (ST_INTERSECTS(c.`line`, :station1)
  133. OR ST_INTERSECTS(c.`line`, :station2))
  134. GROUP BY c.`id`';
  135.  
  136. $stmt = $this->pdo->prepare($sql);
  137. $stmt->bindParam(':floatPrecision', $this->floatPrecision, PDO::PARAM_INT);
  138. $stmt->bindValue(':station1', $this->getStationPoint($beginStationId));
  139. $stmt->bindValue(':station2', $this->getStationPoint($endStationId));
  140. $stmt->execute();
  141.  
  142. while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
  143. $this->spatialDataCurrentConnectionLine[$row['line_id']] = json_decode($row['currentLineSpatialJSONData'], true);
  144. }
  145.  
  146. return $this->spatialDataCurrentConnectionLine;
  147. }
  148.  
  149. public function getDistanceLineOfTwoStations($s1, $s2) {
  150. $sql = 'SELECT
  151. ST_Length(
  152. ST_LineStringFromWKB(
  153. LineString(
  154. (SELECT `point` FROM `stations` WHERE `id` = :s1Id),
  155. (SELECT `point` FROM `stations` WHERE `id` = :s2Id)
  156. )
  157. )
  158. ) AS `distance`';
  159.  
  160. $stmt = $this->pdo->prepare($sql);
  161. $stmt->bindValue(':s1Id', $s1, PDO::PARAM_INT);
  162. $stmt->bindValue(':s2Id', $s2, PDO::PARAM_INT);
  163. $stmt->execute();
  164.  
  165. $row = $stmt->fetch(PDO::FETCH_ASSOC);
  166.  
  167. return $row['distance'];
  168. }
  169.  
  170. public function getStationSpiderLines($stationId, $getDistanceOnly = false) {
  171. if(count($this->simplePathStations) === 0) {
  172. $this->simplePathStations[] = $stationId;
  173. }
  174.  
  175. $sql = 'SELECT
  176. `id`,
  177. ST_AsGeoJSON(`point`, :floatPrecision) AS `secondpoint`,
  178. ST_Length(
  179. ST_LineStringFromWKB(
  180. LineString(
  181. `point`,
  182. (SELECT `point` FROM `stations` WHERE `id` = :stationId)
  183. )
  184. )
  185. )
  186. AS `distance`,
  187. (SELECT ST_AsGeoJSON(`point`, :floatPrecision) FROM `stations` WHERE `id` = :stationId) AS `firstpoint`
  188. FROM `stations`
  189. WHERE `id` <> :stationId
  190. ORDER BY `distance`';
  191.  
  192. $stmt = $this->pdo->prepare($sql);
  193. $stmt->bindParam(':floatPrecision', $this->floatPrecision, PDO::PARAM_INT);
  194. $stmt->bindValue(':stationId', $stationId, PDO::PARAM_INT);
  195. $stmt->execute();
  196.  
  197. $this->distances = [];
  198. $this->stationSpiderLines = [];
  199.  
  200. if($getDistanceOnly) {
  201. while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
  202. $this->distances[$row['id']] = $row['distance'];
  203. }
  204.  
  205. return $this->distances;
  206. }
  207.  
  208. while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
  209. $this->stationSpiderLines['secondpoint'][] = json_decode($row['secondpoint'], true);
  210. $this->stationSpiderLines['firstpoint'] = json_decode($row['firstpoint'], true);
  211. }
  212.  
  213. return $this->stationSpiderLines;
  214. }
  215.  
  216. public function checkStationLineIntersects($stationId) {
  217. $this->stationLineIntersects = [];
  218.  
  219. $sql = 'SELECT `id` FROM `connections` WHERE ST_INTERSECTS((SELECT `point` FROM `stations` WHERE `id` = :stationId), `line`)';
  220.  
  221. $stmt = $this->pdo->prepare($sql);
  222. $stmt->bindValue(':stationId', $stationId, PDO::PARAM_INT);
  223. $stmt->execute();
  224.  
  225. while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
  226. $this->stationLineIntersects[] = $row['id'];
  227. }
  228.  
  229. return $this->stationLineIntersects;
  230. }
  231.  
  232. public function getSimplePath($distances, $targetStationId) {
  233. //var_dump($distances);
  234. $this->lastCheckedLineIds = $this->checkStationLineIntersects(end($this->simplePathStations));
  235. //var_dump($this->lastCheckedLineIds);
  236.  
  237. foreach($distances as $key => $val) {
  238. // echo 'key:';
  239. // var_dump($this->checkStationLineIntersects($key));
  240. // echo 'last:';
  241. // var_dump($this->lastCheckedLineIds[0]);
  242. //if($this->checkStationLineIntersects($key)[0] != $this->lastCheckedLineIds[$this->lineId]) {
  243. /*var_dump($key.' > '.$targetStationId);
  244. var_dump($this->getDistanceLineOfTwoStations($key, $targetStationId));
  245. var_dump($distances[$targetStationId]);
  246. var_dump($distances);*/
  247.  
  248. if(!in_array($this->lastCheckedLineIds[$this->lineId], $this->checkStationLineIntersects($key)) && $this->checkSLI === true ) {
  249. echo 'stacja '.$key.' poza linią numer '.$this->lastCheckedLineIds[$this->lineId].'</br>';
  250. $this->lineId = 0;
  251. continue;
  252. } elseif(($val < $distances[$targetStationId] && $this->getDistanceLineOfTwoStations($key, $targetStationId) < $distances[$targetStationId]) || count($this->checkStationLineIntersects($key)) > 1) {
  253. if(count($this->checkStationLineIntersects($key)) > 1) {
  254. echo 'zmiana linii po dotarciu do = '.$key.'</br>';
  255. //$this->lineId++;
  256. $this->checkSLI = false;
  257. } else {
  258. $this->checkSLI = true;
  259. }
  260.  
  261. echo 'wybrana = '.$key. ' val = '.$val.'</br>';
  262. $this->simplePathStations[] = $key;
  263. $this->getSimplePath($this->getStationSpiderLines($key, true), $targetStationId);
  264. return;
  265. } elseif($key == $targetStationId) {
  266. echo 'dotarłem do ostatniej ('.$key.')</br>';
  267. $this->simplePathStations[] = $key;
  268. var_dump($this->simplePathStations);
  269. return;
  270. } else {
  271. echo 'pominięta = '.$key.'</br>';
  272. }
  273. }
  274. }
  275.  
  276. public function dijkstras($from) {
  277. //$unvisitedStations = ['a','b','c','d','e','f','g','h','i'];
  278. $unvisitedStations = $this->getStationsIds();
  279.  
  280. if($unvisitedStations[0] != $from) {
  281. // unset "from station" from unvisited array
  282. unset($unvisitedStations[array_search($from, $unvisitedStations)]);
  283.  
  284. // prepend "from station" to the beginning of an unvisited array
  285. array_unshift($unvisitedStations, $from);
  286. }
  287.  
  288. //var_dump($unvisitedStations);
  289.  
  290. $visitedStations = [];
  291. $minCost = 0;
  292. $costs = [];
  293. // $distancesTempArr = [
  294. // ['a', 'b', 5],
  295. // ['b', 'c', 4],
  296. // ['c', 'd', 2],
  297. // ['d', 'e', 1],
  298. // ['d', 'f', 7],
  299. // ['f', 'g', 6],
  300. // ['f', 'i', 12],
  301. // ['e', 'i', 11],
  302. // ['g', 'i', 5],
  303. // ['g', 'h', 2]
  304. // ];
  305.  
  306. $distancesTempArr = $this->getSections();
  307. var_dump($distancesTempArr);
  308.  
  309. foreach ($unvisitedStations as $init => $station) {
  310. // find and set min cost
  311. if(count($costs) > 0) {
  312. $minCost = min($costs);
  313. $station = array_keys($costs, min($costs))[0];
  314. }
  315.  
  316. // clean costs
  317. //var_dump($costs);
  318. $costs = [];
  319.  
  320. // push current station into visited
  321. $visitedStations[] = $station;
  322. $this->dijkstrasRouteEngine[$init]['visited'] = $visitedStations;
  323.  
  324. // unset current station from unvisited
  325. unset($unvisitedStations[array_search($station, $unvisitedStations)]);
  326. $this->dijkstrasRouteEngine[$init]['unvisited'] = $unvisitedStations;
  327.  
  328. // foreach for create analyze schema
  329. $analyze = [];
  330.  
  331. foreach ($unvisitedStations as $keyStation => $indexStation) {
  332. // foreach simple lines (A-B)
  333. foreach ($distancesTempArr as $distancesIndex => $pointsDistances) {
  334. //var_dump($distancesTempArr[$distancesIndex]['length']);
  335. // if A-B OR B-A in current then...
  336. //if(($pointsDistances['sections'][0] == $station && $pointsDistances['sections'][1] == $indexStation) || ($pointsDistances['sections'][1] == $station && $pointsDistances['sections'][0] == $indexStation)) {
  337.  
  338. // if(array_key_exists($station, $pointsDistances['sections']) && array_key_exists($indexStation, $pointsDistances['sections'])) {
  339. // echo 'good';
  340. // var_dump($station);
  341. // var_dump($indexStation);
  342. // }
  343.  
  344. if(array_key_exists($station, $pointsDistances['sections']) && array_key_exists($indexStation, $pointsDistances['sections'])) {
  345. var_dump($station. ' -> '. $indexStation);
  346. //var_dump('znalazłem '.$station.'-'.$indexStation.' w '.$distancesIndex.', długość to '.$distancesTempArr[$distancesIndex][2].' dodana z poprzedniej to '.$minCost);
  347. // found nearest point
  348. //$cost = $distancesTempArr[$distancesIndex]['length'] + $minCost;
  349.  
  350. foreach ($pointsDistances['sections'] as $sectionStationId => $units) {
  351.  
  352. foreach ($units as $unitId => $routes) {
  353.  
  354. // if ($pointsDistances['sections'][$indexStation][$unitId] == null || $pointsDistances['sections'][$station][$unitId] == null)
  355. // continue;
  356.  
  357. foreach ($routes as $routeId => $time) {
  358. var_dump('sectionStationId '.$sectionStationId.' | unitId '.$unitId.' | routeId '.$routeId.' | time '.$time);
  359. //var_dump($pointsDistances['sections'][$indexStation][$unitId]);
  360. // if ($pointsDistances['sections'][$indexStation][$unitId] == null || $pointsDistances['sections'][$station][$unitId] == null)
  361. // continue;
  362. //var_dump($pointsDistances['sections'][$station][$unitId][$routeId]);
  363. //var_dump($pointsDistances['sections'][$indexStation][$unitId][$routeId]);
  364.  
  365. $secondsDiff = $pointsDistances['sections'][$indexStation][$unitId][$routeId] - $pointsDistances['sections'][$station][$unitId][$routeId];
  366.  
  367. if($secondsDiff > 0) {
  368. var_dump($secondsDiff);
  369. $cost = $secondsDiff + $minCost;
  370. $analyzeUnitId = $unitId;
  371. }
  372. }
  373. }
  374. //if($pointsDistances['sections'][$station])
  375. //$cost = $distancesTempArr[$distancesIndex]['length'] + $minCost;
  376. }
  377.  
  378. //echo 'jestem w ifie stacji';
  379. if($init == 0) {
  380. $analyze[$indexStation] = ['cost' => $cost, 'station' => $station, 'init' => $init, 'section_id' => $distancesIndex, 'unit_id' => $analyzeUnitId];
  381. $costs[$indexStation] = $cost;
  382. break;
  383. }
  384. } else {
  385. // infinity cost
  386. // if($init > 0 && $this->dijkstrasRouteEngine[$init - 1]['analyze'][$indexStation]['cost'] != 'infinity') {
  387. // $cost = $this->dijkstrasRouteEngine[$init - 1]['analyze'][$indexStation]['cost'];
  388. // }
  389. // else
  390. $cost = 'infinity';
  391.  
  392. $analyze[$indexStation] = ['cost' => $cost, 'station' => $station, 'init' => $init/*, 'section_id' => $distancesIndex*/];
  393. }
  394. //var_dump($station.' | '.$indexStation.' | '.$this->dijkstrasRouteEngine[$init - 1]['analyze'][$indexStation]['cost'].' | '.$cost);
  395. // skip first cycle
  396. if($init > 0) {
  397. //$cost = $distancesTempArr[$distancesIndex][2] + $minCost;
  398. $previousCost = $this->dijkstrasRouteEngine[$init - 1]['analyze'][$indexStation]['cost'];
  399. //var_dump($init.' / '.$indexStation.' = '.$previousCost .' <> '.$station.' = '. $cost);
  400. //var_dump($previousCost.' = '.$cost);
  401. if($previousCost == 'infinity' && $cost == 'infinity') {
  402.  
  403. //$cost = $distancesTempArr[$distancesIndex][2] + $minCost;
  404. $analyze[$indexStation] = ['cost' => $cost, 'station' => $station, 'init' => $init/*, 'section_id' => $distancesIndex*/];
  405. //$costs[] = $cost;
  406. //break;
  407.  
  408. } elseif($previousCost == 'infinity' && $cost != 'infinity') {
  409.  
  410. $analyze[$indexStation] = ['cost' => $cost, 'station' => $station, 'init' => $init, 'section_id' => $distancesIndex, 'unit_id' => $analyzeUnitId];
  411. $costs[$indexStation] = $cost;
  412. break;
  413.  
  414. } elseif($previousCost != 'infinity' && $cost == 'infinity') {
  415.  
  416. $analyze[$indexStation] = $this->dijkstrasRouteEngine[$init - 1]['analyze'][$indexStation];
  417. $costs[$indexStation] = $previousCost;
  418. //break;
  419.  
  420. } elseif($previousCost != 'infinity' && $cost != 'infinity') {
  421. echo 'porównałem dwie liczby';
  422. if($cost <= $previousCost) {
  423. $analyze[$indexStation] = ['cost' => $cost, 'station' => $station, 'init' => $init, 'section_id' => $distancesIndex, 'unit_id' => $analyzeUnitId];
  424. $costs[$indexStation] = $cost;
  425. break;
  426. } else {
  427. $analyze[$indexStation] = $this->dijkstrasRouteEngine[$init - 1]['analyze'][$indexStation];
  428. $costs[$indexStation] = $previousCost;
  429. //break;
  430. }
  431. }
  432. }
  433. }
  434. }
  435.  
  436. // -- OLD COSTS PLACE --
  437.  
  438. // set current
  439. $this->dijkstrasRouteEngine[$init]['current'] = $station;
  440.  
  441. // set unvisited to analyze
  442. $this->dijkstrasRouteEngine[$init]['analyze'] = $analyze;
  443. }
  444.  
  445. //var_dump($this->dijkstrasRouteEngine);
  446. //return $this->dijkstrasRouteEngine;
  447. }
  448.  
  449. public function getShortestRoutePath($from, $to, $toReference = null) {
  450. $toArray = [];
  451.  
  452. // in first method init reference "to station" is null - use "to", clear SRP
  453. if($toReference == null) {
  454. $this->dijkstras($from);
  455. $this->SRP = [];
  456. $this->totalCost = null;
  457. $toReference = $to;
  458. }
  459.  
  460. // walk on dijkstras route engine array to determine backwards SRP step by step
  461. foreach ($this->dijkstrasRouteEngine as $key => $init) {
  462. foreach ($init['analyze'] as $station => $analyzeField) {
  463. if($station == $toReference)
  464. $toArray[] = $analyzeField;
  465.  
  466. if($station == $to)
  467. $this->totalCost = $analyzeField['cost'];
  468. }
  469. }
  470.  
  471. if(count($toArray) > 0) {
  472. $previousStation = end($toArray)['station'];
  473.  
  474. $this->SRP[] = [
  475. 'path_point' => end($toArray)['station'],
  476. 'section_id' => end($toArray)['section_id'],
  477. 'unit_id' => end($toArray)['unit_id']
  478. ];
  479.  
  480. // references on station to set route backwards
  481. $this->getShortestRoutePath($from, $to, $previousStation);
  482. return $this->SRP;
  483. }
  484.  
  485. // prepend "to station" to the beginning of an SRP array
  486. array_unshift($this->SRP, ['path_point' => $to, 'section_id' => null]);
  487.  
  488. // reverse SRP
  489. $this->SRP = array_reverse($this->SRP);
  490.  
  491. // separate station from the sections
  492. foreach ($this->SRP as $key) {
  493. $SRPStations[] = $key['path_point'];
  494. $SRPSections[] = $key['section_id'];
  495. }
  496.  
  497. // set stations and sections
  498. $this->SRPStations = $SRPStations;
  499. $this->SRPSections = $SRPSections;
  500.  
  501. // final SRP return
  502. return $this->SRP;
  503. }
  504.  
  505. public function getStationsIds() {
  506. //$sql = 'SELECT `id` FROM `stations` ORDER BY `id`';
  507. $sql = 'SELECT `s`.`id`
  508. FROM `stations` `s`, `relations` `r`
  509. WHERE `s`.`id` = `r`.`station_id`
  510. GROUP BY `s`.`id`
  511. ORDER BY `s`.`id`';
  512.  
  513. $stmt = $this->pdo->query($sql);
  514. $stmt->execute();
  515.  
  516. while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
  517. $stationsIds[] = $row['id'];
  518. }
  519.  
  520. return $stationsIds;
  521. }
  522.  
  523. public function getSections() {
  524. $sql = 'SELECT `s`.`id` AS `station_id`, `s`.`name`, `c`.`id` AS `line_id`, ST_Length(`c`.`line`) AS `length`, TIME_TO_SEC(`r`.`time`) AS `relation_time`, `r`.`route_id`, `r`.`unit_id`, `r`.`id` AS `relation_id`
  525. FROM `connections` `c`
  526. JOIN `stations` `s` ON (ST_Intersects(`s`.`point`, `c`.`line`) = 1)
  527. JOIN `relations` `r` ON `s`.`id` = `r`.`station_id`
  528. WHERE `r`.`time` >= \'15:50:00\'
  529. ORDER BY `r`.`time`';
  530.  
  531. $stmt = $this->pdo->query($sql);
  532. $stmt->execute();
  533.  
  534. while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
  535. $sections[$row['line_id']]['section_id'] = $row['line_id'];
  536. $sections[$row['line_id']]['length'] = $row['length'];
  537. $sections[$row['line_id']]['sections'][$row['station_id']][$row['unit_id']][$row['route_id']] = $row['relation_time'];
  538. //$sections[$row['line_id']]['sections'][$row['station_id']][] = ];
  539. }
  540.  
  541. foreach ($sections as $key => $val) {
  542. if(count($val['sections']) < 2)
  543. unset($sections[$key]);
  544.  
  545. //var_dump(count($val['sections']));
  546. }
  547.  
  548. return $sections;
  549. }
  550. }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement