| BlueSpray - Help © SchoonerTurtles, Inc. 2012-2015
Introduction
Contents
Getting Started
Download
Navigating
Tutorials
Scripting
Advanced
About
> | # 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