Contents
What can you do with PostGIS and pgRouting?
In the first tutorial we ended up writing simple queries with geospatial data using PostgreSQL and PostGIS. This powerful extension allowed us to add geospatial context to our data and analyse it from the totally different perspective, like calculating the distance between elements, joining tables based on objects’ position and much more.
How to use pgRouting to calculate driving distance?
In this tutorial we will learn to work with pgRouting tool in estimating the driving distance from all nearby nodes which can be very useful in supply chain, logistics and transportation based applications.
How to calculate the length of a road in PostGIS?
Since most of the algorithms need cost parameter assigned to each edge (road), we will use dynamically calculated length of each road using PostGIS st_length function (in the real application we should consider a lot of additional parameters such as type of roads, traffic, velocity limits etc.).
Which is the best dataset for pgRouting?
To explain the basics of pgRouting I’ll be using the public roads network of Masovian Voivodeship in Poland, downloaded from Geofabrik and converted using os2po tool. You can download it here [12MB]. The only table in the dataset is roads which represents road network as LineStrings.
Is the Roman transportation network in PostGIS2?
While the work I was doing with geographic networks originally used general purpose network analysis tools like Gephi, I’ve moved the entire Roman transportation network into a PostGIS2 database to allow for concurrent editing and the use of spatial queries.
How to use pgRouting in a routing function?
As we have seen before, routing functions take as an argument network of connections in the form of a graph (start node id, end node id), so the last thing to do is to convert our actual road network to a graph. Fortunately, pgRouting helps us a lot with this task. Start with adding two additional columns to our roads table.
How to add PostGIS extension to PostgreSQL pgadmin?
Before adding PostGIS extension to Postgres. You first need to install PostGIS on Ubuntu 14.04 You don’t install it to pgAdmin, it is an extension to Postgres itself. Once it is installed, you add it to a particular database, and its functions, nearly 1000, a few views and the spatial_ref_sys table, will appear in pgAdmin in that db.
How to add shape file to PostGIS database?
If you are using pgAdmin3 then you can open the database by providing user name and password. In Database you can add shapefile by clicking option PostGIS shape file and DBF loader. Here first off define the user-name with proper password and database name. Then add shape file and import it.
Which is the basis for the PostGIS geographic type?
The basis for the PostGIS geographic type is a sphere. The shortest path between two points on the sphere is a great circle arc. That means that calculations on geographies (areas, distances, lengths, intersections, etc) must be calculated on the sphere, using more complicated mathematics.
What’s the easiest way to get data into PostGIS?
If you can convert your data to a text representation, then using formatted SQL might be the easiest way to get your data into PostGIS. As with Oracle and other SQL databases, data can be bulk loaded by piping a large text file full of SQL “INSERT” statements into the SQL terminal monitor.
How to use the shortest path function in PostGIS?
To use the shortest path function (using the Dijkstra algorithm) the pgr_dijkstra function requires an SQL query, a start point, an end point and then, two booleans that I have not looked into yet. The starting points refer to nodes in the network and their IDs are the source and target variables in the table we created.
How to use Gist Index in pgRouting command?
The answer is to use special GIST index which instead of treating geom as binary data, uses its bounding box. The detailed explanation on how it works can be found here. To use this feature simply add USING GIST clause to your command. Now it’s time for some benchmarks.