SQL Server R2 地圖報表製作（三）

SQL Server R2 地圖報表製作（三）

如所示：點擊“下一步”，接下來我們建立一個資料集查詢，其中同時包含空間資料和分析資料。在“設計查詢”頁中，單擊“編輯為文本”。查詢設計工具切換到基於文本的模式。

將以下查詢語句粘貼到查詢窗格中：

Select 402 as BusinessEntityID, 'Impromptu Trips' as Store, 'Mountain'as Specialty, 'Union Gap' as City,  'Yakima' as County, CAST(1500000 as money) as  AnnualSales,  CAST('POINT(-120.352399683576 46.5526404267361)' as geography)  AS SpatialLocation UNION ALL SELECT 518 AS BusinessEntityID, 'Latest Sports Equipment' AS  StoreName, 'Mountain' as Specialty, 'Seattle' AS City,   'King' as County, CAST('800000' as money) as  AnnualSales,  CAST('POINT(-122.2716149145 47.6252759540999)' as geography)  AS SpatialLocation UNION ALL Select 656 as BusinessEntityID, 'Outstanding Cycles' as Store, 'Touring'as Specialty, 'Spokane' as City,   'Spokane' as County,  CAST(2000000 as money) as AnnualSales,  CAST('POINT(-117.458326188527 47.7390846943325)' as geography)  AS SpatialLocation UNION ALL Select 762 as BusinessEntityID, 'Friendly Bike Shop' as Store, 'Road' as Specialty, 'Bellingham' as City,    'Whatcom' as County,  CAST(2000000 as money) as  AnnualSales,  CAST('POINT(-122.526710739 48.7985754516)' as geography)  AS SpatialLocation UNION ALL Select 774 as BusinessEntityID, 'Rugged Bikes' as Store, 'Touring' as Specialty, 'Everett' as City,  'Snohomish' as County,  CAST(3000000 as money) as AnnualSales,  CAST('POINT(-122.339928832415 48.0034793435206)' as geography)  AS SpatialLocation UNION ALL Select 870 as BusinessEntityID, 'Vast Bike AnnualSales and Rental' as Store, 'Mountain' as Specialty, 'Chehalis' as City,  'Lewis' as County, CAST(3000000 as money) as AnnualSales,  CAST('POINT(-123.173202922628 46.5949185631117)' as geography)  AS SpatialLocation UNION ALL Select 1138 as BusinessEntityID, 'Roving Sports' as Store, 'Road' as Specialty, 'Puyallup' as City,  'Pierce' as County,  CAST(1500000 as money) as AnnualSales,  CAST('POINT(-122.141962349474 47.2117618655752)' as geography)  AS SpatialLocation UNION ALL Select 1230 as BusinessEntityID, 'Fitness Supplies' as Store, 'Touring' as Specialty, 'Sequim' as City,  'Clallam' as County,  CAST(800000 as money) as AnnualSales,  CAST('POINT(-123.165980874577 48.0931018882565)' as geography)  AS SpatialLocation UNION ALL Select 1270 as BusinessEntityID, 'Outdoor Sports Supply' as Store, 'Mountain' as Specialty, 'North Bend' as City,  'King' as County,  CAST(300000 as money) as AnnualSales,  CAST('POINT(-121.50439946838 47.5225521767213)' as geography)  AS SpatialLocation UNION ALL Select 1388 as BusinessEntityID, 'Official Parts Shop' as Store, 'Road' as Specialty,  'Kelso' as City,  'Cowlitz' as County,  CAST(1000000 as money) as AnnualSales,  CAST('POINT(-122.77961702831 46.1007615739732)' as geography)  AS SpatialLocation UNION ALL Select 1412 as BusinessEntityID, 'City Manufacturing' as Store, 'Mountain' as Specialty, 'Shelton' as City,  'Mason' as County,  CAST(2500000 as money) as AnnualSales,  CAST('POINT(-123.323052823833 47.1401103193325)' as geography)  AS SpatialLocation

我們可在查詢結果網格中看到七列：

BusinessEntityID、Store、Specialty、City、County、AnnualSales 和 SpatialLocation。

Server R2 地圖報表製作（三）" alt="SQL Server R2 地圖報表製作（三）" src="http://s1.sinaimg.cn/middle/66ff669et8429d53958c0&690&690" action-type="show-slide" action-data="http%3A%2F%2Fs1.sinaimg.cn%2Fmiddle%2F66ff669et8429d53958c0%26690%26690" real_src="http://s1.sinaimg.cn/middle/66ff669et8429d53958c0&690&690">

此資料表示華盛頓州銷售 AdventureWorks 單車的一組商店。結果集中的每行都包含一個商店標識符、商店名稱、該商店專銷的單車類型、商店所在的市/鎮、年銷售額以及用經度和緯度表示的空間位置。

點擊“下一步”，進入“選擇空間資料和地圖視圖選項”頁，如所示：
Server R2 地圖報表製作（三）" alt="SQL Server R2 地圖報表製作（三）" src="http://s4.sinaimg.cn/middle/66ff669et8429d538ce73&690&690" action-type="show-slide" action-data="http%3A%2F%2Fs4.sinaimg.cn%2Fmiddle%2F66ff669et8429d538ce73%26690%26690" real_src="http://s4.sinaimg.cn/middle/66ff669et8429d538ce73&690&690">

驗證“空間欄位”為 SpatialLocation，並且“層類型”為“點”。接受本頁上的其他預設值。地圖視圖顯示圓圈，這些圓圈標記銷售 AdventureWorks 產品的商店的位置。

單擊“下一步”。