Sunday, November 30, 2008


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.

Saturday, November 29, 2008


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.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.WriteAttributeString("version", "2.0")
'xmlWriter.WriteStartElement("rdf:RDF", "")
'xmlWriter.WriteAttributeString("xmlns", "rdf", Nothing, "")
xmlWriter.WriteAttributeString("xmlns", "geo", Nothing, "")
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
End If

Dim geoReader As SqlDataReader = Create_Geo_reader(con)
Dim q As String = """"
If Not IsNothing(geoReader) Then
If geoReader.HasRows Then
Do While geoReader.Read

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.WriteElementString("link", "")
xmlWriter.WriteElementString("id", id)

xmlWriter.WriteAttributeString("url", "")

xmlWriter.WriteElementString("geo:lat", CStr(lat))
xmlWriter.WriteElementString("geo:lng", CStr(lng))

'close item element
End If
End If

If con.State = ConnectionState.Open Then
End If

Catch ex As Exception
End Try

End Sub

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

Private Function Create_Connection() As SqlConnection
Dim Connection As New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("baseBSWstr").ConnectionString)
Return Connection
Catch ex As Exception
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]"

If Connection.State = ConnectionState.Closed Then
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
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.

Thursday, November 27, 2008

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
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 _
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)

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 & "')"

i += 1


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

Tuesday, November 25, 2008

Book Recommendation

A colleague let me glance over this book by Chang. I have to say it is one of the better introductory GIS texts I have seen. I've owned a few. The one for my undergrad courses was weak, but in fairness it was one of the least expensive textbooks I had to purchase. Longley et al. produced a nice one as well. I would go with Chang over Longely, though. Chang's book is ESRI-biased, but I think it is of value to everyone. There are a number of worked examples that show how the little black box works, e.g. affine transformations, and ordinary kriging. I'm not talking about point and click, I mean actual mathematics. My colleague also said the section on modeling is excellent, but I didn't look through it in great detail.

Unfortunately the book budget is a little low right now, as I've made a number of purchases recently so I don't own a copy. Speaking of which, I also recommend Bivand et al.'s spatial statistic book for R. This fills a void in Spatial Statistic books that has been growing. Most spatial stat books focus heavily on theoretical, which is fine, but for someone like me that is not in a course there is a lack of worked examples. Waller and Gotway's book is theoretical and has exercises, but now answer key so who knows if I produced the "correct" results....Andy Mitchell's book is a great start, but doesn't go into much depth (doesn't talk much about first and second order effects, doesn't talk about inhomgeneous k-functions, etc...)

Thursday, November 6, 2008

Find values in one table that are missing in another table MapInfo

Took me a little while to find an example of this, so I thought I would add one to the "interweb" to help other people.

One part of MapInfo that is extremely powerful is the SQL functionality. It isn't comprehensive, but there are a number of things you can do with it. Where I'm currently living/working (The Northern Territory) has a number of communities that could have several different names for the same location. This comes from communities named by European settlers in the area, and Indigenous names. Basically, you can have a spreadsheet of data that you want to tie to a geographic location via the name of an organisation, but the spreadsheet names might not necessarily match the database names that have the latitude and longitude. So I want to find out which values in the spreadsheet are "missing" from the database. In other words, which values did not join.

Two Tables: sdss_geography and tutorial_sample_2
Two Columns: sdss_geography.Organisation_Name and tutorial_sample_2.School

The first step is to perform a Join on the two tables. Using Query --> SQL Select

Select Columns: *
From Tables: sdss_geography, Tutorial_Sample_2
Where: sdss_geography.Organisation_Name = Tutorial_Sample_2.School
Ordered By: School
into Table Named: InitialJoin

Next go to File --> Save Query and save the InitialJoin query as a table. Then close the query table and load the table.

Once the table is loaded, go back to Query --> SQL Select.

Select Columns: *
From Tables: Tutorial_Sample_2
Where: not School in (select School from InitialJoin)
Ordered By: School
into Table Named:MissingJoin

Click OK.

This produces a table of missing values. I take this table and make sure the name and spelling matches the central database.

Hopefully that helps someone else.

Monday, November 3, 2008

Does everyone have an API?

Came across this last night when I couldn't sleep (yes I'm even looking at maps at 2am :)).

It started through a great blog called Kelso's Corner. It's a cartography/visualizations oriented blog/website, but he posts examples of some great (mostly interactive) cartography. A lot of them, not surprisingly, come from the New York Times. Newspapers don't always produce the greatest or most appropriate maps, but the NYTimes seems to make the extra effort at doing this (The Economist is another one that does a great job). Anyway, I "discovered" that they have an interactive visualization creator called Visualization lab. They have a number of visualization techniques available for the user to create their own interactive visualization. According to this, it is based on technology from IBM Research. I didn't explore too deeply, so I don't know if the NYTimes site allows you to load your own data, but the Many Eyes does. Honestly, I'm a bit wary of these types of "map your own data" neogeography things. But I think the NYTimes site has done a great job of restricting users so that they make an appropriate map (e.g. a choropleth using derived data instead of raw counts). They are even using a good map projection for their world map data! I'm not too keen on the bubble visualization, because when I see it I expect the countries to be in the right place, so I find it hard to read. I guess I couldn't really find any order to the arrangement (hey I'm a Geographer, I look for spatial patterns automatically). Either way, I thought it was pretty well done, and fast.

Naturally when I see something like the Visualization lab, I immediately think "How can I do that?" Well the NYTimes has made it easier for me to try and make my own. They didn't release the code or anything, but they now have their own data API. One of the first that they have released is the campaign finance api. I believe this would be the same data used for creating this interactive map.