ArcSDE - use native geometry types for later migration away from SDE?

Question

Background: We have a non-spatial reporting web application with a SQL Server backend. We would like to add a spatial component to this application: serve up some basemaps, serve up points and polygons for view and editing, build reports for selected polygons.

To that end, the plan is to use ArcSDE with SQL Server, and use ArcServer to serve up base layers and vector geometries. (something like WMS/WFS-T)

Problem: We may, at some point, decide we want to move away from expensive proprietary software, and use a FOSS alternative (e.g. GeoServer, or TinyOWS if/when it supports SQL Server). If possible, I'd like to avoid building the application irrevocably around ESRI technology.

My understanding of SDE is that it can either use ESRI specific spatial formats, or spatial formats native to the database system it's installed on. (I presume there is some performance penalty for using the native formats.)

My Question(s): If we were to set up SDE on this SQL server database using native SQL Server spatial formats, would we be able to set up say, GeoServer, to run against the tables that SDE was managing? What would it take to rip SDE out and use something else? (Also, a meta-question: is this a reasonable question to be asking? Is there something important I'm missing?)

Answer 1

A partial answer: I'm not a Geoserver user, but I have built a spatial-driven, non-Esri web app (ASP.NET) using the native geometry stored in an actual SDE featureclass (SQL Server 2008). As long as it isn't versioned (or it is versioned and you accept whatever lag exists for your edits to be moved to base), it should be no problem. My app was spatially driven, contained no map, but the spatial functions where heavily used in the SELECT statements and stored procedures.

I have found that ArcGIS can be buggy with native and/or Esri ST_GEOMETRY implementations and often will result in vendor finger pointing. See this question: How to allow more polyline vertices for a ST_GEOMETRY in Oracle/SDE? (short version: Oracle: "That's the way it is. Esri needs to deal with it." Esri:"That's a bug--wait until Oracle fixes it.")

It's not a reason to not use native geometry, but something to be aware of in case you are the first guy to stumble upon a bug (I also suggest having SDE_BINARY versions of your datasets in a test/dev env so you can confirm or rule out strange behaviour in both storage data types).

Answer 2

You mention editing on this planned spatial db. That is where you don't provide enough data for a complete answer.
If there are any esri peculiarities (datatypes) about the edited tables then you are planning for some rough seas.
It will be enough of a challenge to setup the spatial tables (3rd party), work around users permissions, viewer permissions, locks, no versioning supported, editing that non-versioned data in arcgis, ... There are many other bridges, hurdles, and even chasms to get over.
So the more simple the model the less problems for accomplishment.
That being said the short answer is yes.
One of the problems with this road is that as soon as the decision to travel the road is made.
There are many many vehicles to get down the road.
Making the decsion for the right vehicle is often accomplished through months if not years of sandbox playing.

EDIT
If you edit polygons and you don't use some pretty sophisticated techniques and tools you would want topolgy rules in place to help with quality control (maybe another software stack like radius). If you split a polygon you want both to get the same attributes, or not, when a vertex is moved is it to snap to another, if not there is a sliver. so on and so forth. if you don't use esri you are on your own (or the spatial db) to apply some basic gis functionality. you just have to know what you want and how to do it (or that it can be done).

Answer 3

We store sde data in the SQL server 2008 native geometry type and I would not do it another way. This allows for maximum flexibility with spatial querying in the database. Geoserver just made significant improvements to the support for SQL server data types but we have not utilized this so i can not provide much input in that regard. A thing to note is that building an sde database with the standard post install builds a table structure for sde. It would be wise to look into that structure. If I were removing sde in favor of Foss solutions I would not want all the unneeded sde system tables. Therefore I would rebuild a clean DB and move the tables to the new environment. This would be an opportunity to try postgis in place of SQL server as geoserver has a development history with postgis. I wouldn't consider the transition from sde to be difficult for a simple db and simple geoserver setup as long as geoserver works well with SQL server.

Hope that helps!