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。
此数据表示华盛顿州销售 AdventureWorks 自行车的一组商店。结果集中的每行都包含一个商店标识符、商店名称、该商店专销的自行车类型、商店所在的市/镇、年销售额以及用经度和纬度表示的空间位置。
点击“下一步”，进入“选择空间数据和地图视图选项”页，如下图所示：
验证“空间字段”为 SpatialLocation，并且“层类型”为“点”。接受本页上的其他默认值。地图视图显示圆圈，这些圆圈标记销售 AdventureWorks 产品的商店的位置。
单击“下一步”。