New Zealand's new Copyright Law presumes 'Guilt Upon Accusation' and will Cut Off Internet Connections without a trial. is against this unjust law - help us

A Space for All Things .NET Related

Microsoft Spatial - Hot off the Press!

By James Hippolite, in , posted: 22-May-2007 06:51

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:

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


Other related posts:
Teach Yourself Azure in 4 hours
Geek Post Monthly Newsletter Volume 2 Issue 10

Add a comment

Please note: comments that are inappropriate or promotional in nature will be deleted. E-mail addresses are not displayed, but you must enter a valid e-mail address to confirm your comments.

Are you a registered Geekzone user? Login to have the fields below automatically filled in for you and to enable links in comments. If you have (or qualify to have) a Geekzone Blog then your comment will be automatically confirmed and shown in this blog post.

Your name:

Your e-mail:

Your webpage:

JamesHip's profile

James Hippolite
New Zealand

Welcome to my technical blog. 

Here, I attempt to distill the Microsoft Certified Professional Developer knowledge I have accumulated since first qualifying MCP in 1996.  This blog started on 13 September 2007 as an off-shoot from my mixed up personal blog.  But it took a shot in the arm from Scott Hanselman's talk at TechEd New Zealand 08 "32 Ways To Make Your Blog Suck Less".