Tuesday, May 22, 2007

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

No comments:

Post a Comment