Drill-down Geocoder

[MichaelErlich]

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

[MichaelErlich]

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

[JamesMcElroy]

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

[KerrieScholefield]

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

[MichaelErlich]

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.

[KerrieScholefield]

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

[MichaelErlich]

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

[KerrieScholefield]

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

[MichaelErlich]

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.

[MichaelErlich]

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?