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-boun...@lists.osgeo.org] On Behalf Of DelazJ
Sent: Tuesday, 21 May 2019 1:51 PM
To: Bernd Vogelgesang <bernd.vogelges...@gmx.de>
Cc: qgis-user <qgis-user@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.vogelges...@gmx.de<mailto:bernd.vogelges...@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.vogelges...@gmx.de<mailto:bernd.vogelges...@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-requ...@lists.osgeo.org<mailto:qgis-user-requ...@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@lists.osgeo.org<mailto:Qgis-user@lists.osgeo.org>
List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
_______________________________________________
Qgis-user mailing list
Qgis-user@lists.osgeo.org
List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
