1

For a gone experiment I want to link the weather data to recorded track points. The 3 layers envolved are the track layer (points), the layer of privat weather stations ("PWS" points) and a geometry-less table of weather data (each row with "PWS_id" and "datetime").

I did already hard-coded join the track points with the most closest PWS by using the NNJoin plugin.

For each PWS I do have 1-n weather records per day.

Now I need to link/join the correct weather record to the track point by minimum "age" as difference to timestamp of trackpoint.

Is there a way to solve this problem from within QGIS (without python knowledge), or if postgres/-GIS is the best solution, what would be the SQL syntax to get the record with the minimum age(track-point, weather-record).

1

PostgreSQL provides a DISCTINCT ON expression useful for your problem.

SELECT DISTINCT ON (age) *
FROM your_joined_table
ORDER BY age DESC

Other possibility: window functions, something like:

WITH q AS (
SELECT *, rank() OVER (ORDER BY age DESC) FROM your_joined_table
) 
SELECT * FROM q WHERE rank = 1
  • reading it for a second time.... but wouldnt above require an "age" field at the weather record table ? – Pallasch Oct 3 '16 at 13:08
  • I used "age" for the example, thinking about a timestamp field and selecting the last one. – WKT Oct 3 '16 at 13:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.