You've Got the Points - Now What?

So in the last post I showed how I got some points into SQL Server 2008. Not the most elegant bit of code (I'm a dabbler not a developer), but it worked. Now what can you do with it. Besides the many Transact SQL (Microsoft own brand of SQL) methods available for interacting with spatial data (see OGC ones here), you could create your own GeoRSS feed. I'm fairly new to creating RSS feeds, so this might not be exactly correct, but you may find the query useful. The key here is adding a reference to Microsoft.SqlServer.Types. Then you can actually use their geometry type and the methods associated with it to retrieve your results.

This is all in an generic handler (*.ashx). The Imports:

Imports System
Imports System.Web
Imports System.Xml
Imports System.Data
Imports System.Configuration
Imports System.Collections
Imports System.Web.Security
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.Web.UI.WebControls.WebParts
Imports System.Web.UI.HtmlControls
Imports System.Data.SqlClient
Imports System.Net
Imports System.Threading
Imports Microsoft.VisualBasic
Imports System.IO
Imports Microsoft.SqlServer.Types

And the Class:



Public Class geo_content : Implements IHttpHandler
  
   Public Sub ProcessRequest(ByVal context As HttpContext) Implements IHttpHandler.ProcessRequest

      
       context.Response.ClearHeaders()
       context.Response.Clear()
       context.Response.ContentType = "text/xml"
       context.Response.ContentEncoding = System.Text.Encoding.UTF8
       context.Response.AddHeader("ContentType", "text/xml; charset=utf-8")
      
       Dim xmlWriter As New XmlTextWriter(context.Response.Output)
       xmlWriter.WriteStartDocument()
       xmlWriter.WriteStartElement("rss")
       xmlWriter.WriteAttributeString("version", "2.0")
       'xmlWriter.WriteStartElement("rdf:RDF", "http://www.w3.org/1999/02/22-rdf-syntax-ns#")
       'xmlWriter.WriteAttributeString("xmlns", "rdf", Nothing, "http://www.w3.org/1999/02/22-rdf-syntax-ns#")
       xmlWriter.WriteAttributeString("xmlns", "geo", Nothing, "http://www.w3.org/2003/01/geo/wgs84_pos#")
       xmlWriter.WriteStartElement("channel")
       xmlWriter.WriteElementString("title", "geoContentRSS")
       xmlWriter.WriteElementString("description", "latitude and longitude from sql server 2008")
       xmlWriter.WriteElementString("language", "en-US")

       Dim con As SqlConnection = Create_Connection()

       If con.State = ConnectionState.Closed Then
           con.Open()
       End If

       Dim geoReader As SqlDataReader = Create_Geo_reader(con)
       Try
           Dim q As String = """"
           If Not IsNothing(geoReader) Then
               If geoReader.HasRows Then
                   Do While geoReader.Read
                       xmlWriter.WriteStartElement("item")
                  
                       Dim geom As New SqlGeometry
                       geom = CType(geoReader("Geom_Data"), SqlGeometry)
                       Dim lat As Double = CType(geom.STY, Double)
                       Dim lng As Double = CType(geom.STX, Double)
                       Dim id As Integer = CType(geoReader("GeoID"), Integer)
                       Dim name As String = CType(geoReader("Name"), String)
                  
                       xmlWriter.WriteStartElement("title")
                       xmlWriter.WriteCData(name)
                       xmlWriter.WriteEndElement()

                       xmlWriter.WriteStartElement("description")
                       xmlWriter.WriteCData(Name)
                       xmlWriter.WriteEndElement()

                       xmlWriter.WriteElementString("link", "http://www.boxshapedworld.com")
                       xmlWriter.WriteElementString("id", id)

                       xmlWriter.WriteStartElement("source")
                       xmlWriter.WriteAttributeString("url", "http://www.boxshapedworld.com")
                       xmlWriter.WriteString(name)
                       xmlWriter.WriteEndElement()
                      
                       xmlWriter.WriteElementString("geo:lat", CStr(lat))
                       xmlWriter.WriteElementString("geo:lng", CStr(lng))
                      
                       'close item element
                       xmlWriter.WriteEndElement()
                   Loop
               End If
           End If

           If con.State = ConnectionState.Open Then
               con.Close()
           End If

       Catch ex As Exception
           MsgBox(ex.ToString)
           con.Close()
       End Try

       xmlWriter.WriteEndElement()
       xmlWriter.WriteEndElement()
       xmlWriter.WriteEndDocument()
       xmlWriter.Close()
   End Sub

   Public ReadOnly Property IsReusable() As Boolean Implements IHttpHandler.IsReusable
       Get
           Return False
       End Get
   End Property

   Private Function Create_Connection() As SqlConnection
       Try
           Dim Connection As New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("baseBSWstr").ConnectionString)
           Return Connection
       Catch ex As Exception
           MsgBox(ex.ToString)
           Return Nothing
       End Try
   End Function
  
   Private Function Create_Geo_reader(ByVal Connection As SqlConnection) As SqlDataReader
       Dim programReader As SqlDataReader
       Dim sqlStatement As String
       Dim command As New SqlCommand
       Dim dt As New DataTable("geograph_data")
      
      
       sqlStatement = "SELECT * FROM geograph_data WHERE [GeoID] <> @negGeoID ORDER BY [Name]"
      
      
       Try
           If Connection.State = ConnectionState.Closed Then
               Connection.Open()
           End If

           With command
               .CommandText = sqlStatement
               .Parameters.AddWithValue("@negGeoID", -9999)
               .CommandType = CommandType.Text
               .Connection = Connection
           End With

           programReader = command.ExecuteReader
           Return programReader
       Catch ex As Exception
           MsgBox(ex.ToString)
           Connection.Close()
           Return Nothing
       End Try
      
   End Function
End Class

This starts by creating an xmltextwriter that is set to the context output stream. I add the xml namespace for geo. There is a separate function to create the connection string, and a separate function to create the sqldatareader. In this function you see a simple SELECT statement with parameters for the where value. This is returned and the geoReader starts to cycle through each row. A variable called geom is used to house the geometry, and you can see that it is of a type SqlGeometry. Using this type gave access to the STY and STX methods. Again, not the most elegant bit of code, but it works.