Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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?

share|improve this question
    
Are any of the queried tables versioned? –  Michael Todd Mar 22 '11 at 15:57
    
Versioned? I don't think I'm familiar with that term. –  Nathanus Mar 22 '11 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 '11 at 16:26

4 Answers 4

up vote 7 down vote accepted

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

share|improve this answer
    
I definitely will do that! This seems like just what I was looking for, in this context. Thanks a lot. –  Nathanus Mar 22 '11 at 20:07

You should check out Query Layers in ArcMap 10

share|improve this answer

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.

share|improve this answer
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 '11 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 '11 at 17:48
    
Everything is built on ArcObjects internally, including the python functionality. –  James Schek Mar 23 '11 at 14:11
    
if the ArcSDESQLExecute in Python can return results, how can the same be achieved through IWorkspace.ExecuteSQL? –  Petr Krebs Mar 23 '11 at 22:23
    
@petr you can't directly... I've updated my answer. –  James Schek Mar 23 '11 at 23:45

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).

share|improve this answer
    
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 '11 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 '11 at 20:07

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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