1 / 28

Spatial Business Intelligence

Spatial Business Intelligence Abstract. SQL Server 2008 delivered spatial types in the database. SQL Server 2008 R2 brought the ability to produce high quality graphics using that spatial data. Come to this session to learn how to use those capabilities to deliver Business Intelligence solutions th

jenski
Download Presentation

Spatial Business Intelligence

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

    1. Alan Eldridge Technical Specialist - BI Microsoft Spatial Business Intelligence

    3. Spatial Business Intelligence Abstract SQL Server 2008 delivered spatial types in the database. SQL Server 2008 R2 brought the ability to produce high quality graphics using that spatial data. Come to this session to learn how to use those capabilities to deliver Business Intelligence solutions that answer the "Where?" questions that often arise in business decision making.

    4. Spatial Business Intelligence Agenda The beginning… in which I introduce you to spatial intelligence and explain why you should be interested. The middle… in which I show you many interesting examples and demonstrations. The end… in which I recap, give you some pointers to additional information and invite you to ask any questions.

    5. London cholera outbreak John Snow, 1855

    6. Napolean’s 1812 Russian campaign Charles Joseph Minard, 1869

    7. What is Spatial Intelligence? Spatial Intelligence is the capacity to organise and understand complex phenomena through the use of geographic relationships inherent in all information. By combining geographic- and location-related data with other business data, organisations can gain critical insights, make better decisions and optimise important processes and applications.

    8. What is Spatial Intelligence?

    9. What is Spatial Intelligence?

    10. Microsoft Spatial BI Tools SQL Server Spatial data types Maps in SSRS Visio Create spatial diagrams Connect diagram objects to data Bing Maps Geocoding Spatial “tiles” Rich APIs for custom development

    11. Spatial Data in SQL Server Introduced in SQL Server 2008 Two spatial datatypes: Geometry Planar vector spatial data Shapes expressed on a flat, planar coordinate system Cartesian (x, y) coordinates Geography Geodetic vector spatial data Shapes expressed on a spherical coordinate system Latitude, longitude coordinates

    12. Spatial Data in SQL Server 3 basic geometry types: Points A point in space – (lat long), (X Y) Addresses, towns, centroids LineStrings Two or more points and line segments that join them Roads, rivers, routes, flows, contours, isolines Polygons All points enclosed by an exterior ring (a closed LineString) Can be simple or complex Countries, states, postcodes, rooms, buildings, areas

    13. Creating Spatial Data POINT (X Y) or POINT (LONG LAT) DECLARE @Edinburgh geography SET @Edinburgh = geography::STPointFromText (‘POINT(-3.19 55.95)’, 4236) LINESTRING (x1 y1, x2 y2, … , xn yn) DECLARE @SydneyHarbourBridge geography SET @SydneyHarbourBridge = Geography::(STLineFromText( ‘LINESTRING(151.209 -33.855, 151.212 -33.850)’, 4326) POLYGON ( (ax1 ay1, ax2 ay2, … , axn ayn, ax1 ay1), (bx1 by1, bx2 by2, … , bxn byn, bx1 by1), … )

    14. Other ways to get spatial data… Buy it Governments; PSMA; Navteq; ESRI; MapInfo Project Dallas Download it Open data movements; governments; gData Text files; ESRI shape files; GeoRSS; GML Make it yourself Capture it directly Geocoding – Bing; Google; OpenStreetMap Draw it

    15. Using Spatial Data Surface in Reporting Services Map component new in SQL Server 2008 R2

    16. Querying Spatial Data CREATE TABLE SpatialTable ( id int IDENTITY (1,1), GeogCol1 geography, GeogCol2 AS GeogCol1.STAsText() ); GO INSERT INTO SpatialTable (GeogCol1) VALUES (geography::STGeomFromText('LINESTRING(-122.360 47.656, -122.343 47.656)', 4326)); INSERT INTO SpatialTable (GeogCol1) VALUES (geography::STGeomFromText('POLYGON((-122.358 47.653, -122.348 47.649, -122.348 47.658, -122.358 47.658, -122.358 47.653))', 4326)); GO DECLARE @geog1 geography; DECLARE @geog2 geography; DECLARE @result geography; SELECT @geog1 = GeogCol1 FROM SpatialTable WHERE id = 1; SELECT @geog2 = GeogCol1 FROM SpatialTable WHERE id = 2; SELECT @result = @geog1.STIntersection(@geog2); SELECT @result.STAsText();

    17. Querying Spatial Data Some useful methods ------- STPointFromText(); STLineFromText(); STPolyFromText() STAsText(); ToString() STCentroid(); EnvelopeCenter() STArea(), STLength() STEnvelope(); STBuffer() STEquals() STIntersection(); STUnion(); STDifference() STOverlaps(); STCrosses(); STContains() STDistance()

    18. Other Spatial Tools Build your own content using Visio Spatial context inherent in the diagram Connect elements to data

    19. Other Spatial Tools

    20. Other Spatial Tools Bing Maps SOAP Services A set of programmable SOAP services to integrate location intelligence into your applications. Bing Maps REST Services Perform tasks such as geocoding an address, retrieving imagery metadata or calculating a route. Bing Spatial Data Services Includes the Geocode Dataflow API, which allows you to geocode and reverse-geocode lists of spatial data.

    21. Other Spatial Tools

    22. Other Spatial Tools

    23. DEMOS

    24. Useful Tools Shape2SQL – import ESRI shapefiles Safe FME – commercial spatial data converter SQLSpatial Query – WPF spatial query tool ESRI MapIt idvSolutions SpatialWiki – ad-hoc geography creation Photosynth

    25. Related Sessions COS310 Location-Enabling the Cloud – Spatial Data Support in SQL Azure Ed Katibah DAT301 Reporting Services Enhancements in SQL Server 2008 R2 Greg Low ARC302 The Future of Data Graham Elliot OFS204 Visio and SharePoint – Better Together Ed Richard & Jamie Hutchins WEB303 Showcase Your Ideas in Bing Maps Bronwen Zande, John O’Brien

    26. Online Resources Beginning Spatial with SQL Server 2008 - Alastair Aitchison ISBN-13: 978-1430218296 http://blogs.msdn.com/b/edkatibah/ - Ed Katibah http://blogs.msdn.com/b/isaac/ - Isaac Kunen http://blogs.msdn.com/b/davidlean - David Lean http://www.sqlpass.org/LearningCenter/Top10Lists.aspx#SpatialData http://data.australia.gov.au/; http://data.gov.uk/; http://www.data.gov/ http://www.abs.gov.au http://geodata.grid.unep.ch/ http://www.geonames.org/ http://www.dundas.com/Components/Products/Map/NET/Resources/index.aspx http://dbpedia.org http://www.w3.org/standards/semanticweb/

    27. QUESTIONS?

    29. Resources

More Related