topic Re: Drill-down Geocoder in ArcGIS Runtime SDK for WPF (Retired) Questions

Drill-down Geocoder

MichaelErlich — Mon, 15 Aug 2011 13:57:26 GMT

We are trying to create a drill-drown geo-coder similar to what see in tom-tom/garmin systems in your vehicle where you select state, then select city, then select street name. Using the 'Find Local Layer' with a little re-tooling, we have been able to search for attributes in the map meta-data. However, we have not been able to add a filter with the search text. In other words, we can search for a street by the first few letters entered by the user, but have not found a way to also add in the city/state criteria to the search. Are there other ways to the use the FindTask to provide multiple search criteria rather than just setting the 'SearchText' field in the FindParameters, is there an extended version of the FindParameters that we are missing, or are we way off base and possibly you have some other suggestions on how we can achieve the same searching capability?

Thanks,
Mike

Re: Drill-down Geocoder

MichaelErlich — Mon, 15 Aug 2011 15:19:12 GMT

Correction, found the QueryTask which does allow more complex queries. However, next question is: can a query be case-insensitive for text?

Re: Drill-down Geocoder

JamesMcElroy — Tue, 16 Aug 2011 03:08:19 GMT

Correction, found the QueryTask which does allow more complex queries. However, next question is: can a query be case-insensitive for text?

Related to this, in the API for the "Where" parameter for the Query object it says this: "A where clause for the query. Any legal SQL where clause operating on the fields in the layer is allowed, for example: where=POP2000 > 350000 ."

Does this mean we can use the "UPPER" keyword? If so then we can just upper both sides and don't have to worry about case.

Thanks,

James

Re: Drill-down Geocoder

KerrieScholefield — Tue, 16 Aug 2011 10:25:43 GMT

Hi

The QueryTask can be case insensitive for text by using a legal SQL clause as suggested by James. The suggested method of the UPPER keyword works as shown in the code snippet below:

      QueryTask queryTask = new QueryTask();
      queryTask.Url = _LocalDynamicLayer.Url + "/0";
      queryTask.ExecuteCompleted += QueryTask_ExecuteCompleted;
      queryTask.Failed += QueryTask_Failed;
      ESRI.ArcGIS.Client.Tasks.Query query = new ESRI.ArcGIS.Client.Tasks.Query();
      query.OutFields.AddRange(new string[] { "AREANAME"});
      query.ReturnGeometry = false;
      query.Where = "UPPER(AREANAME) = UPPER('hilo')";
      queryTask.ExecuteAsync(query, "initial");

I hope this helps.

Cheers

Kerrie

Re: Drill-down Geocoder

MichaelErlich — Tue, 16 Aug 2011 13:19:55 GMT

That suggestion begs the question on how are the fields searched? Does the QueryTask look at each row of data from start to finish, or is it pre-indexed to speed up searching? Or, can it be forced to be pre-indexed in any way? My concern is that by forcing the UPPER() in the search will add a performance bottle-neck. Hence, I'm leaning towards a standardization process ahead of time to force all text to UPPER so the extra conversion is not needed during searching.

Re: Drill-down Geocoder

KerrieScholefield — Thu, 18 Aug 2011 12:05:46 GMT

Hi,

Using the UPPER function means that the queryTask won't take advantage of indexes so it will slow down performance.

Another SQL where clause that helps with cases insensitivity is using the OR Operator. This is faster than using the UPPER function and maybe useful in your case.

query.Where = "AREANAME = 'HILO' OR AREANAME = 'Hilo'";

In terms of indexes, do you have any attribute indexes on your fields? Are you using File, Personal, or ArcSDE Geodatabase for your data?

Cheers,

Kerrie

Re: Drill-down Geocoder

MichaelErlich — Thu, 18 Aug 2011 15:05:59 GMT

I did not initially create indexes. Adding indexes made a world of difference in performance. I'm still not crazy about using 'OR' or 'UPPER'. A pre-processing step to convert all search fields to one casing ahead of time and then convert user-entered criteria to the same case prior to searching appears to be the optimal way to get good performance.

Thanks,
Mike

Re: Drill-down Geocoder

KerrieScholefield — Thu, 18 Aug 2011 16:11:46 GMT

Hi,

I am glad indexes improved your performance. Are you using a DBMS to store your data in? There are ways to change the case sensitivity in DBMS where case sensitivity is controlled by the collation the database uses.

Often DBMS have other performance tuning functionalities that may be worth investigation for queries and searching as it is a common problem scenario.

Cheers

Kerrie

Re: Drill-down Geocoder

MichaelErlich — Thu, 18 Aug 2011 18:28:43 GMT

We are not using any full DB solution as that is not an option in our environment. However, using the advice from a different thread combined with the indexing, we now have a good solution by using the QueryTask for searching state and city, and using the locator for searching streets (which provides a bit more flexibility in searching streets.

Re: Drill-down Geocoder

MichaelErlich — Thu, 08 Sep 2011 13:20:47 GMT

Update... The locator will not work for providing a list of street names when only a couple of characters are provided.
For example: 'Will', 'Willo' return no results. While, 'Willow' or 'Willow Rd' will return results for 'Willow Rd'.

Using a query in the query task of: (L_ORDER08 = 'CO123' or R_ORDER08 = 'CO123') and UFULLNAME LIKE '%ERIE P%' gives a timeout exception when searching across file consisting of 10 states. All three of those fields have been indexed.

So, any suggestions, or is the runtime not capable of performing this task?

Re: Drill-down Geocoder

KerrieScholefield — Fri, 09 Sep 2011 15:20:31 GMT

Update... The locator will not work for providing a list of street names when only a couple of characters are provided.
For example: 'Will', 'Willo' return no results. While, 'Willow' or 'Willow Rd' will return results for 'Willow Rd'.

Hi,

Are you using the QueryTask for your locator?

Using a query in the query task of: (L_ORDER08 = 'CO123' or R_ORDER08 = 'CO123') and UFULLNAME LIKE '%ERIE P%' gives a timeout exception when searching across file consisting of 10 states. All three of those fields have been indexed.

So, any suggestions, or is the runtime not capable of performing this task?

The runtime is capable of performing the valid OR SQL query.

query.Where = "AREANAME = 'Hilo' OR AREANAME = 'Troy'";

The runtime is also capable of performing the LIKE with % wildcard SQL query.

query.Where = "AREANAME LIKE '%la%'";

What type of file are you searching?

Cheers

Kerrie

Re: Drill-down Geocoder

MichaelErlich — Fri, 09 Sep 2011 16:00:15 GMT

Yes we are using the QueryTask, but it keeps timing out. The data we are using consists of 13 U.S. states using a geo database in a mappack.

Can a locator lookup by partial text? I.e. If I enter 'M', return everything with 'M...'. If I enter 'Mi', return everything with 'Mi...'. So far we have not been able to get the locator to work unless the search name is near complete. Also, when looking at the REST call for the locator, there are a bunch of properties for specifying the sensitivity settings, but I don't see any way to set those settings with the locator API. Am I missing something there? Also, to create a locator in arcmap, is there any way to specify the default sensitivity settings?

Update: double clicking on the apk to edit the sensitivity settings didn't have any effect. Uncompressing the apk, and changing the settings in the .loc file, then recompressing had no effect as well and it reverted back to its original default settings.

Re: Drill-down Geocoder

MichaelErlich — Tue, 13 Sep 2011 16:50:50 GMT

Found out about the address locator properties to edit the settings which do get used (my hack method must have missed something that forced the changes to be ignored). However, even after changing the settings, it doesn't produce any candidates if I only type the first few characters. Is there anyway for this type of search to return candidates?

Re: Drill-down Geocoder

KerrieScholefield — Wed, 14 Sep 2011 08:04:37 GMT

Yes we are using the QueryTask, but it keeps timing out. The data we are using consists of 13 U.S. states using a geo database in a mappack.

Hi,

I am unable to reproduce the time out issue with the QueryTask with a US states dataset. Is it possible for you to send a sample application and data with which I could try to reproduce the issue?

Can a locator lookup by partial text? I.e. If I enter 'M', return everything with 'M...'. If I enter 'Mi', return everything with 'Mi...'. So far we have not been able to get the locator to work unless the search name is near complete. Also, when looking at the REST call for the locator, there are a bunch of properties for specifying the sensitivity settings, but I don't see any way to set those settings with the locator API. Am I missing something there? Also, to create a locator in arcmap, is there any way to specify the default sensitivity settings?

Found out about the address locator properties to edit the settings which do get used (my hack method must have missed something that forced the changes to be ignored). However, even after changing the settings, it doesn't produce any candidates if I only type the first few characters. Is there anyway for this type of search to return candidates?

No, the locator cannot lookup by partial text as our current technology and design does not support the "drill-down" search. The spelling sensitivity setting specifies the variation the address locator allows when it searches for likely candidates but it will not perform the partial text search you describe above. If you enter 'Mark' it will return similar spellings as match candidates such as �??Marcel�?? but not all the words containing �??Mark�??.

The sensitivity settings are not set through the API but as you discovered by double-clicking the address locator in the workspace to display the Address Locator Properties dialog box and editing the sensitivity settings. To use these changes in the .apk you will need to rebuild the locator and then republish to .apk. This can be done in ArcCatalog by the steps below:

To change the spelling sensitivity - right click on the locator > Properties > Geocoding options and change the spelling sensitivity (maybe try the value of 1 to test) > OK

To keep the settings - right click the locator > Rebuild

To get the .apk to reflect these changes - right click the locator > Share As > Locator Package

It is worth remembering that the spelling sensitivity does not affect the match score of each candidate; it only controls how many candidates the address locator considers. The address locator computes the match score of each candidate and ranks the candidates by score.

It might also be worth checking what the minimum candidate score and minimum match score are in the locator properties as these also filter the results whereby those potential matches that have a score lower than the minimum candidate score are removed from the potential match list and an address below the minimum match score is considered to have no match.

Cheers

Kerrie

Re: Drill-down Geocoder

JamesMcElroy — Wed, 14 Sep 2011 08:30:59 GMT

Hi Kerrie,

Either Mike B or Ralf should already have the data, as it's the same stuff we've been using for all our other issues. It's called MergedWest.mpk.

I don't have a sample app for you, but just using the QueryTask against the Streets layer of that data with a where clause like "NAME like '%SEARCH_STRING%'". I tried searching for a street called "E Northern Lights Blvd" starting letter by letter. So first it was '%N%', then it was '%No%' (we've since added a UNAME column with the names upper-cased, but you see the same behavior either way. Where it started timing out for me was about '%Norther%'. Any letters more than about 5 and it returned timeout exceptions. That layer has about 9,000,000 records in it, and we've also added indexes to the columns we are searching.

I'm also uploading the map pack with the UName column and indexes to our FTP site, you can get the details from Ralf or Mike B. It's called MergedWestUC.mpk. It's just over a gig, so it'll probably take a bit to complete. If you're still unable to reproduce then I'll put together a simple sample that replicates what we're doing for you.

Thanks,

James
If you still can't

Re: Drill-down Geocoder

MichaelErlich — Wed, 14 Sep 2011 13:43:30 GMT

Kerrie,

If you can provide your email, we can provide the ftp login information so that you can get the files that we are using for testing.

Regards,
mike

Re: Drill-down Geocoder

KerrieScholefield — Wed, 14 Sep 2011 14:31:00 GMT

Kerrie,

If you can provide your email, we can provide the ftp login information so that you can get the files that we are using for testing.

Regards,
mike

Hi,

I am in the process of downloading the data from your FTP site now and will test it once this is complete.

Cheers,

Kerrie

Re: Drill-down Geocoder

KerrieScholefield — Sat, 11 Dec 2021 20:52:02 GMT

I don't have a sample app for you, but just using the QueryTask against the Streets layer of that data with a where clause like "NAME like '%SEARCH_STRING%'". I tried searching for a street called "E Northern Lights Blvd" starting letter by letter. So first it was '%N%', then it was '%No%' (we've since added a UNAME column with the names upper-cased, but you see the same behavior either way. Where it started timing out for me was about '%Norther%'. Any letters more than about 5 and it returned timeout exceptions. That layer has about 9,000,000 records in it, and we've also added indexes to the columns we are searching.

Hi,

I performed the query "UNAME LIKE '%NORTHER%'"; against the UNAME column of the Streets layer in MergedWestUC.mpk successfully and could not reproduce the issue you are experiencing. Where are you accessing your .mpk from? Below is the csharp code behind I am using in my test application - please let me know if/how your code differs:

using System.Windows;
using ESRI.ArcGIS.Client.Local;
using ESRI.ArcGIS.Client.Tasks;

namespace QueryTest
{
  public partial class MainWindow : Window
  {

    public MainWindow()
    {
      InitializeComponent();

      LocalMapService mapService = new LocalMapService()
      {
        Path = @"C:\forums\zoll\MergedWestUC.mpk",
        MaxRecords = 5000000
      };

      mapService.StartAsync();
      mapService.StartCompleted += (s1, e1) =>
      {
        if (mapService.Error == null)
        {
          query(mapService);
        }
      };
    }
    private void query(LocalMapService ms)
    {   
      QueryTask queryTask = new QueryTask();
      queryTask.Url = ms.UrlMapService + "/4";
      queryTask.ExecuteCompleted += QueryTask_ExecuteCompleted;
      queryTask.Failed += QueryTask_Failed;
      Query query = new ESRI.ArcGIS.Client.Tasks.Query();
      query.OutFields.AddRange(new string[] { "UNAME" });
      query.ReturnGeometry = true;
      query.Where = "UNAME LIKE '%NORTHERN%'";
      queryTask.ExecuteAsync(query);
    }
    private void QueryTask_Failed(object sender, TaskFailedEventArgs args)
    {
      MessageBox.Show("Query failed: " + args.Error);
    }

    private void QueryTask_ExecuteCompleted(object sender, ESRI.ArcGIS.Client.Tasks.QueryEventArgs args)
    {     
      FeatureSet featureSet = args.FeatureSet;

      if (featureSet == null || featureSet.Features.Count < 1)
      {
        MessageBox.Show("No features retured from query");
        return;
      }
    }
  }
}

Cheers,

Kerrie

Re: Drill-down Geocoder

JamesMcElroy — Mon, 19 Sep 2011 00:20:25 GMT

We're pretty much doing that, but I'm not setting the
query.OutFields.AddRange(new string[] { "UNAME" });
query.ReturnGeometry = true;
lines. Does not setting the OutFields parameter affect the performance of the execution?

We're also not setting the MaxRecords property of the service.

Thanks,

James

Re: Drill-down Geocoder

JamesMcElroy — Sat, 11 Dec 2021 20:52:05 GMT

Hi Kerrie,

I created a small test app to see if I could reproduce your results. When I tried with your code verbatim it didn't work at all. This is probably because, as I said earlier in the thread, our app runs a query on some filter text that the user is typing in. So it starts with "N", then "No", then "Nor", etc... With the MaxRecords set to 5000000 searching "N" takes about 20 minutes, pushes memory usage up to over 1GB, and then the process just dies.

Once I removed the MaxRecords property I was able to get results since it was limited to 1000. I wasn't able to get timeouts, but I can only assume that this was due to the fact that, in our main application, the querying is being done on a background worker thread and then sending the results back up to the UI via eventing. I've enclosed the amount of time the queries took, both synchronous as well as asynchronous, (I'm .ToUpper'ing the filter before actually feeding it to the query task). As you can see there is a serious degradation in performance between 5 and 6 characters, and then again between 9 and 10 characters.

I'm guessing that the threading timeouts are due to the query task being run on a background thread, but these performance numbers are still pretty tough.

Synchronous

"N" - 0.216s
"No" - 0.531s
"Nor" - 1.51s
"Nort" - 3.018s
"North" - 3.457s
"Northe" - 12.565s
"Norther" - 12.751s
"Northern" - 12.511s
"Northern " - 12.492s
"Northern L" - 71.665s
"Northern Li" - 64.156s
"Northern Lig" - 64.527s

Asynchronous

"N" - 0.517s
"No" - 0.615s
"Nor" - 1.666s
"Nort" - 3.304s
"North" - 3.776s
"Northe" - 14.143s
"Norther" - 13.362s
"Northern" - 13.219s
"Northern " - 13.160s
"Northern L" - 69.969s
"Northern Li" - 65.064s
"Northern Lig" - 65.164s