Plotting Latitude/Longitude Points in Mathematica using the SQL Server 2008 Geography Data Type

When Microsoft released SQL Server 2008, they introduced a new data type with it called the geography data type. They geography data type filled an important gap in SQL Server data types where previously stored data was usually held as latitude and longitude values in separate columns. The geography data type directly addresses this gap and implements methods as specified by the Open Geospatial Consortium. Mathematica also ships with a full retinue of geospatial functionality and has the ability work with shapefiles and much, much more. It would a nice exercise and great learning opportunity to combine the two.

We would like to extract geospatial information from the AdventureWorks database and plot it in Mathematica. The AdventureWorks database ships with geographic data contained in the Person.Address table.


Let’s assume that we want to extract sales information regarding where products are being shipped to from the AdventureWorks database. If we create a database diagram in SQL Server starting with the Person.Address table and add related tables, we will have a diagram similar to the one below.


In this diagram, we can see that the Person.Address table and the Sales.SalesOrderHeader table are related to each other by a foreign key relationship.

With the column and referential information we have in hand, we’re ready to build a candidate query to extract this information. In this particular case, we’re only interested in plotting lat/long points and so we will use the Lat and Long from Microsoft’s extended methods for the geography data type.

The query itself is a simple inner join pulling lats and longs from the Person.Address table and joining them on the SalesOrderHeader table.

SELECT DISTINCT TOP 500 SpatialLocation.Lat AS Lat, SpatialLocation.Long AS Long

FROM Person.Address INNER JOIN Sales.SalesOrderHeader

ON Person.Address.AddressID = SalesOrderHeader.ShipToAddressID

The output below should be what you get:


With this data in hand, it’s time to move over to Mathematica. We will want to connect to SQL Server using Database Link. Once that is done, we will want to set up a SQLExecute to use the connection we have built and execute the query above. My Mathematica notebook looks like the following:


Next, we will need to use GeoGridPosition and GeoPosition to graph the points on a map. To do that, we will use the following:

Graphics[{Red,Point/@Map[GeoGridPosition[GeoPosition[#],”Mercator”][[1]]&,{spatialquery},{2}],Gray,Polygon[Map[GeoGridPosition[GeoPosition[#],”Mercator”][[1]]&,CountryData[#,”Coordinates”],{2}]]&/@CountryData[“Countries”]}]

The output should then be as follows (click picture for larger size):


It looks like most of our sales are going to the west coast of the United States, the east coast of England and the east coast of Australia.

As a side note, this post couldn’t be possible without the very helpful users at the Mathematica StackExchange. I’ve never worked with geospatial data in Mathematica or SQL Server before and I wanted to learn about how to do this in Mathematica and found myself without the ability to chart a direction. I posted a question and within hours the answers were all pouring in. It’s a pity I can only accept one reply as the answer. The Mathematica code I’m using in this post is borrowed from the accepted solution to my question here.

One Response to “Plotting Latitude/Longitude Points in Mathematica using the SQL Server 2008 Geography Data Type”

  1. Building a Simple Map in SSRS « Cold Logics

    […] a previous post we discussed mapping the SQL Server geography data type in Mathematica. Now we’re going to […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s