10

That question just sounds so redundant...
At any rate, I have been asked to explore the uses of SQL in ArcGIS. We use an SDE DB for all server-side storage and DB managing/large-scale editing. However, I am attempting to delve deeper into just how integrated SQL is in ArcGIS's tools. There is a note in the SQL Query Reference Guide of ArcGIS 10's help that clearly states SQL syntax does not work when using the Field Calculator tool. I find this very disappointing as it would seem very useful to be able to pull and mangle information directly from the database itself when calculating field values.

What are the largest limitations and pitfalls one might fall into when attempting to make use of SQL in ArcGIS? Which tools see best functionality when integrated with SQL?

If an example is required to make this a clearer question, I am attempting to track the number of points of interest that are of a certain type in a given region. Each point has an attribute which describes its type and region. My current solution involves heavy use of cursors for counting and data retrieval, but a superior pointed out that a simple SQL query could do all of this and more. Unfortunately, it seems the functionality is not present for this.

Or is it?

Improve this question
3
  • Are any of the queried tables versioned?
    – Michael Todd
    Mar 22, 2011 at 15:57
  • Versioned? I don't think I'm familiar with that term.
    – Nathanus
    Mar 22, 2011 at 16:17
  • 1
    Versioning a layer allows one to make changes to the layer while retaining prior features. So if a feature is deleted the current version of the layer shows that the feature no longer exists; however, the feature does in fact still exist in the "base" layer and GIS tools can be used to view different versions of the layer that show the feature. You can tell if your layer is versioned by right-clicking the layer in ArcCatalog and selecting Properties. The bottom of the General tab has a section called Versioning and information here will tell you if the layer is versioned.
    – Michael Todd
    Mar 22, 2011 at 16:26

4 Answers 4

Reset to default
9

If you want to use Python take a look at the ArcSDESQLExecute command. This will let you execute arbitrary SQL but it must be connected through SDE. If the result of the query is a set of rows, the attributes will be returned as a Python list of lists. Otherwise it will return True if the SQL executed successfully or None if not. See also: Executing SQL using an ArcSDE connection

Improve this answer
1
  • I definitely will do that! This seems like just what I was looking for, in this context. Thanks a lot.
    – Nathanus
    Mar 22, 2011 at 20:07
5

If you are writing custom ArcObjects, you can execute arbitrary SQL against an SDE Workspace by using the ExecuteSQL method. This method is limited to queries that don't return result sets, such as INSERT, UPDATE, or some stored procedures.

If you are working through ArcMap UI, your options are a bit more limited. The reason why you cannot use SQL through Calculate Field is because the expression is actually VBScript and/or Python. You could use VBScript or Python to execute an arbitrary SQL expression and pull values from the resulting cursor, but I wouldn't recommend it.

Alternatively, you can use Regan's answer and create a Query Layer. For read-only data, this is your best approach. If you need to update data, it's slightly more complicated. You'll need to create a query layer with your "new" values, perform a join against your existing table, and then use calculate fields. This could be combined with the ExecuteSQL to perform complex updates and then view the results (kind of clunky).

Your last alternative is to execute SQL against ArcSDE tables directly.

Improve this answer
5
  • 1
    Your last point (executing SQL against the tables directly) was why I asked my versioning question. A SQL query at the database level only pulls from the base layer (unless one sets up a multiversioned view), so that won't work if the layer is versioned.
    – Michael Todd
    Mar 22, 2011 at 17:29
  • The work I'm doing is mostly in Python, but it seems more and more that the real functionality in the UI relies on ArcObjects.
    – Nathanus
    Mar 22, 2011 at 17:48
  • Everything is built on ArcObjects internally, including the python functionality.
    – James Schek
    Mar 23, 2011 at 14:11
  • if the ArcSDESQLExecute in Python can return results, how can the same be achieved through IWorkspace.ExecuteSQL?
    – Petr Krebs
    Mar 23, 2011 at 22:23
  • @petr you can't directly... I've updated my answer.
    – James Schek
    Mar 23, 2011 at 23:45
1

You should check out Query Layers in ArcMap 10

Improve this answer
1

Depending on what RDBMS you are using, you can leverage SQL libraries outside of ArcObjects or arcpy, as long as you are not depending on getting feature data back. Many of the queries in the SQL Server database I use on a daily basis are not spatial in nature, so I will use the System.Data classes inside of .NET, or the pymssql Python library inside of Python applications. If you do require the use of Spatial data, then Query Layers are your best bet. The only caveat with Query Layers are they only work with data stored using Spatial data types (not the standard SDEBINARY type).

Improve this answer
2
  • This is starting to get intense. I guess I need to more carefully evaluate my needs. I will not be needing "spatial" data, if by that you mean things like the polygons and points/lines themselves, but simply values in text/integer fields.
    – Nathanus
    Mar 22, 2011 at 17:47
  • If you need to return geometry types, then I would use the standard ArcObjects/arcpy data access. If all you are doing is accessing "tabular" data (no spatial types involved) then just make the database calls using regular SqlConnections (for SQL Server - your connection type may vary). The results are more flexible than what you get out of ICursor. If I understand your question though, and you are limited to using the field calculator, then you will not be able to use raw SQL.
    – SagebrushGIS
    Mar 22, 2011 at 20:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.