How to create multiple markers on a google map using data from a database

Hello ,

I have a database with 3 columns (ID,lat ,long) and i want to represent this points with markers on a google map at asp .How can I succeed this ?
Most specific i want to take all the values of the database and put them in ONE variable.
For example my database is like this :
1  20.13  19.20
2  19.15  17.14
3  17.15  16.12

And i want my variable to become like this :
[20.13  , 19.20 , 1],[19.15 , 17.14 , 2],[17.15  ,16.12,3]
CivilAuthAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Big MontyWeb Ninja at largeCommented:
Not sure if you're looking for .NET or classic asp, but here's a great tutorial on how to get it started in .NET using C#:

http://aspfever.blogspot.com/2013/06/show-multiple-markers-on-google-map.html

In this article, I will explain how to show  or add multiple markers to Google maps from database in asp.net website using JavaScript or JavaScript Show multiple markers in Google maps using database in asp.net.

First you need create a new table in your database like as shown below:
Column Name
	
Data Type
	
Allow Nulls
PlaceID
	
int(set isidentity property=true)
	
No
PlaceName
	
varchar(50)
	
Yes
PlaceLatitude
	
numeric(12,6)
	
Yes
PlaceLongitude
	
numeric(12,6)
	
Yes
PlaceDescription
	
Varchar(250)
	
Yes

If table creation completed then enter some sample data such as follows..
PlaceID
	
PlaceName
	
PlaceLatitude
	
PlaceLongitude
	
PlaceDescription
1
	
Kolkata
	
22.5697
	
88.3697
	
This is Kolkata
2
	
Hyderabad
	
17.2667
	
78.5302
	
This is Hyderabad
3
	
Bangalore
	
12.8974
	
77.5195
	
This is Bangalore
4
	
Visakhapatnam
	
17.5183
	
83.3203
	
This is Visakhapatnam
5
	
Chennai
	
12.4974
	
83.3203
	
This is Chennai

Now write the following code in your aspx to show multiple markers in Google map from database like as shown below:

<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>Show/Add multiple markers to Google Maps from database in asp.net website</title>
<style type="text/css">
html { height: 100% }
body { height: 100%; margin: 0; padding: 0 }
#map_canvas { height: 100% }
</style>
<script type="text/javascript" src = "https://maps.googleapis.com/maps/api/js?key=AIzaSyC6v5-2uaq_wusHDktM9ILcqIrlPtnZgEk&sensor=false">
</script>
<script type="text/javascript">
function initialize() {
var markers = JSON.parse('<%=ConvertDataTabletoString() %>');
var mapOptions = {
center: new google.maps.LatLng(markers[0].lat, markers[0].lng),
zoom: 5,
mapTypeId: google.maps.MapTypeId.ROADMAP
//  marker:true
};
var infoWindow = new google.maps.InfoWindow();
var map = new google.maps.Map(document.getElementById("map_canvas"), mapOptions);
for (i = 0; i < markers.length; i++) {
var data = markers[i]
var myLatlng = new google.maps.LatLng(data.lat, data.lng);
var marker = new google.maps.Marker({
position: myLatlng,
map: map,
title: data.title
});
(function(marker, data) {

// Attaching a click event to the current marker
google.maps.event.addListener(marker, "click", function(e) {
infoWindow.setContent(data.description);
infoWindow.open(map, marker);
});
})(marker, data);
}
}
</script>
</head>
<body onload="initialize()">
<form id="form1" runat="server">
<div id="map_canvas" style="width: 500px; height: 400px"></div>
</form>
</body>
</html>



Now add following namespaces in code behind

C# Code

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
After that write the following code in code behind


// This method is used to convert datatable to json string
public string ConvertDataTabletoString()
{
DataTable dt = new DataTable();
using (SqlConnection con = new SqlConnection("Data Source=localhost;Initial Catalog=positionmaster;Integrated Security=true"))
{
using (SqlCommand cmd = new SqlCommand("select title=PlaceName,lat=PlaceLatitude,lng=PlaceLongitude,PlaceDescription from LocationDetails", con))
{
con.Open();
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dt);
System.Web.Script.Serialization.JavaScriptSerializer serializer = new System.Web.Script.Serialization.JavaScriptSerializer();
List<Dictionary<string, object>> rows = new List<Dictionary<string, object>>();
Dictionary<string, object> row;
foreach (DataRow dr in dt.Rows)
{
row = new Dictionary<string, object>();
foreach (DataColumn col in dt.Columns)
{
row.Add(col.ColumnName, dr[col]);
}
rows.Add(row);
}
return serializer.Serialize(rows);
}
}
}

VB.NET Code

Imports System.Collections.Generic
Imports System.Data
Imports System.Data.SqlClient
Partial Class Default3
Inherits System.Web.UI.Page
Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)
End Sub
' This method is used to convert datatable to json string
Public Function ConvertDataTabletoString() As String
Dim dt As New DataTable()
Using con As New SqlConnection("Data Source=localhost;Initial Catalog=positionmaster;Integrated Security=true")
Using cmd As New SqlCommand("select title=PlaceName,lat=PlaceLatitude,lng=PlaceLongitude,PlaceDescription from LocationDetails", con)
con.Open()
Dim da As New SqlDataAdapter(cmd)
da.Fill(dt)
Dim serializer As New System.Web.Script.Serialization.JavaScriptSerializer()
Dim rows As New List(Of Dictionary(Of String, Object))()
Dim row As Dictionary(Of String, Object)
For Each dr As DataRow In dt.Rows
row = New Dictionary(Of String, Object)()
For Each col As DataColumn In dt.Columns
row.Add(col.ColumnName, dr(col))
Next
rows.Add(row)
Next
Return serializer.Serialize(rows)
End Using
End Using
End Function
End Class

Open in new window

[[code added by a3 for cleanup]]
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.