topic How can I create tables in ArcMap using SQL and/or Scripting? in Python Questions

How can I create tables in ArcMap using SQL and/or Scripting?

ChipBrownHBG — Thu, 19 Apr 2018 15:41:28 GMT

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
from [EnterpriseDatabase]
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.

Thanks.

Chip

Re: How can I create tables in ArcMap using SQL and/or Scripting?

DanPatterson_Retired — Thu, 19 Apr 2018 18:05:43 GMT

Select by Attributes followed by a Copy Features... see the script examples

http://pro.arcgis.com/en/pro-app/tool-reference/data-management/select-layer-by-attribute.htm

http://pro.arcgis.com/en/pro-app/tool-reference/data-management/copy-features.htm

you should be able to cobble a separate script or model from these.

Re: How can I create tables in ArcMap using SQL and/or Scripting?

ChipBrownHBG — Tue, 24 Apr 2018 12:57:23 GMT

Thanks, Dan.

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?