1 / 0

Who Needs Google Maps? (when you’ve got SQL Server)

Who Needs Google Maps? (when you’ve got SQL Server). Alastair Aitchison. Google Maps. Snazzy Logo. Search for Location. Map Display. Route between locations. Bing Maps. Snazzy Logo. Search for Location. Map Display. Route between locations. Yahoo Maps. Snazzy Logo.

onofre
Download Presentation

Who Needs Google Maps? (when you’ve got SQL Server)

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. Who Needs Google Maps?(when you’ve got SQL Server)

    Alastair Aitchison
  2. Google Maps Snazzy Logo Search for Location Map Display Route between locations
  3. Bing Maps Snazzy Logo Search for Location Map Display Route between locations
  4. Yahoo Maps Snazzy Logo Route between locations Map Display Search for Location
  5. MapQuest Snazzy Logo Search for Location Route between locations Map Display
  6. Requirements Checklist Snazzy Logo Pannable, Zoomable Map Display Search for a Location Calculate Route Between Destinations This will involve: Importing data from shapefiles, integrating with SQLCLR, using SSRS, passing spatial data to/from stored procedures, and more!
  7. Requirement #1 : Snazzy Logo Take a brand name and add on “maps” Therefore…
  8. Requirements Checklist Snazzy Logo a Pannable, Zoomable Map Display Search for a Location Calculate Route Between Destinations
  9. Ordnance Survey Open Data Free, “lite” OS datasets Different products, e.g: VectorMap(features) Gazetteer (placenames) CodePoint Open (p’codes) Download or DVD ESRI Shapefile format
  10. Loading Shapefiles to SQL Server SQL Server does not support shapefiles Use 3rd party tools Commercial: Safe FME, Manifold Free: OGR2OGR, Shape2SQL Check .prj file to determine geography/geometry and correct SRID
  11. Shape2SQL Demo
  12. Requirement #2 : Map Display What visualisation options does SQL Server provide? SSMS Spatial Results tab (SQL 2008/R2) SSRS Map Component (SQL 2008 R2 only) No new options in SQL Denali
  13. SSMS Spatial Results Tab Demo
  14. SSRS Map Component Demo
  15. Requirement #2 : Map Display SSMS Spatial Results tab + Available after SELECTing geometry/geography data + Pannable, zoomable, labelled - Max 5,000 features - Only for use in SSMS - not embeddable / exportable SSRS Map Component (SQL 2008 R2) + Good for analysis – drilldown by clicking shapes etc. + Can be exported as static image Slow and static. Not “slippy” interface
  16. A Silverlight Map Display App Silverlight provides UI and Graphics classes SqlServer.Types.dll not Silverlight compatible Use intermediary web service OS Eastings / Northings -> Screen (x,y)
  17. SProc to Retrieve OS Tiles CREATEPROCEDURE [dbo].[GetSettlementDataForTile] @TileBoundsgeometry AS BEGIN DECLARE@TileHeightfloat= (@TileBounds.STPointN(3).STY - @TileBounds.STPointN(2).STY); DECLARE @Res float= @TileHeight/256; SELECTgeom27700.STIntersection(@TileBounds).Reduce(@Res) FROMSettlement_AreaWITH(index(geom27700_sidx)) WHEREgeom27700.STIntersects(@TileBounds)= 1 END
  18. Silverlight Slippy Map Demo
  19. Requirements Checklist Snazzy Logo a Pannable, Zoomable Map Display a Search for a Location Calculate Route Between Destinations
  20. Geocoding (Location Searching) Not really a Spatial Operation Address text parsing / search SELECT * FROM OSLocator WHERE ROADNAME = @RoadName Fuzzy matching with full-text search RegEx matching for Postcode Supplied address is freetext user input so be cautious of SQL injection attacks etc.
  21. Geocoding Demo
  22. Requirements Checklist Snazzy Logo a Pannable, Zoomable Map Display a Search for a Location a Calculate Route Between Destinations
  23. Routing Not sufficient to have a table of LineStrings Network topology – how are roads connected? Graph theory: Node (Point) Edge (LineString)
  24. Brute Force Routing with T-SQL Recursive CTE Anchor member selects LineString edges that begin at chosen start node Recursive member joins from STEndPoint() of last edge to STStartPoint() of next edge Create geometry of route already travelled to avoid infinite loops Expand outwards until end node found
  25. Smart Routing with SQLCLR A* algorithm Heuristics prioritise edges more likely to lie on least cost route to goal “Least cost” does not have to mean “shortest” Additional properties of each node: g: cost of route already travelled to reach this node h: estimated remaining cost from this node to goal f: g + h
  26. A* Algorithm in Operation A B C Start D Goal E
  27. A* Algorithm in Operation A B C 8.2 2.8 Start D Goal 3 7 10.2 2 E
  28. A* Algorithm in Operation A B C 8.2 2.8 Start D Goal X 10.2 2 E
  29. A* Algorithm in Operation A B C 1 8.5 2.8 Start D Goal X 10.2 2 E
  30. A* Algorithm in Operation A B C 1 8.5 2.8 Start D Goal X 10.2 2 Goal reached! Route cost: 12.2 E
  31. Requirements Checklist Snazzy Logo a Pannable, Zoomable Map Display a Search for a Location a Calculate Route Between Destinations a Put it all together and what have you got?
  32. SQLBitMaps in action…
  33. Want More? : @alastaira #sqlbits8 : http://alastaira.wordpress.com MSDN Spatial Forum: http://social.msdn.microsoft.com/Forums/en-GB/sqlspatial/threads
More Related