It is safe to say that most map applications are based on data stored in databases.
I can't begin to guess how people design their databases, but getting the data on
to the map is generally speaking a question of drawing out the desired
result set and looping through it creating the relevant overlays in the process. There is nothing
difficult about this and it mirrors pulling data out of a database for many other
purposes.
I should add here that there is no databinding feature in the Google Maps .NET Control.
The reason is that overlays can be created in so many different ways with so many
different options that it becomes unwieldly to do this in a generalized manner.
Most people will choose to use the SqlDataReader (or depending on your database
the MySqlDataReader, OleDbDataReader or OdbcDataReader) class to read their data
back from the database as it is the fastest
way to read data that you need as read-only. In short something like this:
C#
//Set up the database connection.
System.Data.SqlClient.SqlConnection conn =
new System.Data.SqlClient.SqlConnection("Your connection string");
System.Data.SqlClient.SqlCommand cmd =
new System.Data.SqlClient.SqlCommand("SELECT ID, Lat, Lng, Text FROM Overlays", conn);
conn.Open();
System.Data.SqlClient.SqlDataReader dtr = cmd.ExecuteReader();
while (dtr.Read())
{
//Create your overlays.
GoogleMarker marker = new GoogleMarker();
marker.ID = (string)dtr["ID"];
marker.Latitude = (double)dtr["Lat"];
marker.Longitude = (double)dtr["Lng"];
marker.MarkerText = (string)dtr["Text"];
GoogleMap.Overlays.Add(marker);
}
conn.Close();
VB.NET
Dim conn As New SqlConnection("Your connection
string")
Dim cmd As New SqlCommand("SELECT ID, Lat,
Lng, Text FROM Overlays", conn)
conn.Open()
Dim dtr As SqlDataReader = cmd.ExecuteReader
While dtr.Read
Dim marker As New GoogleMarker
marker.ID = dtr("ID").ToString
marker.Latitude = CType(dtr("Lat"), Double)
marker.Longitude = CType(dtr("Lng"), Double)
marker.MarkerText = dtr("Text").ToString
GoogleMap.Overlays.Add(marker)
End While
conn.Close()
The above code doesn't do much processing and should be fairly fast. But if you
are going to do a little more processing of your data to create the overlays then
you quickly end up keeping your database connection open longer than is necessary.
It is not difficult to think of an example where you analyze the data before determining
how to create the overlay, e.g. if the latitudes and longitudes are an array you
create a polyline, if not create a marker. Or, as in the example below you want
to plot only points inside a defined polygon.
Either your could write a long SQL query which does it own calculation or you can
leave that to the Contains() method of the GooglePolygon class. This way you can
limit your SQL query to drawing out those rows where the latitude and longitude
are within the bounds of the polygon and then leave it up to a final determination
up to the Contains() method. This way your code would look something like this:
C#
//Dummy polygon for the sake of example.
GooglePolygon polygon = new GooglePolygon();
//Set up the database connection.
System.Data.SqlClient.SqlConnection conn =
new System.Data.SqlClient.SqlConnection("Your connection string");
System.Data.SqlClient.SqlCommand cmd =
new System.Data.SqlClient.SqlCommand(
@"SELECT ID, Lat, Lng, Text FROM Overlays
WHERE Lat < @MaxLat AND Lat > @MinLat AND Lng < @MaxLng AND Lng > @MinLng",
conn);
cmd.Parameters.AddWithValue("@MaxLat", polygon.Bounds.MaxLatitude);
cmd.Parameters.AddWithValue("@MinLat", polygon.Bounds.MinLatitude);
cmd.Parameters.AddWithValue("@MaxLng", polygon.Bounds.MaxLongitude);
cmd.Parameters.AddWithValue("@MinLng", polygon.Bounds.MinLongitude);
conn.Open();
System.Data.SqlClient.SqlDataReader dtr = cmd.ExecuteReader();
while (dtr.Read())
{
if (polygon.Contains(new GoogleLatLng((double)dtr["Lat"], (double)dtr["Lng"])))
{
//Create your overlays.
GoogleMarker marker = new GoogleMarker();
marker.ID = (string)dtr["ID"];
marker.Latitude = (double)dtr["Lat"];
marker.Longitude = (double)dtr["Lng"];
marker.MarkerText = (string)dtr["Text"];
GoogleMap.Overlays.Add(marker);
}
}
conn.Close();
VB.NET
'Dummy polygon for the sake of example
Dim polygon As New GooglePolygon
'Set up the database connection
Dim conn As New SqlConnection("Your connection
string")
Dim cmd As New SqlCommand( _
"SELECT ID, Lat, Lng, Text FROM Overlays WHERE Lat <
@MaxLat AND Lat > @MinLat AND Lng < @MaxLng AND Lng > @MinLng", _
conn)
cmd.Parameters.AddWithValue("@MaxLat", polygon.Bounds.MaxLatitude)
cmd.Parameters.AddWithValue("@MinLat", polygon.Bounds.MinLatitude)
cmd.Parameters.AddWithValue("@MaxLng", polygon.Bounds.MaxLongitude)
cmd.Parameters.AddWithValue("@MinLng", polygon.Bounds.MinLongitude)
conn.Open()
Dim dtr As SqlDataReader = cmd.ExecuteReader
While dtr.Read
If polygon.Contains(New GoogleLatLng( _
CType(dtr("Lat"), Double), CType(dtr("Lng"), Double))) _
Then
'Create your overlays
Dim marker As New GoogleMarker
marker.ID = dtr("ID").ToString
marker.Latitude = CType(dtr("Lat"), Double)
marker.Longitude = CType(dtr("Lng"), Double)
marker.MarkerText = dtr("Text").ToString
GoogleMap.Overlays.Add(marker)
End If
End While
conn.Close()
This code is obviously not very high quality as it will keep the database connection
open much longer than is necessary. But it serves as an example of how processing
of data can be done while it is being read from the database.
The above is a very good example of when the SqlDataReader is not necessarily the
best thing to use from an overall point of view. Here I think it makes sense to
read the data into a DataTable, close the database connection and then go on processing
the data. When filling DataTables most people would opt for the SqlDataAdapter to
do the work. Personally I like to use the SqlDataReader to create a strongly typed
DataTable and then fill it, like this:
C#
System.Data.SqlClient.SqlDataReader dtr = cmd.ExecuteReader();
for (int i = 0; i < dtr.FieldCount; i++) dt.Columns.Add(dtr.GetName(i), dtr.GetFieldType(i));
while (dtr.Read())
{
DataRow dr = dt.NewRow();
for (int i = 0; i < dtr.FieldCount; i++) dr[i] = dtr[i];
dt.Rows.Add(dr);
}
VB.NET
Dim dtr As SqlDataReader = cmd.ExecuteReader
For i As Integer = 0 To dtr.FieldCount - 1
dt.Columns.Add(dtr.GetName(i), dtr.GetFieldType(i))
Next i
While dtr.Read
Dim dr As DataRow = dt.NewRow
For i As Integer = 0 To dtr.FieldCount - 1
dr(i) = dtr(i)
Next i
dt.Rows.Add(dr)
End While
But that choice is up to you. If you are using data from several tables and intend
to update the information later on you might as well not bother with the SqlDataAdapter
as it is not capable of generating scripts that deal with multiple tables.
After you have filled your DataTable and closed your database connection you can
loop through the data and create your overlays. The following code shows how to
quickly grab the data from the database after a cursory selection, close the connection
as quickly as possible and then analyze the data for the final selection.
C#
DataTable dt = new DataTable();
//Dummy polygon for the sake of example.
GooglePolygon polygon = new GooglePolygon();
//Set up the database connection.
System.Data.SqlClient.SqlConnection conn =
new System.Data.SqlClient.SqlConnection("Your connection
string");
System.Data.SqlClient.SqlCommand cmd =
new System.Data.SqlClient.SqlCommand(
@"SELECT ID, Lat, Lng, Text
FROM Overlays
WHERE Lat < @MaxLat AND
Lat > @MinLat AND Lng < @MaxLng AND Lng > @MinLng",
conn);
cmd.Parameters.AddWithValue("@MaxLat", polygon.Bounds.MaxLatitude);
cmd.Parameters.AddWithValue("@MinLat", polygon.Bounds.MinLatitude);
cmd.Parameters.AddWithValue("@MaxLng", polygon.Bounds.MaxLongitude);
cmd.Parameters.AddWithValue("@MinLng", polygon.Bounds.MinLongitude);
conn.Open();
SqlDataReader dtr = cmd.ExecuteReader();
for (int i = 0; i < dtr.FieldCount; i++) dt.Columns.Add(
dtr.GetName(i), dtr.GetFieldType(i)
);
while (dtr.Read())
{
DataRow dr = dt.NewRow();
for (int i = 0; i < dtr.FieldCount; i++) dr[i] = dtr[i];
dt.Rows.Add(dr);
}
dtr.Close();
conn.Close();
for (int i = 0; i < dt.Rows.Count; i++)
{
DataRow dr = dt.Rows[i];
if (polygon.Contains((double)dr["Lat"], (double)dr["Lng"]))
{
GoogleMarker marker = new GoogleMarker();
marker.ID = (string)dr["ID"];
marker.Latitude = (double)dr["Lat"];
marker.Longitude = (double)dr["Lng"];
marker.MarkerText = (string)dr["Text"];
GoogleMap.Overlays.Add(marker);
}
}
VB.NET
Dim dt As New DataTable
'Dummy polygon for the sake of
example
Dim polygon As New GooglePolygon
'Set up the database connection
Dim conn As New SqlConnection("Your connection
string")
Dim cmd As New SqlCommand( _
"SELECT ID, Lat, Lng, Text
FROM Overlays WHERE Lat < @MaxLat AND Lat > @MinLat AND Lng < @MaxLng AND
Lng > @MinLng", _
conn)
cmd.Parameters.AddWithValue(
"@MaxLat", polygon.Bounds.MaxLatitude)
cmd.Parameters.AddWithValue(
"@MinLat", polygon.Bounds.MinLatitude)
cmd.Parameters.AddWithValue(
"@MaxLng", polygon.Bounds.MaxLongitude)
cmd.Parameters.AddWithValue(
"@MinLng", polygon.Bounds.MinLongitude)
conn.Open()
Dim dtr As SqlDataReader = cmd.ExecuteReader
For i As Integer = 0 To dtr.FieldCount - 1
dt.Columns.Add(dtr.GetName(i), dtr.GetFieldType(i))
Next i
While dtr.Read
Dim dr As DataRow = dt.NewRow
For i As Integer = 0 To dtr.FieldCount - 1
dr(i) = dtr(i)
Next i
dt.Rows.Add(dr)
End While
dtr.Close()
conn.Close()
For i As Integer = 0 To dt.Rows.Count - 1
Dim dr As DataRow = dt.Rows(i)
If polygon.Contains(New GoogleLatLng( _
CType(dr("Lat"), Double), CType(dr("Lng"), Double))) _
Then
'Create
your overlays
Dim marker As New GoogleMarker
marker.ID = dr(
"ID").ToString
marker.Latitude =
CType(dr("Lat"), Double)
marker.Longitude = CType(dr("Lng"), Double)
marker.MarkerText = dr("Text").ToString
GoogleMap.Overlays.Add(marker)
End If
Next i
Using geographic data directly in SQL Server 2005
When working with geographic data it would be nice if many of these calculations
could be performed by the database directly. To this end I have created two user-defined
types (UDT) - SqlLatLng and SqlBounds - to use with Sql Server 2005. This project
is very much in it its infancy but future versions (version 3.4+) will support these
two data types directly from the control.
License holders can download sql scripts to create SqlLatLng and SqlBounds UDTs
here.