SQL Server Spatial SQL
geodatabases, sql server 2008
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
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
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)
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:
Alastair Aitchison’s book Beginning Spatial with SQL Server 2008
Feel free to post more links below.