shrug-l: ArcGIS 10.1 - What's coming - spatial DB connections

Griffin, Jason jgriffin at co.pinellas.fl.us
Mon Jul 18 10:01:07 EDT 2011


There is a sister/cousin? tool called:  "Make Query Table," that acts very similar to the Query Layers.  It's also available as far back as 9.3.1, possibly further, but it's a little tricky to use.

...So it might take a few goes before you get exactly what you want :)

Jason A. Griffin, Ext. 33613 | BTS Senior Technology Specialist, GIS Dev. | Pinellas County Government, Business Technology Services [BTS] | 400 S Ft. Harrison Ave | Clearwater, FL  33756 | Tel: 727-453-3613 | Cell: 850-408-6743 | JGriffin at co.pinellas.fl.us<mailto:JGriffin at co.pinellas.fl.us>|

From: shrug-l-bounces at lists.dep.state.fl.us [mailto:shrug-l-bounces at lists.dep.state.fl.us] On Behalf Of Keith Sandell
Sent: Wednesday, July 13, 2011 4:17 PM
To: shrug-L at lists.dep.state.fl.us
Subject: RE: shrug-l: ArcGIS 10.1 - What's coming - spatial DB connections

Query layers are pretty cool things. I recently completed a web application that uses them as all of the data is in SQL Spatial Types.

>From an existing ODBC connection you can view the tables in the DB and select the table you want, it does not provide any capacity to edit them, it is read only.

Right now query layers can only be created in ArcMap, at 10.1 the python library will be updated to allow for programmatic connections, or so I was told at the Dev Summit by a Product Mgr.

You can use some very minor SQL syntax in creating the query to access the data, but beware that within the query layer dialog box you are actually looking at a view of the table. Query layers do not access the table directly. They did this to create one (1) interface that can connect to the 4 or 5 different spatial databases they support. Only very limited sql is allowable in a view and even some that is legal will generate an error. The docs say that "any" legal sql stmt can be used. They even go to the extent to say you can copy and paste from something like SQL Server Mgmt Studio; this is inaccurate and it will not work all the time because you are pasting into the design of a table view.

The benefit of the query layers in dynamic applications is that they effectively create a disconnect between the table and the GIS part.

An example:

SQL spatial table with current hurricane forecast data is exposed to a REST service via a mxd/msd

You can make a change to the data in SQL without hitting any locks or other hiccups and the change will propagate up through the service to the web app either on reload of the app or if you have a function to do an ajax call to refresh the data in the current session of the application.

Once you create the web service you don't have to stop, restart, refresh...it just comes through.

I've been living and breathing these things for the past 3 months so if anyone has any questions about spatial data in SQL (I know a teensy bit about Oracle as well) and/or query layers feel free to drop me a line.

Keith Sandell, MBA
GIS Manager, Corporate Analytics
Citizens Property Insurance Corporation
-------------------------------------------------------
ofc. 850.521.8341   |   cell 850.727.2897
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.dep.state.fl.us/pipermail/shrug-l/attachments/20110718/b5fb4425/attachment.htm


More information about the SHRUG-L mailing list