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

February 21, 2012

Filed under Mathematica, SQL Server

Tags: Geospatial, Map, Mathematica, Microsoft SQL Server, SQL Server

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.

## Connecting Mathematica to SQL Azure Using Microsoft’s JDBC Drivers

December 21, 2011

Filed under Mathematica, SQL Azure

Tags: ClassPath, Java Database Connectivity, JDBC, Mathematica, Microsoft, Microsoft SQL Server, SQL, SQL Azure

This post will walk you through establishing a database connection in Mathematica to SQL Azure using Microsoft’s SQL Server JDBC driver. SQL Azure is Microsoft’s cloud based database service. I’ve personally enjoyed using SQL Azure for working with data where latency isn’t a factor. A personal example of where it’s made my life easier is that I’ve used SQL Azure in the classroom to expedite the grading process, dropping the amount of time from days to hours between work done by the students and their reflected grade. I’ve received a lot of compliments from my students for being able to do so.

This walk through connects to the SQL Azure AdventureWorks database which you can download here.

The first thing to do is download the JDBC driver and unpack it to C:\Program Files\Microsoft SQL Server JDBC Driver 3.0\.

Once the driver is unpacked, I navigated to sqljdbc_3.0 and removed the sqljdbc.jar file and left sqljdbc4.jar. When I was working with Wolfram support (who, by the way, are extremely knowledgable and professional), we had some issues with ClassPath and the two drivers. The way we resolved this is by removing that file.

Once you’re just left with sqljdbc4.jar, open up Mathematica and create a new notebook file. The notebook will require both Jlink and DatabaseLink so use the Needs[] command to import both of those.

Next, we’ll need to add the Microsoft JDBC driver to the class path so execute:

AddToClassPath[“C:\\Program Files\\Microsoft SQL Server JDBC Driver\\sqljdbc_3.0\\enu\\sqljdbc4.jar”];

Like so:

Then execute:

LoadJavaClass[“com.microsoft.sqlserver.jdbc.SQLServerDriver”];

Like so:

Now it’s time to connect to SQL Azure with Mathematica. The command and parameters you’ll use look like the following:

queryazure = conn=OpenSQLConnection[JDBC[“com.microsoft.sqlserver.jdbc.SQLServerDriver”,”jdbc:sqlserver://<SERVER>.database.windows.net;databaseName=AdventureWorksLTAZ2008R2;user=<USERNAME>@<SERVER>;password=<PASSWORD>;”],”Username”->”<USERNAME>@<SERVER>”,”Password”->”<PASSWORD”]

My connection appears as the following (I’ve blotted out the sensitive information):

Once the connection is made, you can confirm your connection by executing

SQLTableNames[queryazure]

Finally, let’s query for something specifically. Below is a screenshot looking at the SalesLT.Product table via the web interface:

One may can also connect with SQL Server Management Studio 2008 R2 as well:

The query we will want to execute is a simple one: “SELECT * FROM SalesLT.Product”. To do so, we just execute the following:

query =SQLExecute[queryazure, “SELECT *

FROM SalesLT.Product”]

The output is as follows:

That’s it! Enjoy playing with Mathematica and SQL Azure! There’s lots of blog posts on different things to do and they can be applied to SQL Azure just the same. Some of my favorites are: Graphing a Hierarchy from a Common Table Expression Using TreePlot, Graphing Foreign Keys in SQL Server Using Mathematica, and Foreign Keys as an Incidence Matrix: A High Level Look.

Finally, I want to credit Wolfram support for their assistance in getting me to connect. They worked with me in fixing my erronious JDBC configuration in Mathematica. Without them, this post wouldn’t be possible. Thank you Wolfram support!

## The AdventureWorks Database as a Transversal Matroid: Part I – Generating a Bipartite Graph

September 18, 2011

Filed under Database, Mathematica

Tags: AdventureWorks, bipartite, Graph, Mathematica, Matroid, SQL

In previous posts, we took the foreign keys present in the AdventureWorks2008R2 database and used them to represent the database as a multigraph, a simple graph, and an incidence matrix. We even calculated properties such as graph radius and periphery. Another way to investigate relationships between objects is to present them as a matroid (or combinatorial pre-geometry).

To start, the simple graph of the finite key relationships present in the AdventureWorks database is already a cycle matroid. This is a very well known result as all finite graphs are cycle matroids. The independent sets in a cycle matroid are the edges that do not form a closed path. Arguably, the cycle matroid derived from the AdventureWorks database graph may have some interesting properties.

Instead of investigating the properties of this cycle matroid, we will investigate the AdventureWorks database as a transversal matroid. The definition of a transversal matroid in “Matroid Theory” by James Oxley is as follows:

A way to visualize transversals is via bipartite graphs:

The AdventureWorks database actually works as a great candidate for a transversal matroid. The family of subsets of the AdventureWorks database is the schemas. The AdventureWorks2008R2 database has the following schemas: dbo, HumanResources, Person, Production, Purchasing, and Sales. It’s easy to query the database for its schemas:

SELECT DISTINCT SCHEMA_NAME(schema_id) FROM sys.tables

The expected output is shown below.

To visualize the relationship between tables and schemas, we will need to create a bipartite graph of the relationship. We will create a connection to the database the usual way. Once that is accomplished, we will need to run the following command:

The reason we use CASE in the T-SQL statement is because there’s a table named Person too. So to take care of the fact that there’s a schema and a table of the same name, we just rename it. An partial listing of the output is show below:

We will need to combine the two columns into something Mathematica will like to work with for graphing.

I tried having the SQL query seamlessly put out columns of the form “SpecialOffer” -> “Sales”. While the query executed the way one would expect in SSMS, the parser Mathematica uses choked on it. So here we are, inelegantly combining the results. It is what it is. Perhaps I’ll find the trick.

We apply the custom function Combine to the query result set.

Finally, it’s time to see something worth looking at. The command below:

Returns:

To see the entire graph, click on the image. It’s very large.

While we’ve generated a great looking image of the relationship between schemas and tables in the AdventureWorks database, we have not yet described the matroid yet. In the second part of this series, we will generate the maximal independent sets to describe this matroid. I’m looking forward to it!