[postgis-users] points to lines

[Stephen Woodbridge]

ahmed at elegantdesigns.ca wrote:
> Hello list,
> 
> I have a point table with the following format:
> 
> id    node_1    node_2    weight
> 1     1         2         5.4
> 2     1         3         2
> 3     2         4         6
> 4     4         4         1
> 
> 
> the table is about 2,000 rows long.
> 
> i was wondering how can i convert this table into a line layer such that:
> * line should be created between node_1 and node_2
> * a weight field/attribute be added corresponding to the link

select AddGeometryColumn('public', 'lines', 'the_geom', 4326, 
'LINESTRING', 2);

update lines set the_geom=setsrid(st_makeline(
   (select the_geom from points where node_id=node1),
   (select the_geom from points where node_id=node2),
   4326);

Something like the above should work.

-Steve