SQL Server Spatial SQL¶

date:: 2009-12-18 11:05
author:: admin
category:: geodatabases, sql server 2008
slug:: sql-server-spatial-sql
status:: published

I’ve been working more and more with the SQL Server 2008 spatial queries. For standard queries I now rarely use the|sql\_server\_2008| graphic design tools available in SQL Server Management Studio - SQL scripts are far quicker, and easier to reuse. For spatial queries I don’t think there even is a GUI. Anyway the following SQL snippets may be of use to someone. I have only been using the GEOMETRY type, so these may not be relevant to the GEOGRAPHY type, for a distinction see this Microsoft document.

Geometry and Projections¶

Make valid geometry - this can often fix ‘invisible’ datasets in desktop GIS packages such as CadCorp.

UPDATE MyTable SET GeomFieldName = GeomFieldName.MakeValid()

Set the spatial reference for all features in a table (STSrid), this is the equivalent of using a .prj file with a shapefile. You could set a different reference for individual features, although why you’d want to do that I’ve no idea. Note that this does not reproject your data, but it is used to tell client applications what projection the data is in. In MapServer I found the SRID has to be set correctly for WMS layers to appear in OpenLayers. Conversely in CadCorp’s GeognoSISI had to set these values to 0 for data to display correctly. Change the code according to the projection of your data.

UPDATE MyTable SET GeomFieldName.STSrid = 3785 --change this value to your EPSG code

Feature Extents¶

The next SQL code gets the extents of each feature, and saves them into new fields. These fields are “calculated” so if the features are modified then the extents will be updated, yet they are also saved to disk rather than calculated dynamically for each query so they are as fast to display as other fields.

ALTER TABLE MyTable
ADD MinX AS (CONVERT(int,GeomFieldName.STEnvelope().STPointN((1)).STX,0)) PERSISTED
ALTER TABLE MyTable
ADD MinY AS (CONVERT(int,GeomFieldName.STEnvelope().STPointN((1)).STY,0)) PERSISTED
ALTER TABLE MyTable
ADD MaxX AS (CONVERT(int,GeomFieldName.STEnvelope().STPointN((3)).STX,0)) PERSISTED
ALTER TABLE MyTable
ADD MaxY AS (CONVERT(int,GeomFieldName.STEnvelope().STPointN((3)).STY,0)) PERSISTED

Then to get the full extent of your data you can use (although there are alternatives):

SELECT Min(MinX)AS MinX, Min(MinY) AS MinY,
MAX(MaxX) AS MaxX, MAX(MaxY) AS MaxY
FROM MyTable

Spatial Indexes¶

As mentioned in a previous post spatial indexes can greatly improve display and query speeds. To create an index in SQL use the following syntax. You should set the bounding box to the extent of your data, which can be calculated using the query above (and included in the SQL script).

CREATE SPATIAL INDEX MyIndexName ON MyTable(GeomFieldName) USING  GEOMETRY_GRID
WITH (
BOUNDING_BOX =(-1493907.5664457313, 6128509.51667404, -578861.3521250226, 7703103.135644257),
GRIDS =(LEVEL_1 = MEDIUM,LEVEL_2 = MEDIUM,LEVEL_3 = MEDIUM,LEVEL_4 = MEDIUM),
CELLS_PER_OBJECT = 16, PAD_INDEX  = OFF, SORT_IN_TEMPDB = OFF,
DROP_EXISTING = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)

Spatial Queries¶

Sometimes SQL Server doesn’t seem to use spatial indexes automatically. You can force a spatial query to use an index using the WITH(INDEX(IndexName) statement similar to below which finds all geometry in a table at a point specified by an X and Y (in a Web Mercator projection - EPSG:3785)

SELECT *
FROM MyTable
WITH (INDEX (MyIndexName))
WHERE (geometry::Point(@x,@y,3785).STWithin(MyGeomField) = 1)

I have found that sometimes it is quicker when querying by points not to use a spatial index, so it is worth experimenting.

To find all features within a user defined bounding box you can create a stored procedure similar to the following:

CREATE PROCEDURE [SearchByBounds]
 @minx nvarchar(max),
 @miny nvarchar(max),
 @maxx nvarchar(max),
 @maxy nvarchar(max)
AS
BEGIN
 SET NOCOUNT ON;
 declare @g as geometry;
 declare @wkt as nvarchar(max);
 set @wkt = 'POLYGON ((' + @minx + ' ' + @miny + ',' +
 @minx + ' ' + @maxy + ',' + @maxx + ' ' + @maxy + ',' + @maxx +
 ' ' + @miny + ',' + @minx + ' ' + @miny + '))';
 set @g = geometry::STGeomFromText(@wkt, 3785);
 --print @wkt
 SELECT *
 FROM MyTable WITH (INDEX (MySpatialIndex))
 WHERE GeomFieldName.STIntersects(@g) = 1
END

I haven’t found too many tutorials or blogs on the spatial features in SQL Server 2008. The best resources I’ve found are:

Jason Follas’s Introduction to SQL Server Spatial
Issac’s posts on Spatial Indexing
Bob Beauchemin’s SQL Server Spatial Blog
Alastair Aitchison’s book Beginning Spatial with SQL Server 2008
MSDN’s SQL Server Spatial Forums

Feel free to post more links below.

orphan:

Comments¶

1. geographika » A 10 Minute Intro on using BitBucket with Windows **

[…] There are already a couple of official quick start guides to using Mercurial’s Window’s client program TortoiseHG, but below are the bare details of how to use it with a new account on the BitBucket service. […]

Add Comment