In order for the extraction to work as planned, it is required that the distance Units used by Mappoint be set to "miles".

The database is created as "poi.mdb" in the same directory as the application from which the subroutine is executed. Once created, the database contains a single table named "tblPOI" and has columns that store the data as identified in the previous paragraph.

The following subroutine is called from the form's load event to create the database.

Sub CreatePOIDB() Dim oCat As ADOX.Catalog Dim strDBProvider As String Dim oTable As ADOX.Table Dim oIndex As ADOX.Index Set oCat = New ADOX.Catalog 'Create a JET Database (Microsoft Access DB - poi.mdb) strDBProvider = _ "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _ App.Path & "\poi.mdb" oCat.Create strDBProvider 'Add a table to the database oCat.ActiveConnection = strDBProvider Set oTable = New ADOX.Table With oTable .Name = "tblPOI" Set .ParentCatalog = oCat With .Columns 'Add columns to the table .Append "strName", adVarWChar, 100 .Item("strName").Properties("Nullable") = False .Append "dblLatitude", adDouble .Item("dblLatitude").Properties("Nullable") = True .Append "dblLongitude", adDouble .Item("dblLongitude").Properties("Nullable") = True .Append "strCategory", adVarWChar, 50 .Item("strCategory").Properties( _ "Jet OLEDB:Allow Zero Length") = True .Append "strAddress", adVarWChar, 100 .Item("strAddress").Properties( _ "Jet OLEDB:Allow Zero Length") = True .Append "strStreet", adVarWChar, 50 .Item("strStreet").Properties( _ "Jet OLEDB:Allow Zero Length") = True .Append "strCity", adVarWChar, 50 .Item("strCity").Properties( _ "Jet OLEDB:Allow Zero Length") = True .Append "strRegion", adVarWChar, 20 .Item("strRegion").Properties( _ "Jet OLEDB:Allow Zero Length") = True .Append "strCountry", adVarWChar, 20 .Item("strCountry").Properties( _ "Jet OLEDB:Allow Zero Length") = True .Append "strPostalCode", adVarWChar, 12 .Item("strPostalCode").Properties( _ "Jet OLEDB:Allow Zero Length") = True End With End With oCat.Tables.Append oTable Set oIndex = New ADOX.Index 'Primary Key oIndex.Name = "pk" oIndex.Columns.Append ("strName") oIndex.Columns.Append ("dblLatitude") oIndex.Columns.Append ("dblLongitude") oIndex.PrimaryKey = True oCat.Tables("tblPoi").Indexes.Append oIndex 'Indices Set oIndex = New ADOX.Index oIndex.Name = "iCategory" oIndex.Columns.Append ("strCategory") oCat.Tables("tblPoi").Indexes.Append oIndex Set oIndex = New ADOX.Index oIndex.Name = "iCity" oIndex.Columns.Append ("strCity") oCat.Tables("tblPoi").Indexes.Append oIndex Set oIndex = New ADOX.Index oIndex.Name = "iPostalCode" oIndex.Columns.Append ("strPostalCode") oCat.Tables("tblPoi").Indexes.Append oIndex Set oTable = Nothing Set oIndex = Nothing Set oCat = Nothing End Sub
Notice that the Primary Key in the table consists of the POI Name, Latitude and Longitude. Due to the nature of the extraction algorithm, some POIs will be processed more than once. So, by defining the Primary Key using these fields, no POIs will get added to the table more than once. This will become clearer when the extraction algorithm is explained in the following paragraphs.

Also, Indices were placed on the Category, City and Postal Code fields for faster searching and sorting once the database has been created.

Extracting the POIs

Once the form has loaded, the extraction code can be started by clicking the command button to execute the code in the command's OnClick event.

Private Sub Command1_Click() On Error Resume Next 'Mappoint Objects Dim oMap As MapPoint.Map Dim oFR As MapPoint.FindResults Dim oLocLatLong As MapPoint.Location Dim oLocFR As MapPoint.Location 'Database Objects Dim oCnn As ADODB.Connection 'Count of POIs at each extraction point Dim lngCount As Long 'POI record items Dim strName As String Dim dblLat As Double Dim dblLong As Double Dim strCategory As String Dim strAddress As String Dim strStreet As String Dim strCity As String Dim strRegion As String Dim strCountry As String Dim strPostalCode As String 'Loop variables Dim iLat As Integer Dim iLong As Integer 'SQL string used for inserting a POI record Dim strSQLPre As String Dim strSQL As String Set oMap = MappointControl1.ActiveMap Set oCnn = New ADODB.Connection 'Build SQL string used for adding record to the database strSQLPre = "INSERT INTO tblPOI(strName, " strSQLPre = strSQLPre & "dblLatitude, dblLongitude, " strSQLPre = strSQLPre & _ "strCategory, strAddress, strStreet, " strSQLPre = strSQLPre & _ "strCity, strRegion, strCountry, strPostalCode) " 'Establish connection to the database With oCnn .Provider = "Microsoft.Jet.OLEDB.4.0" .Open App.Path & "\poi.mdb" End With 'Latitudinal loop For iLat = 20 To 20 Me.Text1 = iLat 'Longitudinal Loop For iLong = 20 To 180 lngCount = 0 'Move to current lat/long Set oLocLatLong = oMap.GetLocation(iLat, -iLong) 'Get the POIs within 50 mile radius Set oFR = oLocLatLong.FindNearby(50) 'Update UI Me.Text2 = -iLong Me.Text3 = 0 Me.Text4 = oFR.Count Me.Refresh 'Process each POI For Each oLocFR In oFR 'Initialize POI items strName = vbNullString dblLat = 0 dblLong = 0 strAddress = vbNullString strStreet = vbNullString strCity = vbNullString strRegion = vbNullString strCountry = vbNullString strPostalCode = vbNullString 'Get POI record items strName = Replace(oLocFR.Name, "'", "''") 'Calculate the POIs Lat/Long CalcPos oMap, oLocFR, dblLat, dblLong 'Add a record to the database strCategory = oLocFR.PlaceCategory.Name If Not oLocFR.StreetAddress Is Nothing Then strAddress = Replace( _ oLocFR.StreetAddress.Value, "'", "''") strStreet = Replace( _ oLocFR.StreetAddress.Street, "'", "''") strCity = Replace( _ oLocFR.StreetAddress.City, "'", "''") strRegion = oLocFR.StreetAddress.Region Select Case oLocFR.StreetAddress.Country Case geoCountryUnitedStates strCountry = "US" Case geoCountryMexico strCountry = "Mexico" Case geoCountryCanada strCountry = "Canada" End Select strPostalCode = oLocFR.StreetAddress.PostalCode End If 'Complete the SQL string used for adding the record strSQL = strSQLPre & _ "VALUES('" & strName & "'," & _ dblLat & "," & _ dblLong & ",'" & _ strCategory & "','" & _ strAddress & "','" & _ strStreet & "','" & _ strCity & "','" & _ strRegion & "','" & _ strCountry & "','" & _ strPostalCode & "')" 'Insert the record oCnn.Execute (strSQL) 'Update UI lngCount = lngCount + 1 Me.Text3 = lngCount Me.Text3.Refresh DoEvents Next 'POI Next iLong Next iLat 'Cleanup Set oCnn = Nothing Set oFR = Nothing Set oLocLatLong = Nothing Set oLocFR = Nothing Set oMap = Nothing End Sub
The main task behind the POI extraction is performed using the FindNearby method of a Location object to populate a FindResults object. The FindNearby method receives a range as an input. In this application, the range is specified as 50 miles. Therefore, the FindResults object is populated with a collection of Location objects, one for each POI within 50 miles of the location object for which the FindNearby method is executed.

It should be apparent that the Location object for which the FindNearby Method will be executed will need to be moved across the North American continent in such a way as to not allow any POIs to be missed. This is done using two loops, a longitudinal (east to west) loop nested inside a latitudinal (south to north) loop. As seen in the code, the latitudes ranging from 15 to 75 degrees north and the longitudes ranging from 20 to 180 degrees west are covered. Each loop increment represents a single degree in either direction. Using a 50- mile radius with the FindNearby method will provide enough overlap as the Location object is moved to prevent any POIs from being missed. This is where it is important that the table where the records are being stored has a Primary Key set to prevent any POIs from being added more than once.

Looking at the extraction process from the top down, after establishing a connection with the database the latitudinal and longitudinal looping process begins. At each increment within the longitudinal loop, the Location object is repositioned at the location specified by the loop variables (e.g., the first position is lat/long 15.0/-20.0, the second position is 15.0/-21.0, etc., until 75.0/-120.0 is reached). Each time the Location object is repositioned, the FindNearby method is used to populate the FindResults Collection. A loop is then performed to process each POI, which is represented by a Location object in the FindResults collection. Within the loop, the Location object is used as an input to the CalPos function to obtain the POI's Lat/Long and a record is added to the database by executing an INSERT SQL statement.

As the extraction is being performed, the text boxes placed on the form will be updated as follows:

• Text1 = Current latitude position
• Text2 = Current longitude position
• Text3 = Count of POIs processed at current location
• Text4 = Total number of POIs at current location

The time needed for the application to execute will vary based on the system resources of the machine on which it is being executed. When executed on my Dell Inspiron 7500, it took a little over 30 hours to perform the entire extraction, approximately 26,700 POIs/hr. An alternative method would be to modify the code to do the extraction in segments and then append the records together into a single database.

Interesting Findings

Many POIs do not have a street address. In the case of no address, only the Name, Lat/Long and Category are included in the record for the POI.

The POIs extracted from Canada with an address indicate the country as being "US".

McDonalds has 10057 locations listed.

The postal code with the most POIs is 10021 with 676 which as you might expect exists for an area of Manhattan.

The POI category count breakdown is as follows:

Category	Counts
Airports - Major	208
Airports - Minor	5650
Aquariums	13
ATMs and Banks	45361
Auto Services	65958
Bus Stations	319
Campgrounds	10819
Casinos	125
Cinemas	5111
Convention Centers	1279
Exit Services	36804
Galleries	19003
Gas Stations	26022
Hospitals	8026
Hotels and Motels	57331
Landmarks	12687
Libraries	12748
Marinas	1736
Museums	6184
Nightclubs and Taverns	47050
Park and Rides	604
Police Stations	3309
Post Offices	16093
Rental Car Agencies	4161
Rest Areas	1527
Restaurants - Asian	3445
Restaurants - BBQ	6552
Restaurants - Chinese	26676
Restaurants - Delis	21713
Restaurants - French	1838
Restaurants - Greek	2161
Restaurants - Indian	1788
Restaurants - Italian	16942
Restaurants - Japanese	5463
Restaurants - Mexican	23183
Restaurants - Other	196057
Restaurants - Pizza	32729
Restaurants - Seafood	9302
Restaurants - Steak	6926
Restaurants - Thai	2473
Schools	47492
Shopping	2295
Stadiums and Arenas	585
Subway Stations	1601
Theaters	2958
Train Stations	721
Zoos	143

Summary

As often is the case in software development and learning new applications, my curiosity got the best of me. Once I knew how to obtain the Lat/Long given a Location Object (thanks to Gilles Kohl's article), it was just a matter of familiarizing myself a little more with the Mappoint Object Model to come up with a way to extract the POIs. I now have a nicely populated database that I can use to try out many of the other Mappoint data related capabilities as well as to test Mappoint applications I will be responsible for developing.

	MP2Kmag Internet