6

I'm wondering if there is a way to find the minimum value of a group within QGIS? Basically I need to find the minimum distance of each unique value in a table of about 5000 records.

I can do the analysis in excel using vlookups, etc, but wondering if there is a method in QGIS that will allow me to miss out the excel stage and avoid joining tables. I can't figure out what I need in the field calculator.

enter image description here

I need to find the minimum value for each TOID and remove anything that is not the minimum. Hope that clarifies.

  • 1
    Please explain more what you want to achieve and provide some data example. Do you just want to find the smallest value? – wittich Sep 1 '15 at 9:14
  • "Minimum distance" to or from where of each unique value? What defines a "group"? An attribute value? If the output isn't the same length as the number of features then I don't think the field calculator will help - its either a python script or some R code via the processing module. Please clarify your question. – Spacedman Sep 1 '15 at 9:17
  • So I have a list of IDs and I have already calculated a distance to another layer. The IDs are listed as 1,1,1,1,2,2,3,3,3,4,4,5,5,5,5, etc. Each row in the table will have a unique distance. So, I need to reduce the table to just 1,2,3,4,5, etc and the associated minimum distance; basically remove everything apart from the minimum – Graham04 Sep 1 '15 at 9:18
10

You could use the Group Stats plugin from Plugins > Manage and Install Plugins. This calculates various data statistics for your attributes such as finding the minimum value in a group. I made an example of attributes from the data you gave:

Example attributes

Then from the Group Stats interface, select and drag the toid field from the list into the Rows window; and repeat for the HubDist field and the Min option for the Value window. You should then get the minimum values for your group which you can then export to clipboard or to a csv file etc (i.e. ignoring values other than a minimum. You can then import this back into QGIS as a Delimited Text Layer).

Group Stats plugin interface

Hope this helps!

  • 2
    You'll find the "Group Stats" menu item in the "Vector" menu once its installed. Tutorial here: anitagraser.com/2013/02/02/group-stats-tutorial – Spacedman Sep 1 '15 at 9:50
  • 1
    aha, splendid! Thanks very much. I had tried this before, but had obviously not got the correct combination. – Graham04 Sep 1 '15 at 9:51
  • 1
    If I'd not spent five minutes discovering you had to drag stuff around and another five minutes figuring out what to drag where my answer would have beaten yours! Is this basically a spreadsheet "Pivot Table" equivalent? – Spacedman Sep 1 '15 at 10:31
  • 1
    The problem I've found with the GroupStats is that if I show selection (for any particular row) it doesn't simply highlight the row of the TOID and associated min reference, it highlights all rows in the original table with the TOID. While copying to CSV or the clipboard is useful, it would be great if the selection was able to pick out only those rows that had a specified minimum for that particular group of TOIDs. – Graham04 Sep 1 '15 at 10:52
  • 2
    If you write out ID, MinDist records from the Group Stats tool to a table, then join that to your map data on ID, then each feature will then have its Distance and the MinDist for its ID. Then one selection for Distance==MinDist gets you one feature per ID (unless there's MinDist ties...) – Spacedman Sep 1 '15 at 12:59
4

So, we've ended up changing tact and moving to spatialite and seem to have managed to do it with an SQL query, including the geometry field which means I can add it as a new layer:

    Select Distinct
    T1.toid As TOID1,
    Min(T1.Hubdist) As MinDIST,
    T1.ID, T1.geometry
    From
    [Hub Distance] T1 Inner Join
    (Select
    T2.toid As TOID2,
    Min(T2.Hubdist) As MDist
    From
    [Hub Distance] T2
    Group By
    T2.toid) T3 On T1.toid = T3.TOID2 And T1.Hubdist = T3.MDist
    Group By
    T1.toid, T1.ID, T1.geometry
    Order By
    T1.toid

Hope that helps somebody.

  • 1
    +1, Brilliant! Thanks for sharing! I'm sure there will be others who will find this useful :) – Joseph Sep 2 '15 at 9:06

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.