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.