1 / 46

Spatial data types in SQL Server 2008: computation and visualization

Spatial data types in SQL Server 2008: computation and visualization. Marko Tintor Danica Porobi ć Microsoft Development Center Serbia. Agenda. Virtual Earth with SQL Server. What is Spatial about?. Traditional GIS Now Consumer Developing LOB Ubiquity. Spatial Data Scenarios.

major
Download Presentation

Spatial data types in SQL Server 2008: computation and visualization

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. Spatial data types inSQL Server 2008: computation and visualization Marko Tintor DanicaPorobić Microsoft Development Center Serbia

  2. Agenda

  3. Virtual Earth with SQL Server

  4. What is Spatial about? • Traditional • GIS • Now • Consumer • Developing • LOB • Ubiquity

  5. Spatial Data Scenarios

  6. Spatial in SQL 2008 • We’re providing vector support • We’re targeting geospatial... • Spatial data which is referenced to a location on the Earth • Typically uses spherical coordinates or projected planar coordinates • I’ll come back to this distinction in a moment • ...but, there is no restriction that the data is actually geospatial • Only 2D for now (Geodetic is 2D)

  7. What is Spatial Data? • Vector • Points • LineStrings • Polygons (Areas, Regions) • Raster • Satellite Imagery • Digitized Aerial Photos

  8. Sample Query Which roads intersect Microsoft’s main campus? SELECT * FROM roads WHERE roads.geom.Intersects(@ms)=1

  9. It’s a big problem…

  10. It can be complex, too...

  11. All Flat Maps Are Wrong • Flat maps are projections from the spheroid • Small areas don’t distort too much • Large areas do, so no good global projection • Still very important for legislative and historical reasons conic secant projection

  12. Comprehensive Spatial SupportSpatial Data Types

  13. Planar and Geodetic Cover Different Scenarios • Planar (flat-earth) • Supports legacy and legal mapping requirements: surveyors and the specialist GIS crowd • Computationally simpler • Conceptually more difficult for geospatial • Geodetic (round-earth) • Supports existing long-range mapping requirements: military, shipping, etc. • Computationally more complex • Conceptually simpler for geospatial

  14. Spatial Operators World Class

  15. Planar Type • New type: geometry • Built on the same infrastructure • Geometry can store instances of various types • Points • Line strings • Polygons • Collections of the above • Methods for computing • Spatial relationships: intersects, disjoint, etc. • Spatial constructions: intersection, union, etc. • Metric functions: distance, area

  16. Geodetic Type • Second type: geography • Very similar interface to geometry • Some methods have different semantics • Most data commonly available user data is geodetic • Anything expressed as latitude/longitude • This is the type we expect most people to be interested in

  17. Demo Architecture

  18. Demo Data

  19. E75 and Counties

  20. Flood Analysis

  21. Economic Development Analysis

  22. High Performance Spatial CapabilitiesSpatial Indexing • Spatial indexing is built into the query engine • Grid-based quad-tree index structure

  23. Multi-Level Grid 42.3

  24. Spatial Index Performance

  25. Defining edges on round Earth • Requirements: • Locally: edge should appear straight • Pair of points should define unique edge • Edge should have differentiable parameterization • Possible solution: Geodesic curves (shortest path between points) • Not suitable for points that are not on surface • Two points define more than one edge • Computations are extremely difficult

  26. Definition in SQL Server Spatial • Space of directions: • Geographic coordinates of a point on the globe are defined by the direction of a vector that is normal to the surface of the globe at that point • Space of directions – nonzero normal vectors • Definition: • An edge is the image under the Inverse Gaussian Mapping (IGM) of the short great circle arc on the sphere of directions between the unit normal vectors at its endpoints • An edge is the image under the IGM of a line segment in the space of directions

  27. Mapping from normal vectors to points on ellipsoid • Surface of rotational ellipsoid: • Let (u,v,w) be normal at the surface of (x,y,z), so under IGM: • Mapping is:

  28. Parameterization of an edge • Great-circle arc on the sphere of unit normal vectors • Edge on ellipsoid • Linear parameterization of great circle arc’s chord • Parameterization

  29. Numeric computations • Velocity vector: • Length of an edge: • Area: sum of integrals over edges • Computation using Gauss Legendre quadrature • Long edges broken into pieces

  30. Boolean operations • Spherical arcs projected to lines using gnomonic projections • Boolean operations on sphere transform to their planar counterparts after projection • Planar operations rely on geometric library from Windows Vista • Gnomonic projection works for smaller objects • Larger objects handled by tiled projection

  31. Simple gnomonic projection • Central projection from the origin to a plane that is tangent to the unit sphere • The image of a polygon on sphere is a polygon in the plane • Let C be the point of tangency • Basis vectors: two mutually orthogonal unit vectors A and B, orthogonal to C • Projection of vector U: • V = U/U∙C • Planar coordinates: (V-C)∙A and (V-C)∙B

  32. Tiled projection • Map the scene with simple projections onto four facets of the tetrahedron • Affine projection along the axis into the plane

  33. Visualization of geography • Building blocks • Points • LineStrings • Polygons • Points and LineStrings are easy to display • But, how to display the polygons?

  34. Rendering polygons on Earth • Even polygons with small number of vertices can have unintuitive shape

  35. Polygon rendering issues • curved (spherical) surface • polygons can have holes • polygons can be non-convex • polygons can be near / span over poles • polygons can cross ±180 degrees meridian

  36. Possible solutions • Vector • Subdivide the polygon untilall parts are simple enough • Raster • Render the polygon to the textureand wrap that texture around sphere

  37. Our idea • Subdivide entire Earth into patches • Render only the patches that are on the inside of the polygon • Use smaller patches near the border of polygon

  38. Possible relations ofpatch and the polygon • Completely inside • On the border • Completely outside

  39. Core algorithm FUNCRenderPatch(patch, polygon) IF patch is INSIDE the polygon Render(patch) IF patch is ON BORDER of the polygon AND patch is not too small FOREACH piece IN Subdivide(patch) RenderPatch(piece, polygon) IF patch is OUTSIDE polygon ignore it

  40. Best choice for patch shape • Triangle • Easy to render • Easy to subdivide • Easy to tile

  41. How to subdivide the Earth? • Start from octahedron • Subdivide each triangle recursively

  42. End result in wireframe

  43. Questions?

  44. Thank you!

  45. Resources • Sql Server Spatial http://www.microsoft.com/sqlserver/2008/en/us/spatial-data.aspx • Microsoft Development Center Serbia http://www.microsoft.com/scg/mdcs/default.mspx • Defining Edges on a Round EarthMichael Kallay, Microsoft Corporation ACM GIS ’07, Seattle, November 2007

More Related