Geospatial Data Types in SQL Server

The geospatial capabilities in SQL Server are not only powerful, but easy and fun to use! And because they are implemented as CLR types, they can be used just the same in your .NET applications. In this session, Lenni shows you how to integrate location-awareness into your own database applications with the geometry and geography data types. We’ll explore the planar and geodetic spatial models, spatial markup languages such as WKT and GML, and then put these concepts to use and build several spatial desktop and web applications. Demos show how to integrate with Bing Maps, and how to use the geospatial methods to easily calculate area, length, and distance, and project new shapes based on intersection and overlap. We’ll also cover the latest geospatial enhancements in SQL Server 2012. Attend this session and embrace spatial programming today!

Transcript

1. March 8 3:00pm – 4:15pm Geospatial Data Types in SQL Server Leonard Lobel Chief Technology Officer Sleek Technologies Level: Intermediate
2. About Me Leonard Lobel • CTO & Co-Founder – Sleek Technologies, Inc. • Principal Consultant – Tallan, Inc. • Microsoft MVP – Data Platform • Co-organizer – NYC .NET Developers Group • Trainer/Speaker/Author • Programming since 1979 Contact • Email: lenni.lobel@sleektech.com • Blog: lennilobel.wordpress.com • Twitter: @lennilobel sleek technologies Developers Group
3. Agenda • Overview – Defining spatial data – The two spatial models – Spatial standards • Demos – geography – geometry – Area, distance, and length calculations – Intersection and union manipulations – Many more spatial methods – Bing Maps mash-up
4. http://bit.ly/ vslvegas2016_geospatial (all lower case!) Download Slides and Code
5. SQL Server Spaces Out • Integrate location awareness into any application • 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
6. 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
7. 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 Antartica Greenland North America Africa Square KM: - Antartica = 13 million - Greenland = 2 million - N. America = 24 million - Africa = 30 million
8. 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
9. 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)
10. 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)
11. 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)
12. Geospatial
13. 0,0 150,0 150,150 0,150 300,150 300,0 150,300 300,300 50,50 100,100 20,180 180,180
15. Spatial Improvements In SQL Server 2012 • Circular Arc Shapes – 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
16. Spatial Improvements In SQL 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
17. Thank You! • Contact me – lenni.lobel@sleektech.com • Visit my blog – lennilobel.wordpress.com • Follow me on Twitter – @lennilobel • Thanks for coming! 