CREATE TABLE nodes (
  id SERIAL PRIMARY KEY,
  geom GEOMETRY(POINT, 3857)
);
CREATE TABLE edges (
  id SERIAL PRIMARY KEY,
  source INTEGER NOT NULL,
  target INTEGER NOT NULL,
  geom geometry(LineString, 3857),
  cost FLOAT8
);
INSERT INTO nodes (geom) VALUES
(ST_SetSRID(ST_MakePoint(-74.0059, 40.7128), 3857)),
(ST_SetSRID(ST_MakePoint(-73.9866, 40.7484), 3857)),
(ST_SetSRID(ST_MakePoint(-73.9904, 40.7397), 3857)),
(ST_SetSRID(ST_MakePoint(-73.9991, 40.7250), 3857)),
(ST_SetSRID(ST_MakePoint(-73.9926, 40.7488), 3857));
CREATE INDEX idx_nodes_geom ON nodes USING GIST (geom);
CREATE TABLE distances AS
SELECT a.id AS source, b.id AS target, ST_Distance(a.geom, b.geom) AS cost, st_setsrid(st_makeline(a.geom, b.geom), 3857) as geom
FROM nodes a, nodes b;
INSERT INTO edges (source, target, cost, geom)
SELECT source, target, cost, geom FROM distances;
SELECT pgr_createTopology('edges', 0.0001, 'geom', 'id');

SELECT DISTINCT geom FROM edges;

CREATE OR REPLACE VIEW road_ext AS
    SELECT *, st_startpoint(geom), st_endpoint(geom)
    FROM edges;

DROP TABLE node;
CREATE TABLE node AS
SELECT row_number() OVER (ORDER BY foo.p)::integer AS id,
foo.p AS the_geom
FROM (
SELECT DISTINCT road_ext.st_startpoint AS p FROM road_ext
UNION
SELECT DISTINCT road_ext.st_endpoint AS p FROM road_ext
) foo
GROUP BY foo.p;


DROP TABLE network;
CREATE TABLE network AS
SELECT a.*, b.id as start_id, c.id as end_id
FROM road_ext AS a
JOIN node AS b ON a.st_startpoint = b.the_geom
JOIN node AS c ON a.st_endpoint = c.the_geom;



SELECT id AS id,
start_id AS source,
end_id AS target,
ST_Distance(st_startpoint, st_endpoint) AS cost,
ST_X(st_startpoint) as x1,
ST_Y(st_startpoint) as y1,
ST_X(st_endpoint) as x2,
ST_Y(st_endpoint) as y2
FROM network;

SELECT * FROM pgr_dijkstra('
SELECT id AS id,
start_id AS source,
end_id AS target,
ST_Distance(st_startpoint, st_endpoint) AS cost,
ST_X(st_startpoint) as x1,
ST_Y(st_startpoint) as y1,
ST_X(st_endpoint) as x2,
ST_Y(st_endpoint) as y2
FROM network',
1,
4, false);