geoMusings

Spatial References in SQL Server 2008, Part 2

With some guidance from Paul Ramsey, I implemented a check constraint on my sample table to allow only a single SRID in a table. As Paul indicated in his comment on my previous post, PostGIS does this for you. SQL 2008 doesn’t yet but I hope it does. The syntax for my table was:

--only allow WGS84
ALTER TABLE SpatialTable
  ADD CONSTRAINT enforce_srid_geometry CHECK (GeomCol1.STSrid = 4326);

After doing that, the following SQL will properly fail:

UPDATE SpatialTable
	SET GeomCol1.STSrid = 4127;

Thanks to Paul for chiming in!

Posted

in

, , , ,

by

Bill Dollins

Tags:

Comments

2 responses to “Spatial References in SQL Server 2008, Part 2”

  1. Morten Avatar
    Morten

    Thanks! That’s a great trick.
    I agree with you that this should be a standard constraint, and you should make sure you let the SQL Server Spatial know! At least I will always add this to my tables and tools to avoid any issues.

  2. Bill Dollins Avatar
    Bill Dollins

    Morten,

    I’m glad you found it useful and I’ll definitely send the feedback to MS. Thanks for stopping by!