2

I need help with merging two table and create geometry. I have two points layer with features tables and try create polyline layer. When I create virtual layer with next querry then result is polyline layer with specifying columns:

select a.field1, b.field2, makeline(a.geometry, b.geometry) as geometry
from a
inner join
b On b.id = a.id

But when I try select all field from tables result is point layer with features:

select a.*, b.*, makeline(a.geometry, b.geometry) as geometry
from a
inner join
b On b.id = a.id

There is a way to create lines and obtain all column in new layer?

Grumgog
  • 31
  • 2
  • 1
    try to makeline(a.geometry, b.geometry) as line_geom and specify to QGIS that line_geom is the geometry field. – J. Monticolo Jul 26 '22 at 11:27
  • related: https://gis.stackexchange.com/questions/313331/selecting-all-columns-except-geometry-using-virtual-layer-in-qgis – JGH Jul 26 '22 at 12:45

1 Answers1

1

I solved my problem. Problem was in order of elements in select statement. This query work perfectly:

select makeline(a.geometry, b.geometry) as geometry,  a.*, b.*
from a
inner join
b On b.id = a.id
Grumgog
  • 31
  • 2
  • I thing there is some kind of bug: query ignoring specify column and make geometry type based on first column of geometry in select statement. – Grumgog Jul 27 '22 at 13:35
  • 1
    If geometry is literal column name in both tables, the query creates a table with three columns named geometry. This typically leads to confusion and errors (and sometimes is not supported at all). I would exclude the a.geometry and b.geometry from query results if this is the case. Some DBMS support syntax like a.* EXCEPT (geometry), a.geometry AS a_geometry for that. – Michael Entin Jul 29 '22 at 00:30