I have a question similar to this one, but the proposed approach doesn't seem to work. For context, I am new to qgis.
Given a set of parcels, each with 0 or more buildings, I want to select only the largest building in each parcel.
There are about 200k parcels and 320k thousand buildings.
Building A: parcel_APN: 1 (ParcelNumber) SHAPE_Area: 100 Building B: parcel_APN: 1 SHAPE_Area: 500 Building C: parcel_APN: 2 SHAPE_Area: 50 >>> LargestBuildings: [B, C]
I tried two approaches:
SQL Query DB Manager
Seems like this should work, but I let it run for 2 hours and it did not finish. Dataset is big, but seems to be shouldn't take that long for a SQL join. A simpler SELECT with MAX area and groupby but without the join take less than 5 seconds.
The approach is to first group by APN to find the largest shape area, then do inner join using id and max area , with the same table, to retrieve the largest buildings.
SELECT * FROM ( SELECT parcel_APN, MAX(SHAPE_Area) as max_shape FROM buildings as t0 GROUP BY parcel_APN ) as t1 JOIN buildings as t2 ON t1.parcel_APN = t2.parcel_APN and max_shape = t2.SHAPE_area
Using Attribute Expression
This is the approach suggested in the SO mentioned above:
I let this run last night, this morning was still running and eventually QGIS crashed.
Do these approach seem correct? Is there an easier way to go about this? Are the file sizes too big for something like this in QGIS? And maybe I need to work in subsets or work with the data outside?
I created a small subset to test, and the attribute expression method does not seem to work - selects all buildings:
DB Manager Query seems to work! I guess now I just need to find a way to run it without crashing QGIS, or load it into PostGIS to run it.