3

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.

  • Have you found a suitable solution yet? If you're happy with either of the answers below can you please mark the question as answered. – Sir Swears-a-lot Jul 9 '15 at 8:48
  • Sorry i'm new to this, i'll do that now thank again guys! – Olivia Jul 17 '15 at 7:36
1

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

| improve this answer | |
1

Short version is yes its possible to do what you are describing.

Your report will have a spatial query to display the shapes you want. Whether that's a map or a floor plan.

You can then add analytical data in a second query to colour the map. The two queries needs to be joined by a key. Im not familiar with qgis but ideally you'd want both the spatial data and analytics in sql server.

The other option is to have 2 layers. The first layer gives the 'map' shape and the 2nd adds points on top.

If you search Google images for "SSRS map" you'll see lots of examples.

| improve this answer | |
  • Thanks Peter for the reply, my database doesn't have co-ordinates and i was hoping i could 'assign' the points rather than write a spatial query. All (I only have the names and other info for the floor plan as well as an image of the floor plan itself ) But if that's what i have to do then i'll give it a go. Olivia – Olivia Jul 6 '15 at 9:47
  • Hi. If you think of it like a normal sql query you need to join the 2 datasets. I guess the other option would be to give the data xy points and plot them on top of your polygons. Like pins on a map. – Sir Swears-a-lot Jul 6 '15 at 10:22
  • What coordinate system are you using? – Sir Swears-a-lot Jul 6 '15 at 10:34
  • that's the problem i don't have one i need to assign coordinates to my data points – Olivia Jul 6 '15 at 10:36
  • the pins on the map could work the plan will be like a grid as far as i know – Olivia Jul 6 '15 at 10:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.