create extension postgis;
drop table vertext_table;
create table vertext_table (
	id serial not null,
	name varchar(80)  
);
select addgeometrycolumn('','vertext_table','geom',-1,'POINT',2);

insert into vertext_table(name,geom) values ('node1',st_setsrid(st_makepoint(1,1),-1));
insert into vertext_table(name,geom) values ('node2',st_setsrid(st_makepoint(8,8),-1));
insert into vertext_table(name,geom) values ('node3',st_setsrid(st_makepoint(6,6),-1));
insert into vertext_table(name,geom) values ('node4',st_setsrid(st_makepoint(2,5),-1));
insert into vertext_table(name,geom) values ('node5',st_setsrid(st_makepoint(7,6),-1));
insert into vertext_table(name,geom) values ('node6',st_setsrid(st_makepoint(3,4),-1));
insert into vertext_table(name,geom) values ('node7',st_setsrid(st_makepoint(6,0),-1));
insert into vertext_table(name,geom) values ('node8',st_setsrid(st_makepoint(5,7),-1));
insert into vertext_table(name,geom) values ('node9',st_setsrid(st_makepoint(5,5),-1));
insert into vertext_table(name,geom) values ('nodeA',st_setsrid(st_makepoint(0,7),-1));
insert into vertext_table(name,geom) values ('nodeB',st_setsrid(st_makepoint(2,7),-1));
insert into vertext_table(name,geom) values ('nodeC',st_setsrid(st_makepoint(3,7),-1));
insert into vertext_table(name,geom) values ('nodeD',st_setsrid(st_makepoint(4,7),-1));
insert into vertext_table(name,geom) values ('nodeE',st_setsrid(st_makepoint(5,7),-1));
insert into vertext_table(name,geom) values ('nodeF',st_setsrid(st_makepoint(0,7),-1));
insert into vertext_table(name,geom) values ('nodeG',st_setsrid(st_makepoint(3,8),-1));
insert into vertext_table(name,geom) values ('nodeH',st_setsrid(st_makepoint(3,6),-1));

drop table route_table;
create table route_table (
	id serial not null,
	name varchar(80)  
);
select addgeometrycolumn('','route_table','geom',-1,'LINESTRING',2);
insert into route_table(geom,name) select  st_setsrid(st_makeline(v1.geom  ,v2.geom),-1),'Line '|| v1.name || ' to ' || v2.name  from vertext_table v1, vertext_table v2 where v1.name='node1' and v2.name='node7';
insert into route_table(geom,name) select  st_setsrid(st_makeline(v1.geom  ,v2.geom),-1),'Line '|| v1.name || ' to ' || v2.name  from vertext_table v1, vertext_table v2 where v1.name='node2' and v2.name='node7';
insert into route_table(geom,name) select  st_setsrid(st_makeline(v1.geom  ,v2.geom),-1),'Line '|| v1.name || ' to ' || v2.name  from vertext_table v1, vertext_table v2 where v1.name='nodeB' and v2.name='nodeG';
insert into route_table(geom,name) select  st_setsrid(st_makeline(v1.geom  ,v2.geom),-1),'Line '|| v1.name || ' to ' || v2.name  from vertext_table v1, vertext_table v2 where v1.name='nodeC' and v2.name='nodeG';
insert into route_table(geom,name) select  st_setsrid(st_makeline(v1.geom  ,v2.geom),-1),'Line '|| v1.name || ' to ' || v2.name  from vertext_table v1, vertext_table v2 where v1.name='nodeD' and v2.name='nodeG';
insert into route_table(geom,name) select  st_setsrid(st_makeline(v1.geom  ,v2.geom),-1),'Line '|| v1.name || ' to ' || v2.name  from vertext_table v1, vertext_table v2 where v1.name='nodeE' and v2.name='nodeG';

insert into route_table(geom,name) select  st_setsrid(st_makeline(v1.geom  ,v2.geom),-1),'Line '|| v1.name || ' to ' || v2.name  from vertext_table v1, vertext_table v2 where v1.name='nodeB' and v2.name='nodeH';
insert into route_table(geom,name) select  st_setsrid(st_makeline(v1.geom  ,v2.geom),-1),'Line '|| v1.name || ' to ' || v2.name  from vertext_table v1, vertext_table v2 where v1.name='nodeC' and v2.name='nodeH';
insert into route_table(geom,name) select  st_setsrid(st_makeline(v1.geom  ,v2.geom),-1),'Line '|| v1.name || ' to ' || v2.name  from vertext_table v1, vertext_table v2 where v1.name='nodeD' and v2.name='nodeH';
insert into route_table(geom,name) select  st_setsrid(st_makeline(v1.geom  ,v2.geom),-1),'Line '|| v1.name || ' to ' || v2.name  from vertext_table v1, vertext_table v2 where v1.name='nodeE' and v2.name='nodeH';

insert into route_table(geom,name) select  st_setsrid(st_makeline(v1.geom  ,v2.geom),-1),'Line '|| v1.name || ' to ' || v2.name  from vertext_table v1, vertext_table v2 where v1.name='nodeF' and v2.name='node1';

insert into route_table(geom,name) select  st_setsrid(st_makeline(v1.geom  ,v2.geom),-1),'Line '|| v1.name || ' to ' || v2.name  from vertext_table v1, vertext_table v2 where v1.name='node4' and v2.name='node6';

insert into route_table(geom,name) select  st_setsrid(st_makeline(v1.geom  ,v2.geom),-1),'Line '|| v1.name || ' to ' || v2.name  from vertext_table v1, vertext_table v2 where v1.name='nodeF' and v2.name='nodeG';
insert into route_table(geom,name) select  st_setsrid(st_makeline(v1.geom  ,v2.geom),-1),'Line '|| v1.name || ' to ' || v2.name  from vertext_table v1, vertext_table v2 where v1.name='nodeF' and v2.name='node4';

insert into route_table(geom,name) select  st_setsrid(st_makeline(v1.geom  ,v2.geom),-1),'Line '|| v1.name || ' to ' || v2.name  from vertext_table v1, vertext_table v2 where v1.name='node9' and v2.name='node3';
insert into route_table(geom,name) select  st_setsrid(st_makeline(v1.geom  ,v2.geom),-1),'Line '|| v1.name || ' to ' || v2.name  from vertext_table v1, vertext_table v2 where v1.name='node3' and v2.name='node5';

insert into route_table(geom,name) select  st_setsrid(st_makeline(v1.geom  ,v2.geom),-1),'Line '|| v1.name || ' to ' || v2.name  from vertext_table v1, vertext_table v2 where v1.name='node1' and v2.name='node6';
insert into route_table(geom,name) select  st_setsrid(st_makeline(v1.geom  ,v2.geom),-1),'Line '|| v1.name || ' to ' || v2.name  from vertext_table v1, vertext_table v2 where v1.name='nodeH' and v2.name='node6';
insert into route_table(geom,name) select  st_setsrid(st_makeline(v1.geom  ,v2.geom),-1),'Line '|| v1.name || ' to ' || v2.name  from vertext_table v1, vertext_table v2 where v1.name='node6' and v2.name='node9';
insert into route_table(geom,name) select  st_setsrid(st_makeline(v1.geom  ,v2.geom),-1),'Line '|| v1.name || ' to ' || v2.name  from vertext_table v1, vertext_table v2 where v1.name='node2' and v2.name='node5';
insert into route_table(geom,name) select  st_setsrid(st_makeline(v1.geom  ,v2.geom),-1),'Line '|| v1.name || ' to ' || v2.name  from vertext_table v1, vertext_table v2 where v1.name='nodeD' and v2.name='nodeE';
insert into route_table(geom,name) select  st_setsrid(st_makeline(v1.geom  ,v2.geom),-1),'Line '|| v1.name || ' to ' || v2.name  from vertext_table v1, vertext_table v2 where v1.name='nodeG' and v2.name='node2';
insert into route_table(geom,name) select  st_setsrid(st_makeline(v1.geom  ,v2.geom),-1),'Line '|| v1.name || ' to ' || v2.name  from vertext_table v1, vertext_table v2 where v1.name='nodeE' and v2.name='node2';
insert into route_table(geom,name) select  st_setsrid(st_makeline(v1.geom  ,v2.geom),-1),'Line '|| v1.name || ' to ' || v2.name  from vertext_table v1, vertext_table v2 where v1.name='nodeE' and v2.name='node3';
insert into route_table(geom,name) select  st_setsrid(st_makeline(v1.geom  ,v2.geom),-1),'Line '|| v1.name || ' to ' || v2.name  from vertext_table v1, vertext_table v2 where v1.name='nodeF' and v2.name='nodeB';
insert into route_table(geom,name) select  st_setsrid(st_makeline(v1.geom  ,v2.geom),-1),'Line '|| v1.name || ' to ' || v2.name  from vertext_table v1, vertext_table v2 where v1.name='node9' and v2.name='node7';
