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.
Step 1: Get some SQL Server spatial data
For the purpose of this exercise we will use some publicly available data sets. Download the attached States SQL Table (or navigate to the following site and download the States Table: https://uscdsql.codeplex.com/releases/view/26308). Once you have downloaded the zip file, extract the sql file.
- Start the SQL Server management studio
- Login and navigate to the table list and navigate for the database where you want store the data
- Right-Click on the database entre and select “New Query”
- In the query window, copy and poste the entire contents of the file “duo.State.Table.sql”
- Hit the execute button. You will see series of messages saying “(1 row(s) affected)”
- Click on the database and you will now see the new “duo.State” table
- Right-click the new “dbo.State” table and select “Select Top 1000 Rows”
- In the Results pane, click the “Spatial Results” tab and you will see the data displayed on a map
We cannot import this data directly into Spotfire 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/wiki/Well-known_binary
For more information on converting from SQL Server to WKB see: https://technet.microsoft.com/en-us/library/bb933881.aspx
- Right-click on the Views folder and select “New View…”
- When the “Add Table” dialog appears, click “Close”.
- Enter the following into the SQL pane:
geom.STAsBinary() AS Geometry
- Save the View as “VW_State_WKB”.
- 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.
Step 2: Create an Information Link
We will use the Information Designer to setup an Information Link pointing to the VW_State_WKB view we just created. To keep things organized we will create a folder to store our Information Link.
- Start Spotfire and open “Library Administration” from the Tools menu.
- Create a new folder called “VW_State_WKB”.
- Close the Library Administration dialog.
- Open the Information Designer from the Tools menu.
- Select the “Data Sources” tab and expand the tree until the you can see the VW_State_KB view.
- Right-click on the entry and select “Create Default Information Model...”
- Select the folder you created in the previous step as the destination folder and click OK. The Information Link will be created.
- Close the Information Designer.
- 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.
- Before we proceed, we will need to tell Spotfire how to interpret the Geometry column correctly. Re-open the Information Designer
- Expand the Elements tree and locate the Geometry element of the VW_State_WKB view.
- Double-click on the Geometry item to open the Column Element dialog and expand the Properties section.
- Click the “Add...” button to add a new property.
- Set the property name to “MapChart.ColumnTypeId” and the value to “Geometry”.
- Click OK to save the new property.
- 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 shapes of the States display in the Table Visualization.
Troubleshooting Note: If the Table Visualization does not render the shapes of the States, 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.
Step 3: Setup a Data-On-Demand query
Now we can use our foundational steps to create a Data-On-Demand example.
- Create a new DXP file and open the VW_State_WKB Information Link.
- Rename the Data Table to be “US States” and set the data to be Embedded.
- Delete the Geometry Column from the data table.
- Add a Table Visualization of the “US States” data. Remove the StateID column. Call the visualization “US States”.
- Save the DXP file as “Spatial Data On Demand”.
- Click on File > Add Data Table. On the dialog click Add > Information Link.
- Select the VW_State_WKB Information Link.
- Check the “Load on demand” option and click the “Settings...” button.
- In the “On-Demand Settings” dialog, click on the “Define Input...” button and select the “StateID” column from the “US States” data table.
- Click OK.
- Repeat the previous steps for the “StateName” field this time selecting “StateName” from the “US States” data table.
- Check the option to “Load automatically” and click OK and then OK again.
- 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.
- Add a Table Visualization below the US States table visualization. Point it to the VW_States_WKB data.
- When you select an entry in the US States table visualization, the corresponding item will show up in the VW_State_WKB table visualization.
- 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.
- In the Map Chart options, remove the “US States” layer and add a Feature Layer using the VW_State_WKB data table.
- In the Appearance tab, check the “Auto-zoom” option.
Now when you select an entry in the US States table visualization, the corresponding item will show up in the VW_State_WKB table visualization and be highlighted on the Map Chart.