<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:p="urn:schemas-microsoft-com:office:powerpoint" xmlns:a="urn:schemas-microsoft-com:office:access" xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" xmlns:rs="urn:schemas-microsoft-com:rowset" xmlns:z="#RowsetSchema" xmlns:b="urn:schemas-microsoft-com:office:publisher" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:c="urn:schemas-microsoft-com:office:component:spreadsheet" xmlns:odc="urn:schemas-microsoft-com:office:odc" xmlns:oa="urn:schemas-microsoft-com:office:activation" xmlns:html="http://www.w3.org/TR/REC-html40" xmlns:q="http://schemas.xmlsoap.org/soap/envelope/" xmlns:rtc="http://microsoft.com/officenet/conferencing" xmlns:D="DAV:" xmlns:Repl="http://schemas.microsoft.com/repl/" xmlns:mt="http://schemas.microsoft.com/sharepoint/soap/meetings/" xmlns:x2="http://schemas.microsoft.com/office/excel/2003/xml" xmlns:ppda="http://www.passport.com/NameSpace.xsd" xmlns:ois="http://schemas.microsoft.com/sharepoint/soap/ois/" xmlns:dir="http://schemas.microsoft.com/sharepoint/soap/directory/" xmlns:ds="http://www.w3.org/2000/09/xmldsig#" xmlns:dsp="http://schemas.microsoft.com/sharepoint/dsp" xmlns:udc="http://schemas.microsoft.com/data/udc" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sub="http://schemas.microsoft.com/sharepoint/soap/2002/1/alerts/" xmlns:ec="http://www.w3.org/2001/04/xmlenc#" xmlns:sp="http://schemas.microsoft.com/sharepoint/" xmlns:sps="http://schemas.microsoft.com/sharepoint/soap/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:udcs="http://schemas.microsoft.com/data/udc/soap" xmlns:udcxf="http://schemas.microsoft.com/data/udc/xmlfile" xmlns:udcp2p="http://schemas.microsoft.com/data/udc/parttopart" xmlns:wf="http://schemas.microsoft.com/sharepoint/soap/workflow/" xmlns:dsss="http://schemas.microsoft.com/office/2006/digsig-setup" xmlns:dssi="http://schemas.microsoft.com/office/2006/digsig" xmlns:mdssi="http://schemas.openxmlformats.org/package/2006/digital-signature" xmlns:mver="http://schemas.openxmlformats.org/markup-compatibility/2006" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns:mrels="http://schemas.openxmlformats.org/package/2006/relationships" xmlns:spwp="http://microsoft.com/sharepoint/webpartpages" xmlns:ex12t="http://schemas.microsoft.com/exchange/services/2006/types" xmlns:ex12m="http://schemas.microsoft.com/exchange/services/2006/messages" xmlns:pptsl="http://schemas.microsoft.com/sharepoint/soap/SlideLibrary/" xmlns:spsl="http://microsoft.com/webservices/SharePointPortalServer/PublishedLinksService" xmlns:Z="urn:schemas-microsoft-com:" xmlns:st="" xmlns="http://www.w3.org/TR/REC-html40"><head><meta http-equiv=Content-Type content="text/html; charset=us-ascii"><meta name=Generator content="Microsoft Word 12 (filtered medium)"><style><!--
/* Font Definitions */
@font-face
{font-family:Wingdings;
panose-1:5 0 0 0 0 0 0 0 0 0;}
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:Tahoma;
panose-1:2 11 6 4 3 5 4 4 2 4;}
@font-face
{font-family:"Lucida Bright";
panose-1:2 4 6 2 5 5 5 2 3 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri","sans-serif";}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
p
{mso-style-priority:99;
mso-margin-top-alt:auto;
margin-right:0in;
mso-margin-bottom-alt:auto;
margin-left:0in;
font-size:12.0pt;
font-family:"Times New Roman","serif";}
span.EmailStyle18
{mso-style-type:personal;
font-family:"Calibri","sans-serif";
color:windowtext;}
span.EmailStyle19
{mso-style-type:personal;
font-family:"Calibri","sans-serif";
color:#1F497D;}
span.EmailStyle20
{mso-style-type:personal;
font-family:"Lucida Bright","serif";
color:blue;}
span.EmailStyle21
{mso-style-type:personal;
font-family:"Calibri","sans-serif";
color:#1F497D;}
span.EmailStyle22
{mso-style-type:personal;
font-family:"Calibri","sans-serif";
color:#1F497D;}
span.EmailStyle23
{mso-style-type:personal-reply;
font-family:"Calibri","sans-serif";
color:windowtext;}
.MsoChpDefault
{mso-style-type:export-only;
font-size:10.0pt;}
@page WordSection1
{size:8.5in 11.0in;
margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]--></head><body lang=EN-US link=blue vlink=purple><div class=WordSection1><p class=MsoNormal>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. <o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>…So it might take a few goes before you get exactly what you want <span style='font-family:Wingdings'>J</span><o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><div><p class=MsoNormal><span style='font-size:12.0pt;font-family:"Tahoma","sans-serif"'>Jason A. Griffin</span><span style='font-size:10.0pt;font-family:"Tahoma","sans-serif"'>, Ext. 33613</span> <span style='color:gray'>| BTS Senior Technology Specialist, GIS Dev. | Pinellas County Government, Business Technology Services [BTS] | 400 S Ft. Harrison Ave | Clearwater, FL 33756 | </span>Tel: 727-453-3613 <span style='color:gray'>| Cell: 850-408-6743 | <a href="mailto:JGriffin@co.pinellas.fl.us"><span style='color:blue'>JGriffin@co.pinellas.fl.us</span></a>|<o:p></o:p></span></p></div><p class=MsoNormal><o:p> </o:p></p><div><div style='border:none;border-top:solid #B5C4DF 1.0pt;padding:3.0pt 0in 0in 0in'><p class=MsoNormal><b><span style='font-size:10.0pt;font-family:"Tahoma","sans-serif"'>From:</span></b><span style='font-size:10.0pt;font-family:"Tahoma","sans-serif"'> shrug-l-bounces@lists.dep.state.fl.us [mailto:shrug-l-bounces@lists.dep.state.fl.us] <b>On Behalf Of </b>Keith Sandell<br><b>Sent:</b> Wednesday, July 13, 2011 4:17 PM<br><b>To:</b> shrug-L@lists.dep.state.fl.us<br><b>Subject:</b> RE: shrug-l: ArcGIS 10.1 - What's coming - spatial DB connections<o:p></o:p></span></p></div></div><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal><span style='color:#1F497D'>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.<o:p></o:p></span></p><p class=MsoNormal><span style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='color:#1F497D'>>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.<o:p></o:p></span></p><p class=MsoNormal><span style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='color:#1F497D'>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.<o:p></o:p></span></p><p class=MsoNormal><span style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='color:#1F497D'>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.<o:p></o:p></span></p><p class=MsoNormal><span style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='color:#1F497D'>The benefit of the query layers in dynamic applications is that they effectively create a disconnect between the table and the GIS part. <o:p></o:p></span></p><p class=MsoNormal><span style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='color:#1F497D'>An example:<o:p></o:p></span></p><p class=MsoNormal><span style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='color:#1F497D'>SQL spatial table with current hurricane forecast data is exposed to a REST service via a mxd/msd<o:p></o:p></span></p><p class=MsoNormal><span style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='color:#1F497D'>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.<o:p></o:p></span></p><p class=MsoNormal><span style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='color:#1F497D'>Once you create the web service you don’t have to stop, restart, refresh…it just comes through.<o:p></o:p></span></p><p class=MsoNormal><span style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='color:#1F497D'>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.<o:p></o:p></span></p><p class=MsoNormal><span style='color:#1F497D'><o:p> </o:p></span></p><div><p class=MsoNormal><b><span style='font-family:"Times New Roman","serif";color:#1F497D'>Keith Sandell, MBA<o:p></o:p></span></b></p><p class=MsoNormal><span style='font-size:10.0pt;font-family:"Times New Roman","serif";font-variant:small-caps;color:gray'>GIS Manager, Corporate Analytics<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:10.0pt;font-family:"Times New Roman","serif";font-variant:small-caps;color:gray'>Citizens Property Insurance Corporation<o:p></o:p></span></p><p class=MsoNormal style='line-height:5.0pt;mso-line-height-rule:exactly'><span style='font-size:10.0pt;font-family:"Times New Roman","serif";color:gray'>-------------------------------------------------------<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:10.0pt;font-family:"Times New Roman","serif";color:gray'>ofc. 850.521.8341 | cell 850.727.2897</span><o:p></o:p></p></div></div></body></html>