3

PostgreSQL/QGIS/LizMap environment

I have a Postrgre DB POINT layer:

-[ RECORD 1 ]+-----------------------------------------------------------------------------------
buildingid   | 1
nodeid       | 2
nodename     | term.home.myspan
ipaddress    | 10.10.10.2
hub          |
buildingname | Madrid Home
buildingcity | Madrid
geom         | 01010000E0E6100000BDB0AF7CC3010DC0BE1E5DCCD133444000000000000000000000000000000000
type         | TERM
span         | MAD05-PAR01
coloprovider | REE
cpphone      | +34 91 111 222
cpemail      | [email protected]
-[ RECORD 2 ]+-----------------------------------------------------------------------------------
buildingid   | 3
nodeid       | 3
nodename     | term.lis01.myspan
ipaddress    | 10.10.10.12
hub          | LIS01
buildingname | Lisbon Hub
buildingcity | Lisbon
geom         | 01010000E0E61000000A57271C004722C0D1FD0BCEA25D434000000000000000000000000000000000
type         | TERM
span         | LIS01-BIO02
coloprovider | Refer
cpphone      | +351 51 666 777
cpemail      | [email protected]

I would like to create a new LINE layer (new DB table linked by FK to the POINT table), and make a line joining those two points. I would like to have the LINE changing end point when I change the geometry value on POINT table.

Any help?

Improve this question
1
  • is there more than 2 points in the table? if yes, which points should be joined to which other (i.e. which field value is to be used for the join condition)?
    – JGH
    Commented Jun 3, 2021 at 12:55

1 Answer 1

Reset to default
3

Use st_makeline

create view "FIBER" as
select 
row_number() over (order by a."PK") as gid,
st_makeline(a.geom,b.geom) as geom,
from 
"BUILDING" a 
inner join 
"NODE" b on a."PK" = b."FK"

The row_number() function is useful if you intend on saving this as a view (so it's dynamic), as it produces a field that passes as a primary key for GIS desktop applications.

EDIT: updated object names after questioner's comment.

Improve this answer
3
  • Those two records belong to a view table I made by joining a table called BUILDING which contains several point records, with a table called NODE. (each building can have more than one node). NODE has a FK to BULDING PK. Now I need to create a table called FIBER with a record NODE_A, NODE_B (and more details) linked by FK to NODE PK table and generate a geom line column taking geom values from NODE_A and NODE_B from table NODE. I hope I was clear enough. (perhaps I have to provide ou the whole tables schema)
    – Enrico
    Commented Jun 3, 2021 at 19:01
  • Why a table? Why not a view? Also, I updated the query to reflect your table and field names. Are you capitalizing the table and field names, as your comment suggests? That's a really bad habit when working with PostgreSQL, as not only will you have to comment the crap out of your SQL code, but you'll have major issues connecting to those tables from certain applications (like ArcGIS).
    – Encomium
    Commented Jun 3, 2021 at 19:10
  • Thanks Encomium. I'm going to test your suggestion and I'll let you know. (CAPS was to emphasize, table name are small letters.)
    – Enrico
    Commented Jun 3, 2021 at 19:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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