1

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?

2

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')
    );
  • 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') – haresfur May 27 at 3:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.