Distinct Query?

[RobChouinard]

Why is there no "Return Distinct Values" in Query? Am I missing it somewhere?

I am running ArcGIS Server 10.1.1 and Silverlight API 3.1. On the Query form for a REST service layer there is a "Return Distinct Values" and works as expected. It is exactly what I am looking for. As a workaround I have views made for about a dozen fields. It would be really nice to have this flexibility especially since the feature is already on the server side.

Rob

[vipulsoni]

Hi,

While developing an application I too faced the same hurdle and I guess my workaround might help you out.

First of all , as per the below link - Because you are selecting columns as a whole, you cannot restrict the SELECT to return only some of the columns in the corresponding table because the SELECT * syntax is hard-coded. For this reason, keywords, such as DISTINCT, ORDER BY, and GROUP BY, cannot be used in a SQL query in ArcGIS except when using subqueries. 

http://help.arcgis.com/en/arcgisdesktop/10.0/help/index.html#//00s50000002t000000

What I did is to get the results in a string Array and then apply distinct to the array....also further then I applied sort to it.
see the code pasted below it will give you an idea. but yes for getting the distinct through the API i am also unaware , this all i     posted is a workaround.
---------------------------------------------------------------------------------------------------

int f = 0;

foreach (var g in e.FeatureSet.Features)
                {

myStringArray = g.Attributes["Title"].ToString();
f++;
                    }

string[] unique = myStringArray.Distinct().ToArray();

Array.Sort(unique);

foreach(string s in unique)
                {

                 }

-------------------

also have a look at this post..

http://forums.esri.com/Thread.asp?c=213&f=2455&t=281270

[RobChouinard]

That help link is for ArcGIS Desktop. I have/do used LINQ to get distinct attribute values from a FeatureSet after querying all rows. This is ok for small to medium sized tables. For a very large table this won't do and is not practical. What I would like to do is utilize the new "Return Distinct Values" boolean variable when querying a REST MapService Layer as seen at the bottom of the page here: http://sampleserver6.arcgisonline.com/arcgis/rest/services/EmergencyFacilities/MapServer/0/query?whe...

I plan on using ArcGISWebClient and FeatureSet.FromJson() to build a QueryDistinctTask class. If all goes well I will post it here.

What I really was looking for was an out of the box Silverlight API feature. Since it is a feature of ArcGIS Server why did ESRI not build this feature in Silverlight API? Or maybe I can't find it.

Rob

[RobChouinard · Accepted solution]

Well I wrote my own class to handle this and I am here to share. Is there a better place to share code like this?

Thanks to ESRI for making this easier by providing ArcGISWebClient and FeatureSet.FromJson() in the SDK.

QueryDistinctTask.cs

/**  * Created by Rob Chouinard  *   * Free to use and destroy!  * Requires Silverlight API 3.1+ and ArcGIS Server 10.1 SP1+  * Use is similar to ESRI.ArcGIS.Client.Tasks.QueryTask  **/  using System; using System.ComponentModel; using System.Collections.Generic; using ESRI.ArcGIS.Client; using ESRI.ArcGIS.Client.Tasks;  namespace ESRI.ArcGIS.Extensions {     public class QueryDistinctTask     {         ArcGISWebClient webClient = null;          /// <summary>         /// Initializes a new instance of the <seealso cref="QueryDistinctTask"/> class.         /// </summary>         /// <param name="Url">The URL of the task.</param>         public QueryDistinctTask(string Url = "")         {             this.Url = Url;         }          #region Properties         /// <summary>         /// If true, adds a timestamp parameter ("_ts") to the request to prevent it from being loaded from the browser's cache.         /// </summary>         public bool DisableClientCaching          {             get { return disableClientCaching; }              set             {                 if (disableClientCaching != value)                 {                     disableClientCaching = value;                     OnPropertyChanged("DisableClientCaching");                 }             }         }         bool disableClientCaching = false;          /// <summary>         /// Gets a value that indicates whether a Web request is in progress.         /// </summary>         public bool IsBusy         {             get             {                 if (webClient != null)                     return webClient.IsBusy;                 else                     return false; ;             }         }          /// <summary>         /// Gets or sets the URL to proxy the request through.           /// </summary>         public string ProxyURL         {             get { return proxyURL; }             set             {                 if (proxyURL != value)                 {                     proxyURL = value;                     OnPropertyChanged("ProxyURL");                 }             }         }         string proxyURL;          /// <summary>         /// Gets or sets the last query result.         /// </summary>         public FeatureSet LastResult          {             get { return lastResult; }             private set             {                 if (lastResult != value)                 {                     lastResult = value;                     OnPropertyChanged("LastResult");                 }             }         }         FeatureSet lastResult;          /// <summary>         /// Gets or sets the URL of the task.           /// </summary>         public string Url         {             get { return url; }             set             {                 if (url != value)                 {                     url = value;                     OnPropertyChanged("Url");                 }             }         }         string url;         #endregion          #region Events         /// <summary>         /// Occurs when the query completes.         /// </summary>         public event EventHandler<QueryDistinctEventArgs> ExecuteCompleted;          /// <summary>         /// Occurs when the query completes and fails.         /// </summary>         public event EventHandler<AsyncCompletedEventArgs> Failed;          /// <summary>         /// Occurs when a property value changes.         /// </summary>         public event PropertyChangedEventHandler PropertyChanged;         #endregion          #region Public Methods         /// <summary>         /// Cancels a pending asynchronous operation.         /// </summary>         public void CancelAsync()         {             if (webClient != null)                 webClient.CancelAsync();         }          /// <summary>         /// Executes a query against an ArcGIS Server map layer. The result is returned as a FeatureSet. If the query is successful, the <seealso cref="QueryDistinctTask.ExecuteCompleted"/> event is raised with the result. A FeatureSet contains an array of Graphic features without geometery. This array will not be populated if no results are found.         /// </summary>         /// <param name="query">Specifies the attributes filter of the query.</param>         /// <param name="userToken">A user-defined object that is passed to the method invoked when the asynchronous operation completes.</param>         public void ExecuteAsync(QueryDistinct query, object userToken = null)         {             webClient = new ArcGISWebClient();             webClient.DisableClientCaching = DisableClientCaching;             webClient.ProxyUrl = ProxyURL;             webClient.DownloadStringCompleted += DownloadStringCompleted;              string extendedUrl = Url;             if (!Url.EndsWith("/"))                 extendedUrl += "/";             extendedUrl += "query";              if (!webClient.IsBusy)                 webClient.DownloadStringAsync(new Uri(extendedUrl), GetParameters(query), ArcGISWebClient.HttpMethods.Auto, userToken);             else             {                 throw new Exception("The web client is busy processing it's current task. \nWait for the current process to complete before processing another request.");             }         }         #endregion          #region Private Methods         IDictionary<string, string> GetParameters(QueryDistinct query)         {             IDictionary<string, string> parameters = new Dictionary<string, string>();             if (query.Where != null && query.Where.Length > 0)                 parameters.Add("where", query.Where);             else if (query.Text != null)                 parameters.Add("text", query.Text);              parameters.Add("outFields", string.Join(",", query.OutFields)); // Return all of the fields.             parameters.Add("returnGeometry", "false"); // Do not include the geometry coordinate values.             parameters.Add("returnDistinctValues", "true"); // Since that is what we are after.             parameters.Add("f", "json"); // Get the results back in the 'pretty' JSON format.              return parameters;         }          void DownloadStringCompleted(object sender, ArcGISWebClient.DownloadStringCompletedEventArgs e)         {             if (e.Error != null || e.Cancelled)             {                 OnFailed(new AsyncCompletedEventArgs(e.Error, e.Cancelled, e.UserState));                 return;             }              if (e.Cancelled)                 return;              try             {                 FeatureSet featureSet = FeatureSet.FromJson(e.Result);                 int count = featureSet.Features.Count;                 lastResult = featureSet;                 QueryDistinctEventArgs queryEventArgs = new QueryDistinctEventArgs(featureSet)                 {                     UserState = e.UserState                 };                 OnExecuteCompleted(queryEventArgs);             }             catch (Exception)             {                 try                 {                     ServiceException se = ServiceException.FromJson(e.Result);                     OnFailed(                         new AsyncCompletedEventArgs(se, e.Cancelled, e.UserState));                 }                 catch (Exception)                 {                     OnFailed(                     new AsyncCompletedEventArgs(                         new Exception("Failed to parse featureSet from json.\nCheck your URL and query."),                         e.Cancelled,                         e.UserState)                     );                 }             }         }          void OnFailed(AsyncCompletedEventArgs asyncCompletedEventArgs)         {             if (Failed != null)                 Failed(this, asyncCompletedEventArgs);         }          void OnPropertyChanged(string Name)         {             if (PropertyChanged != null)                 PropertyChanged(this, new PropertyChangedEventArgs(Name));         }          void OnExecuteCompleted(QueryDistinctEventArgs queryEventArgs)         {             if (ExecuteCompleted != null)                 ExecuteCompleted(this, queryEventArgs);         }         #endregion     }      public class QueryDistinct     {         public OutFields OutFields { get; set; }         public string Text { get; set; }         public string Where { get; set; }          public QueryDistinct()         {             OutFields = new OutFields();         }     }      public class QueryDistinctEventArgs : EventArgs     {         public FeatureSet FeatureSet { get; private set; }         public object UserState { get; set; }          public QueryDistinctEventArgs(FeatureSet featureSet)         {             FeatureSet = featureSet;         }     } }

[DominiqueBroux]

The 'returnDistinctValues' option has been added recently at the server side (10.1 SP1) and is not yet exposed by the Silverlight API.
We'll try to add this capability in a future version.

Thanks for sharing your workaround, might be useful for others having the same need.