[Qgis-user] Query cross-table "similar" time stamps?

Mon May 20 20:50:31 PDT 2019

Hi Bernd

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.

>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:
#. Open the birds layer attribute table
# Use the field calculator button to add a new (virtual?) field (it will be
used store the track id)
#. Populate the field using the provided expression

attribute(get_feature ('track_points','time', aggregate(
layer:='track_points',

aggregate:='max',

expression:="time",

filter:="time"< attribute(@parent, 'time'))),'fid')

#. Apply

This expression is a kind of WHERE clause so basically we say *update
bird_layer set yournewfield=fid from track_points where
whatisinget_featureparenthesis*. @parent indeed is a imho hidden variable
that refers to the feature in the bird layer. [0]

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.

Yes single or double quotes are important and have their meaning [3]

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...)

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)

[0]
https://docs.qgis.org/3.4/en/docs/user_manual/working_with_vector/expression.html#variables-functions
actually polished at https://github.com/qgis/QGIS-Documentation/pull/3753
[1]
https://docs.qgis.org/3.4/en/docs/user_manual/working_with_vector/style_library.html#the-geometry-generator
[2]
https://docs.qgis.org/3.4/en/docs/user_manual/working_with_vector/expression.html
[3]
http://osgeo-org.1560.x6.nabble.com/SQL-Expressions-tp5190508p5196052.html

Hope that helps,
Harrissou

Le lun. 20 mai 2019 à 19:42, Bernd Vogelgesang <bernd.vogelgesang at gmx.de> a
écrit :

> Am 18.05.19 um 11:42 schrieb DelazJ:
>
> Hi Bernd,
> Hum.. interesting. Assuming you have two layers: *tracks (id_track,
> date_track)* and *birds(id_bird, date_bird)*, 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?
> I gave it a shot and i think i have some working expression. Here I
> display the matching track id in birds labels
>
> attribute(
>   get_feature ( 'tracks',
>                       'date_track',
>                       aggregate( layer:='tracks',
>                                       aggregate:='max',
>                                       expression:="date_track",
>                                       filter:="date_track"<
> attribute(@parent, 'date_bird')
>                                   )
>                  ),
>   'id_track'
> )
>
> I used temporary layers with datetime field.
> Now, without sample of dataset to actually see the date formatting issue
> you were afraid of...
>
> Hope that helps,
> Harrissou
>
> Hi Harrissou,
>
> I tried your expression in a virtual layer, but it throws an error
>
> Query execution error on CREATE TEMP VIEW _tview AS attribute(get_feature
> ('track_points','time', aggregate( layer:='track_points',
>
> aggregate:='max',
>
> expression:="time",
>
> filter:="time"< attribute(@parent, 'time'))),'fid'): 1 - near "attribute":
> syntax error
>
> 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? ;)
>
> As I can't see the bird point mentioned somewhere, I assume that @parent
> is the selected feature in the bird layer?
>
> What kind of "dialect" is spoken there, and is there any "dictionary" on
> this somewhere?
>
> 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.
>
> Cheers,
>
> Bernd
>
> Le ven. 17 mai 2019 à 20:34, Bernd Vogelgesang <bernd.vogelgesang at gmx.de>
> a écrit :
>
>> Hi Mike,
>>
>> thank you for your hint, but the distance between the point and the
>> track is of no relevance for me.
>>
>> It seems that the screenshot I posted is a bit misleading
>> (https://i.stack.imgur.com/MqPhK.jpg). Seems I picked a point where the
>> associated trackpoints are also the closest ones. But the information we
>> need is the time, where the observer was when taking the observation.
>> And this might also be on a more distant location.
>>
>> Furthermore, I do not intend to create a new layer. I'm just looking for
>> a way to highlight the track point(s) which compare best by time with
>> the selected observation point.
>>
>> So, still coulnd't find out how to query points in another layer through
>> an expression/ function, and in case I'll find out, how to compare it
>> with datetime values.
>>
>> Cheers,
>>
>> Bernd
>>
>> Am 16.05.19 um 13:28 schrieb Mike Flannigan:
>> >
>> > Hi,
>> >
>> > I'm thinking this is easier done outside of QGIS, but if you
>> > want to do in within QGIS this is one way:
>> > https://www.qgistutorials.com/en/docs/nearest_neighbor_analysis.html
>> >
>> >
>> > Mike
>> >
>> >
>> > On 5/15/2019 1:17 PM, qgis-user-request at lists.osgeo.org wrote:
>> >> Hi folks,
>> >> I'm sure there must be some "easy" solution, but hours of searching
>> >> provided no examples I could learn from to develop a solution:
>> >>
>> >> I have a point layer of bird observations and a GPS-track recorded
>> while
>> >> observing the birds in an area.
>> >> For the interpretation of the findings it would be good to easily find
>> >> out, from which position the recorded observation was taken.
>> >>
>> >> My idea was to compare the timestamp of the bird point with the
>> >> timestamps of the track points, but I run into huge obstacles right at
>> >> start:
>> >> I know that I "could" query across layers by the expression
>> aggregate().
>> >> Unfortunately, the docs on that is so sparse, that I have not the
>> >> slightest idea how to even start with that
>> >> (
>> https://docs.qgis.org/testing/en/docs/user_manual/working_with_vector/expression.html?highlight=expression#aggregates-functions
>> )
>> >>
>> >>
>> >> Ok, in case I could find out how to properly use aggregate, how do I
>> >> best compare those time stamps?
>> >> The layers derive from gpx-files and QGIS identifies the time tag as
>> >> QDateTime.
>> >>
>> >> As it is not very likely that a bird observation was recorded at the
>> >> exact same time as a track point was generated, there is no way to
>> >> actually compare the values, cause the bird time value will always lie
>> >> in between two track point values.
>> >> What kind of query can be used to identify those two trackpoints ?
>> >>
>> >> In short: How can I identify/highligt/mark track points that have a
>> >> timestamp close the selected point in another layer?
>> >>
>> >> I already created a question on this on gis stackexchange with a
>> >> screenshot,but no reactions so far.
>> >>
>> >>
>> >> Thankful for any hint
>> >> Bernd
>> >
>> _______________________________________________
>> Qgis-user mailing list
>> Qgis-user at lists.osgeo.org
>> List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
>> Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/qgis-user/attachments/20190521/ba3c8b93/attachment.html>