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

Mon May 20 21:56:14 PDT 2019

Hi Bernd,
I replied to your question on stack exchange (with a similar expression to Harrissou)

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.

Here is the SQL I used in the DB Manager which could be loaded as a layer:

SELECT  *, (select "Tracks"."fid" as PositionId
                      FROM "Tracks"
                      where julianday("Sightings"."ObsTime" ) -julianday("Tracks"."PositionTime")>=0
                      order by "Tracks"."PositionTime" DESC LIMIT 1) as PositionID
FROM "Sightings"

My two test tables where Sightings(fid,ObsTime) and Tracks(fid, PositionTime).

It creates a look alike of the Sightings layer with an additional column for the id of the Position.

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.

Regards,
Martin.

From: Qgis-user [mailto:qgis-user-bounces at lists.osgeo.org] On Behalf Of DelazJ
Sent: Tuesday, 21 May 2019 1:51 PM
To: Bernd Vogelgesang <bernd.vogelgesang at gmx.de>
Cc: qgis-user <qgis-user at lists.osgeo.org>
Subject: Re: [Qgis-user] Query cross-table "similar" time stamps?

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<mailto: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<mailto: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<mailto: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<mailto: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/be502945/attachment-0001.html>