Camels

Well, the final report on the Camel project has officially been released. Here is a link where you can download it. There is a separate report (54) for the stuff I worked on. THis was incorporated into chapter 11 of the main report. Essentially it is a multi-criteria evaluation using weighted linear combination. There is python code in the appendix for performing (modularly) a weighted linear combination, including calculating weights. To calculate the weights you need NumPy.

Google Earth In Marketing

Was in Brisbane last week and noticed a new marketing campaign for Qantas. Here is a picture of one their posters. Map pushpins have been around since the seventies at least, that is according to old cop movies. But I think the notion of a pushpin being put into 3-dimensional space is really influenced by Google Earth. Perhaps I can even coin a term - ge-marketing. :)

Cool

I do have interests outside of GIS and Geography. One is movies...It's great when the two meet. Here is a really beautiful information visualization of movie revenues for the past 30 years. To me it looks the way oil paint can layer together. Very well made.

Correction Manifold and SQL Server 2008

I misunderstood about Manifold and SQL Server 2008. It sounds like you actually need the Enterprise edition of the product to use the native spatial data type...A point for MapInfo on this one, minus 1 for Manifold....If anyone is keeping a tally.

Tutorials

My tutorials page is fairly dormant, which I apologize for. I'd like to add to it, but I've been busy. Also, the page is hosted on Google Pages, and it has been announced that Google Pages will be discontinued and migrated to Google Sites. I think having the tutorials on Google Sites will actually be better, but I'm not sure when this "migration" will actually happen. I just hope that I don't lose any of that work...

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.

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