- Do you have WKB (well-known binary) data that you want to bring into Spotfire?
- Are you struggling with SQL geometry columns in Spotfire?
- Do you want to understand more about how Spotfire processes or handles spatial data?
- Do you want to import spatial data into Spotfire but don’t know how to configure the information link?
In the oil and gas industry, we love maps! We like to put wells on maps and then view different features on maps relative to the wells like lease shapes, pad shapes, wellbore paths, and other objects in their “natural” shape. While there are many great geo and mapping software packages out there, like ArcGIS, Spotfire is superior at mashing up spatial data with production, completions, and other data sets. Now, getting spatial data into Spotfire can be a challenge simply because the folks who understand the geo software don’t understand Spotfire and vice versa. It is actually pretty easy to get this data into Spotfire. What’s missing is the translator between these two worlds. That’s where I come in.
In this article, I’ll explain how to create an information link connecting to a SQL database like ArcGIS SDE and then render that data in a Spotfire table and on a map. I’ll also say that there is a lot of good TIBCO documentation on this subject, but…
- If you don’t know the right terms, it can be hard to find.
- It’s sometimes too technical to understand without more background info or broader context.
- It’s spread out in multiple articles/posts.
As a result, I view my role as that of the information aggregator/translator since I dance around in a lot of different technical spaces.
Technical Background for Spatial Data
Relational databases, of which SQL Server is one, allow you to store and query geometric and geographic data. Spotfire users want to see this data as interactive shapes, lines, and points on a map visualization. Now, SQL Server uses internal representation to store and query this type of data. Don’t worry about what internal representation means. Just know that it means the data can’t be imported into Spotfire with converting the data. In SQL, spatial data is stored in a geometry column. Geometry is the data/column type, just like datetime or nvarchar are data/column types. They need to be converted from geometry to a form Spotfire understands and can process. That form is WKB (Well-Known Binary).
Since I mentioned ArcGIS, I also want to mention ArcSDE. I said I would be the translator, so here is a part that might be missing. ArcSDE is a server-software sub-system produced and marketed by Esri that aims to enable the usage of Relational Database Management Systems (RDBMS) Systems for spatial data. The spatial data may then be used as part of a geodatabase. SDE stands for Spatial Database Engine. ArcSDE is sometimes described as middleware, a layer of software that sits between Esri’s ArcGIS products and RDBMS software and manages data exchanges between them. It makes it possible for GIS data users to utilize their data without the need for special knowledge of the underlying RDBMS.
Alright, let’s bushwack our way out of the weeds. After converting the data into a form Spotfire can read and import, we must make further modifications to render the shapes and put labels on a map. Surprisingly, getting labels on a feature layer from spatial data is a bit tricky.
The Moving Parts
Now, let’s get into more detail. Getting everything to work involves three configuration steps.
- Convert the SQL geometry column to WKB. There are two ways to do this….
- Create a view that will expose the data as WKB and connect an information link to the view.
- Rather than creating a view with specific SQL code, modify the information link SQL in the information designer (using the same SQL code as if you created a view).
- Modify the properties of the geometry column element.
- Add calculated columns to the DXP.
Now that you have an idea of where you are going in Spotfire, here are the nitty-gritty details. As mentioned, I am not listing all steps for creating an information link, only what requires modification for this purpose.
Additionally, these instructions follow the path of modifying the information link SQL in the information designer (second method from list above). You may prefer this method if IT policy within your organization involves a length change process for creating or modifying views. In my opinion and experience, the second method is faster and easier. If you would like to create a view in SQL (first method), look at this TIBCO documentation.
- After adding the column elements to the information link in the Information Designer, click on the SQL button.
- Modify the SQL statement for the geometry column (named SHAPE in screenshot). I have also copied and pasted the line of code below the screenshot in case the punctuation in the screenshot is hard to read. C1.”SHAPE”.STAsBinary() AS “SHAPE”,
- Select the geometry column in the information link, and click the Edit button.
- Scroll down to the Properties section and add the two properties shown.
- Open the information link.
- Add 6 calculated columns to the DXP using the spatial functions XCenter, XMax, XMin, YCenter, YMax and YMin. The expressions will look like this — XCenter([SHAPE]). These calculations are explained in this blog post. I first stumbled upon this when I couldn’t add labels to a feature layer in a map chart.
Before I wrap up this post, I want to be very clear about which step achieved which result. This is the type of context I often find missing in other places.
- Modifying the SQL in the information designer — This converts the geometry column into WKB, which Spotfire can read. The information link won’t open with this. You’ll get an error.
- Adding the mapchart.columntypeid property — This makes the geometry visible to the map. If this property is missing, upon opening the information link, you won’t be able to add a feature layer with the shapes.
- Adding the ContentType property — Without this property, the Shape column in the data table visualization would not show the geometry. The screenshots below illustrate with and without the ContentType property.
- Adding the calculated columns — Labels on the feature layer would not be possible without the calculated columns.
And now, you know what I know about spatial data in Spotfire. If you would like more information converting from SQL Server to WKB see: https://technet.microsoft.com/en-us/library/bb933881.aspx. I also recommend this TIBCO Community article.