Creating one layer through use of SQL queries for land use categories

Question

I have a map with about 63 grids and trying to determine the land cover user category of the greatest area size in each grid. The land cover categories have been converted to polygons and I already have attribute tables with a field for "Area" in each grid. I need to create a layer that will give me which land use category has the greatest area size. So I need to sum up the "area" of each category and then determine the max (highest area) and then make a layer for only that category.

i.e. Grid 1 contains - water = 900m, development = 1000m, forest = 2000m Grid 1 - category with highest area size = forest at 2000m - I would need just one layer displaying forest only in that grid. But then do this quickly for all other grids in my map.

I am a mid-level user in ArcMap with decent SQL query skills, so I feel most comfortable in SQL and I'm certain a making a query with a join statement that joins onto itself can get that done for me. I'm looking where/how best to run a SQL query that will be a little more complex than the regular "select * from". Looking at tools like the "Select attribute by" is nice but the SQL query section in there doesn't really allow for inner join statements to be performed. Or if all this needs to be done a different way how I go about to create the layers I need. I could of course trudge through each grid and do it manually but I am looking to learn how to do such things in a faster method and use in the future.

Will I be venturing into ArcPy and/or ModelBuilder?

I want to sum up "Area" per "Gridcode" then determine which "Gridcode" has the highest sum area. Then make that into a new layer. I would not need any other gridcodes if they do not sum up as the max.

Answer 1

If I am understanding you right you started out with an NLCD raster and a polygon feature class of your grids. You then converted the raster to polygon within each of your grids cells to yield polygons with the corresponding NLCD land cover class.

Spatial Analyst Approach: I would redesign your workflow as follows: Before converting to polygons I recommend using the Zonal Statistics tool on the original NLCD, with your zones being the grid polygons and the NLCD land cover class being the value. You set the statistics field to be 'MAJORITY'. You then use the Raster Calculator tool to convert anything that is not of the majority land cover class to no data.

SetNull([NLCD]<>[zonalstat], [zonalstat])

What remains will be a raster of only the majority land cover. You could similarly use the Reclass tool. Then you can do your conversion to polygon, the resulting polygons will have the only majority land cover. You should note however that if more than one land cover class has the same number of cells only one will be returned by this procedure. So you may want to do Zonal Statistics as Table to find grids that have more than one land cover in the majority status.

SQL approach: For an SQL based approach on your area polygons, you can try the Make Query Table tool if you have a geodabase. I have never used it so I do not know how powerful it is. Your SQL you would be something like this:

SELECT grid_id, land_use_class, max(area) OVER (PARTITION BY area) 
FROM table
WHERE area = max(area)

You can take a look at the Window Functions documentation for PostgreSQL for more detail.

But you may want to check that your polygons did not get split, for example by an area of different land cover in between two areas of the same land cover. You need to make multipolygons when you convert from raster, otherwise your dominant land cover could be masked because the dominant is not contiguous.