SQL Server SSRS – How to Create a Custom Map Report

spatialsql serverssrs

not sure if this is the right forum. But just wondering is there a way one can make a map report out of an image or a map made on excel. I have the data and I've looked around and found that its possible to edit world maps, etc, to show information. Is it possible to make a simple map from scratch, to show something like a floor plan, something that isn't available on google maps.
Thanks guys in advance sorry if I'm not being clear on what I'm asking. If i can be clearer please ask.

Note: I'm currently using SSRS and QGIS to try do this. I have the data i need to show but don't have the co-ordinates for the thing i want to show. I thought that could be something i could do when I'm making the map. Only a small map to show one piece of information about 200 distinct data points.

Best Answer

This is not a full answer as I haven't ever used SRSS for mapping, however reading the comments on Peter's answer it would appear that you need to create some spatial data to work with.

Here's a simple example of how you could put build spatial data for a seating plan without having to use GIS software. Once that is done it should just be a matter of joining to the HallSeating table with your exam/student data.

-- Create the hall
CREATE TABLE HallOutline (
    MyID INT IDENTITY(1,1)  NOT NULL PRIMARY KEY,
    SHAPE Geometry
);

INSERT INTO HallOutLine (SHAPE)
VALUES (
    Geometry::STGeomFromText(
        'LINESTRING (0 0, 20 0, 20 50, 0 50, 0 0)' -- Simple 20 x 50 box
        ,0 -- SRID is 0 for all the data
    )
);

-- Create seating plan
CREATE TABLE HallSeating (
    MyID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    Row INT NOT NULL, -- I'm using row and column as a key to the
    Col INT NOT NULL, -- exam data, but you could use anything.
    SHAPE Geometry
);

INSERT INTO HallSeating(Row, Col, Shape)
SELECT Row, 
       Col, 
       Geometry::STGeomFromText(
         Concat('Point (',XLocation,' ',YLocation,')') -- Point location of the seat
         ,0 -- 0 SRID again
       ).STBuffer(.5) -- Create circle diameter 1 at seat location
FROM (VALUES(1,40), (2,37), (3,34), (4,31), (5,28), (6,25), (7,22), (8,19), (9,16), (10,13), (11,10), (12,7))  AS R(Row,YLocation), -- Rows
    (VALUES(1,2), (2,3), (3,4), (4,5), (5,6), (6,7), (7,8), (8,11), (9,12), (10,13), (11,14), (12,15), (13,16), (14,17), (15,18))  AS C(Col,XLocation) -- Columns

This creates a plan that looks like this

enter image description here

Related Question