The Geometry Data Type and SSRS Floorplan Reports

I was looking for a solution on how to build dynamic floorplans in SSRS, expecting to have to battle Visio to do such a thing. It turned out that a new data type, the Geometry spatial data type was introduced way back in SQL2008. This allows the creation and storage of polygon shapes in SQL Server. So now I have a database based mechanism to store objects on the floorplan, the floor itself and all the objects positions on the floor. Reporting Services can then surface all this using standard reporting capabilities.

How the Geometry Data Type works

The Geometry data type works on a simple x,y coordinate system to describe a polygon (official MSDN docco here). It can get more complicated than that, but this is the basic idea:

SQL Server Polygon Geometry Data Type description
SQL Server Polygon Geometry Data Type description

 

 

 

 

 

 

 

 

 

 

 

 

On a simple zero based set of x,y coordinates, you describe the path you take around the polygon – importantly ending back where you started – using the coordinates of each point of the polygon. In the case above, I’ve used a square, so that needs 5 points:

  1. 1,1 (start)
  2. 3,1
  3. 3,3
  4. 1,3
  5. 1,1 (finish back at start)

We can describe the Polygon construct as follows in T-SQL:

‘POLYGON((1 1, 3 1, 3 3, 1 3, 1 1))’

Creating and loading a simple Geometry table

So, to use this data first we need to construct a table:

CREATE TABLE [dbo].[GeometryTest](
[ID] [int] NOT NULL,
[Shape] [geometry] NOT NULL,
[Name] [nvarchar](50) NOT NULL,
[Value] [int] NULL,
CONSTRAINT [PK_GeometryTest] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

Note there’s no configuration of the Geometry data type, or anything that needs to be enabled on the server to support this. I’ve included a Value column for the report, and an ID & Name column for metadata purposes.

Next I’ll load the table with a floorspace, and a few shapes – a couple of triangles and squares:

INSERT INTO [GeometryTest]
VALUES (1,’POLYGON((0 0, 7 0, 7 7, 0 7, 0 0))’,’Container’, NULL);

INSERT INTO [GeometryTest]
VALUES (2,’POLYGON((1 1, 3 1, 3 3, 1 3, 1 1))’,’Box One’,1);

INSERT INTO [GeometryTest]
VALUES (3,’POLYGON((4 1, 6 1, 6 3, 4 3, 4 1))’,’Box Two’,5);

INSERT INTO [GeometryTest]
VALUES (4,’POLYGON((1 4, 3 4, 3 6, 1 4))’,’Triangle One’,2);

INSERT INTO [GeometryTest]
VALUES (5,’POLYGON((4 4, 6 4, 4 6, 4 4))’,’Triangle Two’,3);

We can check this works with the handy spatial previewer in SSMS, running a select all against the table:

SSMS Spatial Previewer
SSMS Spatial Previewer

 

 

 

 

 

 

 

 

 

 

 

Using SSRS to generate a Spatial report

Next, in SSDT (or BIDS, if not running SQL2012), create a new report with this table we just created and loaded as a data source. Drag a Map report item from the toolbox onto the report canvas, and choose to use a SQL Server Spatial Query as your data source. You should have a data set available – if not, just create one as a select * from the our test table. Click next to get to the previewer – it will autodetect your geometry column, so just click next again.

When choosing a report type, I’ve opted to take a Color Analytical Map, then clicked next. For the Analytical dataset, I’m reusing the same table as I stored the values with the shapes. At the Data Visualisation screen, we need to change the “Field to Visualise” to the [Sum(Value)] option as there’s no autodetect here. Then click finish.

To make it display in pretty colours, select the MapPolygonLayer object, and change the Polygon Rules > Colour Rules > Distribution Type property to “Equal Distribution”. Then run the report:

SSRS Spatial Report
SSRS Spatial Report

 

 

 

 

 

 

 

 

 

Simple, but proves a point and shows how you can create floorplan style reports from a dynamic data source.

 

 

 

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>