Hello all,

I met Eric Frost on the Microsoft Newsgroup and he convinced me to visit his website. I am putting here the post i put on Microsoft MapPoint Newsgroup.

My name is Chrystian and i am a 10 years experienced french canadian Visual Basic Expert, CEO of Intellyum Consulting in Quebec, Canada. In the next 3 weeks, i will be developing a fleet management system using MapPoint 2002 for a desktop version... We are as well, working on a web based version using ESRI ArcIMS...

I would like to team up with other programmer in here to open source our project. I am making lots of research relating to MapPoint 2002 and
accumulate lots of Visual Basic Source code... I see that most of people
are trying to find solution to their problems... So i am looking for some
prototype of GPS tracking components using MapPoint 2002. In echange, i
have lots of source code in any field, Can have about every program you
desired and i have as well, the European and North American of MapPoint 2002 and 2001.

As well, for who that might interest, We are developping a web based
view of the MapPoin 2002 version without needing to have the web based
version of MapPoint which require to pay for every transaction you make on their Web site. In the following days, i will make available Source code (especially VB Based) relating to MapPoint 2002. I invite you as well to post all your VB Source code here for the use of all...

If my offer interest you, I invite you to contact me at the following
addresses, as well to post all your VB source here and your articles so we can find them all in one post of interest :



ChrystianBourassa@hotmail.com
or
christianb@oceanwide.com
or
Cbourassa@intellyum.com

Regards,

Christian Bourassa.
CEO Intellyum Consulting.

www.Intellyum.com

Using a DSN-less connection to a SQL Server database to get data for maps

Whether you are building a COM add-in or using the MapPoint ActiveX Control, you may need to access SQL Server data. Here are some ideas on doing it efficiently and flexibly.

assumptions:
· You have a basic understanding of data access.
· You need to get at SQL Server data to do something with MapPoint.

(Let me apologize in advance about how I have mixed VB code with SQL Server table, view and stored procedure definitions in this article. My goal is to give you all of the elements you need to dynamically grab SQL data and make it available in MapPoint. I hope I have succeeded.)
MapPoint provides import and linking support for SQL Server data, but that is limited by the need for using Universal Data Link as the mechanism to get at SQL Server data. You can't execute dynamically created SQL or SQL Server stored procedures. Therefore, we need a way to get data onto maps in a more flexible way. We can use a DSN-less connection in our code to get at SQL Server data and create points from that data.
Why go DSN-less?

· In general, DSN-less connections are faster than System DSNs (data source names), which are faster than File DSNs.
· Microsoft Access is a file-based database, so don't expect it to perform well with concurrent users under IIS [or otherwise].
· For SQL Server, SQLOLEDB, the SQL provider, is recommended over MSDASQL, the OLEDB provider for ODBC for performance and reliability.
· It is always good practice to explicitly close your object variables.
The code below shows a number of ways to make connections and create recordsets:

======================================
VB Code
======================================

' DSN-less connection to a database.
'
Dim objConn as ADODB.Connection
Dim objRS as ADODB.Recordset
Dim strConn as String
Dim strSQL as String
Set objConn = New ADODB.Connection
Set objRS = New ADODB.Recordset

'Create a connection string.
strConn = "Provider=SQLOLEDB;Data Source=MyServer;" & _
"Initial Catalog=Northwind;User Id=MyId;Password=123aBc;"

'If you have to use MS Access, build a connection string this way.
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Data\MappingDatabase.mdb"

'You can find OLEDB providers and connection syntax for all kinds of
'other data sources including text files.

'The following string is equivalent to the first used for access to
'SQL Server:
strConn = "Provider=SQLOLEDB;Server=MyServer;" & _
"Database=Northwind;User Id=MyId;Password=123aBc;"

objConn.Open strConn 'will open the database connection.

'An alternate way to open the connection is:
With objConn
.ConnectionString = strConn
.ConnectionTimeout = 120
.CommandTimeout = 120
.Open
End With

'The ability to change connection properties is
'a major reason to explicitly create a Connection object.

'This can also be done without the connection string as:
With objConn
.Provider = "SQLOLEDB"
.DefaultDatabase = "Northwind"
.Properties("Data Source") = "MyServer"

.Properties("User Id") = "MyId"
.Properties("Password") = "123aBc"
.Open
End With

'Lets create the simplest possible SQL we want to execute.
strSQL = "Select * from tbAddresses "

'You can now open the recordset.
With objRS
.Open strSQL, objConn
End With

'or
objRS.Open strSQL, objConn

'or without using the connection object
objRS.Open strSQL, strConn, adOpenForwardOnly

'See documentation on ADO to understand the cursor type options
'(such as adOpenForwardOnly).
'You can now use the data in objRS.
'=====
'Be sure you close and destroy your objects.
objRS.Close
objConn.Close
Set objConn = Nothing
Set objRS = Nothing


======================================
Create the table
======================================

CREATE TABLE [dbo].[tbAddresses] (
[LocationID] [int] IDENTITY (1, 1) NOT NULL ,
[LocationName] [varchar] (100) NULL ,
[Address1] [varchar] (100) NOT NULL ,
[Address2] [varchar] (100) NULL ,
[City] [varchar] (50) NOT NULL ,
[StateCode] [char] (2) NOT NULL ,
[ZipCode] [char] (10) NULL ,
[Latitude] [decimal](18, 7) NOT NULL ,
[Longitude] [decimal](18, 7) NOT NULL ,
[GeoCodeStatus] [smallint] NULL
) ON [PRIMARY]
GO

How to get Latitude and Longitude Using VB on MapPoint



===========================================
VB Source Code
===========================================

Sub GetLatLong(Street As String, City As String, State As String)

Dim oApp As MapPoint.Application
Dim oMap As MapPoint.Map
Dim oPush As MapPoint.Pushpin
Dim oPushA As MapPoint.Pushpin
Dim oLoc As MapPoint.Location
Dim oLocA As MapPoint.Location
Dim oLocB As MapPoint.Location

'A Mile = 0.01471 Degrees of Lat/Long, a Half mile = 0.007355
Dim Measure As Double
Dim zLat As Double
Dim zLong As Double
Dim DistX As Double
Dim DistY As Double
Dim DistZ As Double
Dim PointX As MapPoint.Location
Dim PointY As MapPoint.Location

Set oApp = CreateObject("Mappoint.Application")
Set oMap = oApp.ActiveMap

'myLat and myLong are a known point. In this case, Wichita, Kansas
myLat = 37.70212
myLong = -97.31775

'Initially, set zLat and zLong equal to the known point
zLat = myLat
zLong = myLong

'Measure is used to adjust the starting distance. In this case, start off with 750 miles.
'Measure is made smaller in the Do While loop below but it is a good idea to start off
'with a large number
Measure = 0.01471 * 750

'Create two points: oLoc as the reference point and your address for which you need lat/long
Set oLoc = oMap.GetLocation(myLat, myLong)
'Find your address from the form frmLatLong
Set oPushA = oMap.ShowFindDialog(Street & ", " & City & ", " & State, , , True)
On Error GoTo EndNow 'If there is no address found, then Set oLocA will fail.
Set oLocA = oPushA.Location
On Error Resume Next

'Update the form with the chosen address from the Find Dialog
frmLatLong.txtStreet = oMap.ParseStreetAddress(oPushA.Name).Street
frmLatLong.txtCity = oMap.ParseStreetAddress(oPushA.Name).City
frmLatLong.txtState = oMap.ParseStreetAddress(oPushA.Name).Region

x = 0
'Create a loop that will continue until your desired precision. As indicated below
'this loop will repeat until the lat/long is found to be within 20 feet.
Do While Measure > 0.00005572 'This value is determined using the following formula:
' .01471/5280*Number of Feet for Precision
' Ex: .01471 / 5290 * 20 = .00005572
' Trivia: There are 5,280 feet in a mile.
x = x + 1

'Create two other reference points: PointX is one Measure off oLoc's Lattitude
' PointY is one Measure off oLoc's Longitude
Set PointX = oMap.GetLocation(zLat + Measure, zLong)
Set PointY = oMap.GetLocation(zLat, zLong + Measure)

'Measure the distances from each of the three reference points to our main address (oLocA)
DistX = oLocA.DistanceTo(PointX)
DistY = oLocA.DistanceTo(PointY)
DistZ = oLocA.DistanceTo(oLoc)

'Determine which reference point is closer to oLocA, our main address
If DistX < DistY And DistX < DistZ Then
'Make the master reference point oLoc equal to PointX since PointX was the closest to
'our main address
Set oLoc = oMap.GetLocation(zLat + Measure, zLong)
zLat = zLat + Measure 'Don't forget to add the Measure to zLat for the next iteration
'Uncomment the next line if you want to see how the algorithm found the lat/long
'Set oPush = oMap.AddPushpin(oMap.GetLocation(zLat + Measure, zLong), x)
End If
If DistY < DistX And DistY < DistZ Then
Set oLoc = oMap.GetLocation(zLat, zLong + Measure)
zLong = zLong + Measure
'Set oPush = oMap.AddPushpin(oMap.GetLocation(zLat, zLong + Measure), x)
End If
If DistZ < DistX And DistZ < DistY Then
'The main reference point is closer than PointX or PointY.
'PointX and PointY were too far away by ADDING a Measure, so here we need
'to subtract a measure from both the Latitude and the Longitude
Set oLoc = oMap.GetLocation(zLat - Measure, zLong - Measure)
zLat = zLat - Measure
zLong = zLong - Measure
'Set oPush = oMap.AddPushpin(oMap.GetLocation(zLat - Measure, zLong - Measure), x)
End If

'Here is where Measure gets adjusted. Check to see if the distance between the new
'reference point is smaller than Measure. If so, reduce Measure by half.
'Don't forget that Measure is in degrees of Lat/Long while the distance will be
'in miles. To convert degrees to miles, multiply by .01471, the number of degrees
'in a mile.
If oLocA.DistanceTo(oLoc) < Measure / 0.01471 Then Measure = Measure / 2
Loop

'Create a PushPin with the new location that matches our address
'Add how many iterations of the loop it took (x) and the the Latitude, Longitude
Set oPush = oMap.AddPushpin(oLoc, x & ": " & zLat & ", " & zLong)

MsgBox "The Lat/Long of your address is " & zLat & ", " & zLong & _
Chr(13) & Chr(13) & "It was found in " & x & " iterations.", _
vbOKOnly, "Lat/Long Results"

'The next four lines are just for presentation. Uncomment them if you like
'oPush.BalloonState = geoDisplayBalloon 'Turn on the balloon state so we can see the lat/long
'oPush.Highlight = True 'Highlight the pushpin so we really see it
'oMap.DataSets.ZoomTo 'Zoom the map to the individual pushpin
'oApp.Visible = True 'Turn on the map so we can see it!

EndNow:
oMap.Saved = True
End Sub

Converting Strings Into Decimal Degree Format

Below is a small utility routine and the corresponding documentation that will help convert a user-supplied string in degree, minute, second format (e.g. N 49° 12' 12.3") and will return a double in the decimal degree format MapPoint expects.

Microsoft MapPoint can accept geographical coordinates on input (e.g. in the "Find" dialog: select Edit/Find and switch to the "Lat/Lon" tab) as well as display them in the location sensor window (Tools/Location sensor).
While the "Find" dialog is smart and can handle values entered as degrees, minutes and seconds (simply separate them with a blank) or as decimal degrees, the GetLocation function available to the programmer requires doubles in decimal format.

Sometimes, coordinates are available in degree, minute, second format though, or users would like to enter values in this more familiar format (e.g. because that is the format their GPS is set to).

This is a VB subroutine for the MapPoint programmer that accepts a string in DMS (degree, minute, second) format and will convert it to decimal format. The routine is called DMSVal (in analogy to the Visual Basic "Val" conversion function).

The signature of the function is as follows:

Function DmsVal(ByVal strDms As String, bValid as Boolean) As Double

DMSVal tries to be tolerant about the format of the strDMS string it accepts on input. There may be leading and trailing blanks, an optional compass point (N, E, S, W), and values for degrees, minutes and seconds separated by arbitrary delimiters except comma and period.

The decimal separator may be both the decimal point (.) common in North America as well as the comma (,) used in Europe (no thousands separator allowed nor needed though)

If compass points South (S or s) or West (W or w) are encountered, the return value is negative according to the convention used in MapPoint.
The following are examples of valid DMS strings:

N 49° 1' 5.1"
49/1/5.1
E49d1m5.1s
E49 1 5.1

DMSVal will set the "bValid" parameter to "False" if it could not convert the input string. A successful conversion is indicate by a bValid value of "True".

If you already have the values available in separate fields, the following function can be used:

Function DmsValCalc(strCompassPoint As String, dblDeg As Double, _
dblMin as Double, dblSec as Double) As Double

DMSValCalc can be called directly with a compass point (N, E, S, W), and the degree, minutes and seconds values. It is used internally by DMSVal.


==================================
VB Source Code
==================================

Attribute VB_Name = "modDmsVal"

Option Explicit

'
' Helper function DmsValCalc - computes decimal degrees given compass point (N, S, E W)
' and degree, minutes and seconds. Can also be used directly
'
Function DmsValCalc(strCompassPoint As String, dblDeg As Double, dblMin As Double, dblSec As Double) As Double
DmsValCalc = dblDeg + dblMin / 60 + dblSec / 3600

' change sign if west or south
If strCompassPoint <> "" And InStr("SWsw", strCompassPoint) Then DmsValCalc = -DmsValCalc
End Function


'
' Function DmsVal - on input, strDms should contain a string with degree, minute and second
' values, e.g. N 49° 45' 12.3"
' It will return the value converted into decimal degrees. If the provided compass point
' is West or South, the value will be negative.
'
Function DmsVal(ByVal strDms As String, bValid As Boolean) As Double
Dim i As Integer ' Various usages

bValid = False
' Remove leading and trailing spaces
strDms = Trim(strDms)

Dim intDmsLen ' Length of trimmed original string
intDmsLen = Len(strDms)

' return with error if string too short
If intDmsLen < 1 Then Exit Function

' Extract Compass Point if present
Dim strCompassPoint As String
If InStr("neswNESW", Left(strDms, 1)) > 0 Then
strCompassPoint = UCase$(Left(strDms, 1))
strDms = Mid(strDms, 2) ' remove it
End If

Dim strNorm ' Will contain normalized string
Dim blnGotSeparator As Boolean ' Keeps track of separator sequences

blnGotSeparator = False ' Not in separator sequence right now

' Loop over string, replacing anything that is not a digit or a
' decimal separator with
' a single blank
For i = 1 To intDmsLen
Dim strChr As String

' Get current character
strChr = Mid(strDms, i, 1)

' either add character to normalized string or replace
' separator sequence with single blank
If InStr("0123456789,.", strChr) Then
' add character but replace comma with point
strNorm = strNorm & IIf(strChr <> ",", strChr, ".")
blnGotSeparator = False
Else
' ensure only one separator is replaced with a blank -
' suppress the rest
If Not blnGotSeparator Then
strNorm = strNorm & " "
blnGotSeparator = True
End If
End If
Next i

' Split normalized string into array of max 3 components
Dim arrDegMinSec() As String
arrDegMinSec = Split(Trim(strNorm), " ")

' If too many components, return error
If UBound(arrDegMinSec) > 2 Then Exit Function

Dim dblDeg As Double
Dim dblMin As Double
Dim dblSec As Double

' convert specified components to double
i = UBound(arrDegMinSec)
If i >= 0 Then dblDeg = Val(arrDegMinSec(0))
If i >= 1 Then dblMin = Val(arrDegMinSec(1))
If i >= 2 Then dblSec = Val(arrDegMinSec(2))

' convert components to value
DmsVal = DmsValCalc(strCompassPoint, dblDeg, dblMin, dblSec)

' All's well
bValid = True

End Function

Extracting the Mappoint 2002 Points of Interest

Extract all 801,171 POIs from the North American version of Mappoint into a database, for subsequent searches.

Introduction

While reviewing the threads on the Microsoft.Public.Mappoint newsgroup I noticed that there was some interest in gaining access to the Mappoint Points of Interest (POIs). My curiosity led me into investigating the possibility of using the Mappoint object model to extract the POIs from Mappoint and load them into a database. What I found is that with a little code and some patience it was possible to extract 801,171 POIs from the North American version of Mappoint. This article explains how this can be done. Before getting started, the reader must be fore warned that the use of the extracted data is subject to Mappoint licensing agreements.
Assumptions

The following items will be required to build and execute the application described in this article:
· Mappoint 2002 North America
· Visual Basic 6.0
· Microsoft Data Access Components (MDAC) 2.6 or later (Note: if "Microsoft ActiveX Data Object Library" can be referenced from Visual Basic project then you already have MDAC installed otherwise you can download it from the following location http://www.microsoft.com/data/download.htm)

Lat/Long Acquisition Algorithm (posted to the MP2KMag site by Gilles Kohl http://www.mp2kmag.com/articles.asp?ArticleId=13 )

Application Overview

The application creates a database and uses the Mappoint 2002 object model to extract the POIs. For each POI extracted it calculates the POI's Lat/Long and adds a record to the database with the following data:
· Name
· Latitude
· Longitude
· Category (Cinemas, Shopping, etc.)
· Full Street Address
· Street
· City
· Region
· Country
· Postal Code

(Note: For a large number of POIs, the address is not provided. When not available, the POI is added to the database with only the Name, Lat/Long and Category included in the record.) The application includes a single form that includes a Mappoint ActiveX control, 4 textbox controls and a single command button using the default names (i.e., MappointControl1, Text1, Text2, Text3, Text4 and Command1). The following Project References will need to be made for the VB application:

· Microsoft Mappoint Control 9.0
· Microsoft Mappoint 9.0 Object Library (North America)
· Microsoft ActiveX Data Object Library
· Microsoft ADO Ext for DDL and Security

Initialization

Code executed from the form's load event is used to initialize the application. The initialization includes configuring Mappoint and creating the database.

====================================
VB Source Code
====================================
Private Sub Form_Load()

Dim oMap As MappointCtl.Map
Dim oPC As MapPoint.PlaceCategory

'Initialize the Mappoint Control
Set oMap = MappointControl1.NewMap(geoMapNorthAmerica)

'Make all the Mappoint categories visible
'to insure maximum number of POIs are extracted
For Each oPC In oMap.PlaceCategories
oPC.Visible = True
Next

'Use miles as the Mappoint distance
MappointControl1.Units = geoMiles

'Create the database
CreatePOIDB

End Sub

====================================
====================================

As seen in the code above, once the Mappoint control is initialized, all the Place categories are made visible to allow for the maximum number of POIs to be extracted. This is necessary since the FindNearby method will only select the POIs from the categories that are visible.

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.

====================================
VB Source Code
====================================

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.


====================================
VB Source Code
====================================

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

====================================
====================================

Finding sites within a specified distance from an address

Note: This code uses MapPoint North America, but it can be easily modified to use fields more appropriate for European addresses.
In your add-in, create a form. Add a combo box called "cmbPushpin". This will be used to list all pushpins in the "My Pushpins" collection on your map. Create a text box called "txtTolerance" where you will enter the distance around the selected pushpin in which to find addresses. For this example the distance is in miles. Also, add a command button "cmdMap" that will be used to trigger the map creation.

In the Add-in designer procedure fired when the form above will be displayed, add the following code:

====================================
VB Source Code
====================================
'At the module level...
Dim mfrmMap As New frmMap
-------------------------------------------------
Sub ShowFormMap()
On Error GoTo ErrSub
Dim objDataSet As MapPoint.DataSet
Dim objMap As MapPoint.Map
Dim objPushpin As MapPoint.Pushpin
Dim objRecordset As MapPoint.Recordset
Dim bPushpinFound As Boolean

'Pass the reference to MapPoint to the form.
Set mfrmMap.objMapPoint = objMapPoint

'Use active map.
Set objMap = objMapPoint.ActiveMap
'Clear the pushpin combo box.
mfrmMap.cmbPushpin.Clear
For Each objDataSet In objMap.DataSets
If objDataSet.Name = "My Pushpins" Then
If objDataSet.RecordCount > 0 Then
bPushpinFound = True
Set objRecordset = objDataSet.QueryAllRecords
objRecordset.MoveFirst
'Add the name of each Pushpin found to the combo box.
Do Until objRecordset.EOF
mfrmMap.cmbPushpin.AddItem Trim(objRecordset.Pushpin.Name)
objRecordset.MoveNext
Loop
End If
End If
Next

'Disable the combo box if there are no pushpins on the map.
If bPushpinFound = False Then
mfrmMap.cmbPushpin.Text = "No pushpins available."
mfrmMap.cmbPushpin.Enabled = False
Else
mfrmMap.cmbPushpin.Enabled = True
mfrmMap.cmbPushpin.Text = "Select pushpin."
End If

'Pass the designer's self-reference to form to allow the form to hide itself.
Set mfrmMap.Connect = Me

'Display the form.
mfrmMap.Show vbModal, Me

ExitSub:
Exit Sub

ErrSub:
MsgBox Err.Description
Resume ExitSub

End Sub

====================================
====================================


You now have a form loaded with a combo box listing any Pushpins that are on the underlying map.

Before going on let me spend a minute on data issues. Unfortunately, MapPoint's ability to handle data is not all that it could be. If you build an ADO recordset, you can't directly import the points into a MapPoint dataset. You can only create pushpins as demonstrated in the code example below. This means that you really only have 2 fields to use for data: Name and Note. The other way to get data in and bring in all desired fields is to use the ImportData method, but with SQL Server that means using a UDL (Universal data link) file and limiting the syntax of how you access SQL Server objects. For example, I don't know how you could use a stored procedure. This is my single biggest gripe with MapPoint. (If I am incorrect about this, someone please set me straight.)
Put the following code behind the "cmdMap" command button. See comments in the code for details of how this works. At a high level, the code determines the latitude and longitude of the selected pushpin, queries the database for points whose latitudes and longitudes are in a rectangle that is slightly larger than the desired radius and uses the MapPoint "Distance" method for each point in the rectangle to determine whether it falls within the desired distance.



====================================
VB Source Code
====================================

Private Sub cmdMap_Click()
On Error GoTo ErrSub
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim dbDistance As Double
Dim dbLatitude As Double
Dim dbLongitude As Double
Dim dbLatNorth As Double
Dim dbLatSouth As Double
Dim dbLongEast As Double
Dim dbLongWest As Double
Dim dbTolerance As Double
Dim objDS As MapPoint.DataSet
Dim objDataSet As MapPoint.DataSet
Dim objMap As MapPoint.Map
Dim objRecordset As MapPoint.Recordset
Dim objLocCtr As MapPoint.Location
Dim objLocTst As MapPoint.Location
Dim objPin As MapPoint.Pushpin
Dim strNote As String
Dim strPushpinSetName As String
Dim strSQL As String

Screen.MousePointer = vbHourglass
If IsNumeric(Me.txtTolerance) Then
If CDbl(Me.txtTolerance) < 0 Then
Err.Raise -100001, "doMapProximity", _
"Tolerance distance is less than or equal to zero. Please fix and retry."
End If
Else
Err.Raise -100002, "doMapProximity", _
"Tolerance distance is not numeric. Please fix and retry."
End If

dbLatitude = 0
dbLongitude = 0
Set objMap = objMapPoint.ActiveMap
Set objLocCtr = objMap.FindPushpin(cmbPushpin.Text).Location
'Here is where Gilles Kohl's code is used to find the latitude and longitude
'of the reference pushpin.
CalcPos objMap, objLocCtr, dbLatitude, dbLongitude
If dbLatitude = 0 Then
MsgBox "The pushpin name entered was not found"
GoTo ExitSub
End If

Set cn = New ADODB.Connection
With cn
'Appropriate connection string for your database.
.ConnectionString = gstrDBConnection
.ConnectionTimeout = 120
.CommandTimeout = 120
.Open
End With

'These calculations are customized for Minnesota.....
'------------------------------------------------------------------------
'A degree of latitude is always about 69 miles. Using 1/68th of a degree
'per mile ensures that the rectangle is slightly larger than the radius
'we are looking for.
dbLatNorth = dbLatitude + (CDbl(txtTolerance.Text) / CDbl(6)
dbLatSouth = dbLatitude - (CDbl(txtTolerance.Text) / CDbl(6)
'Near Minnesota's northern border 1 degree of longitude is a bit more than
'44 miles. It is about 50 miles at the southern border. Using 1/44th of a
'degree per mile again ensures that we capture all points that are close.
dbLongEast = dbLongitude + (CDbl(txtTolerance.Text) / CDbl(44))
dbLongWest = dbLongitude - (CDbl(txtTolerance.Text) / CDbl(44))

'Adapt this SQL to what is appropriate for your database.
Set rs = New ADODB.Recordset
strSQL = "SELECT LocationID, LocationName, Address1, Address2, City, "
strSQL = strSQL & "StateCode, ZipCode, Latitude, Longitude, GeoCodeStatus, "
strSQL = strSQL & "LocationFound , ResultQuality "
strSQL = strSQL & "From tbMapLocations "
strSQL = strSQL & "WHERE Longitude Between " & dbLongWest & " and " & dbLongEast
strSQL = strSQL & " AND Latitude Between " & dbLatSouth & " and " & dbLatNorth
With rs
.Open strSQL, cn, adOpenKeyset, adLockOptimistic
End With

'Get rid of any previous results, but not my reference pushpin.
For Each objDataSet In objMap.DataSets
If Not objDataSet.Name = "My Pushpins" Then
objDataSet.Delete
End If
Next

'Create a new pushpin set.
strPushpinSetName = "Locations within " & CStr(Me.txtTolerance) & _
" miles of " & cmbPushpin.Text
objMap.DataSets.AddPushpinSet strPushpinSetName
Set objDS = objMap.DataSets(strPushpinSetName)
objDS.Select
Do While Not rs.EOF
Set objLocTst = objMap.GetLocation(rs!Latitude, rs!Longitude)
'This adds the pushpins to the "My Pushpins" set.
Set objPin = objMap.AddPushpin(objLocTst, rs!LocationName)
objPin.BalloonState = geoDisplayNone
strNote = rs!Address1 & vbCrLf & _
IIf(IsNull(rs!Address2), "", rs!Address2 & vbCrLf) & _
rs!City & ", " & rs!StateCode & " " & rs!ZipCode & vbCrLf & _
"Location ID = " & rs!LocationID
objPin.Note = strNote
'Cut and paste the pushpin into the new set.
objPin.Cut
objDS.Paste
rs.MoveNext
Loop

On Error Resume Next
objDS.Symbol = 25
objDS.DisplayDataMap DataMapType:=geoDataMapTypePushpin
objDS.ZoomTo
objDS.Name = strPushpinSetName
Set objRecordset = objDS.QueryAllRecords
dbTolerance = CDbl(txtTolerance.Text)
objRecordset.MoveFirst
Do Until objRecordset.EOF
Set objLocTst = objRecordset.Pushpin.Location
'Use the distance method to find distance between a pushpin
'and the reference point.
dbDistance = objMap.Distance(objLocCtr, objLocTst)
'If outside the desired distance, change the color of the symbol.
If dbDistance > dbTolerance Then
objRecordset.Pushpin.Symbol = 30 'green circle
'although you could just delete the pushpin.
'objRecordset.Pushpin.Delete
Else
objRecordset.Pushpin.Symbol = 25 'red circle
End If
objRecordset.MoveNext
Loop

Err.Clear
On Error GoTo ErrSub
'Hide the form.
Connect.HideFormMap

ExitSub:
On Error Resume Next
Screen.MousePointer = vbDefault
'housekeeping...
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
Set objLocCtr = Nothing
Set objLocTst = Nothing
Set objPin = Nothing
Set objRecordset = Nothing
Set objDS = Nothing
Set objDataSet = Nothing
Set objMap = Nothing
Exit Sub

ErrSub:
MsgBox Err.Description
Err.Clear
Resume ExitSub

End Sub

====================================
====================================

To use this:
1. Put a pushpin on the map,
2. Select the map form from your add-in options,
3. Select the desired pushpin and enter the desired distance in the text box,
4. Click the map button.

You now have all of the points on your database that are within the specified distance from the reference point. Clearly, additional filtering can be done to limit the displayed data and make this even more useful.

Get Map with MapPoint 3.0

====================================
VB Source Code
====================================

Imports System
Imports System.Drawing
Imports System.Collections
Imports System.Windows.Forms
Imports System.Data
Imports System.IO
Imports System.Net
Imports PocketFindMap.MapPointService

Public Class Form1
Inherits System.Windows.Forms.Form
Private pictureBox1 As System.Windows.Forms.PictureBox
Private textBox1 As System.Windows.Forms.TextBox
Private WithEvents button1 As System.Windows.Forms.Button
Private mainMenu1 As System.Windows.Forms.MainMenu

'Enter your MapPoint Web Service Username and Password
Private Const myUserName As String = "UserNameHere"
Private Const myPassword As String = "PasswordHere"

'Enter the name of your proxy server
Private Const proxy As String = "MYPROXY"

Public Sub New()
'
' Required for Windows Form Designer support
'
InitializeComponent()
End Sub

'
' TODO: Add any constructor code after InitializeComponent call
'
Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
MyBase.Dispose(disposing)
End Sub
#Region "Windows Form Designer generated code"

Private Sub InitializeComponent()
Me.mainMenu1 = New System.Windows.Forms.MainMenu
Me.pictureBox1 = New System.Windows.Forms.PictureBox
Me.textBox1 = New System.Windows.Forms.TextBox
Me.button1 = New System.Windows.Forms.Button
'
'pictureBox1
'
Me.pictureBox1.Location = New System.Drawing.Point(8, 40)
Me.pictureBox1.Size = New System.Drawing.Size(224, 216)
'
'textBox1
'
Me.textBox1.Location = New System.Drawing.Point(16,
Me.textBox1.Text = "Waikiki"
'
'button1
'
Me.button1.Location = New System.Drawing.Point(144,
Me.button1.Text = "Get Map"
'
'Form1
'
Me.Controls.Add(Me.pictureBox1)
Me.Controls.Add(Me.textBox1)
Me.Controls.Add(Me.button1)
Me.Menu = Me.mainMenu1
Me.Text = "Form1"

End Sub 'InitializeComponent
#End Region


Shared Sub Main()
Application.Run(New Form1)
End Sub


Private Sub button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles button1.Click


Dim proxyObject As New WebProxy(proxy, 80)

' Disable proxy use when the host is local.
proxyObject.BypassProxyOnLocal = True

' All requests this proxy information.
GlobalProxySelection.Select = proxyObject


Dim fws As New FindServiceSoap
Dim ws As New RenderServiceSoap
fws.Credentials = New System.Net.NetworkCredential(myUserName, myPassword)
ws.Credentials = New System.Net.NetworkCredential(myUserName, myPassword)

Dim myFindSpec As New FindSpecification
myFindSpec.DataSourceName = "MapPoint.NA"
myFindSpec.InputPlace = Me.textBox1.Text

Dim results As FindResults = fws.Find(myFindSpec)

If results.NumberFound = 0 Then
MessageBox.Show("Location not found")
Return
End If

Dim views(0) As ViewByScale
views(0) = New ViewByScale
views(0).CenterPoint = New LatLong
views(0).CenterPoint.Latitude = results.Results(0).FoundLocation.LatLong.Latitude
views(0).CenterPoint.Longitude = results.Results(0).FoundLocation.LatLong.Longitude
views(0).MapScale = 150000

'Set a blank pushpin for the map due to a bug in the compact framework
Dim pushpins(0) As Pushpin
pushpins(0) = New Pushpin
pushpins(0).IconDataSource = "MapPoint.Icons"
pushpins(0).IconName = "-1"
pushpins(0).LatLong = views(0).CenterPoint
pushpins(0).ReturnsHotArea = True

Dim options As New MapOptions
options.Format = New ImageFormat
options.Format.Height = Me.pictureBox1.Height
options.Format.Width = Me.pictureBox1.Width

Dim myMapSpec As New MapSpecification

myMapSpec.DataSourceName = myFindSpec.DataSourceName
myMapSpec.Options = options
myMapSpec.Pushpins = pushpins
myMapSpec.Views = views

Dim images As MapImage() = ws.GetMap(myMapSpec)

Me.pictureBox1.Image = New System.Drawing.Bitmap(New MemoryStream(images(0).MimeData.Bits))
End Sub

Hi Chrystian.
I'll be interested in following your project as it goes along. (Don't know how much I can contribute at this point, since I have a couple of other projects going on that are taking most my time.)

Couple questions/comments on your code.
1. Glad to see some VB.NET samples!

2. On the section "How to get Latitude and Longitude Using VB on MapPoint "
I'm curious about your routine. Since you're passing in a street address, why don't you just use the build in "find" function?
Also, somewhere on this site is a routine for an alternate way to determine location. I forget who wrote it off the top of my head, but it may be worth looking at.
Or is this routine specific for your application. (I don't know anything about writing a tracking program.)

Also, your constants:

'A Mile = 0.01471 Degrees of Lat/Long, a Half mile = 0.007355

Is that always correct? I'd think it would be dependent on where you are. That is, if I'm up in the Yukon Territory, I think the degrees of latitude may be a litte different.

Looking forward to seeing more.
Rahn

bigRahn, I think you are on the right track.. but it's the longitude that changed based on your latitude. I think...

Latitude lines are parallel so I think no matter where you are, latitude degrees can be converted to distance as a constant. But for longitude, I think the closer you are to the poles, the less distance a degree covers.

Actually, ya, that makes sense cause if you were on the north pole, you could walk a circle and cover all 360 degrees by only walking a few feet.

good call Rahn, i never would have thought about it before!

Could be helpful in converting Degrees into Kilometers (as well as Miles):

Use nAt_Latitude parameter to get Kilometers per Long.Degree at a given latitude, othervice - Kilometers per Lat.Degree (const):

Code:

Public Function GetKmsPerGrad(Optional nAt_Latitude As Double = -1) As Double 'Kms
   
   If nAtLatitudeGrad <> -1 Then 'Horizontal
        GetKmPerGrad = 111.2945 * Cos&#40;nAtLatitudeGrad * &#40;3.1415 / 180&#41;&#41;
   Else
        GetKmPerGrad = 111.1015
   End If

End Function

[/b]