SQL SSRS Unable to determine the spatial data type in the specified dataset field: GEO

2
votes

I need some help, I have a stored procedure(SP) that contains NO parameters in SSMS. The SP generates spatial data by joining 2 tables one table containing two fields needed for labelling purposes and the other from a table that containsthe GEO field.

This SP populates spatial data in SSMS which I can view and interact with. All seems well so far...

I then use SSRS to create a data source and a dataset of the said stored procedure. Following this I create a new .rdl and insert a map choosing SQL Server spatial query as the source then selecting the stored procedure dataset. Following this SSRS attempts to join the data and I then receive the error from the Microsoft SQL Server Report Designer:

Unable to determine the spatial data type in the specified dataset field: GEO

I have searched the the internet for answers already and none suit predominately because they all refer to parameters causing the issue however my query does not contain nor pass any. I have tried the geo field as both a geography and geometry datatype no avail.

Any help or advice would be great.

1
sql-servertsqlstored-proceduresreporting-services
Is it possible that the data in the GEO field isn't in the exact format that SSRS expects? – Tab Alleman
Hi, the format is as follows. In SSMS GEO field it is 0xE610000001046B0000005C6480CEBDEE.....etc and if ran in the query designer of SSRS its: POLYGON ((-7.4614609548374524 53.86516743916647, -7.4649651224764479 53.863865004822252, -7.4669858550019663 53.864005868056061, -7.4614609548374524 53.86516743916647)) I have removed most of the lat and lngs so comment limit isn't hit. – user3459884

1 Answers

0
votes

I have just been playing with a Heat Map in SSRS using SQL Jason's example (http://www.sqljason.com/2012/03/heat-maps-for-ssrs-using-map-control.html).

It looks like your data doesn't have enough points. According to Teo Lachev (http://prologika.com/CS/blogs/blog/archive/2009/08/30/heat-maps-as-reports.aspx):

SQL Server describes polygons in Well-Known Text (WKT) standard sponsored by Open Geospatial Consortium. For instance, a polygon is described as five (surprise) points on the coordinate system, as shown below. The fifth point is the same as the first point.

The example format you gave only had four points. The first and last are the same so there seems to be a missing point or you are not graphing rectangles. It doesn't look like the Spatial Data supports any other shapes. The Map wizard only shows rectangles in the Wizard when creating the map.