Can SSRS XML data sources contain spatial data

spatialssrsxml

I've been given the task of producing a report which will extract data from a web service.

The web service provides data from a list of sites including aggregates. Lets say total sales by month.

Id like to plot these values on a map and locate them using geometry points. The developer is investigating how he can include the spatial reference in the xml reply.

But before i get him change the Web service is there any limitation or restriction in ssrs that would stop this working?

If so, what are my alternatives? If i can retrieve the easting and northing in the xml can i convert to a spatial datatype with an expression on the fly?

Best Answer

As far as i have been able to read/research it seems that the answer is no. However i have found that I can merge 2 datasets to achieve what I wanted to do.

Related Solutions

Sql-server – How to tell which data sources are being used in SSRS

Okay, I got it. Found this link which helped: http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/581c7068-0abe-49f9-a1a5-93e94f472641/

I distilled that information to this query:

select
  Catalog.name,
  cat1.Name datasource
from
  Catalog
  join DataSource
    on Catalog.ItemID = DataSource.ItemID
  join Catalog cat1
    on DataSource.Link = cat1.ItemID
where
  Catalog.Type = 2

Hope this helps someone else in the future!

Sql-server – SQL Server – Using spatial data

Yes you should use spatial indexes, but you should also consider there are limitations that simple sargability concerns can resolve.

A spatial index is good for finding locations in a particular area. It's not good for working out which distance is shortest.

So... when trying to find the closest item, start with a small distance and find items in that area. Something like:

select id
from dbo.locations
where location.STIntersects(@point.STBuffer(@somedistance)) = 1;

...and slowly increase the value of @somedistance until you find what you're looking for. This will allow Seeks to be done, quickly eliminating locations that are not of interest.

Best Answer

Related Solutions

Sql-server – How to tell which data sources are being used in SSRS

Sql-server – SQL Server – Using spatial data

Related Question