Although I don't find the spatial maps in SSRS all that useful (I'll post another article in the future on generating kml files for Google Earth); one thing that some folks have found tedious is establishing the coordinates once the latitude and longitude have been updated in a table.
This can be easily done with a while loop, as demonstrated below. Suppose you want to create a bubble map of sales data.
First you need some invoices. To be pragmatic here, I'll assume you are familiar with your own invoice tables, so instead of creating them here, we'll just show the code I used to extract invoice information from my employer's system:
use DBA
declare @divcode nvarchar(10) -- divisional code
declare @yr int -- year to extract
set @divcode = '00'
set @yr=2012
if exists(select * from sysobjects where type = 'u' and [name]='tbl_mapdinvoices')
drop table tbl_mapdinvoices
;
select IDENTITY(int, 1,1) rowid,
cb.[div-code], cb.[Cust-no], c.[address],c.[city],c.[st],c.[zip-code],c.[map-grid],c.[num-lat],c.[num-lng], cb.[Total]
into tbl_mapdinvoices
from
(
SELECT
case when GROUPING([div-code])=1 then 'All' else [div-code] end [div-code],
case when grouping([Charge-cust])=1 then 'All' else [Charge-cust] end [Charge-cust],
case when grouping([Cust-no])=1 then 'All' else [Cust-no] end [Cust-no],
sum([Gross-amt])+sum([Tax-amt__1])+sum([Tax-amt__2])+sum([Tax-amt__3])+sum([Extra-Charge__1])+sum([Extra-Charge__2])+sum([Extra-Charge__3])
+sum([Extra-Charge__4])+sum([Extra-Charge__5])+sum([Extra-Charge__6])+sum([Extra-Charge__7])+sum([Extra-Charge__8])+sum([Extra-Charge__9])
+sum([Extra-Charge__10])[Total]
FROM [dbo].[invoice]
where DATEPART(yyyy,[invoice-date]) IN (@yr)
group by [div-code], [Charge-cust], [Cust-no]
with CUBE
having [div-code] IN ( @divcode )
and [Charge-cust] <> 'All'
and [Cust-no] <> 'All'
) cb
inner join customer c on cb.[Cust-no]=c.[Cust-no]
and c.[num-lat]+c.[num-lng]<>0
;
Notice the IDENTITY column above? The column rowid will serve as a means to 'loop through' the entire table. If one has say, 10 or 20 invoices in the table, manually updating the table to get coordinates isn't a big deal. But if you have a table of 1,000 or 10,000 or 100,000 invoices to map, well, you need a better way.
Here's a redacted listing of the first two rows of my invoice table to give you an idea of what we have so far:
rowid div-code city st num-lat num-lng total
1 00 MARYVILLE TN 35.754724 -83.971210 524.10
2 00 IRVING TX 32.917917 -97.002038 511.31
Now we're going to add a column to our table for coordinates, we will call it 'spatialdata'
alter table tbl_mapdinvoices add spatialdata geography;
Next, we set up the WHILE LOOP to create the coordinates for us. We're going to do a number of things here, so let's look at the code:
declare @counter int
set @counter = 1
declare @sql nvarchar(max)
declare @lat decimal(10,6)
declare @lng decimal(10,6)
while @counter < (select MAX(rowid)+1 from tbl_mapdinvoices)
begin
set @lat = (select ([num-lat]) from tbl_mapdinvoices where rowid = @counter)
set @lng = (select ([num-lng]) from tbl_mapdinvoices where rowid = @counter)
set @sql = 'update tbl_mapdinvoices set spatialdata ='+''''+'POINT('+(select cast(@lng as varchar(16)) )+' '+(select cast(@lat as varchar(16)) )+')'+''''
set @sql = @sql+' where rowid ='+(select cast(@counter as nvarchar(10)) )+';'
--print @sql
exec sp_executesql @sql
set @counter = @counter+1
end
First we declare our counter as an integer and set it's value to '1'.
While declaring "stuff", we're going to create a string for our UPDATE sql, as well as declaring our latitude and longitude variables.
We initiate our loop with a WHILE statement that essentially says, while the counter variable is less than the value of the maximum row id plus 1 from the table of invoices continue looping.
(You may be asking 'Why not just use a cursor'? The answer is that there are multiple posts and myriad books and other analyses that demonstrate cursors are rarely more efficient than using other T-SQL tactics. I have therefore abandoned using cursors completely as I have never found a need for them.)
Next we set our latitude variable to the value of the num-lat column in our invoice table where the rowid in the table is equal to the counter. Likewise we set the value of longitude variable to our num-lng column value.
Now we employ the DYNAMIC SQL technique to build our UPDATE string.
If you are not familiar with DYNAMIC SQL, once you have an invoice table built with a rowid as described in the code, comment out the exec sp_executesql @sql statement and uncomment out the print @sql statement and run the WHILE LOOP. You should see a series of statements in your answer set that look something like this:
update tbl_mapdinvoices set spatialdata ='POINT(-83.971210 35.754724)' where rowid =1;
update tbl_mapdinvoices set spatialdata ='POINT(-97.002038 32.917917)' where rowid =2;
update tbl_mapdinvoices set spatialdata ='POINT(-80.949954 34.071876)' where rowid =3;
update tbl_mapdinvoices set spatialdata ='POINT(-79.221837 35.431453)' where rowid =4;
(DYNAMIC SQL is a good way to programmatically build statements for subsequent execution.)
Don't forget to uncomment the exec sp_executesql @sql statement and comment out the print @sql statement before you run the code though.
The exec sp_executesql @sql statement is the last thing in the WHILE LOOP before incrementing the counter and returning to the top of the loop.
Once this WHILE loop is finished, you will have populated the spatialdata column of your table.
Below is a redacted listing of the first two rows of my invoice table:
rowid city st num-lat num-lng spatialdata
1 MARYVILLE TN 35.754724 -83.971210 0xE6100000010CCFC0C8CB9AE0414015E3FC4D28FE54C0
2 IRVING TX 32.917917 -97.002038 0xE6100000010CA2B8E34D7E7540405AD6FD63214058C0
Now we want to convert the spatialdata into coordinates.
alter table tbl_mapdinvoices add coordinates nvarchar(255);
update tbl_mapdinvoices
set coordinates = convert(nvarchar(255),[spatialdata])
;
Below is a redacted listing of the first two rows of my invoice table with coordinates.
rowid city st num-lat num-lng coordinates
1 MARYVILLE TN 35.754724 -83.971210 POINT (-83.97121 35.754724)
2 IRVING TX 32.917917 -97.002038 POINT (-97.002038 32.917917)
Now I have the table ready to map the data.
To see an excellent example of how to spatially map data in SSRS, see the posting at this link:
http://www.mssqltips.com/sqlservertip/2174/maps-in-sql-server-2008-r2-reporting-services/
No comments:
Post a Comment