pgrouting createTopology error: column source not found!

Question

I tried to create a topology, this is the first steps in pgrouting.

I executed select pgr_createTopology('public.line_shapefile', 0.00001, 'geom', 'id');

It returned the following error. seems like there are no column "source" which I don't know what value I should put in.

NOTICE:  PROCESSING:
NOTICE:  pgr_createTopology('public.line_shapefile', 1e-05, 'geom', 'id', 'source', 'target', rows_where := 'true', clean := f)
NOTICE:  Performing checks, please wait .....
NOTICE:  ----> PGR ERROR in pgr_createTopology: Column source not found
HINT:    ----> Check your column name
NOTICE:  Unexpected error raise_exception

I tried reading the documentation on pg_createTopology and I still don't understand what source or target is. This post gives the clue but I do not now how to create the SQL statement as I am very much new to PostGIS/pgrouting/SQL.

my column names for the table (where the geom stores the geometry information):

id
geom
name
path
layer

Answer 1

The use of pg_routing is not always clear.

You need to modify the table that contains your paths to add a source and a target columns before calling the pgr_createTopology function (see this workshop). The pgr_createTopology function will fill the source and target columns.

Here is the code to use :

-- Add the needed columns
ALTER TABLE public.line_shapefile ADD COLUMN "source" integer;
ALTER TABLE public.line_shapefile ADD COLUMN "target" integer;

-- Run topology function
select pgr_createTopology('public.line_shapefile', 0.00001, 'geom', 'id');