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.