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.
Select by Attributes followed by a Copy Features... see the script examples
you should be able to cobble a separate script or model from these.
I have the part about creating the model by using the select by layer and copy features, what I am stuck on is how to create a queried table that contains just the information (columns, summarized records, etc) that I am looking for. As I said above, when the I am using a table that is accessible through Microsoft SQL Server Management studio, I can create the table easily. I am looking for any resources to be able to query the copied features that are in a local file geodatabase. I think the query is too complicated to use in the "Query Builder" GUI in ArcMap (10.5 or Pro) and think it might be a candidate for a python script.
Thoughts? Resources for building SQL queries in a python script and then adding these to a model?