PHP connection with pgRouting for shortest path



PHP connection with pgRouting for shortest path

Here in this post we do PHP connection with pgRouting for shortest path. Here we have explained how we can add source and destination point in table and how can we query for shortest route. In this demo we have made a connection between pgrouting and PHP using code igniter using code igniter framework.
Here we have uploaded shape file in postgis. For doing this you need to  install postgres and create a database. Everything is already explained in earlier tutorial.
You can also check Postgres with QGIS.
Now the same procedure is implemented using PHP in code igniter framework. As we have an idea about code igniter, which has three main component as view, model and controller.

VIEW-

In view section  we will be adding PHP code for presenting the shapefile and the output shortest route.

Model-

In model section will be writing query for getting two end points and shortest path.

Controller-

In controller section we will make function for registration, login and distance.

First of all you need to render your shape file on map. The map can be Google map, Open street map or any other map. For rendering the shape file using leaflet you need to add plugin in your script, which can be downloaded from https://github.com/calvinmetcalf/leaflet.shapefile link. Passing shapefile in L.Shapefile() method and calling addTo(map) method we can add the shapefile on map. After that we need to create input boxes for user to enter end points latitude and longitude. Using ajax we have send these latitude and longitude of both start and end point to model and got the result back in view.

$.ajax({
type: 'POST',
url: "<?php echo base_url('index.php/User_Authentication/distance');?>",
data:{dlat1:lat1,dlong1:long1,dlat2:lat2,dlong2:long2},
dataType: 'JSON',
success: function (response) {
var data= response;
var latitude= parseFloat(response['latitude']);
var longitude= parseFloat(response['longitude']);
var geodata= GeoJSON.parse(data, {Point: ['longitude','latitude']});
geojsonLayer = L.geoJson(geodata).addTo(map);
},
error: function (errorThrown){
check_status= 0;
console.log(errorThrown);
}
});

Write Query in Model

In model section we have written an insert function, which will help us to insert the obtained latitude longitude points from user in query. The query takes latitude longitude as input. As given

insertLatLong($dlat1,$dlat2,$dlong1,$dlong2){}

Then take out the id for given source and destination point and saved them as result in variable start and end.
$start = $this->db->query("SELECT source FROM network1 ORDER BY geom <-> ST_SetSRID(ST_Point ($dlong1,$dlat1),4326) LIMIT 1")->result();
$end= $this->db->query("SELECT source FROM network1 ORDER BY geom <->  ST_SetSRID(ST_Point ($dlong2,$dlat2),4326) LIMIT 1")->result();

After completing this wrote query using pgr_dijsktra method. This is an algorithm to find out the shortest path. This takes shape file table, start point , end point, boolean result for directed/undirected route and boolean result for having/not having route cost. To get the geometry column from network table applied join. Take this result in calculation variable.
After this check for the number of row i.e. the result of query is not empty and take result in distance variable and return is to controller.

public function insertLatLong($dlat1,$dlat2,$dlong1,$dlong2) {
$calculation= $this->db->query("select seq, id1, id2, cost, geom, ST_X(ST_AsText(ST_StartPoint((ST_AsText(ST_LineMerge(ST_GeomFromText(ST_AsText(geom)))))))) as latitude, ST_Y(ST_AsText(ST_StartPoint((ST_AsText(ST_LineMerge(ST_GeomFromText(ST_AsText(geom)))))))) as longitude
from pgr_dijkstra('Select gid as id, source, target, st_length(geom::geography)/1000 as cost from network1',
$start ,$end ,false, false) as di JOIN network1 pt ON (di.id2 = pt.gid)");

if($calculation->num_rows()>0) {
$distance= $try->result();

return $distance;

} else {

return false;
}
}

Call Function in Controller

In the controller, we can call the insert function and pass the required input to called function and also can load the view. Here for this article we have made login session and registration of user. This distance function takes the data in variable in sends to insertLatLong function in Login_Database model.

public function distance() {
$dlat1 = $this->input->post('dlat1');
$dlong1 = $this->input->post('dlong1');
$dlat2 = $this->input->post('dlat2');
$dlong2 = $this->input->post('dlong2');
$res= $this->Login_Database->insertLatLong($dlat1,$dlat2,$dlong1,$dlong2);
echo json_encode($res);
}

After writing everything properly you can run the project in browser. See the shortest path as given-

Export shapefile from postgreSQL - pgsql2shp
Export shapefile from postgreSQL – pgsql2shp

Hope you enjoyed the tutorial. If you face any problem in working with pgRouting please let us know.

Author: Akshay Upadhyay

Owner and Director of a Private Limited company which serves individual to large scale industries in the field of Maps and GIS. He is a Gold Medalist in M.Tech(Spatial Information Technology) and owns some famous Technology blogs and website... Know more