Microsoft Spatial - Hot off the Press!

Microsoft Spatial – Hot off the Press

From the SQL Server User Group Meeting - Thursday 17 May

---

Microsoft Spatial – Hot off the Press!

• SQL Server Spatial & Virtual Earth
• SQL Server User Group
• May 2007
• “Matty” from e-Spatial

Introduction – e-Spatial

· NZ’s premier location intelligence specialists

· True integrators:

§ Mapping engine

• Address specialist
• E-Government specialists

Introduction – Where is the Industry Now?

· Spatial has reached the mainstream

· Spatial databases and data warehouses – all vendors support them, or do they?

· AJAX mash-ups are everywhere – international solutions as well as local, but how to make money from them?

· Location Intelligence – a natural extension to Business Intelligence. SQL Server Spatial was announced at the Seattle BI conference last week

· Image and vector – two types of spatial data

· 2D, 3D (Z) and 4D(M) – use of multi-dimensional data

· Various spatial standards now in use

Spatial in SQL Server – Standards

· Support for several OGC Specifications

§ Geometry data type will comply with Simple Features for SQL

§ Can store Geography Markup Language (GML) in SQL Sever with existing XML data type

§ Can use Well-Known text and Well-Known Binary (WKT, WKB) for data transfer and access

· Other OGC standards

§ Styled Layer Descriptor

§ Web Feature Services (WFS)

§ Web Map Service (WMS)

Spatial in the database

• Spatial objects
  • Points – point(X,Y)
  • (Multi) lines – line (point (x1, y1), point(x2, y2),)
  • (Multi) polygons – polygon (line (…), line (…),)
• Geometry data type
  • Object definition (using points, lines and polygons) and type
  • Geometry collections
  • Project and datum
  • Default style (colour, thickness, pattern)
• Spatial indexing – R-tree or Quad-tree (or Bi-tree)
  • SQL Server Spatial will use a 4-stage grid (like Quad-tree)
• Spatial functions – intersects, touches, centroid (100+)…
• XY Layers

Spatial in the database

• This example map includes:
  • Properties
  • Address
  • Street
  • Postcode
• Database tables would be:
  • Properties (100)
  • Address (80)
  • Street (20)
  • Postcode (1)
• Relational data model:
  • Very complicated
• Relational spatial can be complicated (overlaps) as typically 50+ layers in a map!

Spatial in SQL Server

• SQL example: “In postcode 4102 show all properties with addresses on Van Asch Road”
• No need to define many-to-many relationship tables, nor foreign-key links
• Data maintenance much easier
• Most line-to-point relationships are not spatial (point is not usually on a line), but a spatial relationship can be achieved using a buffer
• SQL example: “Show all properties within 10 metres of Van Asch Road”
• The buffer query applied a 10m buffer to the whole Van Asch Road
• Buffer example – now included the area of the property
• SQL example: “Show all properties and their area in square metres within 10 metres of Van Asch Road”
• The returned properties are also ordered by largest area
• We could continue – for example to do an intersect from the properties to surrounding properties (hazardous substance burning on the road)
• Large fire (200m radius) on a given property
• Let’s find its nearest neighbours

Uploading spatial data – Overview

• Option 1: use data uploaded (“ETL”) facilities from external mapping vendors
  • Safe Software FME – will support uploading data in almost all formats
  • MapInfo EasyLoader – will support uploading MapInfo and some other major spatial formats
  • ESRI ArcGIS – will support uploading ESRI format
  • Freeware and Open Source upload plug-ins
• Option 2: use Microsoft SSIS (Integration Services) to upload with WKT or WKB
  • Lots of methods/functions in SL Spatial to migrate spatial data
• Option 3: create SQL to insert all data

Option 2: Upload spatial data with SSIS

• SQL Server Spatial supports the OGC (Open Geospatial consortium) standards WKT and WKB for import/export
• Also support importing and exporting OGC GML data
• Most spatial data vendors and databases support these standards, allowing upload of their spatial data
• Example: Create SpatialTable and populate it with ID, GeomText (WKT format) and null Geometry:
  • CREATE TABLE SpatialTable (id INT IDENTITY (1,1), Geometry geometry, GeomText as VARCHAR(2000));
• Update the geometry with a spatial function:
  • Update SpatialTable ST set ST.GEOMETRY = ST.GEOMETRY.STGEOMFFROMTEXT(ST.GEOMETRY)

Accessing spatial data:

• SQL Server Spatial does not include a map viewer!
• Fast Access: use a mapping engine with off-the-shelf access
• Slower Access: build your down data provider using WKT or WKB
• Web services: several examples available for building web services interfaces between spatial data base and mapping engine

Image and vector data

• As defined previously, vector data stores all points for spatial objects point, line or polygon)
• Image data is stored as tiles, so only the lower-left and upper-right XY pair need to be recorded
• In NZ we often use metre-based coordinate systems (projects) – NZTM or NZMG

Examples, Blogs and Announcements

• SQL Server Spatial: http://blogs.msdn.com/Isaac/
• Virtual Earth – SilverLight
• http://sqlspatial.redmond.corp.microsoft.com/