I'll be the first to admit that my skills as a programmer are fairly poor.. but I was quite pleased with this idea I came up with. Below is an ASP.net page I wrote that executes a SQL query based on a querystring, the code formats the text, trims the "N 35d50'36" and does some matht o get decimal degrees, then writes the results via WriteLine to an xml document. After that I use an XML parse to read the data back in and to plot the information in Google Maps. I was having a hard time with the array I was trying to use and getting variables from basically the page_load script into the html body.. so I came up with this method instead.
This is based off of this great tutorial:
http://econym.org.uk/gmap/basic3.htm
And the msdn writeline examples I found.
<%@ Import Namespace="System.Data.Sql" %>
<%@ Import Namespace="System" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.IO" %>
<%@ Page Language="VB" MasterPageFile="~/MasterPageWider.master" Title="Find Airport by Number" %>
<script runat="server">
Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)
Page.RegisterHiddenField("__EVENTTARGET", "btnSearch")
End Sub
</script>
<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
<center>
<%
Dim connection As New Data.SqlClient.SqlConnection("Server=ad40intsql2\sv;Database=AIRPORT;uid=wasp;pwd=w4sp")
Dim queryString As String = "SELECT NfdcFacilities$.State, NfdcFacilities$.ARPLatitude, NfdcFacilities$.ARPLongitude, NfdcFacilities$.City, NfdcFacilities$.LID, NfdcFacilities$.FacilityName, projects.ProjectNumber FROM NfdcFacilities$ INNER JOIN projects ON NfdcFacilities$.LID = projects.LID WHERE (projects.ProjectNumber = '" & (Request("PRN")) & "')"
Dim command As New Data.SqlClient.SqlCommand(queryString, connection)
connection.Open()
Dim reader As Data.SqlClient.SqlDataReader = command.ExecuteReader()
Dim i
i = 0
Dim FileNameToRead As String = "out4.xml"
Dim FilePathToRead As String = "L:\webroot\"
Dim FileToRead As String = FilePathToRead + FileNameToRead
Dim TestFile As FileStream
TestFile = New FileStream(FileToRead, FileMode.Create, FileAccess.ReadWrite)
Dim Writer As New StreamWriter(TestFile)
Writer.WriteLine("<markers>")
While reader.Read()
Dim LIDA(0 to i, 0 to 6) As String
'String 0 is State
LIDA(i, 0) = reader.GetString(0)
'String 1 is Latitude
LIDA(i, 1) = reader.GetString(1)
'String 2 is Longitude
LIDA(i, 2) = reader.GetString(2)
'String 3 is City
LIDA(i, 3) = reader.GetString(3)
'String 4 is LID
LIDA(i, 4) = reader.GetString(4)
'String 5 is Project Number
LIDA(i, 5) = reader.GetString(5)
'String 6 is Facility Name
LIDA(i, 6) = reader.GetString(6)
'get LID
Dim LID0 As String = LIDA(0, 1)
'Store States in array
Dim State(i) as string
State(i) = LIDA(i, 0)
Dim City(i) as string
City(i) = LIDA(i, 3)
Dim LID(i) as string
LID(i) = LIDA(i, 4)
Dim FacilityName(i) as string
FacilityName(i) = LIDA(i, 5)
Dim PRN(i) as string
PRN(i) = LIDA(i, 6)
'get ARP Latitude from query string, break it into pieces
Dim latdegs(i) As Decimal
latdegs(i) = LIDA(i, 1).Substring(0, 2)
Dim latmins(i) As Decimal
latmins(i) = LIDA(i, 1).Substring(3, 2)
Dim latsecs(i) As Decimal
latsecs(i) = LIDA(i, 1).Substring(6, 5)
'calculate dedicmal degrees
Dim latdd(i) As Decimal
Dim longdd(i) As Decimal
latdd(i)= latdegs(i) + ((latmins(i) / 60) + (latsecs(i) / 3600))
'get ARP Longitude from query string, break it into pieces
Dim longdegs(i) As Decimal
longdegs(i) = LIDA(i, 2).Substring(0, 3)
Dim longmins(i) As Decimal
longmins(i) = LIDA(i, 2).Substring(4, 2)
Dim longsecs(i) As Decimal
longsecs(i) = LIDA(i, 2).Substring(7, 5)
'calculate dedicmal degrees
longdd(i)= longdegs(i) + ((longmins(i) / 60) + (longsecs(i) / 3600))
dim quote as string = """"
dim squote as string = "'"
dim linkquote as string = "<"
dim last as string = ">"
dim first as string = "<"
Writer.WriteLine("<marker lat=" & quote & latdd(i) & quote & " lng=" & quote & "-" & longdd(i) & quote & " html=" & squote & first & "a href=" & quote & "Default.aspx?LID=" & LID(i) & quote & last & LID(i) & " - " & FacilityName(i) & first & "/a" & last & first & "br/" & last & City(i) & ", " & State(i) & squote & "/>")
i = i + 1
End While
reader.Close()
Writer.WriteLine("</markers>")
Writer.flush()
TestFile.Seek(0, SeekOrigin.Begin)
TestFile.Close()
%>
</center>
<br />
<script src="http://maps.google.com/maps?file=api&v=2&key=YOUR_KEY"
type="text/javascript"></script>
<body onunload="GUnload()">
<!-- you can use tables or divs for the overall layout -->
<table border=0>
<tr>
<td>
<asp:FormView ID="FormView1" runat="server" DataSourceID="SqlDataSource2" Width="607px">
<ItemTemplate>
<span style="font-size: 10pt; font-family: Verdana">
<strong>Project Number:</strong>
<asp:Label ID="ProjectNumberLabel" runat="server" Text='<%# Bind("ProjectNumber") %>' >
</asp:Label><br />
<strong>Project Name:</strong>
<asp:Label ID="ProjectNameLabel" runat="server" Text='<%# Bind("ProjectName") %>' >
</asp:Label><br />
<strong>Client:</strong>
<asp:Label ID="ClientLabel" runat="server" Text='<%# Bind("Client") %>' ></asp:Label><br />
</span>
</ItemTemplate>
</asp:FormView>
<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:airportConnectionString %>"
SelectCommand="SELECT DISTINCT [ProjectNumber], [ProjectName], [Client] FROM [projects] WHERE ([ProjectNumber] = @ProjectNumber)">
<SelectParameters>
<asp:QueryStringParameter Name="ProjectNumber" QueryStringField="PRN" Type="String" />
</SelectParameters>
</asp:SqlDataSource>
<div id="map" style="width: 980px; height: 700px"></div>
</td>
</tr>
</table>
<noscript><b>JavaScript must be enabled in order for you to use Google Maps.</b>
However, it seems JavaScript is either disabled or not supported by your browser.
To view Google Maps, enable JavaScript by changing your browser options, and then
try again.
</noscript>
<script type="text/javascript">
//<![CDATA[
if (GBrowserIsCompatible()) {
// this variable will collect the html which will eventualkly be placed in the side_bar
var side_bar_html = "";
var bounds = new GLatLngBounds();
var baseIcon = new GIcon();
baseIcon.iconSize=new GSize(16,32);
baseIcon.shadowSize=new GSize(16,32);
baseIcon.iconAnchor=new GPoint(8,28);
baseIcon.infoWindowAnchor=new GPoint(8,32);
// arrays to hold copies of the markers used by the side_bar
// because the function closure trick doesnt work there
var gmarkers = [];
// A function to create the marker and set up the event window
function createMarker(point,name,html) {
var marker = new GMarker(point);
GEvent.addListener(marker, "click", function() {
marker.openInfoWindowHtml(html);
});
// save the info we need to use later for the side_bar
gmarkers.push(marker);
// add a line to the side_bar html
side_bar_html += '<a href="BLOCKED SCRIPTmyclick(' + (gmarkers.length-1) + ')">' + name + '<\/a><br>';
return marker;
}
// This function picks up the click and opens the corresponding info window
function myclick(i) {
GEvent.trigger(gmarkers[i], "click");
}
// create the map
var map = new GMap2(document.getElementById("map"));
map.addControl(new GLargeMapControl());
map.addControl(new GMapTypeControl());
map.setCenter(new GLatLng( 0,0), 0);
map.removeMapType(G_HYBRID_MAP);
map.addMapType(G_SATELLITE_3D_MAP);
// Read the data from example.xml
GDownloadUrl("out4.xml", function(doc) {
var xmlDoc = GXml.parse(doc);
var markers = xmlDoc.documentElement.getElementsByTagName("marker");
for (var i = 0; i < markers.length; i++) {
// obtain the attribues of each marker
var lat = parseFloat(markers[i].getAttribute("lat"));
var lng = parseFloat(markers[i].getAttribute("lng"));
var point = new GLatLng(lat,lng);
var html = markers[i].getAttribute("html");
var label = markers[i].getAttribute("label");
var type = markers[i].getAttribute("type");
// create the marker
var marker = createMarker(point,label,html);
map.addOverlay(marker);
bounds.extend(point);
}
// ===== determine the zoom level from the bounds =====
map.setZoom(map.getBoundsZoomLevel(bounds));
// ===== determine the centre from the bounds ======
map.setCenter(bounds.getCenter());
});
}
else {
alert("Sorry, the Google Maps API is not compatible with this browser");
}
//]]>
</script>
</body>
</asp:Content>