Skip to main content

Why does the map chart only load in part of the SQL Server spatial data?

Rich Talbot
  • Created
Date Posted:
Product: TIBCO Spotfire®

Problem:

Why does the map chart only load in part of the SQL Server spatial data?


Solution:

Follow the instructions in the Wiki article to create a view for the sample data set in the database, and create a default information model for it in Information Designer. Then Open data from the information link. The issue is that only some of the geometry data (shapes) are loaded in the map. See the following screenshot.
User-added image
The geometry data is rendered with different WKB types in Spotfire. Currently, Spotfire only supports 6 WKB types:

  • Point
  • LineString, Polygon
  • MultiPoint
  • MultiLineString
  • MultiPolygon
Spotfire does not render the rest of the types:
  • GeometryCollection
  • CircularString
  • CompoundCurve
  • CurvePolygon
  • MultiCurve
  • MultiSurface
  • Curve
  • Surface
  • PolyhedralSurface
  • Tin
  • Triangle
For example, the WKB type of Alabama, one of the not rendered states, is GeometryCollection. Additionally, if you follow the instructions in the Troubleshooting Note to change the "Geometry" Column Element -->Expression: %1.STAsBinary() [default is %1], you may get the following error: 
Error message: Unable to import data. See the details for more information.
 
ImportException at Spotfire.Dxp.Data:
Failed to execute data source query for data source "VW_State_WKB". (HRESULT: 80131500)
 
Stack Trace:
   at Spotfire.Dxp.Data.DataSourceConnection.ExecuteQuery2()
   at Spotfire.Dxp.Data.DataFlow.Execute()
   at Spotfire.Dxp.Data.DataFlow.DataFlowConnection.ExecuteQueryCore2()
   at Spotfire.Dxp.Data.DataSourceConnection.ExecuteQuery2()
   at Spotfire.Dxp.Data.Producers.SourceColumnProducer.<>c__DisplayClass27.<CreateView>b__24()
   at Spotfire.Dxp.Framework.ApplicationModel.Progress.ExecuteSubtask(String title, ProgressOperation operation)
   at Spotfire.Dxp.Data.Producers.SourceColumnProducer.CreateView(CxxSession session, DataPropertyRegistry propertyRegistry, GlobalMethodRegistry globalMethodRegistry, DataSourceConnection connection, PartialDataLoadReport& partialLoadReport)
   at Spotfire.Dxp.Data.Producers.SourceColumnProducer.GetColumnsAndProperties(DataSourceConnection connection)
   at Spotfire.Dxp.Data.Persistence.DataItem.PerformUpdate(SourceColumnProducer producer, DataSourceConnection connection)
   at Spotfire.Dxp.Data.Persistence.DataItem.Update(SourceColumnProducer producer, DataSourceConnection connection)
   at Spotfire.Dxp.Data.Persistence.DataPool.<LoadData>d__2.MoveNext()
   at Spotfire.Dxp.Data.Producers.SourceColumnProducer.OnConfigure()
   at Spotfire.Dxp.Framework.DocumentModel.Node.ConfigureSubTree()
   at Spotfire.Dxp.Framework.DocumentModel.Node.<ConfigureSubTree>b__13(Node node)
   at Spotfire.Dxp.Framework.DocumentModel.UndoableListAvlLeaf`1.ForEachChild(Action`1 action, Boolean includeFrozen)
   at Spotfire.Dxp.Framework.DocumentModel.Node.ConfigureSubTree()
   at Spotfire.Dxp.Framework.DocumentModel.Node.<ConfigureSubTree>b__13(Node node)
   at Spotfire.Dxp.Framework.DocumentModel.UndoableList`1.ForEachChild(Action`1 action, Boolean includeFrozen)
   at Spotfire.Dxp.Framework.DocumentModel.Node.ConfigureSubTree()
   at Spotfire.Dxp.Framework.DocumentModel.Node.<ConfigureSubTree>b__13(Node node)
   at Spotfire.Dxp.Framework.DocumentModel.State.NodeState.<>c__DisplayClass9.<ForEachManagedChild>b__8(IDocumentNodeChild documentNodeChild)
   at Spotfire.Dxp.Framework.DocumentModel.State.NodeState.ForEachChild(IDocumentNodeChild[] children, Action`1 action)
   at Spotfire.Dxp.Framework.DocumentModel.Node.ConfigureSubTree()
   at Spotfire.Dxp.Framework.ApplicationModel.Progress.ExecuteSubtask(String title, IndeterminateProgressFormatter progressFormatter, ProgressOperation operation)
   at Spotfire.Dxp.Framework.DocumentModel.DocumentNode.ConfigureAndAttachFromNew()
   at Spotfire.Dxp.Framework.DocumentModel.DocumentNode.AttachSubTreeWhileExecuting(UndoableNodeBase newOwner)
   at Spotfire.Dxp.Framework.DocumentModel.UndoableNode.Spotfire.Dxp.Framework.DocumentModel.IUndoableNode.AttachItemToUndoableNode(Object item)
   at Spotfire.Dxp.Framework.DocumentModel.UndoableKeyedCollection`2.<>c__DisplayClass6.<Insert>b__5()
   at Spotfire.Dxp.Framework.DocumentModel.Node.InternalTransaction(Executor executor, Boolean rollbackNestedInternalTransactionAtException)
   at Spotfire.Dxp.Framework.DocumentModel.UndoableKeyedCollection`2.Insert(Int32 index, TNode item)
   at Spotfire.Dxp.Data.DataTableCollection.<>c__DisplayClass37.<Add>b__36()
   at Spotfire.Dxp.Framework.DocumentModel.Node.InternalTransaction(Executor executor, Boolean rollbackNestedInternalTransactionAtException)
   at Spotfire.Dxp.Data.DataTableCollection.Add(DataTable dataTable)
   at Spotfire.Dxp.Data.DataTableCollection.Add(String dataTableName, DataSource dataSource, Boolean autoCreateFilters, Boolean includeInAxisControls)
   at Spotfire.Dxp.Forms.Data.Import.TableContext.DataFlowBuilderContext.<>c__DisplayClass3.<AddDataTables>b__0()
   at Spotfire.Dxp.Framework.ApplicationModel.Progress.ExecuteSubtask(String title, ProgressOperation operation)
   at Spotfire.Dxp.Forms.Data.Import.TableContext.DataFlowBuilderContext.AddDataTables(DataManager dataManager, Form baseForm, List`1& addedTables, String& errorMessage)
 
InformationModelException at Spotfire.Dxp.Data:
Failed to execute query: Cannot call methods on varbinary(max). (HRESULT: 80131500)
 
Stack Trace:
   at Spotfire.Dxp.Data.InformationModel.InternalInformationModelManager.DataStream.GetNextBlock()
   at Spotfire.Dxp.Data.InformationModel.InternalInformationModelManager.DataStream.Read(Byte[] buffer, Int32 offset, Int32 count)
   at Spotfire.Dxp.Internal.Utilities.SeekableStream.Read(Byte[] buffer, Int32 offset, Int32 count)
   at System.IO.BinaryReader.FillBuffer(Int32 numBytes)
   at System.IO.BinaryReader.ReadUInt16()
   at Spotfire.Dxp.Data.Formats.Sbdf.SbdfSectionHeader.ReadMagicNumber(BinaryReader reader)
   at Spotfire.Dxp.Data.Formats.Sbdf.SbdfSectionHeader.ReadSectionType(BinaryReader reader, SbdfSectionTypeId expectedType)
   at Spotfire.Dxp.Data.Import.SbdfDataRowReader.DelayedInit()
   at Spotfire.Dxp.Data.Import.InformationLinkDataSourceConnection.ExecuteQueryCore2()
   at Spotfire.Dxp.Data.DataSourceConnection.ExecuteQuery2()
 
InformationModelServiceException at Spotfire.Dxp.Services:
Failed to execute query: Cannot call methods on varbinary(max). (HRESULT: 80131509)
 
Stack Trace:
   at Spotfire.Dxp.Services.WebServiceBase`1.InvokeService[T](ServiceMethod`1 serviceMethod, ExceptionFactoryMethod exceptionFactoryMethod)
   at Spotfire.Dxp.Services.Data.InformationModel.QueryManagerService.GetDataBlock(String jobId, Int32 timeout)
   at Spotfire.Dxp.Data.InformationModel.InternalInformationModelManager.DataStream.GetNextBlock()
 The reason you get the error "Failed to execute query: Cannot call methods on varbinary(max). (HRESULT: 80131500)" is you are trying to use STAsBinary() on a column where you already have executed the same function on earlier. So it is no longer a Geometry column and therefore does not have that function available to it. Work with your DBA/GIS team to assure that your data uses one of the supported WKBType geometry types (Point, LineString, Polygon, MultiPoint, MultiLineString and MultiPolygon). Wiki: Using SQL Server spatial data with Spotfire

Comments

0 comments

Article is closed for comments.