[GIS] SQL Server to Google Maps

google-maps-apisql server

Does someone have a beginning to end example of how to take data from a SQL Server geography type field and pass it to the Google Maps API to display the shape on a map? The geography data I'm storing is either a polygon or multipolygon, some of which have holes in them. I would like to do this using .NET code and JavaScript on the fly with whatever data is stored.

Best Answer

Here is how I did it using MVC and VB.NET

This SchoolDistrictVertices function is in a partial class of a SchoolDistrict object which was generation using EntityFramework from a SQL database. It contains a geography field named DistrictLocation. 

Public Structure Coordinate
  Public Property Latitude As Double
  Public Property Longitude As Double
End Structure

Public Function SchoolDistrictVertices() As List(Of List(Of Coordinate))
  If SchoolDistrict Is Nothing OrElse SchoolDistrict.DistrictLocation Is Nothing Then
    Return Nothing
  Else
    'get the points that define the shape by parsing the shape's WKT
    Dim result = New List(Of List(Of Coordinate))
    Dim wkt = SchoolDistrict.DistrictLocation.Location.WellKnownValue().WellKnownText
    Dim startPos = wkt.IndexOf("((")

    Do Until startPos = -1
      Dim list = New List(Of Coordinate)
      Dim endPos = wkt.IndexOf(")", startPos)
      Dim piece = wkt.Substring(startPos + 1, endPos - (startPos + 1))
      Dim coords = piece.Split(","c)

      For Each item In coords
        item = item.Trim().Replace("(", "")
        Dim parts = item.Split(" "c)
        Dim lng = parts(0)
        Dim lat = parts(1)
        Dim coord = New Coordinate()
        coord.Latitude = Convert.ToDouble(lat)
        coord.Longitude = Convert.ToDouble(lng)

        list.Add(coord)
      Next
      result.Add(list)

      startPos = wkt.IndexOf("(", endPos)
    Loop

    Return result
  End If

End Function

Here is the View file which takes a SchoolDistrict object as the model:

@<div style="margin-top: 5px; height: 500px" id="map-canvas">
</div>

@Section Scripts
<script src="https://maps.googleapis.com/maps/api/js?v=3.exp&signed_in=false"></script>

<script>
  $(function () {
    google.maps.event.addDomListener(window, 'load', InitializeMap());
  });

  function InitializeMap()
  {
    var mapOptions = {
      zoom: 12
    };
    var map = new google.maps.Map(document.getElementById('map-canvas'),  mapOptions);


    @code
      Dim count = 0
      Dim shapeList = ""
    End Code
    @If Model.SchoolDistrict IsNot Nothing Then
  @For Each shape In Model.SchoolDistrictVertices()
  @:var coordList@(count) = [
    @For Each coordinate In shape
    @:new google.maps.LatLng(@coordinate.Latitude, @coordinate.Longitude),
    Next

                     If shapeList <> "" Then
                       shapeList += ", "
                     End If
                     shapeList += "coordList" + count.ToString()
                     count += 1
    @:];
         Next

    @:districtShape = new google.maps.Polygon({paths: [@shapeList], strokeColor: '#A366E0', strokeOpacity: 0.8, strokeWeight: 1, fillColor:  '#A366E0', fillOpacity: 0.35});
                    @:districtShape.setMap(map);
                         End If
  }

</script>
End Section
Related Question