Displaying polygons on Google Maps from SQL Server geography data type

Question

I have an SQL Server 2008 database with a column of type geography storing the shape of various Australian regions. I want to be able to draw these shapes on Google Maps.

This is for a ASP.NET C# website.

I have searched for any samples of how to do this but cannot find anything?

Does anyone have some samples of how to do this, specifically using geography data from SQL Server?

Answer 1

The answer from AdamW is correct, however doesn't address the data being in the SqlGeography Data format.

Include a reference to Microsoft.SqlServer.Types

SqlCommand cmd = new SqlCommand("SELECT STATEMENT",ConnectionString);
connectionString.Open();
SqlDataReader polygon = cmd.ExecuteReader();

While (polygon.read())
{
  string kmlCoordinates = string.Empty;
  SqlGeography geo = (SqlGeography)polygon["GeoColumn"];
  for(int i = 1; i <= geo.STNumPoints(); i++)
  {
       SqlGeography point = geo.STPointN(i);
       kmlCoordinates += point.Long + "," + point.Lat + " ";
  }
{

ConnectionString.Close();

Note: Geography points are 1 indexed not 0 indexed, and it is not foreach friendly either.

Answer 2

I have used KML files in the past to overlay polygons on webpages.

I would suggest reading googles KML tutorials

• Create a function to read from your database
• Create KML File
• Call KML File from Google API

While KML provide you with a quick and easy way to overlay shapes, Google do place a limit on the number of items displayed.

The below should help get you started with the KML approach.

public ActionResult Kml()
    {
        DataAccess da = new DataAccess();
        string cellColor = "0032FB";

        string kml = @"<?xml version=""1.0"" encoding=""UTF-8""?>
        <kml xmlns=""http://earth.google.com/kml/2.1"">
            <Document>
                <Style id="polygon">
                    <LineStyle>
                        <color>FF" + cellColor + @"</color>
                    </LineStyle>
                    <PolyStyle>
                        <color>44" + cellColor +@"</color>
                        <fill>1</fill>
                        <outline>1</outline>
                    </PolyStyle> 
                </Style>
                <name>some name</name>
                <description>some des</description>

        ";
        DataTable polygons;

        foreach (DataRow polygon in polygons.Rows)
        {
                kml += @"
                    <Placemark>
                        <name>"somename @"</name>
                        <description><![CDATA[<p>some text</p>]]></description>" +
                        @"<styleUrl>#polygon</styleUrl>
                        <Polygon>
                            <extrude>1</extrude>
                            <altitudeMode>clampToSeaFloor</altitudeMode>
                            <outerBoundaryIs>
                                <LinearRing>
                                    <coordinates>" +
                                        polygon["Cell Limit  Longitude West"].ToString() + "," + polygon["Cell Limit Latitude North"].ToString() + " " +
                                        polygon["Cell Limit Longitude East"].ToString() + "," + polygon["Cell Limit Latitude North"].ToString() + " " +
                                        polygon["Cell Limit Longitude East"].ToString() + "," + polygon["Cell Limit Latitude South "].ToString() + " " +
                                        polygon["Cell Limit  Longitude West"].ToString() + "," + polygon["Cell Limit Latitude South "].ToString() + " " +
                                        polygon["Cell Limit  Longitude West"].ToString() + "," + polygon["Cell Limit Latitude North"].ToString() + " " +
                                    @"</coordinates>
                                </LinearRing>
                            </outerBoundaryIs>
                        </Polygon>
                    </Placemark>
                ";
        }

        kml += @"</Document>
        </kml>";
        byte[] data = Encoding.ASCII.GetBytes(kml);

        return File(data, "application/vnd.google-earth.kml+xml", id);
    }

Javascript

var url = 'http://www.example.com/AppName/GMap/file.kml &rand=' + Math.random();

layer_paperCharts = new google.maps.KmlLayer(url);

if (loadedonce) {
  layer_paperCharts.set('preserveViewport', true);
} else {
  loadedonce = true;
}

layer_paperCharts.setMap(map);

Google Caches KML files so the addition of the Math.random() will get around this.

You could also look at Fusion Tables. However you must upload your data to Google. Also Google group the presented data. But you want SQL so this option may not be available to you.