1

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"

enter image description here

2
  • Welcome to Geographic Information Systems! Welcome to GIS SE! We're a little different from other sites; this isn't a discussion forum but a Q&A site. Your questions should as much as possible describe not just what you want to do, but precisely what you have tried and where you are stuck trying that. Please check out our short tour for more about how the site works
    – Ian Turton
    Sep 6, 2021 at 8:01
  • Did you try ST_MakeLine?
    – geozelot
    Sep 6, 2021 at 8:08

1 Answer 1

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;
5
  • That creates a geom column in the data output which shows the correct line, great! How could I then set that generated geom as the 'Drops' table's geom?
    – pmwill
    Sep 6, 2021 at 8:31
  • I have update my anwswer. Tell me if is work correctly, I cannot test this request.
    – Atm
    Sep 6, 2021 at 8:57
  • Thank you. I get "ERROR: syntax error at or near "TABLE" If I remove 'TABLE' it does update the correct column, but all rows are given the same line geom
    – pmwill
    Sep 6, 2021 at 9:12
  • Now it should be works (I hope)
    – Atm
    Sep 6, 2021 at 9:33
  • it does! Thank you so much :)
    – pmwill
    Sep 6, 2021 at 9:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.