Programming MapPoint in .NET

3.5. Querying a MapPoint DataSet

A MapPoint DataSet object gives you the ability to query the dataset to access the records. These queries can be pure data access queries (using the DataSet.QueryAllRecords method) or geometry- and location-based queries (using the DataSet.QueryCircle, DataSet.QueryPolygon, and so on). In any case, a successful query returns a RecordSet object that you can loop through the records and access the fields and the values contained in that record. With this introduction, let's now look at different ways to query a dataset.

3.5.1. Executing Location Queries Using MapPoint DataSet

You can query a DataSet using specific location queries for which the DataSet object provides methods, such as QueryCircle, QueryPolygon, and QueryShape. I will discuss the QueryCircle and QueryPolygon methods in this section and the QueryShape method shortly after introducing the shape concepts in the next section.

The QueryCircle method allows you to limit your query based on geographic distance. An example of this type of query is, "Find all orders that are being shipped to locations more than 100 miles away from my warehouse." In this query, you would use the QueryCircle method, specify the center of the circle as your warehouse, and set the radius of the circle to 100 miles.

Now, let's get back to our supply-chain optimization problem: imagine that you have a warehouse in Redmond, WA, and you have a database of all orders shipped on the West Coast. You can query the shipment records to find out which orders travel for more than 100 miles from your warehouse in Redmond by following these steps:

1. Import all orders from SQL Server into a dataset:

   //Create a new dataset MapPoint.DataSet dataset = map.DataSets.AddPushpinSet("NorthWind Orders"); //Import orders records from your SQL Server try { //Open the connection connection.Open( ); //Get a sql reader from the query System.Data.SqlClient.SqlDataReader sqlReader = command.ExecuteReader(System.Data.CommandBehavior.CloseConnection); if(sqlReader != null) { while(sqlReader.Read( )) { //Get the name of the customer string customername = sqlReader["ContactName"] as String; //Get the company name string companyname = sqlReader["CompanyName"] as String; //Get the address string string address = sqlReader["Address"] + ", " + sqlReader["City"] + ", " + sqlReader["Region"] + ", " + sqlReader["PostalCode"] + " " + sqlReader["Country"]; //Find location MapPoint.Location location = null; try { //Find the address MapPoint.FindResults findrs = map.FindResults(address); //If no results found, skip to next record if(findrs == null || findrs.Count <= 0) throw new Exception(address); //Get the location location = findrs.get_Item(ref index) as MapPoint.Location; //Create a pushpin if(location != null) { MapPoint.Pushpin pushpin = map.AddPushpin(location, companyname); //Assign the contact name pushpin.Note = "Contact : " + customername; //Move to the pushpin dataset pushpin.MoveTo(dataset); } } catch { //Do some logging } } //Close the reader; this will automatically close the connection //due to the command behavior setting during the //ExecuteReader method sqlReader.Close( ); } } catch { //Do clean up }

2. Find the location of your warehouse using the FindResults or FindAddress method:

   //Now find out how many orders are shipping within 100 miles //of your warehouse in Redmond, WA //First step is to find out the warehouse MapPoint.Location warehouse = map.FindResults("1 Microsoft Way, Redmond, WA").get_Item(ref index) as MapPoint.Location;

3. Define the distance limit (the radius of the circle):

   //Define the radius of the circle double radius = 100;

4. Query the dataset using the warehouse location and the distance limit:

   //Now query for records (orders) that fall within 100 miles of distance around //this warehouse using the QueryCircle method //Call the Query circle method with warehouse as center and the radius MapPoint.Recordset orders = dataset.QueryCircle(warehouse, 100);

5. Count the number of matching records for this query to get the number of orders shipping from your warehouse in Redmond that travel for distances of 100 miles or more:

   //Count the orders int orderCount = 0; if(orders != null) { orders.MoveFirst( ); while(!orders.EOF) { orderCount ++; orders.MoveNext( ); } } MessageBox.Show( orderCount.ToString( ) + " out of " + dataset.RecordCount.ToString( ) + " orders are shipping from Redmond, WA warehouse");

Using the standard NorthWind database order data, there are 17 orders out of total 113 being transported for more than 100 milesthat's more than 10% of your company's orders. Now you can extend this code to find out specific locations that are outside the 100-mile radius, find a place at the center of these locations, and recommend it as your new warehouse.

Along the same lines, you can use the QueryPolygon method if you have an array of locations and want to get the records from a dataset that resides within that polygon.

In some cases, you may need to query all the records in a dataset and loop through them. It is possible to accomplish this task with the DataSet object using the QueryAllRecords method.

3.5.2. Querying a Dataset for All Records

You can query a DataSet object for all records using the DataSet.QueryAllRecords method:

//Query all records and loop through MapPoint.Recordset recordSet = dataset.QueryAllRecords( ); //Move the first record in the cursor recordSet.MoveFirst( ); //Loop through the record set and see the values while(!recordSet.EOF) { //Get the row-level values for each field foreach(MapPoint.Field field in recordSet.Fields) { MessageBox.Show(field.Value.ToString( )); } //Move to next record recordSet.MoveNext( ); }

In addition to querying for all records and looping through the records, this code also shows the value for each field contained in the record. Is there a way to query for all records and access fields by their name or index? Yes, using the RecordSet.Fields collection. The following example shows how to access the value of a field selected by the name of the field:

//Query all records and loop through MapPoint.Recordset recordSet = dataset.QueryAllRecords( ); //Move the first record in the cursor recordSet.MoveFirst( ); //Define the field name to that we are interested in object stateField = "State"; //Loop through the record set and see the values while(!recordSet.EOF) { MapPoint.Field field = recordSet.Fields.get_Item(ref stateField); //Find the corresponding pushpin MapPoint.Pushpin pp = map.FindPushpin(field.Value.ToString( )); //For fun show each pushpin detail in a loop if(pp != null) { pp.Highlight = true; pp.BalloonState = MapPoint.GeoBalloonState.geoDisplayBalloon; System.Threading.Thread.Sleep(1000); pp.BalloonState = MapPoint.GeoBalloonState.geoDisplayNone; pp.Highlight = false; } else { //MessageBox.Show("Field not found!"); } //Move to next record recordSet.MoveNext( ); }

I'm querying for all records and then for each individual record. I use the value of the location field to find the corresponding pushpin and highlight it. The processing I'm doing in this case (highlighting a pushpin based on a field value) may look trivial, but you need this kind of simple capability when you are building more complex applications like fleet tracking (which we will build in the next chapter). You can also obtain a pushpin corresponding to a dataset record by using the Recordset.Pushpin property.

Now that you have seen how to query a dataset for all the records and use the QueryCircle method, it's time to look at the Shapes and how to query Shapes to explore the power of MapPoint 2004 DataSet APIs.