How to create source and target ID columns for a linestring table in PostGIS?

Question

I have table paths_3D which has column "the_geom". I need to create a new column source_id which will have starting points from the geom. This statement doesn't work:

ALTER TABLE paths_3d ADD COLUMN source_id AS SELECT *, startpoint(the_geom)FROM paths_3d;

Answer 1

Start and end node geometries

CREATE OR REPLACE VIEW paths_3d_ext AS
  SELECT *, startpoint(the_geom), endpoint(the_geom)
  FROM paths_3d;

Creating node IDs

CREATE TABLE node AS
  SELECT row_number() OVER (ORDER BY foo.p)::integer AS id,
         foo.p AS the_geom
  FROM (
    SELECT DISTINCT paths_3d_ext.startpoint AS p FROM paths_3d_ext
    UNION
    SELECT DISTINCT paths_3d_ext.endpoint AS p FROM paths_3d_ext
  ) foo
  GROUP BY foo.p;

Finally, a routable network table

CREATE TABLE network AS
  SELECT a.*, b.id as start_id, c.id as end_id
  FROM paths_3d_ext AS a
    JOIN node AS b ON a.startpoint = b.the_geom
    JOIN node AS c ON a.endpoint = c.the_geom

... Like in my pgRouting guide.