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:
The output should then be as follows (click picture for larger size):
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.