Are joins at MXD level supported by Geocortex?
Hi,
I believe i have uncovered either a design short-coming or a bug in Geocortex 3.5. For further info regarding the incident, please refer to (http://support.geocortex.com/Forums/Thread.aspx?pageid=0&mid=2&ItemID=7&thread=46666) Feature Report: Error performing the query .
While i am aware of the fact introducing a join changes the manner in which the rest endpoint references a field (it becomes fully qualified), I am having trouble getting it to work with reporting.
Can anyone withing @Latitude confirm this is a known issue (or design contrainst)?
I wish to use join @ mxd level to enable thematic mapping based on the joined attributes. As the rest endpoint recognized the join, i leaning towards this being a bug.
Appreciate fast response on this to enable a work around.
Brad
-
Forgot to add, i have repeated the same report with the layer without the JOIN & it works.. Hence I believe this is a direct consequence of the join & geocortex not handling field name convention with its object binding for use by active reports.
Brad
0 -
Further investigation, if i 'Add Fields' through manager for the attributes associated with the layer with joined attributes, I get a stack-trace error on site initialisation;
04/19/2012 11:10:44:653: CRITICAL: Unhandled exception occured: Geocortex.EssentialsSilverlightViewer.Infrastructure.Exceptions.CriticalViewerException: Cannot open configuration file. ---> System.Net.WebException: The remote server returned an error: NotFound. ---> System.Net.WebException: The remote server returned an error: NotFound.
at App.OnUnhandledException(Object sender, ApplicationUnhandledExceptionEventArgs e) Geocortex.EssentialsSilverlightViewer.Viewer
at EventHandler`1.Invoke(Object sender, TEventArgs e) System
at Errorstatic .CallApplicationUEHandler(Exception e) MS.Internal
at Errorstatic .GetXresultForUserException(Exception ex) MS.Internal
at DispatcherOperation.Invoke() System.Windows.Threading
at Dispatcher.Dispatch(DispatcherPriority priority) System.Windows.Threading
at Dispatcher.OnInvoke(Object context) System.Windows.Threading
at CallbackCookie.Invoke(Object[] args) System.Windows.Hosting
at ScriptingInterfacestatic .InvokeDelegate(DelegateWrapper delegateWrapper, ScriptParam[] pParams, ScriptParam& pResult) System.Windows.Hosting
at ManagedHoststatic .InvokeDelegate(IntPtr pHandle, Int32 nParamCount, ScriptParam[] pParams, ScriptParam& pResult) System.Windows.HostingDetails:
at System.Net.Browser.BrowserHttpWebRequest.InternalEndGetResponse(IAsyncResult asyncResult)
at System.Net.Browser.BrowserHttpWebRequest.<>c__DisplayClass5.<EndGetResponse>b__4(Object sendState)
at System.Net.Browser.AsyncHelper.<>c__DisplayClass4.<BeginOnUI>b__1(Object sendState)
--- End of inner exception stack trace ---
at System.Net.Browser.AsyncHelper.BeginOnUI(SendOrPostCallback beginMethod, Object state)
at System.Net.Browser.BrowserHttpWebRequest.EndGetResponse(IAsyncResult asyncResult)
at System.Net.WebClient.GetWebResponse(WebRequest request, IAsyncResult result)
at System.Net.WebClient.OpenReadAsyncCallback(IAsyncResult result)
--- End of inner exception stack trace ---
at Geocortex.EssentialsSilverlightViewer.Infrastructure.Configuration.ConfigurationTree.WebClientOpenReadCompleted(Object sender, OpenReadCompletedEventArgs e)
at System.Net.WebClient.OnOpenReadCompleted(OpenReadCompletedEventArgs e)
at System.Net.WebClient.OpenReadOperationCompleted(Object arg)Logging a formal support call to get greater clarity around this issue.
0 -
Hi Brad,
I haven't seen this as a challenge, but also highly discourage making "data changes" in the ArcGIS Server application stack if using ArcSDE/Oracle/SQL. Is there a reason you are using this technique over a proper database view with joins specified?
ArcGIS is not efficient at database operations, because fundamentally it isn't a database. Making joins using views that are registered as spatial in ArcSDE get optimized by the database and thus are typically faster, more memory efficient and scalable. You can create spatial views by using the ArcSDE command "sdetable -o create_table" where you at least include the shape and objectid fields from the layer. In the command's where clause, you define the join logic. This creates a new view in the database, which appears as a completely separate (new) layer. Add this to your MXD and avoid doing any data manipulation in the database, working around your problem, increasing performance and system stability too.
Now if you are using file/non-enterprise geodatabases, then unfortunately the only way to create joins is in the MXD. But if using an enterprise database, doing data operations in the application tier is a generally discouraged for any systems integration implementation teachings I've learned.
0 -
Shawn,
We are trying to use SDE spatial views here, but are not getting multiple records on an ID'd polygon when there is a one-to-many relationship. Only the first record in the join is being identified. The arcmap ID returns 5 records from the same view.
It seems that this functionality worked at one point, but now does not. (Can you verify that this is a limitation (if it is)?
Thanks,Jim
GCE 3.8
SLV 1.5
0 -
Nice one Shawn , as i wasn't too concerned about performance (given size of my datasets) i was a little tunnel vision in my solution & ignored the obvious re spatial view. Thanks for reminding me of this. Seems to be working quite nicely..
Jim - perhaps have a bit of a read of (http://www.jamesrichards.com/post/2009/07/16/How-To-Create-an-ArcSDE-Spatial-View-With-an-Outer-Join.aspx) How To Create an ArcSDE Spatial View With an Outer Join or the Online Help @ ESRI's resource pages as i suspect the devil is in the detail.
Brad
0 -
Brad,
I had tested the Left Outer Join in the past with little luck. The issue is that the related records are being returned, but the Identify only returns a single geometry. In ArcMap when I open the view's table, it shows me only 424 records. When I select a polygon in arcmap, it shows only one record in the table, but the bottom says 5 out of 424 selected (See attached) which is a correct result, but I need 5 results in the results table (in Geocortex).
The view syntax is below. (Note that when I run the query in SQL Management Studio, I get the correct 1139 records returned.
SELECT TOP (100) PERCENT GISLOADER.CTY_PLAN_OF_OPERATION.OBJECTID, GISLOADER.CTY_PLAN_OF_OPERATION.Shape, PR.FILE_NUM, PR.MAPID,
PR.OPERATIONNAME, PR.COMMENTS, GISLOADER.CTY_PLAN_OF_OPERATION.MAPID AS MAPID_PR
FROM GISLOADER.CTY_PLAN_OF_OPERATION LEFT OUTER JOIN
GISLOADER.CTY_PLANOPRELATE AS PR ON GISLOADER.CTY_PLAN_OF_OPERATION.MAPID = PR.MAPID
ORDER BY PR.FILE_NUMI would be interested to know if any other users have had success seeing more than one result in the results panel for a 1:many spatial view.
(https://support.geocortex.com/Data/Sites/1/userfiles/773/screenshot008.png) /Data/Sites/1/userfiles/773/screenshot008.png
0 -
Jim and Brad,
I've used spatial views in Geocortex successfully. The join was created using the sdetable -o create_view where the join condition was simple equals (the backend was Oracle last I tried). In this example, the join took one spatial record and produced 10's or even on a rare case 100's of duplicated records (address points). When you identified on one point, you'd get a separate feature for each combination of the single spatial feature with one for each of the many related records.
I recommend reviewing the SQL code generated after the sdetable -o create_view SQL statement and tweaking the view creation to get the results you want. Once you bring it in, the view should be visible to ArcGIS Desktop as a layer. It will not be a "join" or "link" in the ArcMap environment, instead just a be treated as any normal feature class. Once you get here, ArcGIS Server will see it normally too (test against the REST interface for that layer to determine if it returns the many-records, as desired). Then Geocortex will work.
Simply put, Geocortex just sees what is coming back from the layer search against the ArcGIS Server REST interface. If that doesn't return what you want to see, you'll need to change how the data are seen by the system to make it work.
Thanks,
Shawn.0 -
Jim, i can't answer your query right now as nothing is obvious to me but this form (http://forums.arcgis.com/threads/56581-Spatial-View-one-to-many-relationships-unexpected-results?p=194319#post194319) http://forums.arcgis.com/threads/56581-Spatial-View-one-to-many-relationships-unexpected-results?p=194319#post194319 may give you some ideas as to understanding what is going on.
Or are you saying your 1 to many query (where you have 1 spatial record to many attribute records) is not behaving as you would expect? If so, then i think the above forum is relevant as it highlights the fact a one 2 many is not supported in spatial views. That said, i'm intrigued re Shawn's scenario (its not quite clear to me on his relationship that is resulting in duplicate records?) Shawn, intrigued, are you tweaking your 'join' in the view to get duplicates?
Brad
0 -
Brad, the way we got around the problem was answered in the second reply to that arcgis forum post: add a unique key. This made each record appear unique to ArcSDE and thus appears like it would. Therefore a complete "data" solution. Registering the related table in ArcGIS will produce an ObjectID field. Include that field in the final join, rather than the one from the spatial table.
Shawn.
0
Please sign in to leave a comment.
Comments
9 comments