1

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.

Example

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:

maximum("SHAPE_Area", group_by:="parcel_APN")

I let this run last night, this morning was still running and eventually QGIS crashed.

Questions

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?


Update

I created a small subset to test, and the attribute expression method does not seem to work - selects all buildings:

screenshot attribute table not working

Update 2

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.

enter image description here

  • Definitely a task for PostGIS... my approach would be to intersect the buildings to the parcels, and give each building a row_number which sorts based on the area of the building. Then you can use a subquery or common table expression approach to select only the 1st building - the largest one - from each polygon. Definitely needs a bit more muscle than the QGIS layer-driven approach can yield, in my opinion given the large datasets... – DPSSpatial Oct 9 at 17:53
  • thanks for your input - updated the question, I was able to get DB manager approach to work on a small subset. I will try one more time, if it doesn't work I will try the PostGIS approach – gtalarico Oct 9 at 19:53
  • OK cool. Let me know if it works. – DPSSpatial Oct 9 at 20:00
  • 1
    Also - careful with that SHAPE_AREA column. It's from a ESRI geodatabase, and since you're using shapefiles, it's not dynamic anymore. You should use the area of the geometry instead of this column – DPSSpatial Oct 9 at 20:01
  • loaded into PostGis and completed it in 2 seconds :) Thanks for the suggestion. – gtalarico Oct 10 at 0:31
2

While a SQL approach with DB Manager and Virtual layers inside QGIS worked with small samples, it would run for hours or crash agains the entire datates.

As suggested by @DSSPatial, I loaded the data into PostGIS and it worked really well, and fast. Here is the final query:

INSERT INTO main_buildings
SELECT 
    t2.*
FROM
 ( 
   SELECT 
     t0."parcel_APN",
     MAX(t0."SHAPE_Area") as max_shape
   FROM main_buildings as t0
   GROUP BY t0."parcel_APN"
 ) as t1
INNER JOIN main_buildings as t2
ON t1."parcel_APN" = t2."parcel_APN" and max_shape = t2."SHAPE_Area";

Not perfect but this is what I was looking for.

enter image description here

| improve this answer | |
  • Try using the ST_Area() of the GEOM instead of SHAPE_AREA... glad you got it going in postgis!!! – DPSSpatial Oct 10 at 16:39

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.