Using Statistics by category in QGIS?

Question

I use the "statistics by category" tool from QGIS to learn what grain yields I got from different combinations of scenarios like:

Soil A + Hybrid A + Planting Date A = Yield n Soil B + Hybrid A + Planting Date A = Yield n2 ... so on and so forth.

Here`s what I do today:

I take the yield data from the combines that come as points and convert to SHP.

The shapefile now have 1 column only (Yield).

Then I use the "Join attributes by location" tool to extract data from other polygon layers that I want to analyse (like Planting maps with Hybrids locations, soil type maps etc) and add those info into my yield shapefile.

Then I run the statistics from this shapefile that has each point with all the atributes that I want to analyse.

It does work but it`s a really time consuming process, specially because my yield shapefile contains millions of points so every step takes several minutes to be processed.

Any Ideas on a more efficient procedure?

Here`s a screenshot of a final product that I have today and that I would be looking for something similar with a different solution.

Answer 1

I also work in precision ag and deal with large amounts of data. I learned quickly that shapefiles become unwieldy fast, as the farm equipment is collecting and recording data continuously. Shapefiles have limitations, as there have been no changes in its characteristics (to my knowledge) since it was first described in this ESRI whitepaper from 1998.

The sheer volume of data and some of the characteristics of shapefiles (e.g., 10 character limit for header names) led me to find other data management and processing strategies. This forced me to investigate other options and led me to my main tool that use today, the spatially enabled relational database! PostgreSQL with the PostGIS extension is what I can recommend. PostgreSQL provides the data management part and PostGIS provides the geoprocessing algorithms. It is free to use. The only cost is time, as there is a definite learning curve. To help learn I can strongly recommend the book 'PostGIS in Action'. Another free option is Spatialite. I've not used this option, but I know a couple of the benefits are that it is lightweight and is self-contained (data + program can be stored in a singular location like a thumbdrive). Also, there is no client/server architecture to navigate.

The beauty of switching to a relational DB approach with spatial capabilities are many. Here are just a few off the top of my head:

1. The ability to query small portions of a dataset instead of having to load an entire shapefile and wait for a GUI GIS to re-draw all features with each pan/zoom is priceless.
2. Like you, I do a lot of joining yield values to nearest points such as fertilizer, previous yield, etc. This geoprocessing is all easily done using the PostGIS capabilities and the data can be stored as views rather than having to constantly create new copies of data and keeping track of the subsequent shapefile and its various 'parts'.
3. You can store rasters. I use topographic variables (elev, slope, aspect, etc), as well as weather data stored as rasters in my database. Yield points are then related to those underlying values, just like 'Point Sampling' in QGIS or something similar in ArcMap.
4. You can visualize any data that you want using QGIS, ArcMap, or OpenJump and linking to your database. There may be other options as well.
5. Processing (nearest neighbor, point sampling, inserts, updates, etc) still take time when dealing with millions of points. I've not found a way around that, but in my experience and on my computer, the database can handle those operations while I do other work, whereas with the GUI GIS/shapefile approach my entire computer would often be tied up for a long time (or just break!).

I'm still not a database expert by a long shot, but I've learned enough to know that this is more efficient than using and keeping track of shapefiles. I now only use the shapefile as an exchange format, as this is still the method most commonly used by farmers, ag software, and consultants. I would encourage you to do some research and see if these tools may work for you as well.