1

I have:

  • classified GPS-data them according to "Starting city - Destination city".
  • a geodataset for German highways, that contains a "Route-ID" for each section.

Now I want to count how many times I drove over which "Route-ID" at which relation in QGIS.

For this, I joined the "Route-ID" per location and kept only one GPS-point per trip per "Route-ID". So I want to count the GPS-points per Relation per "Route-ID". Any suggestions?

The Dataset looks like:

Example

As example: I drove 5 times from Cologne to Frankfurt and used 4 times the combination "A4" and "A45" but one time the "A3". On the way back i used the combination "A4" and "A45" 2 times and the "A3" 3 times. The result should look like this:

enter image description here

  • Have I correctly understood that connections like 'München - Köln' and 'Köln - München' if they share the same "Route-ID" should be aggregated as one connection? – Taras May 7 at 13:03
  • @Taras no, every Relation stands anlone. I edited the Question. Hope its clear now. – Benny94 May 7 at 13:22
2

Tested on QGIS 2.18

I can suggest using a "Virtual Layer" through Layer > Add Layer > Add/Edit Virtual Layer...

Let us assume that we have such connections

Example

With the following Query, it is possible to achieve the result

SELECT Relation, Route_ID, COUNT(*) AS count
FROM Dataset
GROUP BY Relation, Route_ID
ORDER BY count DESC

The output will look like

Result


References:

  • thanks! I had the same idea, just forgot to group also by Relation. Easier than thought. – Benny94 May 7 at 13:40
  • Always welcome! BTW if it solved your issue please accept it as a valid answer. – Taras May 7 at 13:42

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.