This uses a spatial index to snap start/end points to the nearest vertex.

The function pgr_createTopology builds node connections based on geometry intersections.

ALTER TABLE roads ADD COLUMN x1 FLOAT, y1 FLOAT, x2 FLOAT, y2 FLOAT; UPDATE roads SET x1 = ST_X(ST_StartPoint(geom)), y1 = ST_Y(ST_StartPoint(geom)), x2 = ST_X(ST_EndPoint(geom)), y2 = ST_Y(ST_EndPoint(geom));