Can I restrict a SQL Server spatial field to hold values of specified spatial types and SRIDs?

Question

I am trying to understand geography data types so I can integrate my database more closely with GIS processes. From my reading, it appears that a geography field can hold many different types of objects.

If this is the case, how do I prevent someone from loading, say, polygons into a field that I want to use to retain points, or loading points in the incorrect coordinate system? Will I have to check the data type and projection each time I write a query so I don't get erroneous results?

Answer 1

The usual way to do this would be through a check constraint.

The properties you want to check are .STSrid and .STGeometryType()

So something like

ALTER TABLE Test 
 ADD CONSTRAINT chk_shape_gtype_srid 
 CHECK (
 shape.STSrid = 4326 AND 
 shape.STGeometryType() in ('POLYGON','MULTIPOLYGON')
 );

• haresfur: Thank you. I had trouble adding the constraint using SQL Server Management Studio, so used SQL based on your example. The syntax for the expression that then shows up in the SQL Server Management Studio constraint dialog (for my column called 'GeogLoc') is ([GeogLoc].[STSrid]=(4283) AND [GeogLoc].[STGeometryType]()='POINT')