As I indicated in my previous post, I'm not enamored of the geospatial mapping in SQL Server reports. But what if you don't have a brilliant person who can write code to interface to Google maps or Bing? What if your executives would rather use Google Earth?  What can we do with geospatial data under those circumstances?

Well this is what we can do. We can create a script that will write our kml file data file for use in the Google Earth application.

Here is the exercise. Suppose the President of your company comes to you and asks if it is possible to show the total sales a customer did with your company for the year, and then to plot that data in Google Earth.

Suppose further that the request is that when you hover over the marker, particular data is displayed such as the total number of orders, the type of order, the sale amount and some address information identifying the customer (number, name, city, etc.).

Of course this exercise pre-supposes that you: a) have a version of SQL Server that supports geospatial data, and b) that you have coordinates for the customer so that their location can be plotted in Google Earth.

From that point forward it is merely a straightforward matter of:

a) understanding Google Earth's kml file structure

b) a bit about manipulating markers

c) stringing kml syntax and customer data together into strings that can be inserted between the 'header' and 'footer' sections of your kml file.

So here is the first part of the script:


USE [<database>]  -- set the location to the database holding your customer info.

/*  -- here is a google red dot marker URL
GOOGLE MARKERS
http://maps.google.com/mapfiles/ms/icons/red-dot.png
*/

DECLARE @kmlhdr  NVARCHAR(MAX) -- your kml header text
DECLARE @kmlftr  NVARCHAR(MAX) -- your kml footer text
DECLARE @red  NVARCHAR(255) -- your marker text
DECLARE @divcode NVARCHAR(4)   -- an operating division to report sales on
DECLARE @yr  INT           -- the year to report on

SET @divcode =   '03'
SET @yr =        2012
SET @red =  'http://maps.google.com/mapfiles/ms/icons/red-dot.png'

-- first the easy part; set the footer
SET @kmlftr = '</Document></kml>'

-- more involved, but once you have it down, it is pretty simply and static
SET @kmlhdr = 
(
-- use a select statement to string kml 'snippets' together with descriptive info
SELECT 
'<?xml version="1.0"?><kml xmlns="http://earth.google.com/kml/2.0">
<Document>

-- below is description data that shows up in the Places section of Google Earth.
-- careful consideration of how you want to group and report sales can pay off
-- by using the tags below against data that is grouped by the tags.
-- then when multiple kml files are loaded for different sale types for 
-- different divisions, Places can be used to toggle each kml file on/off to 
-- view sales concentrations.

<name>DISTRIBUTION Sales SC Division</name>      
<description>Division Sales | My Company, Inc.</description>   

-- next comes 3 style sections; 
-- A1 is the normal size of the marker

<Style id="A1"><IconStyle><scale>1</scale><Icon><href>'+@red+'</href></Icon><hotSpot x=".3" y=".8" xunits="fraction" yunits="fraction" /></IconStyle><LabelStyle><scale>0</scale></LabelStyle><BalloonStyle><text><![CDATA[ <b>$[name]</b><br />$[address]<br />$[description]<br/><br/>$[geDirections] ]]></text></BalloonStyle></Style>\n

-- ok a bit about the above kml code; CDATA is going to format some info in 

-- each <Placemark> row we create in the next T-SQL script section.

-- A2 is going to show the marker 'inflated' when the President mouses over it
<Style id="A2"><IconStyle><scale>1.5</scale><Icon><href>'+@red+'</href></Icon><hotSpot x=".3" y=".8" xunits="fraction" yunits="fraction" /></IconStyle><LabelStyle><scale>1.0</scale></LabelStyle><BalloonStyle><text><![CDATA[ <b>$[name]</b><br />$[address]<br />$[description]<br/><br/>$[geDirections] ]]></text></BalloonStyle></Style>\n

-- A0 is the style map
<StyleMap id="A0"><Pair><key>normal</key><styleUrl>#A1</styleUrl></Pair><Pair><key>highlight</key><styleUrl>#A2</styleUrl></Pair></StyleMap>'

)

-- I won't go into the details of the above style sections, you can read up on 

-- them with the Google Earth documents.  Just trust me, a lot of time when into

-- figuring it out, and it works!!

-- Again, the point of this exercise is to show how data can be pulled from 

-- invoicing information and customer information, and mapped in Google Earth.

-- so this part of the code is simple mechanics to create some sales data... 

-- I'll assume you, the reader can pull your own data.

-- Gets basic sales information...

SELECT ISNULL(wo.[serv-code],'')[serv-code],
i.[disp-no], i.[order-no],
CASE WHEN i.[disp-no]<> '' THEN 'service' ELSE 'distribution' END [ordtype], 
i.[Document], i.[Invoice-date], 
i.[Charge-cust], i.[Cust-no], i.[Ship-name], i.[Ship-address__1], i.[Ship-city], i.[Ship-st], i.[Ship-zip],
i.[Gross-amt]+i.[Extra-Charge__9]+i.[Extra-Charge__10][Gross-amt] 
INTO #stg_invoicedata
FROM [dbo].[invoice] AS i
LEFT OUTER JOIN [dbo].[wo] AS wo ON i.[disp-no]=wo.[wo-no]
WHERE DATEPART(yyyy,i.[Invoice-date]) = @yr
AND i.[div-code]=@divcode
;

-- cubes the sales data to get a sales total ytd for each customer
SELECT
CASE WHEN GROUPING([serv-code])= 1 THEN 'All' ELSE [serv-code] END [serv-code],
CASE WHEN GROUPING([ordtype])= 1 THEN 'All' ELSE [ordtype] END [ordtype],
-- CASE WHEN GROUPING([Document])= 1 THEN 'All' ELSE [Document] END [Document], 
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], 
CASE WHEN GROUPING([Ship-name])= 1 THEN 'All' ELSE [Ship-name] END [Ship-name], 
CASE WHEN GROUPING([Ship-address__1])= 1 THEN 'All' ELSE [Ship-address__1] END [Ship-address__1], 
CASE WHEN GROUPING([Ship-city])= 1 THEN 'All' ELSE [Ship-city] END [Ship-city], 
CASE WHEN GROUPING([Ship-st])= 1 THEN 'All' ELSE [Ship-st] END [Ship-st], 
SUM([Document])[Orders],
SUM([Gross-amt])[Dollars]
INTO #stg_invoicecube2
FROM #stg_invoicecube
GROUP BY [serv-code],[ordtype],[Document],[Charge-cust],[Cust-no],[Ship-name],[Ship-address__1],[Ship-city],[Ship-st]
WITH cube
HAVING  [serv-code]<> 'All'
AND [ordtype]<> 'All'
AND [Charge-cust]<> 'All'
AND [Cust-no]<> 'All'
AND [Ship-name]<> 'All'
AND [Ship-address__1]<> 'All'
AND [Ship-city]<> 'All'
AND [Ship-st]<> 'All'
;

-- joins the sales data with the customer data to get the coordinates
SELECT c.*, c2.[Zip-code], c2.[num-lat],  c2.[num-lng], 
CASE WHEN c2.[num-lat]='0' THEN 'geocode'  END [num-latflag], 
CASE WHEN c2.[num-lng]='0' THEN 'geocode'  END [num-lngflag] 
INTO #tmp_invoicecube 
FROM #stg_invoicecube2 c
INNER JOIN [dbo].[customer] AS c2 ON c.[Cust-no]=[c2].[Cust-no]
;

So below is a row from the table just to make clear what we want...

Cust-no Ship-name   Ship-address__1     Ship-city Ship-st

10170 MY SAMPLE CUSTOMER 123 Anywhere Street Anytown SC

Orders Dollars Zip-code num-lat  num-lng

1 3066.58 29071 35.43802 -80.522617

-- Now we want to create <Placemark> rows for each row in the table.  

-- In the example above I plot this row from this data.

-- Here is the script to do that...

SELECT '<Placemark><name>ORDERS: '+cast([orders] as nchar(1))+' DOLLARS: '+cast(cast([dollars] as decimal(10,2))as nvarchar(255))+' TYPE: '+Upper([ordtype])+' - BILL TO: '+[cust-no]+' '+[ship-name]+' - SHIP TO: '+
[cust-no]+' '+[ship-name]+' '+[ship-address__1]+'</name><description>'+
[serv-code]+'</description><Point><coordinates>'+cast([num-lng]as nvarchar(255))+','+cast([num-lat] as nvarchar(255))+',0</coordinates></Point><address>'+[ship-address__1]+' '+[ship-city]+', '+cast([ship-st] as nvarchar(255))+' '+cast([zip-code] AS nvarchar(255))+'</address><styleUrl>#A0</styleUrl></Placemark>'

FROM #tmp_invoicecube

If you know a bit about strings, you should be able to follow the SELECT statement above. Simply stated, the name element is going to hold descriptive data about the total number of orders 'ORDERS: ' and the [orders] data value; total sales ' DOLLARS: ' and the [dollars] data value; plus various other data you and your President decide to have shown when a marker is moused over.  Next we provide the geospatial plot between the <Point><coordinates> tags. Lastly we provide the full customer address data between the <address> tags.

Substitute your own column names from your own invoiced data table.  To help you I have encased my column names in square brackets in the SELECT statement.

The resulting text string(s) your script generates should be in the form of the one below.

<Placemark><name>ORDERS: 1 DOLLARS: 3066.58 TYPE: DISTRIBUTION - BILL TO: 10170 MY SAMPLE CUSTOMER - SHIP TO: 10170 MY SAMPLE CUSTOMER 123 Anywhere Street</name><description>DIST</description><Point><coordinates>-80.522617,35.438020,0</coordinates></Point><address>123 Anywhere Street Anytown, SC 29071</address><styleUrl>#A0</styleUrl></Placemark>

Now take the resulting answer sets and paste into a simple text editor. Put the output for your @kmlhdr variable into the text editor first.  Next place the data set for the script that generates the Placemark strings into the text editor. Last, do not forget to place the text for your @kmlftr variable into the text editor. Then save the file and replace the .txt with .kml.

After you have Google Earth installed, double click your .kml file. When your file is opened, you will see a red dot marker designating the location of sales for a Placemark row. When you hover over it with your mouse, you'll see a label that describes the order - total orders and sales dollars - and customer information.

Setting coordinates set once you have spatial data

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/