Using SQL Server Spatial

Introduction

SQL Server 2008 adds a true spatial engine to SQL Server.  The engine follows the OGC Simple Feature standard for object definitions and functions. There is addtional information on the Microsoft SQL Server 2008 web site.

Features Stored as Well Known Binary Format

As mentioned above, spatial data is typically stored as binary data. While SQL Server uses it's own internal format for vector data we can query data in the Well Known Binary (WKB) format.This allows data to be interchanged with other packages within BlueSpray. You will tyipcally use layers to move data around in BlueSpray and this works fine but if you want to improve performance or redcue the amount of memory required, you can work on individual geometry objects using just Geometries. The Java Topology Suite provides functions for working with these geometries.

Features as Well Known Text Format

The queries below use the Well Known Text (WKT) format to describe geometric objects. You will probably want to review the information on these Simple Features before continuning.

Performing Spatial Queries

Selecting Geometries that Overlap with a Bounding Box

GeometryString  =“POLYGON((X1 Y1,X2 Y2, X1 Y1))”

SELECT TableName.RefX
   FROM TableName with(index(TBL_Test_Geometry)) 
 WHERE TestGeometry.STWithin(geometry::STGeomFromText('"  GeometryString "', 0))=1";

• STGeomFromText ( 'geometry_tagged_text' , SRID )

Geometry Functions

This information is from Microsoft’s Geometry Reference.  See the bottom of this page for the ‘geometry Data Type Method Reference’.

• Double             Geometry.STArea()
• Bytes[]             Geometry.STAsBinary()
• String               Geometry.STAsText()
• MultiPoint         Geometry.STBoundary()
• Geometry         Geometry.STBuffer(distance)
• Point                Geometry.STCentroid()
• Bit                    Geometry.STContains(Geometry)
• Geometry         Geometry.STConvexHull()
• Bit                    Geometry.STCrosses(Geometry) – true if geometries cross
• Geometry         Geometry.STDifference(Geometry) – subtract one from the other
• Int                    Geometry.STDimension – max dimension (2 for XY, 3 for XYZ, 4 for XYZM)
• Bit                    Geometry.STDisjoint(Geometry) – 1 if the geometries do not overlap
• Float                Geometry.STDistance(Geometry) – shortest distance
• Geometry         Geometry.STEndpoint() – end point of geometry
• Geometry         Geometry.STEnvelope() – minimum axis-bounding rectangle
• Bit                    Geometry.STEquals(Geometry) – true if identical points
• Geometry         Geometry.STExteriorRing()  - exterior polygon
• Geometry         Geometry.STGeometryN(int) – geometries by index
• nvarchar(4000) Geometry.STGeometryType() – OGC type name
• Geometry         Geometry.STInteriorRingN(int) – interial ring by index
• Geometry         Geometry.STIntersection(Geometry)
• Bit                    Geometry.STIntersects(Geometry)
• Bit                    Geometry.STIsClosed() – end points match
• Bit                    Geometry.STIsEmpty() – no points
• Bit                    Geometry.STIsRing() – is simple and closed
• Bit                    Geometry.STIsSimple() – figures do not overlap themselves or with each other
• Bit                    Geometry.STIsValid() – well formed (interior polygons inside exterior polygons)
• Float                Geometry.STLength()
• Int                    Geometry.STNumGeometries() – number of geometries
• Int                    Geometry.STNumInteriorRing()
• Int                    Geometry.STNumPoints()
• Bit                    Geometry.STOverlaps(Geometry)
• Int                    Geometry.STPointN(int) – returns point by index
• Point                Geometry.STPointOnSurface() -  returns arbitrary point in geometry
• Bit                    Geometry.STRelate( other_geometry, intersection_pattern_matrix ) – returns 1 if geometries related as specified by matrix
• Int                    Geometry.STSrid()
• Point                Geometry.STStartPoint() – first point
• Geometry         Geometry.STSymDifference(Geometry) – returns points that are not in either geometry
• Bit                    Geometry.STTouches(Geometry) – 1 if they touch but do not intersect
• Geometry         Geometry.STUnion(Geometry) –
• Bit                    Geometry.STWithin(Geometry) – 1 if
• Float                Geometry.STX – X coordinate of a point (no parens)
• Float                Geometry.STY – X coordinate of a point (no parens)

The following use the spatial index:

• geometry1 . STContains ( geometry2 ) = 1
• geometry1 . STDistance ( geometry2 ) < number
• geometry1 . STDistance ( geometry2 ) <= number
• geometry1 . STEquals ( geometry2 ) = 1
• geometry1 . STIntersects ( geometry2 ) = 1
• geometry1. STOverlaps (geometry2) = 1
• geometry1 . STTouches ( geometry2 ) = 1
• geometry1 . STWithin ( geometry2 ) = 1

Spatial Reference

Spatial ReferenceID = projection and datum, uses EPSG

Spatial Indexing