Find the majority area within another area

Question

I have a survey grid overlaid unto several administrative areas. Both are polygon features and I have performed a union on both features in order to determine the areal contribution of the segments created within each survey grid square. The purpose of doing this is so that I can assign each survey grid to an administrative area based on which administrative area is of a majority within each survey grid.

How can I perform this using the operators in the search query builder WHERE clause?

So I have an example extracted from the table: 'CCAFS_ID' is the survey grid and 'prcntArea' is the areal contribution to 'Area' calculated by dividing 'segArea' by 'Area'

    CCAFS_ID    Area    NAME_0  NAME_2  segArea     prcntArea
    3138    0.00203158  Uganda  Bugahya 0.00063856  31.43169356
    3138    0.00203158  Uganda  Buhaguzi  0.00060369    29.71529548
    3138    0.00203158  Uganda  Kiboga  0.00078933  38.85301096
    2373    0.00203777  Kenya   Kwale   0.00026393  12.95190331
    2252    0.00203429  Kenya   Makueni 0.00076357  37.53496306
    2252    0.00203429  Kenya   Taita Taveta    0.00064302  31.60906262
    2252    0.00203429  Kenya   Kajiado 0.0006277   30.85597432
    2419    0.00203864  Kenya   Kwale   0.00070346  34.50633756
    2457    0.00203889  Kenya   Kwale   0.00088593  43.45158395
    2567    0.00203911  Kenya   Kwale   0.00015073  7.39195041

Answer 1

Using DB Manager plugin, you are not restricted to the SQL where clause. Instead you can write any kind of query. Just insert Alexandre's suggestion here:

Answer 2

Try

SELECT DISTINCT ON (CCAFS_ID) CCAFS_ID, NAME_0, NAME_2
FROM TABLE
ORDER BY CCAFS_ID, segArea DESC

The "DISTINCT ON (CCAFS_ID)" will filter the result so only the first row with a CCAFS_ID is shown. The "ORDER BY (...) segArea DESC)" guarantee that the first row will be the one with the biggest area.

Hope it helps!