Using SQL Server spatial data with Spotfire

SQL Server and other relational databases provide a means of storing and querying geometric and geographic data. This can include shapes, lines, points etc. 

This article will show how to bring data from SQL Server into Spotfire and display it on a Spotfire map. Additionally we will look at how to dynamically load data using Data-On-Demand. This feature is particularly useful when the data sets are too large to load permanently into memory, or are changing with time. 

This article was written using the Spotfire 6.0 release.
The steps described below are no longer needed since Spotfire 7.14. Spatial objects from Microsoft SQL, Oracle and Postgres / PostGIS geospatial databases are now directly supported in import.

1. Expose data as WKB

Prior to Spotfire 7.14, Spotfire cannot import spatial objects directly from Microsoft SQL as the geometry data uses SQL Servers internal representation. So we need to create a view that will expose the data as a WKB (Well Known Binary) representation which Spotfire can digest. 

For more information on WKB see: http://en.wikipedia.org/s/article/Well-known_binary
For more information on converting from SQL Server to WKB see: https://technet.microsoft.com/en-us/library/bb933881.aspx
For the purpose of this exercise we assume you already have a Microsoft SQL Server running with a dataset containing geographies.

1. Start the SQL Server management studio
2. Login and navigate to the table list and navigate to the database you want to visualize on a Spotfire map
3. Right-Click on the database enter and select "New Query"
4. Right-click on the Views folder and select "New View..."
5. When the "Add Table" dialog appears, click "Close"
6. Enter the following into the SQL pane: 

   SELECT 
     tablenameID,
     tablenameName,
     geom.STAsBinary() AS Geometry
   FROM dbo.tablename

7. Save the View as "VW_tablename_WKB"

8. Right-click on the newly created view and select "Select Top 1000 Rows"

The results pane will show the content of the view. The will not be a "Spatial Results" tab. Now we are ready to bring this data into Spotfire.

2. Create an Information Link

We will use the Information Designer to setup an Information Link pointing to the VW_tablename_WKB view we just created. To keep things organized we will create a folder to store our Information Link. 

1. Start Spotfire and open "Library Administration" from the Tools menu. 
2. Create a new folder called "VW_tablename_WKB". 
3. Close the Library Administration dialog.
4. Open the Information Designer from the Tools menu. 
5. Select the "Data Sources" tab and expand the tree until the you can see the VW_tablename_KB view. 
6. Right-click on the entry and select "Create Default Information Model..." 
7. Select the folder you created in the previous step as the destination folder and click OK. The Information Link will be created. 
8. Close the Information Designer. 
9. Open the newly created Information Link from the Library. Switch the visualization to a Table if it is not the default view. You will notice that there is no geometry data.
10. Before we proceed, we will need to tell Spotfire how to interpret the Geometry column correctly. Re-open the Information Designer
11. Expand the Elements tree and locate the Geometry element of the VW_tablename_WKB view. 
12. Double-click on the Geometry item to open the Column Element dialog and expand the Properties section. 
13. Click the "Add..." button to add a new property. 
14. Set the property name to "MapChart.ColumnTypeId" and the value to "Geometry". 
15. Click OK to save the new property. 
16. Repeat the above process to create a property called "ContentType" with a value of "application/x-wkb". 

At this point if you close and re-open the Information Link, you should now see the spatial object from your table displayed in the Table Visualization.

Troubleshooting Note:  If the Table Visualization does not render the spatial objects, you more than likely did not create the "view" from Step 1; therefore, you will need to change the "Geometry" Column Element -->Expression:  %1.STAsBinary()     [default is %1]

Now we can go ahead and display the data on the Map Chart.  Switch the visualization to Map Chart.

3. Setup a Data-On-Demand query

Now we can use our foundational steps to create a Data-On-Demand example. 

1. Create a new DXP file and open the VW_tablename_WKB Information Link. 
2. Rename the Data Table and set the data to be Embedded. 
3. Delete the Geometry Column from the data table. 
4. Add a Table Visualization of the "tablename" data. Remove the tablenameID column. Call the visualization "tablename". 
5. Save the DXP file as "Spatial Data On Demand". 
6. Click on File > Add Data Table. On the dialog click Add > Information Link. 
7. Select the VW_tablename_WKB Information Link. 
8. Check the "Load on demand" option and click the "Settings..." button. 
9. In the "On-Demand Settings" dialog, click on the "Define Input..." button and select the "tablenameID" column from the "tablename" data table. 
10. Click OK. 
11. Repeat the previous steps for the "tablenameName" field this time selecting "tablenameName" from the "tablename" data table. 
12. Check the option to "Load automatically" and click OK and then OK again. 
13. Spotfire will open a Map Chart on a new page. We need to do some further configuration so just go ahead and delete the entire page. 
14. Add a Table Visualization below the "tablename" table visualization. Point it to the VW_tablename_WKB data. 
15. When you select an entry in the "tablename" table visualization, the corresponding item will show up in the VW_tablename_WKB table visualization. 
16. Add a Map Chart visualization and position it to the right with the two table visualizations stacked in the left pane, one above the other. 
17. In the Map Chart options, remove the "tablename" layer and add a Feature Layer using the VW_tablename_WKB data table. 
18. In the Appearance tab, check the "Auto-zoom" option. 

Now when you select an entry in the "tablename" table visualization, the corresponding item will show up in the VW_tablename_WKB table visualization and be highlighted on the Map Chart.