More and more developers want to add auto complete to their map applications, and for good reason. It’s convenient for users and can help make your map application a user-friendly delight. In this blog post I will show you how to use Bing Spatial Data Services to create custom auto complete functionality in your next app.
I recently had a discussion with a customer who was interested in this kind of custom auto complete functionality. However, they wanted the suggestions to only be cities in which they have locations. Their data is stored in the Bing Spatial Data Services and wanted to know if it would be possible to create an auto complete functionality that was powered by their data in the Bing Spatial Data Services. It is, and that is exactly what I’ll be showing you how to do.
Full source code and the data sources I created for this blog post can be found on the MSDN Code Samples gallery here.
Creating our data sources
A lot of data starts out as a two-dimensional table inside of Microsoft Excel. With this in mind, we will use this as the starting point for our application. In the code samples I’ve included an Excel file that contains a set of mock coffee shops in Seattle, San Antonio, San Diego and San Francisco. It seems our coffee shops business strategy is to only open up locations in cities that start with “S”. Here is a screenshot of what the data looks like:
Looking at this table, we can make the following observations:
- The ID column contains a unique value for each location. We will be able to use this as our primary key in our data source.
- Location information is stored using the AddressLine, Locality, AdminDistrict, PostalCode, and CountryRegion columns. These are the standard address related column names the Bing Spatial Data Services looks for when geocoding locations.
- There is a Latitude and Longitude column that contains the coordinates of each coffee shop. If you are using your own data, you likely don’t have this information yet. That’s ok because the Bing Spatial Data Services will fill this column in for us when we upload the data source.
- There are some additional columns that contain some metadata related to the coffee shops. You can create your own columns that contain metadata that’s more relevant to your location data.
Now that we have a set of data to work with, we need to format it so that it aligns with the required data source schema for the Bing Spatial Data Services. The main changes required to align our data to this schema are as follows:
1. Add a new row that contains the versioning information of the Bing Spatial Data Services and the entity type name. The entity type name should be something that describes a single location in your data source. In our case it makes sense to call this “CoffeeShop”. We will set the first cell in the first row of the table to “Bing Spatial Data Services, 1.0, CoffeeShop”.
2. Specify the data type as an OData type in brackets beside each column header name. Here is a list of the different OData type values that can be used:
|Data Type||OData Type|
|Well Known Text shape||Edm.Geography|
3. Finally we need to identify which column contains the primary key for uniquely identifying each location.
Here is a screenshot of how these changes look when completed.
Screenshot: Data with changes applied
From here we need to export the data into a file format that can be uploaded. The Bing Spatial Data Services supports XML, CSV, Tab and Pipe delimited formatted files. When working with Excel, I prefer to use Tab or Pipe delimited files, as it makes the upload process a bit easier. To export this data as a Tab delimited file in Excel press the Save As button and in in the Save as type drop down select Text (Tab delimited)(*.txt). We will call this file CoffeeShops_tab.txt.
Screenshot: Selecting tab delimited file type
Now that we have a Tab delimited file we need to open it in a text editor and make a couple of minor edits. The first one is to remove all the trailing Tab characters that are in the first row of the file. The second edit is to remove all the quote characters from the file as these are not needed and can cause some issues when uploading. If using notepad you can easily do this by pressing Ctrl+H to open the find replace functionality. In the screenshot below you can see where the trailing Tab characters are highlighted in blue:
Screenshot: Text file in Notepad
At this point we have our main data source for all our coffee shop locations. Before we upload these we will create a second data source that powers our auto complete functionality. If we tried to do an auto complete against the Locality (city/town) column in the main data source, we will end up with a lot of identical suggestions. We need to create a data set that contains a list of each unique city only once. To do this open a new Excel file and add the following columns: ID, Locality, AdminDistrict, CountryRegion, Latitude, and Longitude. Next copy the Locality, AdminDistrict and CountryRegion data from the main data source into this table, leaving the ID, Latitude, and Longitude columns empty. Next select all the data in the table and then go to the Data tab and press the Remove Duplicates button. This will give us a set of unique cities.
Next create a new column called FormattedAddress. We will populate this column with a nicely formatted address string that we can display to the user. To populate this column we can do a simple string concatenation in Excel using a formula like this: =CONCATENATE(B3,”, “,C3,” “,D3)
The string comparison functionality in the Bing Spatial Data Services is case sensitive. Since our users are unlikely to use the proper text casing in the search box, we will need to create an additional column that contains the formatted address information in lower case. We can later ensure that the data we send to the service to do the auto complete is in lowercase as well. To do this create a new column called FormattedAddress_Lower and use of the Lower function in Excel to convert the formatted address data to lowercase.
Next we need to populate the ID column with a unique value. An easy way to do this is to set the first ID to 1 and then use a formula that increments the ID. For example, in cell A4 use the formula =A3+1 and then copy this cell to all the other ID cells.
The final step is to ensure your data has the correct data schema information. Set the entity type value to CoffeeShopCities. Export this as a tab delimited file called CoffeeShopCities_tab.txt. Here is a screenshot of the final data source:
Screenshot: Final data source
Uploading our data sources to Bing
Now that we have our data sources we need to upload them to the Bing Spatial Data Services. To do this log into the Bing Maps portal using your Microsoft Account ID. If you don’t have a Bing Maps account you can sign up to create a new one.
Once signed in, on the left side panel you will see a Data Sources section, select the Upload data to a data source link. You will be presented with a form to upload your data source. Set the data source name of the main data set to CoffeeShops. Next select a Bing Maps key to be your master key. A master key allows you to programmatically query, edit, update and delete your data source. You can optionally provide a second Bing Maps key to be a query key. A Bing Maps key that is specified as a query key will only be able to search against the data sources and not modify them. If test dropdowns are empty you need to create a Bing Maps key. Next set the data type to TAB and then press the Browser button to select your data source file to upload. Once this is done press the Upload button.
Screenshot: Data Source section of Bing Maps Portal
Repeat these steps for the second data source and give it a data source name of CoffeeShopCities.
From here click on the Manage my data sources link in the left side panel. You will see a panel listing the data sources that have been geocoded. Press the publish button to have these data sources exposed as a spatial REST service through the Bing Spatial Data Services.
Screenshot: Manage data sources in Bing Maps Portal
The publishing step may take a few minutes. You can monitor this on the Published Data Sources tab. Press the Refresh button from time to time update the status.
Screenshot: Published data sources tab in Bing Maps Portal
Once complete we can stop here if we want. To make things a bit easier for people who download the code, I’ve pressed the Make Public button next to both of these data sources. By doing this any Bing Maps key can be used to query these data sources. This is a great way to share data sources with other people.
Screenshot: Make data sources public in Bing Maps Portal
Now that the data sources are uploaded we will need to get the URL information to query the data sources. On the left side panel select the View Data Source Information link. You should see all your published data sources listed along with the URL, Master key and Query key to access them. We will need the URLs later in our code.
Screenshot: View data source information in Bing Maps Portal
Creating the auto complete client application
Before we create an app to use the Bing Spatial Data Services and our data sources to create an auto complete functionality, lets first take a closer look at the logic behind how the auto complete will work. The Bing Spatial Data Services expose the data source data as a spatial REST service, as such our query against the services must have some sort of spatial context. Since we want to be able to search against all our data, we can make the spatial part of the query a bounding box search against the whole globe. From here all we need to do is add a StartsWith filter to the query that checks to see if the FormattedAddress_Lower column starts with the keys that have been typed. Putting this together we get a query URL for the service that looks like this:
In our application we will of course update the values that are in the square brackets. We will also specify the format of the response to be JSON, and add an option to limit the number of results.
Now that we understand how to use the REST services that expose our location data, we can easily use it to power an auto complete function in a client application. Our client application can use any of the Bing Maps APIs. To keep things simple we will use the Bing Maps AJAX Control, Version 7.0 to create a web page that has a map and a search box. To do this open Visual Studio and create a new ASP.NET Web Application project called BingSDSAutoComplete.
Screenshot: New project
When prompted select the Empty ASP.NET template and press OK to continue.
Screenshot: ASP.NET template
Open the index.html file. In here we will load in script references for Bing Maps, jQuery, jQuery UI and our BingSDSAutoComplete.js file. We will also create a textbox for our search query and add a div for the map. To help keep things simple, we will use the auto complete widget in jQuery UI. Update the HTML in this file with the following:
If you run the application a web page will open that looks like this:
Example: Web page when running application
At this point all we need to do is wire up the auto complete widget from jQuery UI. When the user types more than one character in the search box, the auto complete widget will call the GetSuggestions function. If a user selects any of the suggestions, the map will be centered and zoomed into that city and the FindLocations function will be called. To do this add the following code to the BingSDSAutoComplete.js file.
At this point the auto complete functionality is complete and we can test it out. If you run the application and type in “sa” you will see a list of suggestions appear under the search box. If you click on any of them the map will zoom into that location and load in all the coffee shop locations we have in our data source that are near that city. If we click on a pushpin we will then see additional data about that coffee shop. Here is a screenshot that shows all this:
Example: Auto complete functionality
Now we could stop here, but what if the user enters in a query that there are no suggestions for? We should then fall back to using geocoding. Add the following code to the BingSDSAutoComplete.js file. If the user presses enter in the search box or presses the search button it will geocode the users query and then do a nearby search around the resulting locations.
The application is now complete. You can download the full source code for the web app in this blog from the MSDN Code Samples here.