I am interested in creating a queried table/report within ArcMap using SQL and/or a scripting language (python, perhaps). Can anyone suggest an approach or resources to get this done? Eventually, I'd like to have this as part of a model.
Here is what I can do:
I can create a table using SQL in Microsoft SQL Server Management Studio of an enterprise database. Here is a query that works in Management Studio:
select substring(MAP_ZONING, 1, 3) as ZONING, sum((Shape_Area/43560)) as Acres, COUNT(MAP_ZONING) as Frequency
where MAP_ZONING is not null and ExLandUse = 'VACANT'
group by substring(MAP_ZONING, 1, 3)
order by ZONING
Note: I need only the first three characters of the values in the MAP_ZONING, thus the "substring" function.
Here is what I'd like to do:
Create a similar table, but querying a feature class that is in a file geodatabase not an enterprise geodatabase on a server.
I have tried the MakeQueryTable geoprocessing tool, but there are limitations I can't seem to work around. One is that the "Query Builder" GUI seems to only like selecting all records after a where clause, and will not accept a query like the one above. I have tried to figure out if I can put my query in a subquery to get it to work, but have had no success.
I think this could be done using a python script, but I am a beginner and need to find some examples and resources.