<div dir="ltr"><div dir="ltr"><div>Hi Bernd</div><div><br></div><div>I did not understand that you were looking for a way to create a virtual layer. Virtual layers require SQL instructions and in this
particular case, I think you'll need window function (I don't know if
supported by the SpatiaLite provider in the backend and don't have time to give it a try). This is quite a different thing.</div><div><br></div><div></div><div>From what I understood you wanted to be able to join both layers. This is what the expression I'm suggesting is meant to do: <br></div><div>#. Open the birds layer attribute table</div><div># Use the field calculator button to add a new (virtual?) field (it will be used store the track id)<br></div><div>#. Populate the field using the provided expression <br></div><div><p style="margin:0px;text-indent:0px">attribute(get_feature ('track_points','time', aggregate( layer:='track_points',</p>
<p style="margin:0px;text-indent:0px"> aggregate:='max',</p>
<p style="margin:0px;text-indent:0px"> expression:="time",</p>
<p style="margin:0px;text-indent:0px"> filter:="time"< attribute(@parent, 'time'))),'fid')</p><p style="margin:0px;text-indent:0px">#. Apply</p><p style="margin:0px;text-indent:0px"><br></p><p style="margin:0px;text-indent:0px">This expression is a kind of WHERE clause so basically we say <i>update bird_layer set yournewfield=fid from track_points where whatisinget_featureparenthesis</i>. @parent indeed is a imho hidden variable that refers to the feature in the bird layer. [0] <br></p><p style="margin:0px;text-indent:0px">What dialect is it? QGIS'
(highly inspired from sql... (and python?)). I don't know if it has a
proper name and there's no better doc as far as I know than looking at
the functions' help in the expression dialog and practice. You quickly
get the logic. You also have some samples in the user manual [2]. And
probably in some tutorials out there.</p><p style="margin:0px;text-indent:0px">Yes single or double quotes are important and have their meaning [3]<br></p><p style="margin:0px;text-indent:0px"><br></p><p style="margin:0px;text-indent:0px">Then you can do whatever you want with this field (label, symbolize with geometry generator [1] eg line connecting track_point and bird, the usual join of layers...)<br></p><br><p style="margin:0px;text-indent:0px">PS: I did not test your data since the date formatting I had in the temporary layers I used was the same as in your picture (that i only viewed after I sent my first reply)<br></p><p style="margin:0px;text-indent:0px"><br></p></div><div>[0] <a href="https://docs.qgis.org/3.4/en/docs/user_manual/working_with_vector/expression.html#variables-functions">https://docs.qgis.org/3.4/en/docs/user_manual/working_with_vector/expression.html#variables-functions</a> actually polished at <a href="https://github.com/qgis/QGIS-Documentation/pull/3753">https://github.com/qgis/QGIS-Documentation/pull/3753</a></div><div>[1] <a href="https://docs.qgis.org/3.4/en/docs/user_manual/working_with_vector/style_library.html#the-geometry-generator">https://docs.qgis.org/3.4/en/docs/user_manual/working_with_vector/style_library.html#the-geometry-generator</a></div><div>[2] <a href="https://docs.qgis.org/3.4/en/docs/user_manual/working_with_vector/expression.html">https://docs.qgis.org/3.4/en/docs/user_manual/working_with_vector/expression.html</a></div><div>[3] <a href="http://osgeo-org.1560.x6.nabble.com/SQL-Expressions-tp5190508p5196052.html">http://osgeo-org.1560.x6.nabble.com/SQL-Expressions-tp5190508p5196052.html</a></div><div><br></div><div>Hope that helps,</div><div>Harrissou<br></div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">Le lun. 20 mai 2019 à 19:42, Bernd Vogelgesang <<a href="mailto:bernd.vogelgesang@gmx.de" target="_blank">bernd.vogelgesang@gmx.de</a>> a écrit :<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">
<div bgcolor="#FFFFFF">
<p>
</p>
<div class="gmail-m_6757570536394673185gmail-m_-8560208869261267017moz-cite-prefix">Am 18.05.19 um 11:42 schrieb DelazJ:<br>
</div>
<blockquote type="cite">
<div dir="ltr">
<div>Hi Bernd,</div>
<div>Hum.. interesting. Assuming you have two layers: <b>tracks
(id_track, date_track)</b> and <b>birds(id_bird, date_bird)</b>,
the idea is to get the id of the highest among the
date_track(s) lower than the date_bird for each bird. Am I
right?<br>
</div>
<div> I gave it a shot and i think i have some working
expression. Here I display the matching track id in birds
labels<br>
</div>
<div><br>
attribute(<br>
get_feature ( 'tracks',</div>
<div> 'date_track',<br>
aggregate( layer:='tracks',<br>
aggregate:='max',<br>
expression:="date_track",<br>
filter:="date_track"<
attribute(@parent, 'date_bird')<br>
)<br>
),<br>
'id_track'</div>
<div>)<br>
</div>
<div><br>
</div>
<div>I used temporary layers with datetime field.</div>
<div>Now, without sample of dataset to actually see the date
formatting issue you were afraid of...</div>
<div><br>
</div>
<div>Hope that helps,</div>
<div>Harrissou<br>
</div>
</div>
<br>
</blockquote>
<p>Hi Harrissou,</p>
<p>I tried your expression in a virtual layer, but it throws an error</p>
<p>
</p>
<p style="margin:0px;text-indent:0px">Query execution error on CREATE TEMP VIEW _tview AS attribute(get_feature ('track_points','time', aggregate( layer:='track_points',</p>
<p style="margin:0px;text-indent:0px"> aggregate:='max',</p>
<p style="margin:0px;text-indent:0px"> expression:="time",</p>
<p style="margin:0px;text-indent:0px"> filter:="time"< attribute(@parent, 'time'))),'fid'): 1 - near "attribute": syntax error</p>
<p style="margin:0px;text-indent:0px">
</p>
<p style="margin:0px;text-indent:0px">Unfortunately, I have no clue at all how to debug this. Especially the part with single or double quotes ... is there any system in that? ;)</p>
<p style="margin:0px;text-indent:0px">As I can't see the bird point mentioned somewhere, I assume that @parent is the selected feature in the bird layer?
</p>
<p style="margin:0px;text-indent:0px">What kind of "dialect" is spoken there, and is there any "dictionary" on this somewhere?</p>
<p style="margin:0px;text-indent:0px">
</p>
<p style="margin:0px;text-indent:0px">Ok, will try to send some testdata with 105kb, hope it will go through. As both layers derive from gpx, their time columns are both named time.</p>
<p style="margin:0px;text-indent:0px">
</p>
<p style="margin:0px;text-indent:0px">Cheers,</p>
<p style="margin:0px;text-indent:0px">Bernd
</p>
<p></p>
<p>
</p>
<blockquote type="cite">
<div class="gmail_quote">
<div dir="ltr" class="gmail_attr">Le ven. 17 mai 2019 à 20:34,
Bernd Vogelgesang <<a href="mailto:bernd.vogelgesang@gmx.de" target="_blank">bernd.vogelgesang@gmx.de</a>> a
écrit :<br>
</div>
<blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">Hi
Mike,<br>
<br>
thank you for your hint, but the distance between the point
and the<br>
track is of no relevance for me.<br>
<br>
It seems that the screenshot I posted is a bit misleading<br>
(<a href="https://i.stack.imgur.com/MqPhK.jpg" rel="noreferrer" target="_blank">https://i.stack.imgur.com/MqPhK.jpg</a>).
Seems I picked a point where the<br>
associated trackpoints are also the closest ones. But the
information we<br>
need is the time, where the observer was when taking the
observation.<br>
And this might also be on a more distant location.<br>
<br>
Furthermore, I do not intend to create a new layer. I'm just
looking for<br>
a way to highlight the track point(s) which compare best by
time with<br>
the selected observation point.<br>
<br>
So, still coulnd't find out how to query points in another
layer through<br>
an expression/ function, and in case I'll find out, how to
compare it<br>
with datetime values.<br>
<br>
Cheers,<br>
<br>
Bernd<br>
<br>
Am 16.05.19 um 13:28 schrieb Mike Flannigan:<br>
><br>
> Hi,<br>
><br>
> I'm thinking this is easier done outside of QGIS, but if
you<br>
> want to do in within QGIS this is one way:<br>
> <a href="https://www.qgistutorials.com/en/docs/nearest_neighbor_analysis.html" rel="noreferrer" target="_blank">https://www.qgistutorials.com/en/docs/nearest_neighbor_analysis.html</a><br>
><br>
><br>
> Mike<br>
><br>
><br>
> On 5/15/2019 1:17 PM, <a href="mailto:qgis-user-request@lists.osgeo.org" target="_blank">qgis-user-request@lists.osgeo.org</a>
wrote:<br>
>> Hi folks,<br>
>> I'm sure there must be some "easy" solution, but
hours of searching<br>
>> provided no examples I could learn from to develop a
solution:<br>
>><br>
>> I have a point layer of bird observations and a
GPS-track recorded while<br>
>> observing the birds in an area.<br>
>> For the interpretation of the findings it would be
good to easily find<br>
>> out, from which position the recorded observation was
taken.<br>
>><br>
>> My idea was to compare the timestamp of the bird
point with the<br>
>> timestamps of the track points, but I run into huge
obstacles right at<br>
>> start:<br>
>> I know that I "could" query across layers by the
expression aggregate().<br>
>> Unfortunately, the docs on that is so sparse, that I
have not the<br>
>> slightest idea how to even start with that<br>
>> (<a href="https://docs.qgis.org/testing/en/docs/user_manual/working_with_vector/expression.html?highlight=expression#aggregates-functions" rel="noreferrer" target="_blank">https://docs.qgis.org/testing/en/docs/user_manual/working_with_vector/expression.html?highlight=expression#aggregates-functions</a>)<br>
>><br>
>><br>
>> Ok, in case I could find out how to properly use
aggregate, how do I<br>
>> best compare those time stamps?<br>
>> The layers derive from gpx-files and QGIS identifies
the time tag as<br>
>> QDateTime.<br>
>><br>
>> As it is not very likely that a bird observation was
recorded at the<br>
>> exact same time as a track point was generated, there
is no way to<br>
>> actually compare the values, cause the bird time
value will always lie<br>
>> in between two track point values.<br>
>> What kind of query can be used to identify those two
trackpoints ?<br>
>><br>
>> In short: How can I identify/highligt/mark track
points that have a<br>
>> timestamp close the selected point in another layer?<br>
>><br>
>> I already created a question on this on gis
stackexchange with a<br>
>> screenshot,but no reactions so far.<br>
>><br>
>><br>
>> Thankful for any hint<br>
>> Bernd<br>
><br>
_______________________________________________<br>
Qgis-user mailing list<br>
<a href="mailto:Qgis-user@lists.osgeo.org" target="_blank">Qgis-user@lists.osgeo.org</a><br>
List info: <a href="https://lists.osgeo.org/mailman/listinfo/qgis-user" rel="noreferrer" target="_blank">https://lists.osgeo.org/mailman/listinfo/qgis-user</a><br>
Unsubscribe: <a href="https://lists.osgeo.org/mailman/listinfo/qgis-user" rel="noreferrer" target="_blank">https://lists.osgeo.org/mailman/listinfo/qgis-user</a></blockquote>
</div>
</blockquote>
</div>
</blockquote></div>
</div>