Section 3.5. Querying a MapPoint DataSet

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.