Reading Overlay Data From a Database

Tags: Tutorial, Google Maps, Database, SQL, SQL Server

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();

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();

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);
}

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);
    }
}

Latest Tweets