PostgreSQL – Creating Line Geometry From Two Point Columns After a Join Operation

Question

I have the following query which joins two tables and returns two geom columns, I would now like to make a line between the two geom columns and set that as the Geom for the Drops table.

Table 1 – Drops (contains the required geoms, and the node names)

Table 2 – Nodes (contains the two node names I would like to draw a line between)

select
      drops."DP Name",
      drops."East Node Name",
    
 node_dp."Geom" as dp_geom,
 east_node."Geom" as eastnode_geom
      
      
from crm."Drops" drops

join crm."Nodes" node_dp
on drops."DP Name" = node_dp."Node Name"

join crm."Nodes" east_node
on drops."East Node Name" = east_node."Node Name"

Answer 1

You just need to add a line on you request to calculate a table with a line between node_dp."Geom" and east_node."Geom":

 select
      drops."DP Name",
      drops."East Node Name",        
      node_dp."Geom" as dp_geom,
      east_node."Geom" as eastnode_geom,
      -- Line Creation
      st_makeline(node_dp."Geom", east_node."Geom") as geom
      
      
from crm."Drops" drops

join crm."Nodes" node_dp
on drops."DP Name" = node_dp."Node Name"

join crm."Nodes" east_node
on drops."East Node Name" = east_node."Node Name";

But for update the geometry column of the drops table more modifications are necessary :

UPDATE crm."Drops" SET "Geom" = st_makeline(temp.geom_1,temp.geom_2)
FROM (
    SELECT 
      node_dp."Geom" as geom_1,
      east_node."Geom" as geom_2,
      drops."DP Name" as name
   FROM crm."Drops" drops
   JOIN crm."Nodes" node_dp
      ON drops."DP Name" = node_dp."Node Name"
   JOIN crm."Nodes" east_node
      ON drops."East Node Name" = east_node."Node Name"
) as temp
WHERE crm."DP Name" = temp.name;