Using SQL Server 2008 with Google Maps.

The latest version of SQL Server (SQL Server 2008) supports spacial types.  Specifically, there are 2, Geography and Geometry.  The former can be used to represent shapres and points on the earths surface.   The goal of this excercise is to leverage this technology in a simple Google Maps app.  Google documentation has examples using MySql as the DB engine and it’s native types for Google Map applications, and there are other posts on using SQL Server with Microsofts Virtual Earth technology.

The functionality will be quite simple, the app will:

1. Draw a map, and place markers for cities that it extracts out of the database. 
2. Provide a text box into which the user can enter a distance (in miles), to search.
3. When user clicks “GO!” the app will place a secondary marker for those cities that are within the specified distance of New York.

To get started you need to install SQL Server 2008 (express or full version will work), and sign up for Google Maps (you will recieve a key which you need to place in the code).  I also used VS ‘08 with the MVC extensions.  The code (which you can download here) should be fairily straight forward so I’ll dive right into the interesting bits. I created one table called ‘Cities’ with two columns: ‘Name’ of type varchar and ‘Coordinates’ of type geography, with ‘Name’ the primary key.  To insert data into the table I used the following syntax:

INSERT INTO [google_maps_test].[dbo].[Cities]
           ([Coordinates]
           ,[Name])
     VALUES
           ('POINT (-73.58 40.47)'
           ,'New York' )

with longitude preceeding lattitude.  Note that POINT is but one of several variants of the Geography type.

Now to the C# side.  The Index action simply puts  the collection returned by the following function:

public static List<City> GetCities()
        {
            using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["google_maps_testConnectionString"].ConnectionString))
            {
                SqlDataAdapter adapter = new SqlDataAdapter("SELECT * FROM CITIES", connection);
                DataSet dataSet = new DataSet();
                adapter.Fill(dataSet);
                List<DataRow> drl = new List<DataRow>();
                foreach (DataRow row in dataSet.Tables[0].Rows)
                    drl.Add(row);
                return drl.ConvertAll<City>(x => (new City((string) x["Name"], (Microsoft.SqlServer.Types.SqlGeography)x["Coordinates"])));
            }
        }

into the view data.  Note that you have to download Microsoft SQL Server System CLR Types here for this and add reference to Microsoft.SqlServer.Types for this to work.  The View code displays the info in a hidden div, and javascript is used to parse this, and send it to the map object. The only other thing I’ll note is the actual query used to get all the cities within a certain distance:

SELECT Name from Cities c INNER JOIN (SELECT TOP 1 Coordinates FROM Cities WHERE name = 'New York') As NewYork On NewYork.Coordinates.STDistance(c.Coordinates) < @radius * 1000 /0.621371192

1000 /0.621371192 is to convert from meters to miles. 

You can download the sourcode here.  It should look like the so:

 

A couple of quirks I ran into:

1. When I tried to create a LINQ to Sql connection I was greated with: “One or more selected items contain data type that is not supported by the designer.”  So spacial types are not supported by LINQ.
2. When I tried to use the Microsoft.SqlServer.Types.SqlGeography type in the aspx code I recieved a “System.Web.HttpCompileException was unhandled by user code” error.  Still not quite sure how one would get around that one without just moving it to C# which is what I did.

The code may use a bit of cleanup especially wrt not having to create the db and mess with connections strings.  If you have any questions/problems let me know.