SQL Server 2008 Express

I've finally gotten a chance to play around with SQL Server 2008 express edition. In case you haven't heard 2008 introduced native spatial support. I'm not sure how the internals store spatial data but you can add geometry (or geography) data types as Well-Known Text or Well-Known Binary. Well-Known Text is pretty easy to understand and work with. It's pretty easy to work with SQL Server using the Management Studio Express Edition. If you have MapInfo 9.5, or Manifold version 8 you can directly connect to express edition and edit geometry that way. I don't think there is support for this in ArcGIS 9.3 without SDE (personal or otherwise) which means you probably need an ArcEditor license. I happened to have an XML file with latitude and longitude locations in it that I used to store locations for a mini (personal) google maps project. Using VB.NET 3.5 I could easily connect to both the xml file and sql server to add the geometry as point locations.

Here is the code that I used. It is server side code, with the connection string stored in the web.config file.



    Dim xDoc As New XmlDocument
    xDoc.Load(Server.MapPath("cities.xml"))
    Dim cNodes As XmlNodeList
    cNodes = xDoc.GetElementsByTagName("marker")
    Dim i As Integer = 0
    Dim con As Data.SqlClient.SqlConnection
    con = New Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings _
("baseBSWstr").ConnectionString)
    For Each XNode As XmlNode In cNodes
        Dim ac As XmlAttributeCollection = XNode.Attributes

        Dim lat As Double = CDbl(ac.Item(0).InnerText.ToString)
        Dim lng As Double = CDbl(ac.Item(1).InnerText.ToString)
        Dim name As String = CStr(ac.Item(2).InnerText.ToString)

        con.Open()
        Dim cmd As New SqlCommand("INSERT INTO geograph_data
VALUES (" & i & "," & "geometry::STGeomFromText('POINT
(" & lng & " " & lat & ")', 0), '" & name & "')", con)
        'cmd.CommandText = "INSERT INTO geograph_data
VALUES (" & i & "," & "geometry::STGeomFromText('POINT
(" & lat & " " & lng & ")', 0), '" & name & "')"
        cmd.ExecuteNonQuery()

        con.Close()
        i += 1

    Next

This was just run on my hard drive with a local instance, so I wasn't too concerned about security. From what I understand using parameters is meant to be more secure. Here is an example.

Now if you want to play around with sql server 2008 with just management studio here are some tutorials.
Boston GIS
JasonFollas - this describes the difference between geometry and geography...pretty good series.
Developer Fusion
MSDN