Which one do you recommend when storying features in SQL Server 2008, geometry or geography.
Right now, I'm storing the features in a geometry field using SRID 4326. Should I use geography instead?
My features represent corn fields or soybean fields. Does it even matter when I use SRID 4326?
Peter,
In your case, I still suggest you use the Geography instead of Geometry. The Geometry is only used in the case of planar system. You could get more information from following links:
mssqltips.com/tip.asp?tip=1847
msdn.microsoft.com/en-us/library/bb964707.aspx
Please feel free to let me know any more questions.
Thanks.
Yale
I recreated my table using the geography field instead but I get this error when I try to get the bounding box.
I do call sql2008Layer.MakeAllGeometriesValid(); before hand. Could that create problems in my geography fields?
It says to use MakeValid to correct it but I don’t see that in the ThinkGeo API. This must be a command that I have to run directly again the SQL Server?
code: sql2008Layer.FeatureSource.GetBoundingBox();
error:
A .NET Framework error occurred during execution of user-defined routine or aggregate “geometry”:
System.ArgumentException: 24144: This operation cannot be completed because the instance is not valid. Use MakeValid to convert the instance to a valid instance. Note that MakeValid may cause the points of a geometry instance to shift slightly.
System.ArgumentException:
at Microsoft.SqlServer.Types.SqlGeometry.ThrowIfInvalid()
at Microsoft.SqlServer.Types.SqlGeometry.STEnvelope()
.
I was able to solve my problem after some time with Google. Here is how I solved the problem.
1) I uploaded my features from a shape file to a SQL Server table using the Shape2SQLServer2008 tool. I kept the spatial field a geometry but set the SRID to 4326.
2) Then I created a geography field in the SQL Server table that was created.
3) Then I ran this update statement to translate my geometry feature into a geography feature with a correct ring orientation.
UPDATE CurrentField_TEST
SET CFGeom = geography::STGeomFromWKB(geom.MakeValid().STUnion(geom.MakeValid().STStartPoint() ).MakeValid().STAsBinary(), geom.STSrid)
4) Then I removed the geometry field from the table and loaded the table as a layer in ThinkGeo.
Everything came up as expected and my bounding box method called was correct.
After reading more about shape files and OCG standards, it makes sense why I have to do this. They say shape files don’t really follow ring orientation standards like SQL Server 2008 and Oracle. It seems geometry accepts almost any shape and will correct the orientation using certain methods but geography is picky and expects a correct shape before inserting it.
My plan is to use a geometry field as a staging field before loading it official into a geography field when the feature is coming from a shape file.
Peter,
Thanks for your post and sharing in using SQLServer.
In my experience, I use the Shape2SQLServer2008 tool to upload the shape files into SQLServer2008 directly with Geography selected, and it works fine. Probably my testing sample data are too simple.
Any all, glad you got it. Any more questions just feel free to let me know.
Thanks.
Yale