[Qgis-user] query for Virtual Layer in relation to another layer

[Uwe Fischer]

Hello Michael,

 

sorry for the delay, I had some days out of office.

The query you sent is working (that is: no error message), but the resulting virtual layer is empty. Are there other ideas?

 

Mit freundlichen Grüßen,

Uwe Fischer

 

--

 

Ingenieurbüro Fischer

Esbecker Str. 8

31036 Eime

Tel.: 05182/8325

Mobil: 0172/8876934

 

Von: Qgis-user [mailto:qgis-user-bounces at lists.osgeo.org] Im Auftrag von Reetz, Michael (NLPV) via Qgis-user
Gesendet: Montag, 24. Oktober 2022 15:32
An: qgis-user at lists.osgeo.org
Betreff: Re: [Qgis-user] query for Virtual Layer in relation to another layer

 

Hello Uwe,

 

you could try this:

 

select * from my_lines l join my_polys p on l.my_line_attribute like '%' + p.my_poly_attribute + '%'

 

Best regard

 

Michael

 

Von: Qgis-user <qgis-user-bounces at lists.osgeo.org <mailto:qgis-user-bounces at lists.osgeo.org> > Im Auftrag von Uwe Fischer via Qgis-user
Gesendet: Montag, 24. Oktober 2022 13:52
An: qgis-user at lists.osgeo.org <mailto:qgis-user at lists.osgeo.org> 
Betreff: [Qgis-user] query for Virtual Layer in relation to another layer

 

ACHTUNG!! Diese E-Mail erreicht Sie von einem Absender außerhalb der niedersächsischen Landesverwaltungs-Infrastruktur mit TLS-Verschlüsselung. Bitte klicken Sie auf keine Links oder öffnen Sie keine E-Mail-Anhänge, falls Sie den Absender nicht kennen und nicht wissen, ob der Inhalt sicher ist.

Hello,

 

I am looking for a way to create a virtual layer in 3.24 in a 'special' way. I have a line layer with attributes like:

 

xxxx_123_xxxxxxxxx

oooo_abc_000_123_0

 

and a polygon layer with attributes like:

 

abc

123

 

Now, I would like to use only lines that contain one of the strings that are present in the polygon layer.

A prototype for the SQL query might be:

 

select * from my_lines where my_line_attribute in (select my_poly_attribute from my_polys)

 

The problem is that I cannot use the IN clause because the line attributes contain additional surrounding characters around the polygon attributes. The surrounding characters can be any token, so it is not possible to remove them in a systematic way prior to the query. I thought about working with '%123%'. But how do I put every possible value in my_poly_attribute in that clause? That is, '%123%' would have to be in the query as well as '%abc%'

 

Thanks a lot for ideas!

 

Regards, Uwe

 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/qgis-user/attachments/20221104/b75b9b21/attachment.htm>