SQL Server 2008 Spatial

Document Sample
SQL Server 2008 Spatial Powered By Docstoc
					Spatial Support
Relational and Non-Relational
Data
•   Relational data uses simple data types
    − Each type has a single value
    − Generic operations work well with the types
•   Relational storage/query may not be optimal
    for
    − Hierarchical data
    − Sparse, variable, property bags
•   Some types
    − benefit by using a custom library
    − use extended type system (complex types,
      inheritance)
    − use custom storage and non-SQL APIs
    − use non-relational queries and indexing
Spatial Data
•   Spatial data provides answers to location-
    based queries
    − Which roads intersect the Microsoft campus?
    − Does my land claim overlap yours?
    − List all of the Italian restaurants within 5
      kilometers
•   Spatial data is part of almost every
    database
    − If your database includes an address
Spatial Data Types
•   The Open Geospatial Consortium defines a
    hierarchy of spatial data types
    −   Point
    −   Linestring
    −   Polygon
    −   MultiPoint
    −   MultiLinestring
    −   MultiPolygon
    −   GeomCollection
    −   Non-instanciable classes based on these
OGC Hierarchy of Spatial Types
SQL Server 2008 and Spatial
Data
•   SQL Server supports two spatial data types
    − GEOMETRY - flat earth model
    − GEOGRAPHY - round earth model
•   Both types support all of the instanciable
    OGC types
    − InstanceOf method can distinguish between
      them
•   Supports two dimension data
    − X and Y or Lat and Long members
    − Z member - elevation (user-defined semantics)
    − M member - measure (user-defined semantics)
GEOGRAPHY Requirements
•   GEOGRAPHY type has additional
    requirements
•   Coordinate order is
    − Longitude/Latitude for WKT, WKB
    − Latitude/Longitude for GML
•   Exterior polygon rings must have their
    describing coordinates in counter-clockwise
    order (left-hand rule) with interior rings
    (holes) in clockwise-order (right-hand rule)
•   A single GEOGRAPHY object cannot span
    more than a logical hemisphere
Spatial Data
Properties and Methods
•   The spatial data types are exposed as
    SQLCLR UDTs
    −   Use '.' syntax for properties
    −   Use '.' syntax for instance methods
    −   Use '::' syntax for static methods
    −   Methods and Properties are case-sensitive
•   Each type uses a set of properties and
    methods that correspond to OGC functionality
    − With Extensions
    − Geometry implements all OGC properties and
      methods
        − Geography implements most OGC properties and
          methods
    − 2-D vector only implemented
Input
•   Spatial data is stored in a proprietary binary format
•   Instance of the type can be NULL
•   Can be input as
    − Well Known binary - ST[Type]FromWKB
    − Well Known text - ST[Type]FromText
    − Geography Markup Language (GML) - GeomFromGml
•   Can also use SQLCLR functions
    − Parse
    − Point - extension function
•   Input from SQLCLR Type - SqlGeometry,
    SqlGeography
•   Spatial builder API – Populate,
    IGeometrySink/IGeographySink
Output
•   Spatial Data Can Be Output As
    −   Well Known binary - STAsBinary
    −   Well Known text - STAsText
    −   GML - AsGml
    −   Text with Z and M values - AsTextZM
•   SQLCLR standard method
    − ToString - returns Well Known text
•   As SQLCLR object - SqlGeometry,
    SqlGeography
•   Other useful formats are GeoRSS, KML
    − Not Directly Supported
SRID
•   Each instance of a spatial type must have an SRID
    − Spatial Reference Identifier
•   SRID specifies the specification used to compute it
    − SRID 4326 - GPS, default for GEOGRAPHY
    − SRID 4269 - usually used by ESRI
    − SRID 0 - no special reference, default for GEOMETRY
•   Methods that use multiple spatial types (e.g.,
    STDistance) must have types with matching SRID
    − Else method returns NULL
•   Geography instance must reference one of these
    SRID stored in sys.spatial_reference_systems
Useful Methods/Properties
•   Descriptive
    − STArea
    − STLength
    − STCentroid
•   Relation between two instances
    − STIntersects
    − STDistance
•   Manipulation
    − STUnion
    − STSymDifference
•   Collections
    − STGeometryN
    − STPointN
Sample Query




  Which roads intersect Microsoft’s main
      SELECT *
      FROM roads
  campus? roads.geom.STIntersects(@ms)=1
      WHERE
Extension Methods
•   SQL Server 2008 extends OGC methods
    − MakeValid - Converts to OGC valid instance
    − BufferWithTolerence - similar to STBuffer, allows
      approximation and variation
    − Reduce - Simplify a complex geography or
      geometry
    − NumRings, RingN - polygons with multiple rings
    − GML support
    − Z and M properties and AsTextZM method
    − Filter - provides a quick intersection set but with
      false positives
    − EnvelopeCenter,EnvelopeAngle for Geography
      types
Spatial Indexes
•   SQL Server Spatial Indexes Based on B-Trees
    − Uses tessellation to tile 2D to linear
    − Divides space into grid of cells(uses Hilbert algorithm)
•   Meant as a first level of row elimination
    − Can produce false positives
    − Never false negatives
•   You specify
    − Bounding box of top level grid - GEOMETRY index only
    − Cells per object - number of cells recorded for matching
    − Grids
       −   Four Grid Levels
       −   Three Grid Densities Per Level - Low, Medium, High
Tessellation process
Spatial Analytics
Review
•   Spatial data provides answers to location-
    based queries
•   SQL Server supports two spatial data types
    − GEOMETRY - flat earth model
    − GEOGRAPHY - round earth model
•   Spatial data has
    −   Useful properties and functions
    −   Library of spatial functions
    −   Three standard input and output formats
    −   Spatial indexes
Resources
•   SQL Server Spatial Data Technology Center
    http://www.microsoft.com/sql/2008/technologies/spatial.mspx
•   Whitepaper: Delivering Location Intelligence
    with Spatial Data
    http://www.microsoft.com/sql/techinfo/whitepapers/spatialdata.mspx
•   MSDN Webcast: Building Spatial Applications
    with SQL Server 2008, Event ID: 1032353123
•   Whitepaper: What's New for XML in SQL
    Server 2008
    http://www.microsoft.com/sql/techinfo/whitepapers/sql_2008_xml.mspx
•   Whitepaper: Managing Unstructured Data with
    SQL Server 2008
    http://www.microsoft.com/sql/techinfo/whitepapers/sql_2008_unstructured.mspx
© 2009 Microsoft Corporation. All rights reserved. Microsoft, Windows, Windows Vista and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries.
The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market
     conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation.
                                 MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.

				
DOCUMENT INFO
Shared By:
Categories:
Stats:
views:292
posted:3/19/2010
language:English
pages:21
Description: Microsoft Training Kit for SQL Server 2008, Microsoft training kit for ASP.NET MVC