1 / 35

Europe’s Premier Community SQL Server Conference

Europe’s Premier Community SQL Server Conference. # SQLBITS. Premium Sponsor. Platinum Sponsor. Gold Sponsors. Please visit our Gold Sponsor stands, we couldn't do it without you…. Silver, Bronze and Exhibitors. All of you…. Fusion IO Lounge.

gayle
Download Presentation

Europe’s Premier Community SQL Server Conference

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. Europe’s Premier Community SQL Server Conference • #SQLBITS

  2. Premium Sponsor

  3. Platinum Sponsor

  4. Gold Sponsors Please visit our Gold Sponsor stands, we couldn't do it without you…

  5. Silver, Bronze and Exhibitors All of you…

  6. Fusion IO Lounge • Don’t forget to check out the Fusion IO Lounge • Find out about SQL Relay, SQL Saturdays, Pass and the User Groups in your area!

  7. Housekeeping • Health and Safety • If there is a fire alarm today, make your way outside to the meeting point in the main car park • There is no fire drill today • Breaks • A.M. - 10:30, 11:50 • Lunch - 13:00-13:40 • P.M. - 15:50 • Sponsor Prize Giving Exhibition Hall – 17:15 • Smoking • Don’t do it mate - it’s not worth it… • Prayer Room – ask at the Hotel reception

  8. Internet and Guide Book Wi-Fi – The details are as follows: • Connect to ‘Venues Wi-Fi’ • Open the web browser • Enter your email and password: venues • Click to accept the terms and conditions • Click on FREE Wi-Fi icon Guide Book Use the iPad app or browse http://m.guidebook.com/g/sqlbits11

  9. Fusion IO Lego Draw Fusion IO will be drawing numbers at random throughout the day and tweeting the winners of their Lego IO drive competition

  10. Enjoy SQLBits 11 • #SQLBITS

  11. About Me sleektechnologies Leonard Lobel • CTO & Co-Founder • Sleek Technologies, Inc. • Principal Consultant • Tallan, Inc. • Microsoft MVP • SQL Server • .NET consultant and trainer • Speaker • Author • Programming since 1979 Contact • Email: lenni.lobel@sleektech.com • Blog: lennilobel.wordpress.com • Twitter: @lennilobel

  12. Read All About It!

  13. Agenda • Overview • Defining Spatial Data • The two spatial models • Spatial standards • Demos • geography • geometry • Area, distance, length calculations • Intersection and union manipulations • Many more spatial methods • Bing Maps mash-up

  14. Download Slides and Code http://bit.ly/sqlbits2013_geospatial (all lower case!)

  15. SQL Server Spaces Out • Integrate location awareness into any application • Long been the domain of sophisticated GIS applications • GIS • A system for capturing, storing, analyzing, and managing data and associated attributes which are spatially referenced to the earth • Allow a user to interact with information that is relevant to locations that they care about: • Home, work, school, or vacation destinations • Two geospatial models • Planar • Geodetic

  16. Spatial Data Types • Two spatial models = Two system CLR types • geometry • Planar (flat) model • Flat 2-dimensional Cartesian Coordinate system • X and Y coordinates with uniform units of measure • Use for mapping small areas • geography • Geodetic (round-earth) model • Latitude and longitude • Use for larger mapping where land mass is too big to fit on one planar projection

  17. Planar Spatial Model • Two-Dimensional Surface • X and Y coordinates on an arbitrary plane • Flat Earth Projection • To work with geospatial data on a 2D surface, a projection is created to flatten the geographical objects on the spheroid • Example: Planar Model based on Mercator Projection Square KM: - Antarctica = 13 million - Greenland = 2 million - N. America = 24 million - Africa = 30 million Greenland North America Africa Antarctica

  18. Geodetic Spatial Model • Accurate geographic measurements • Locations on planet surface described by latitude and longitude angles • Ellipsoidal sphere • Latitude = angle N/S of the equator • Longitude = angle E/W of the Prime Meridian

  19. Spatial Data Standards • Open Geospatial Consortium (OGC) • International standards body • Microsoft belongs to the OGC • SQL Server 2008 uses the OGC’s Simple Feature Access standards • OpenGIS Simple Feature Interface Standards (SFS) • A well-defined way for applications to store and access spatial data in relational databases • Described using vector elements; such as points, lines and polygons • Three ways to import geospatial data • Well-Known Text (WKT) • Well-Known Binary (WKB) • Geographic Markup Language (GML)

  20. Well-Known Text (WKT) • WKT examples: • POINT(6 10) • POINT(-111.06687 45.01188) • LINESTRING(3 4,10 50,20 25) • POLYGON((1 1,5 1,5 5,1 5,1 1),(2 2, 3 2, 3 3, 2 3,2 2)) • POLYGON((-75.17031 39.95601, -75.16786 39.95778,-75.17921 39.96874, -75.18441 39.96512,-75.17031 39.95601)) • MULTIPOINT(3.5 5.6,4.8 10.5) • MULTILINESTRING((3 4,10 50,20 25),(-5 -8,-10 -8,-15 -4)) • GEOMETRYCOLLECTION(POINT(4 6),LINESTRING(4 6,7 10)) • CIRCULARSTRING(1 5, 6 2, 7 3)

  21. Geospatial Methods • STArea • STBuffer • STCentroid • STDifference • STDimension • STDistance • STEnvelope • STGeomFromText • STIntersection • STIntersects • STPointFromText, STLineFromText, STPolyFromText • STPointFromWKB, STLineFromWKB, STPolyFromWKB • STSymDifference • STUnion • GeomFromGml • Parse • ToString • and more (about 70 total)

  22. Spatial Data Types

  23. 0,0 150,0 300,0 50,50 100,100 0,150 150,150 300,150 20,180 180,180 300,300 150,300

  24. Spatial Improvements InSQL Server 2012 • Circular Arcs • CircularString • CompoundCurve • CurvePolygon • All existing methods work on circular objects • New spatial methods • BufferWithCurves • STNumCurves, STCurveN • STCurveToLine • CurveToLineWithTolerance • IsValidDetailed • HasZ, HasM, AsBinaryZM • ShortestLineTo • UnionAggregate, EnvelopeAggregate, CollectionAggregate, ConvexHullAggregate • MinDbCompatibilityLevel

  25. Spatial Improvements InSQL Server 2012 • Improved Precision • Constructions and relations use 48 bits of precision (previously 27 bits) • geography Enhancements • Support for objects larger than a logical hemisphere (“FullGlobe”) • Support for new and previous “geometry-only” methods • New SRID • Spatial reference ID 104001 (sphere of radius 1) • Performance Improvements • Better tuning and hints • Auto Grid indexing with 8 levels (previously 4 levels) • Other Improvements • New histogram stored procedures • Support for persisted computed columns

  26. Spatial Improvements in SQL Server 2012

  27. Europe’s Premier Community SQL Server Conference • #SQLBITS

  28. Premium Sponsor

  29. Platinum Sponsor

  30. Gold Sponsors Please visit our Gold Sponsor stands, we couldn't do it without you…

  31. After… Sponsor Competition Draws in the Exhibition Hall 17:15

  32. Community Events

  33. Feedback • Please complete feedback • http://sqlbits.com/SQLBitsXIThursday • http://sqlbits.com/SQLBitsXIFriday • http://sqlbits.com/SQLBitsXISaturday • http://sqlbits.com/SQLBitsXI (General feedback)

  34. We hope you had a great conference day! Keep checking www.sqlbits.com for slides, videos and news of the next conference • #SQLBITS

More Related