Who needs google maps when you ve got sql server
Download
1 / 33

Who Needs Google Maps? (when you’ve got SQL Server) - PowerPoint PPT Presentation


  • 74 Views
  • Uploaded on

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.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about ' Who Needs Google Maps? (when you’ve got SQL Server)' - sulwyn


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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
Who needs google maps when you ve got sql server

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

Alastair Aitchison


Google maps
Google Maps

Snazzy Logo

Search for Location

Map Display

Route between locations


Bing maps
Bing Maps

Snazzy Logo

Search for Location

Map Display

Route between locations


Yahoo maps
Yahoo Maps

Snazzy Logo

Route between locations

Map Display

Search for Location


Mapquest
MapQuest

Snazzy Logo

Search for Location

Route between locations

Map Display


Requirements checklist
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!


Requirement 1 snazzy logo
Requirement #1 : Snazzy Logo

  • Take a brand name and add on “maps”

  • Therefore…


Requirements checklist1
Requirements Checklist

  • Snazzy Logo a

  • Pannable, Zoomable Map Display

  • Search for a Location

  • Calculate Route Between Destinations


Ordnance survey open data
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


Loading shapefiles to sql server
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



Requirement 2 map display
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




Requirement 2 map display1
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


A silverlight map display app
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)


Sproc to retrieve os tiles
SProc to Retrieve OS Tiles

CREATEPROCEDURE [dbo].[GetSettlementDataForTile]

@TileBoundsgeometry

AS BEGIN

[email protected]=

(@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


Silverlight slippy map demo
Silverlight Slippy Map Demo


Requirements checklist2
Requirements Checklist

  • Snazzy Logo a

  • Pannable, Zoomable Map Display a

  • Search for a Location

  • Calculate Route Between Destinations


Geocoding location searching
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.



Requirements checklist3
Requirements Checklist

  • Snazzy Logo a

  • Pannable, Zoomable Map Display a

  • Search for a Location a

  • Calculate Route Between Destinations


Routing
Routing

  • Not sufficient to have a table of LineStrings

  • Network topology – how are roads connected?

  • Graph theory:

    Node (Point)

    Edge (LineString)


Brute force routing with t sql
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


Smart routing with sqlclr
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


A algorithm in operation
A* Algorithm in Operation

A

B

C

Start

D

Goal

E


A algorithm in operation1
A* Algorithm in Operation

A

B

C

8.2

2.8

Start

D

Goal

3

7

10.2

2

E


A algorithm in operation2
A* Algorithm in Operation

A

B

C

8.2

2.8

Start

D

Goal

X

10.2

2

E


A algorithm in operation3
A* Algorithm in Operation

A

B

C

1

8.5

2.8

Start

D

Goal

X

10.2

2

E


A algorithm in operation4
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


Requirements checklist4
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?


Sqlbitmaps in action
SQLBitMaps in action…


Want more
Want More?

: @alastaira #sqlbits8

: http://alastaira.wordpress.com

MSDN Spatial Forum:

http://social.msdn.microsoft.com/Forums/en-GB/sqlspatial/threads


ad