[postgis-users] Creating linestring from two points

[Per-Olof Norén]

Hi again all,
I managed to work it out. Is this the smartest way of doing it though?
Maybe a similar example should find its way into the documentation?


update lank
set lank_geom =  GeometryFromText('LINESTRING(' || x(parent.nod_geom) || 
' ' || y(parent.nod_geom) || ',' || x(child.nod_geom) || ' ' || 
y(child.nod_geom) || ')', 30800)
from    lank l,
        nod parent,
        nod child
where   lank.parent_nod_id = parent.nod_id
        and lank.child_nod_id = child.nod_id
        and l.parent_nod_id = l.parent_nod_id

/Per-Olof Norén


> create table nod (
> nod_id varchar(4) primary key not null,
> namn varchar(50) not null,
> status int not null
> );
> select AddGeometryColumn('crm', 'nod', 'nod_geom', 30800, 'POINT', 2 );
>
> create table lank (
> parent_nod_id varchar(4) not null,
> child_nod_id varchar(4) not null,
> nat_id varchar(4) not null,
> nat_shortnbr int not null,
> primary key(parent_nod_id, child_nod_id, nat_id)
> );
> select AddGeometryColumn('crm', 'lank', 'lank_geom', 30800, 
> 'LINESTRING', 2 );
>
> Theese tables describes a network topology and I have populated table 
> nod with a few tuples, including their geometry (points)
> I Also populated everything *but* the geometry (lank_geom) for the 
> lank table.
> My question then boils down to:
>
> How do I construct a linestring value to insert into lank.lank_geom 
> from two given points from nod.nod_geom using update?
> The two points is then obtain by joining the lank table twice against 
> nod table.
> Its basacially the postgis functions/operators that creates a 
> linestring from two points I´m looking for