Creating routable network table in PostGIS

Question

I am trying to create the routable network table, as per http://underdark.wordpress.com/2011/02/07/a-beginners-guide-to-pgrouting/. Everytime I do it in PostGIS extension, QGIS crashes. The area is no so huge, consist of one British county. I'm also trying to do it with with pgAdminIII of PostgreSQL, it takes ages to do so. I do have the limited knowledge of queries, indexing etc. so I am not sure how to speed the process up. Is there something developed since 2011 (the last posts on that subject I found with this date) would enable me to do it efficiently? Thanks

UPDATE: (per @Pawel, Feb 19, 2013)

I have reinstalled PosrgreSQL and PostGIS. When I am trying to execute assign_vertex_id() I have: function assign_vertex_id(unknown, numeric, unknown, unknown) does not exist. What would be the problem?

Which step "takes ages"? I don't think QGIS crashes, it's just waiting for PostGIS to finish - which can take longer. — underdark, Feb 19 '13 at 19:13
Running the query in PgAdminIII takes a long time and I still do not know if it is achievable. How long does it usually takes? QGIS definitely crashes, both PostGIS and QGIS freeze and do not respond. Number of start and end point geometries - 52867, unique network nodes - 82257. — Pawel, Feb 19 '13 at 19:29
The one that creates routable network table, 'CREATE TABLE network AS ...' , the other ones ran with no problems. — Pawel, Feb 19 '13 at 19:36
I tried on the very beginning. And it didn't work. Now I am running a query already in pgAdminIII so cannot really check if its working properly. What would it do, would creating indices help? As far as I can see I would have to create node table and network table anyway, am I right? — Pawel, Feb 19 '13 at 20:05
What dataset are you using for 'one British county' - If using OS MasterMap ITN your going to need a more powerful solution. — Mapperz, Feb 20 '13 at 02:17
I apologize for my lack of basic knowledge. I ran the query to create network in pgAdminIII, was it ok to do so?. Results are somehow strange. I have a network of points instead of lines. And when I try to run the query as per example its shows me that column "shape_leng" does not exist. It is actually true, it has not been defined earlier on. I am running imposm shapefile downloaded from http://metro.teczno.com/. Where am I going wrong? — Pawel, Feb 20 '13 at 10:59

score 1 · Answer 1 · edited Apr 13 '17 at 12:33

You need to install pgRouting to get assign_vertex_id working. To install it, you can follow the steps in the link in your post itself.

I can add to it, here.

Suppose your directory where pgRouting gets installed is /usr/share/postlbs

To setup pgRouting extension to a database (say dbname):

    psql -U username -h localhost dbname -c 'CREATE EXTENSION postgis;'
    psql -U username -d dbname -h localhost < /usr/share/postlbs/routing_topology.sql
    psql -U username -d dbname -h localhost < /usr/share/postlbs/routing_core.sql
    psql -U username -d dbname -h localhost < /usr/share/postlbs/routing_core_wrappers.sql

Again, its just to help you try assign_vertex_id, I cant guarantee whether it could speed up your task. To know how assign_vertex_id works, you can follow How does assign_vertex_id() in pgRouting work?.

Creating routable network table in PostGIS

1 Answers1