1 / 21

virtual techdays

INDIA │ 18-20 august 2010. virtual techdays. Developing with SQL Server Spatial & Deep Dive into Spatial Indexing. Pinal Dave │ Mentor, Solid Quality Mentors │ SQLAuthority.com. INDIA │ 18-20 august 2010. virtual techdays. Yes No May Be I Don’t know!. Question to You!.

lidia
Download Presentation

virtual techdays

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. INDIA │ 18-20 august2010 virtual techdays Developing with SQL Server Spatial & Deep Dive into Spatial Indexing Pinal Dave│ Mentor, Solid Quality Mentors│SQLAuthority.com

  2. INDIA │ 18-20 august2010 virtual techdays • Yes • No • May Be • I Don’t know! Question to You! Is the Earth Flat?

  3. INDIA │ 18-20 august2010 virtual techdays The Earth is Flat…

  4. INDIA │ 18-20 august2010 virtual techdays The Earth is Flat…

  5. INDIA │ 18-20 august2010 virtual techdays The Earth is Flat…

  6. INDIA │ 18-20 august2010 virtual techdays …but the Earth is Sphere (almost)!

  7. INDIA │ 18-20 august2010 virtual techdays • Introduction to Spatial Database • One line definition • Planer vs Geographic • Understanding Spatial Indexing • Planer vsGeographic • Index Internals • Query Hinting • Index Maintenance • Performance Troubleshooting Agenda Session Objectives And Key Takeaways

  8. INDIA │ 18-20 august2010 virtual techdays • SQL Server MVP • Mentor – Solid Quality Mentors India • Founder – SQLAuthority.com • Regional Mentor – Professional Association for SQL Server (PASS) Asia, Pacific & Middle East • User Group Lead – Ahmedabad SQL Server UG, Gandhinagar SQL Server UG • Author, Trainer, Speaker, Consultant • MCT, MCTS, MCP, MCDBA, MCAD • pdave@solidq.com, pinal@sqlauthority.com • http://twitter.com/pinaldave Agenda Session Objectives And Key Takeaways

  9. INDIA │ 18-20 august2010 virtual techdays • spa·tial [spey-shuhl] –adjective 1.of or pertaining to space. 2.existing or occurring in space. 3.having extension in space. • Database that models space, objects in space, or a combination of both and provide capabilities to store and manipulate spatial data What is Spatial? One Line Definition

  10. INDIA │ 18-20 august2010 virtual techdays • GEOMETRY data type • Infinite X and Y SQL Server 2008 Systems Story Geographic Systems Planar Systems • GEOGRAPHY data type • Latitude -90 to +90 • Longitude 0 to 360

  11. INDIA │ 18-20 august2010 virtual techdays DEMO: Various Datatypes DEMO: World Map

  12. INDIA │ 18-20 august2010 virtual techdays • Requires bounding box • Only one grid SQL Server 2008 Indexing Story Geographic Index Planar Index • No bounding box • Two top-level projection grids

  13. INDIA │ 18-20 august2010 virtual techdays • 4 levels • Customizable grid granularity • Three Grid Densities Per Level - Low, Medium, High • Customizable max number of cells per object SQL Server 2008 Indexing Story Multi-Level Grid

  14. INDIA │ 18-20 august2010 virtual techdays MAIN SLIDE TITLE Sub Slide Title /4/2/3/1 / (“cell 0”)

  15. INDIA │ 18-20 august2010 virtual techdays Tessellation Process

  16. INDIA │ 18-20 august2010 virtual techdays • Create index example GEOMETRY: CREATE SPATIAL INDEX sixd ON spatial_table(geom_column) WITH ( BOUNDING_BOX = (0, 0, 500, 500), GRIDS = (LOW, LOW, MEDIUM, HIGH), CELLS_PER_OBJECT = 20) • Create index example GEOGRAPHY: CREATE SPATIAL INDEX sixd ON spatial_table(geogr_column) WITH ( GRIDS = (LOW, LOW, MEDIUM, HIGH), CELLS_PER_OBJECT = 20) • Use ALTER and DROP INDEX for maintenance. Index Creation and Maintenance

  17. INDIA │ 18-20 august2010 virtual techdays DEMO: Geometry Index DEMO: Index Analysis

  18. INDIA │ 18-20 august2010 virtual techdays • FROM T WITH (INDEX (<Spatial_idxname>)) • Spatial index is treated the same way a non-clustered index is • the order of the hint is reflected in the order of the indexes in the plan • multiple index hints are concatenated • no duplicates are allowed • The following restrictions exist: • The spatial index must be either first in the first index hint or last in the last index hint for a given table. • Only one spatial index can be specified in any index hint for a given table. Index Hints

  19. INDIA │ 18-20 august2010 virtual techdays • Make sure you are running SQL Server 2008 SP1 • Check query plan for use of index • Make sure it is a supported operation • Hint the index (and/or a different join type) • Do not use a spatial index when there is a highly selective non-spatial predicate Checklist for Performance

  20. INDIA │ 18-20 august2010 virtual techdays • Michael Rys • http://sqlblog.com/blogs/michael_rys/ • Pinal Dave • http://blog.sqlauthority.com RESOURCES

  21. THANKS│18-20 august2010 virtual techdays pdave@solidq.com│ http://blog.sqlauthority.com

More Related