<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<meta name="Generator" content="Microsoft Word 15 (filtered medium)">
<style><!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:0 0 0 0 0 0 0 0 0 0;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman",serif;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
p
{mso-style-priority:99;
mso-margin-top-alt:auto;
margin-right:0cm;
mso-margin-bottom-alt:auto;
margin-left:0cm;
font-size:12.0pt;
font-family:"Times New Roman",serif;}
span.EmailStyle18
{mso-style-type:personal;
font-family:"Calibri",sans-serif;
color:#1F497D;}
span.EmailStyle19
{mso-style-type:personal-compose;
font-family:"Calibri",sans-serif;
color:windowtext;}
.MsoChpDefault
{mso-style-type:export-only;
font-family:"Calibri",sans-serif;
mso-fareast-language:EN-US;}
@page WordSection1
{size:612.0pt 792.0pt;
margin:72.0pt 72.0pt 72.0pt 72.0pt;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang="EN-AU" link="blue" vlink="purple">
<div class="WordSection1">
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US">Hi Bernd,<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US">I replied to your question on stack exchange (with a similar expression to Harrissou)<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US">I initially started with a virtual layer (because I’m a database guy – you know the expression “when you only have hammer everything
looks like nail”) but then I thought I should try to find a more accessible, QGIS way to do it.<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US">Here is the SQL I used in the DB Manager which could be loaded as a layer:<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal">SELECT *, (select "Tracks"."fid" as PositionId<span style="font-size:11.0pt;font-family:"Calibri",sans-serif"><o:p></o:p></span></p>
<p class="MsoNormal"> FROM "Tracks" <o:p></o:p></p>
<p class="MsoNormal"> where julianday("Sightings"."ObsTime" ) -julianday("Tracks"."PositionTime")>=0
<o:p></o:p></p>
<p class="MsoNormal"> order by "Tracks"."PositionTime" DESC LIMIT 1) as PositionID<o:p></o:p></p>
<p class="MsoNormal">FROM "Sightings"<o:p></o:p></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US">My two test tables where Sightings(fid,ObsTime) and Tracks(fid, PositionTime).<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US">It creates a look alike of the Sightings layer with an additional column for the id of the Position.<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US">I also made another SQL statement which created Line geometries connecting the Sighting and the associated position. That’s on my
computer at home which I don’t have access to ATM.<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US">Regards,<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US">Martin.<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><b><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif">From:</span></b><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif"> Qgis-user [mailto:qgis-user-bounces@lists.osgeo.org]
<b>On Behalf Of </b>DelazJ<br>
<b>Sent:</b> Tuesday, 21 May 2019 1:51 PM<br>
<b>To:</b> Bernd Vogelgesang <bernd.vogelgesang@gmx.de><br>
<b>Cc:</b> qgis-user <qgis-user@lists.osgeo.org><br>
<b>Subject:</b> Re: [Qgis-user] Query cross-table "similar" time stamps?<o:p></o:p></span></p>
<p class="MsoNormal"><o:p> </o:p></p>
<div>
<div>
<div>
<p class="MsoNormal">Hi Bernd<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">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.<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">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:
<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">#. Open the birds layer attribute table<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"># Use the field calculator button to add a new (virtual?) field (it will be used store the track id)<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">#. Populate the field using the provided expression <o:p></o:p></p>
</div>
<div>
<p style="margin:0cm;margin-bottom:.0001pt">attribute(get_feature ('track_points','time', aggregate( layer:='track_points',<o:p></o:p></p>
<p style="margin:0cm;margin-bottom:.0001pt">aggregate:='max',<o:p></o:p></p>
<p style="margin:0cm;margin-bottom:.0001pt">expression:="time",<o:p></o:p></p>
<p style="margin:0cm;margin-bottom:.0001pt">filter:="time"< attribute(@parent, 'time'))),'fid')<o:p></o:p></p>
<p style="margin:0cm;margin-bottom:.0001pt">#. Apply<o:p></o:p></p>
<p style="margin:0cm;margin-bottom:.0001pt"><o:p> </o:p></p>
<p style="margin:0cm;margin-bottom:.0001pt">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]
<o:p></o:p></p>
<p style="margin:0cm;margin-bottom:.0001pt">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.<o:p></o:p></p>
<p style="margin:0cm;margin-bottom:.0001pt">Yes single or double quotes are important and have their meaning [3]<o:p></o:p></p>
<p style="margin:0cm;margin-bottom:.0001pt"><o:p> </o:p></p>
<p style="margin:0cm;margin-bottom:.0001pt">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...)<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p style="margin:0cm;margin-bottom:.0001pt">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)<o:p></o:p></p>
<p style="margin:0cm;margin-bottom:.0001pt"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">[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><o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">[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><o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">[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><o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">[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><o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">Hope that helps,<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">Harrissou<o:p></o:p></p>
</div>
</div>
<p class="MsoNormal"><o:p> </o:p></p>
<div>
<div>
<p class="MsoNormal">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 :<o:p></o:p></p>
</div>
<blockquote style="border:none;border-left:solid #CCCCCC 1.0pt;padding:0cm 0cm 0cm 6.0pt;margin-left:4.8pt;margin-right:0cm">
<div>
<div>
<p class="MsoNormal">Am 18.05.19 um 11:42 schrieb DelazJ:<o:p></o:p></p>
</div>
<blockquote style="margin-top:5.0pt;margin-bottom:5.0pt">
<div>
<div>
<p class="MsoNormal">Hi Bernd,<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">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?<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">I gave it a shot and i think i have some working expression. Here I display the matching track id in birds labels<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><br>
attribute(<br>
get_feature ( 'tracks',<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"> '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'<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">)<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">I used temporary layers with datetime field.<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">Now, without sample of dataset to actually see the date formatting issue you were afraid of...<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">Hope that helps,<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">Harrissou<o:p></o:p></p>
</div>
</div>
<p class="MsoNormal"><o:p> </o:p></p>
</blockquote>
<p>Hi Harrissou,<o:p></o:p></p>
<p>I tried your expression in a virtual layer, but it throws an error<o:p></o:p></p>
<p style="margin:0cm;margin-bottom:.0001pt">Query execution error on CREATE TEMP VIEW _tview AS attribute(get_feature ('track_points','time', aggregate( layer:='track_points',<o:p></o:p></p>
<p style="margin:0cm;margin-bottom:.0001pt">aggregate:='max',<o:p></o:p></p>
<p style="margin:0cm;margin-bottom:.0001pt">expression:="time",<o:p></o:p></p>
<p style="margin:0cm;margin-bottom:.0001pt">filter:="time"< attribute(@parent, 'time'))),'fid'): 1 - near "attribute": syntax error<o:p></o:p></p>
<p style="margin:0cm;margin-bottom:.0001pt">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? ;)<o:p></o:p></p>
<p style="margin:0cm;margin-bottom:.0001pt">As I can't see the bird point mentioned somewhere, I assume that @parent is the selected feature in the bird layer?
<o:p></o:p></p>
<p style="margin:0cm;margin-bottom:.0001pt">What kind of "dialect" is spoken there, and is there any "dictionary" on this somewhere?<o:p></o:p></p>
<p style="margin:0cm;margin-bottom:.0001pt">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.<o:p></o:p></p>
<p style="margin:0cm;margin-bottom:.0001pt">Cheers,<o:p></o:p></p>
<p style="margin:0cm;margin-bottom:.0001pt">Bernd <o:p></o:p></p>
<blockquote style="margin-top:5.0pt;margin-bottom:5.0pt">
<div>
<div>
<p class="MsoNormal">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 :<o:p></o:p></p>
</div>
<blockquote style="border:none;border-left:solid #CCCCCC 1.0pt;padding:0cm 0cm 0cm 6.0pt;margin-left:4.8pt;margin-right:0cm">
<p class="MsoNormal">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" 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" 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" 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" target="_blank">
https://lists.osgeo.org/mailman/listinfo/qgis-user</a><br>
Unsubscribe: <a href="https://lists.osgeo.org/mailman/listinfo/qgis-user" target="_blank">
https://lists.osgeo.org/mailman/listinfo/qgis-user</a><o:p></o:p></p>
</blockquote>
</div>
</blockquote>
</div>
</blockquote>
</div>
</div>
</div>
</body>
</html>