Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

With the release of SQL Server 2008 Microsoft added geometry data types. So geometries can now in an ESRI enviroment be either be stored using SQL-Server datatypes or ArcSDE datatypes.

A colleague of mine asked today when should we choose one product over the other?

We already have business databases in SQL-Server and in ArcSDE, if we need to create a new database, what options do we have? Are there any advantages/disadvantages to storing data in one way or other?

Refrased, Old Question is here:

"With the release of SQL Server 2008 Microsoft added geometry data types. So geometries can now either be stored in SQL-Server or ArcSDE. A colleague of mine asked today when should we choose one product over the other? We already have business databases in SQL-Server and in ArcSDE, if we need to store a new polyline, is there any reason to promote one place over the other? "

share|improve this question
2  
I think the question should be re-worded to ask for advantages/disadvantages to 3 different storage options: SDEBINARY, Geography or Geometry. help.arcgis.com/en/arcgisdesktop/10.0/help/index.html#/… –  Kirk Kuykendall Oct 26 '10 at 18:27
    
I think your right, I was a bit too focused on how our installations looks like, instead of of what limitations do I get if I choose to use one way or the other. –  MathiasWestin Oct 26 '10 at 18:58
1  
Early on (9.3 timeframe), we were advised by ESRI to not use the geometry datatype for feature classes that participated in topologies or networks (we were experiencing some severe performance issues). Maybe someone can comment as to whether this is still an issue? –  Jay Cummins Oct 26 '10 at 19:01
add comment

3 Answers

So geometries can now either be stored in SQL-Server or ArcSDE.

The two are not mutually exclusive. ArcSDE always requires an RDBMS to store data so you're not storing your data "in ArcSDE" or "in SQL Server." If you're using SQL Server, your data will always be stored in tables in a database in a SQL Server instance.

How your data is stored is up to you. ArcSDE works with data stored as Microsoft's geometry (and geography) types. This is the most versatile option as you get access to your geometries with raw SQL but you can also used advanced functionality only available through Esri's Geodatabase. The alternative to using Microsoft's spatial types is to use Esri's SDE binary type which is not readily usable with raw SQL. Check out this help topic for more info: Feature classes in a geodatabase in SQL Server

share|improve this answer
add comment

If you store your data with SQL Server geometry/geography datatypes, you can do some pretty slick stuff, such as essentially bypassing the ESRI stack when updating your featureclasses. You can use stored procedures (or ETLs) and triggers to update your geometry/geography anytime, with services or user connected. Here's an example of a trigger that gets fired after insert (via a ETL job) that sets the lat/long columns and adds a datetime stamp to the record:

ALTER TRIGGER [dbo].[tgr_tpw_insert_geog_shl]
  ON [dbo].[ThirdPartyWellsSurface]
  AFTER INSERT
AS 
BEGIN
SET NOCOUNT ON;
UPDATE [dbo].[ThirdPartyWellsSurface]
SET [dbo].[ThirdPartyWellsSurface].[Shape] = 'POINT(' + CAST([dbo].  [ThirdPartyWellsSurface].[SH_LONGITUDE] AS VARCHAR(15)) + ' ' + CAST([dbo].[ThirdPartyWellsSurface].[SH_LATITUDE] AS VARCHAR(15)) + ')',
    [dbo].[ThirdPartyWellsSurface].[DATE_ADDED] = getdate()
FROM [dbo].[ThirdPartyWellsSurface], inserted
WHERE [dbo].[ThirdPartyWellsSurface].[API] = inserted.[API]
END

Basically near real time updates to SDE that are transparent to your users. They refresh the map or pan, and changes just show up.

share|improve this answer
    
Yes that's slick- In the actual case I discussed with my colleage the users said they only needed to store the data, so that it could be fetched later on, so a BLOB in the database would do. :) But to make it future-proof it must be stored as geometry. –  MathiasWestin Oct 26 '10 at 19:22
add comment

I wrote a windy response to a similar question ages ago:

http://gis.stackexchange.com/questions/132/what-is-the-difference-between-esris-sde-and-geospatial-dbs/143#143

One deciding factor could be whether or not you want to write SQL statements directly in the database (e.g. stored procedures, triggers, etc) that can manipulate or query geometry.

share|improve this answer
    
that's a great response, also @swingley response focusing on the versioning and mulit user editing capabilities in ArcSDE. –  MathiasWestin Oct 26 '10 at 18:50
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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