What's the best way to store State Plane data in SQL 2008R2?

Question

I have a client with data that is currently in a State Plane coordinate system. They are looking to store this data in SQL Server 2008R2.

SQL does not "support" the SRID for the State Plane in question.

I've seen some people suggesting using geometry instead of geography for storing State Plane data.

I also know that I could convert the State Plane data over to WGS84 if necessary.

What I'm wondering is what's the "best" way to store State Plane data in SQL 2008R2?

Answer 1

I have heard that Microsoft plans to start supported projected coordinate reference systems, but I don't know what release that will be. SQL Server 2008 supports geometry and geography data types. The Geography type supports geographic coordinate reference systems only. You can store projected data as Geometry but the Geometry type does not support any SRIDs. All projected data is the same!

If the client's data is always going to use the same projected CRS, that would be ideal, as the missing metadata information wouldn't be a problem.

According to Spatial Reference Identifiers (SRIDs), it implies that you can tag geometries with an SRID, but they're not used.

Answer 2

My current projects are using 2008 not R2, but we are storing our data in SP, since that is our standard. The key is to set your DB to geometry (spatial but not expecting to be a globe) and then using your loading app to define the appropriate projection.

In my case we use FME to load the data, and it works very smooth, we overlay it with our maps from ESRI from the DB and falls right in place as expected; even using project-on-the-fly.

Not too much more to explain; as mentioned above, the 2012 option to define a project seperate of the actual geometry loaded is a good option once it ships and is shown to be stable.