Intro: It's easy with OSM XML to run my software algorithm converting the XML to my custom software objects representing routing graph: You have ways with easy links to the (ordered) nodes constructing the segment with lat/lon.

Problem: Usually one starts with data as Shapefile, so the easy solution is to use Shape->OSM converter. I want to work directly with the Shapefiles.

Question: It's easy to import Shapefile to PostgreSql and using PostGis to query bounding boxes of the coverage based on geospatial logic. The next step is to use pgRouting in order to parse the raw Shapefile data (roads) to software objects representing the routing graph, for example in Java something like:

class Node() {
    double lat;
    double lon;
    int nodeId;

class Segment() {
    LinkedList byOrderNodes;  //of type class Node above
    int segmentId;
    boolean oneWay;

class graph() {
    ArrayList segments;  //of type Segment above

How to achieve this with pgRouting - What queries and functions to use?

Note - I don't mind between Python/Java/etc.

  • 1
    BTW: If you do not mind Java and no pgRouting, graphhopper has a shapefile import procedure since a few weeks: github.com/graphhopper/graphhopper/pull/874 (note: I'm one of the authors)
    – Karussell
    Jan 13, 2017 at 11:03
  • @Karussell Thanks for the suggestion. But I'm struggling to understand the whole graphhopper concept. Is it local DB sever+wrapper lib for operations? Or some cloud solution from 3rd company?
    – michael
    Jan 13, 2017 at 13:03
  • 1
    The link is to the open source routing engine, which stores its network in a custom data format and offers fast routing responses and customizable. So if you use it you are independent from anyone and can host it on your local machine
    – Karussell
    Jan 13, 2017 at 14:52
  • @Karussell So basically it's one stop shop replacing {PostgreSql+pgRouting+PostGis+some SW access lib like geotools for later manipulations}? (of course I always can further process even graphhopper with geotools)
    – michael
    Jan 13, 2017 at 15:51
  • 1
    It is not a generic database, just a very specific tool which does routing and related tasks
    – Karussell
    Jan 13, 2017 at 17:26

1 Answer 1


For pgRouting, use pgr_CreateTopology function. http://docs.pgrouting.org/2.3/en/src/topology/doc/pgr_createTopology.html#pgr-create-topology

Basic steps:

1) Load your shapefile into PostgreSQL/PostGIS with shp2pgsql or shp2pgsql-gui or ogr2ogr or any other tool like QGIS

2) Add two columns to the table source / target

ALTER TABLE your_table ADD COLUMN "source" integer;
ALTER TABLE your_table ADD COLUMN "target" integer;


SELECT pgr_createTopology('your_table', 0.00001, 'geom', 'gid');

gid may be different depending on which tool you used to load the shapefile, but should be integer that is unique for the table.

0.00001 is the tolerance at which two nodes are considered the same. This will vary depending on projection of your data. For example for a meter projection,you may want to make this higher like 0.5 (1/2 meter)

The function will populate the source and target fields with nodes ids and also create a table your_table_vertices_pgr which is basically like your Node table.

From there use the pgRouting routing functions.

This and many other topics are covered in our book currently in draft form - http://locatepress.com/pgrouting


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.